passing data to modal dialog box in VBA

[rettigcd]

Hello,

I need to create a custome form that operates similar to the MsgBox()
and InputBox() functions. I can't figure out how to pass data to a
MODAL dialog box (form).

I've tried 3 approaches:

Try #1:

Dim f as MySpecial Form
f = new MySpecialForm
f.SetInputParam eters( ... )
f.Open()

dim result as int
result = f.GetResults()

But of course this didn't work because Forms don't seem to have an
Open() command.

Try #2
Dim m_Input as string
Dim m_Output as integer
static public function Open( intput as string ) as int
m_Input = input
DoCmd.Open "MyForm"
Open = m_Output
end function

but the "global" variables aren't letting me pass values into/outof the
class.

Try #3

DoCmd.Open "MyForm"
Forms[MyForm].Set Parameters

This attempt was just wrong.

How can I pass variables into a MODAL dialog box?
OR
How can I create a form, call some member functions, open it, close it,
call more member functions, then destroy the object?

Very frustrating...

Dean Rettig

[jv]

If the form is not open with the WindowMode=acDi alog then you should be
able to do Forms!MyForm!Co ntrolName=xxxx. Granted that you'll have a
create a form control to hold the data that you are passing in, but
that control doesn't have to be visible.

You can also simply reference the data that is on the main form from
the popup form (i.e. variableName=Fo rms!frmMainForm !ControlName)

Another way is to pass in a string using the OpenArgs parameter of the
Docmd.OpenForm method. Then you'll be able to access it using
Me.OpenArgs from the popup form. The limitation here is that you can
only pass in one string. However, you can concatenate your data with
delimiters and the parse it out from the popup form.

I hope others can come up with better solutions. Good luck.

[Justin Hoffman]

<re******@bigfo ot.com> wrote in message
news:11******** **************@ g44g2000cwa.goo glegroups.com.. .

Hello,

I need to create a custome form that operates similar to the MsgBox()
and InputBox() functions. I can't figure out how to pass data to a
MODAL dialog box (form).

I've tried 3 approaches:

Try #1:

Dim f as MySpecial Form
f = new MySpecialForm
f.SetInputParam eters( ... )
f.Open()

dim result as int
result = f.GetResults()

But of course this didn't work because Forms don't seem to have an
Open() command.

Try #2
Dim m_Input as string
Dim m_Output as integer
static public function Open( intput as string ) as int
m_Input = input
DoCmd.Open "MyForm"
Open = m_Output
end function

but the "global" variables aren't letting me pass values into/outof the
class.

Try #3

DoCmd.Open "MyForm"
Forms[MyForm].Set Parameters

This attempt was just wrong.

How can I pass variables into a MODAL dialog box?
OR
How can I create a form, call some member functions, open it, close it,
call more member functions, then destroy the object?

Very frustrating...

Dean Rettig

Like the reply from jv, this idea uses the OpenArgs idea, but gives some
code for parsing out the parameters. In this example, we create a function
similar to InputBox() except that ours uses a custom form. The form has a
textbox 'txtResponse', a label 'lblPrompt' and two command buttons
'cmdCancel and 'cmdOK'. You also need a separate module.
To test it out, try
debug.print MyInputBox("Ple ase enter your surname","Surna me
Missing","Smith ")
' *************** *************** *************** *********
' Code for the form
Option Compare Database
Option Explicit

Private Sub cmdCancel_Click ()

On Error GoTo Err_Handler

DoCmd.Close

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description , vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

Private Sub cmdOK_Click()

On Error GoTo Err_Handler

Me.Visible = False

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description , vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

Private Sub Form_Open(Cance l As Integer)

On Error GoTo Err_Handler

Dim strOpenArgs As String
Dim astrValues() As String
Dim strValue As String
Dim lngPosition As Long
Dim lngCount As Long

Cancel = True

strOpenArgs = Nz(Me.OpenArgs, "")

If Len(strOpenArgs ) > 0 Then

astrValues = Split(strOpenAr gs, "|")

For lngCount = 0 To UBound(astrValu es())

strValue = astrValues(lngC ount)

lngPosition = InStr(strValue, "=")

If lngPosition > 1 Then

Select Case UCase(Left$(str Value, lngPosition))

Case "PROMPT="
Me.lblPrompt.Ca ption = Trim(Mid$(strVa lue, lngPosition + 1))

Case "TITLE="
Me.Caption = Trim(Mid$(strVa lue, lngPosition + 1))

Case "DEFAULT="
Me.txtResponse = Trim(Mid$(strVa lue, lngPosition + 1))

End Select

End If

Next lngCount

End If

If lngCount = 3 Then
' The right number of parameters have been passed
Cancel = False
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description , vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub
' *************** *************** *************** *********
' *************** *************** *************** *********
' Code in a separate module
Option Compare Database
Option Explicit

Public Function MyInputBox(strP rompt As String, _
strTitle As String, _
strDefault As String) As String

On Error GoTo Err_Handler

Dim strReturn As String
Dim strOpenArgs As String

strOpenArgs = "Prompt=" & strPrompt & "|" & _
"Title=" & strTitle & "|" & _
"Default=" & strDefault

DoCmd.OpenForm "frmMyInputBox" , , , , , acDialog, strOpenArgs

If IsFormLoaded("f rmMyInputBox") Then
strReturn = Nz(Forms!frmMyI nputBox.txtResp onse, "")
DoCmd.Close acForm, "frmMyInput Box"
End If

MyInputBox = strReturn

Exit_Handler:
Exit Function

Err_Handler:
MsgBox Err.Description , vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Function

Private Function IsFormLoaded(st rName As String)
IsFormLoaded = (SysCmd(acSysCm dGetObjectState , acForm, strName) <> 0)
End Function
' *************** *************** *************** *********

[rettigcd]

Justin,

I see what you are trying to do. I tried something similar. Did you
try it? Does it work for you?

I tried this but since the box is opened modally (acDialog), the
function stopped executing on that statement until the dialog closed.
Then the function resumed execution at "IsFormLoaded(. ..)" but was too
late to grab the return value because the form was gone..

I've hacked it by storing the parameters in global variables inside a
code module. Its awfully ugly but I guess it works. Yuck!

Dean

[Justin Hoffman]

<re******@bigfo ot.com> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.com.. .

Justin,

I see what you are trying to do. I tried something similar. Did you
try it? Does it work for you?

I tried this but since the box is opened modally (acDialog), the
function stopped executing on that statement until the dialog closed.
Then the function resumed execution at "IsFormLoaded(. ..)" but was too
late to grab the return value because the form was gone..

I've hacked it by storing the parameters in global variables inside a
code module. Its awfully ugly but I guess it works. Yuck!

Dean

I did double-check it before I posted it. There is no need for global
variables. I guess you didn't put the code in for the two buttons as
described. There two buttons on the form cmdOK and cmdCancel should do
different things. The cancel button closes the form, so the value is lost
and the function returns an empty string. The OK button sets the form's
visible to false, so the function continues, the value is picked up from the
textbox before the form is actually closed.

Can you double-check what you did? I don't think I made a mistake - but it
has been known in the past!
' *************** *************** *************** *********
' Code for the form
Option Compare Database
Option Explicit

Private Sub cmdCancel_Click ()

On Error GoTo Err_Handler

DoCmd.Close

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description , vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

Private Sub cmdOK_Click()

On Error GoTo Err_Handler

Me.Visible = False

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description , vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

Private Sub Form_Open(Cance l As Integer)

On Error GoTo Err_Handler

Dim strOpenArgs As String
Dim astrValues() As String
Dim strValue As String
Dim lngPosition As Long
Dim lngCount As Long

Cancel = True

strOpenArgs = Nz(Me.OpenArgs, "")

If Len(strOpenArgs ) > 0 Then

astrValues = Split(strOpenAr gs, "|")

For lngCount = 0 To UBound(astrValu es())

strValue = astrValues(lngC ount)

lngPosition = InStr(strValue, "=")

If lngPosition > 1 Then

Select Case UCase(Left$(str Value, lngPosition))

Case "PROMPT="
Me.lblPrompt.Ca ption = Trim(Mid$(strVa lue, lngPosition + 1))

Case "TITLE="
Me.Caption = Trim(Mid$(strVa lue, lngPosition + 1))

Case "DEFAULT="
Me.txtResponse = Trim(Mid$(strVa lue, lngPosition + 1))

End Select

End If

Next lngCount

End If

If lngCount = 3 Then
' The right number of parameters have been passed
Cancel = False
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description , vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub
' *************** *************** *************** *********
' *************** *************** *************** *********
' Code in a separate module
Option Compare Database
Option Explicit

Public Function MyInputBox(strP rompt As String, _
strTitle As String, _
strDefault As String) As String

On Error GoTo Err_Handler

Dim strReturn As String
Dim strOpenArgs As String

strOpenArgs = "Prompt=" & strPrompt & "|" & _
"Title=" & strTitle & "|" & _
"Default=" & strDefault

DoCmd.OpenForm "frmMyInputBox" , , , , , acDialog, strOpenArgs

If IsFormLoaded("f rmMyInputBox") Then
strReturn = Nz(Forms!frmMyI nputBox.txtResp onse, "")
DoCmd.Close acForm, "frmMyInput Box"
End If

MyInputBox = strReturn

Exit_Handler:
Exit Function

Err_Handler:
MsgBox Err.Description , vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Function

Private Function IsFormLoaded(st rName As String)
IsFormLoaded = (SysCmd(acSysCm dGetObjectState , acForm, strName) <> 0)
End Function
' *************** *************** *************** *********

[Justin Hoffman]

<re******@bigfo ot.com> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.com.. .

Justin,

I see what you are trying to do. I tried something similar. Did you
try it? Does it work for you?

I tried this but since the box is opened modally (acDialog), the
function stopped executing on that statement until the dialog closed.
Then the function resumed execution at "IsFormLoaded(. ..)" but was too
late to grab the return value because the form was gone..

I've hacked it by storing the parameters in global variables inside a
code module. Its awfully ugly but I guess it works. Yuck!

Dean

repost - as it still hasn't appeared on my news server:
I did double-check it before I posted it. There is no need for global
variables. I guess you didn't put the code in for the two buttons as
described. There two buttons on the form cmdOK and cmdCancel should do
different things. The cancel button closes the form, so the value is lost
and the function returns an empty string. The OK button sets the form's
visible to false, so the function continues, the value is picked up from the
textbox before the form is actually closed.

Can you double-check what you did? I don't think I made a mistake - but it
has been known in the past!
' *************** *************** *************** *********
' Code for the form
Option Compare Database
Option Explicit

Private Sub cmdCancel_Click ()

On Error GoTo Err_Handler

DoCmd.Close

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description , vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

Private Sub cmdOK_Click()

On Error GoTo Err_Handler

Me.Visible = False

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description , vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

Private Sub Form_Open(Cance l As Integer)

On Error GoTo Err_Handler

Dim strOpenArgs As String
Dim astrValues() As String
Dim strValue As String
Dim lngPosition As Long
Dim lngCount As Long

Cancel = True

strOpenArgs = Nz(Me.OpenArgs, "")

If Len(strOpenArgs ) > 0 Then

astrValues = Split(strOpenAr gs, "|")

For lngCount = 0 To UBound(astrValu es())

strValue = astrValues(lngC ount)

lngPosition = InStr(strValue, "=")

If lngPosition > 1 Then

Select Case UCase(Left$(str Value, lngPosition))

Case "PROMPT="
Me.lblPrompt.Ca ption = Trim(Mid$(strVa lue, lngPosition + 1))

Case "TITLE="
Me.Caption = Trim(Mid$(strVa lue, lngPosition + 1))

Case "DEFAULT="
Me.txtResponse = Trim(Mid$(strVa lue, lngPosition + 1))

End Select

End If

Next lngCount

End If

If lngCount = 3 Then
' The right number of parameters have been passed
Cancel = False
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description , vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub
' *************** *************** *************** *********
' *************** *************** *************** *********
' Code in a separate module
Option Compare Database
Option Explicit

Public Function MyInputBox(strP rompt As String, _
strTitle As String, _
strDefault As String) As String

On Error GoTo Err_Handler

Dim strReturn As String
Dim strOpenArgs As String

strOpenArgs = "Prompt=" & strPrompt & "|" & _
"Title=" & strTitle & "|" & _
"Default=" & strDefault

DoCmd.OpenForm "frmMyInputBox" , , , , , acDialog, strOpenArgs

If IsFormLoaded("f rmMyInputBox") Then
strReturn = Nz(Forms!frmMyI nputBox.txtResp onse, "")
DoCmd.Close acForm, "frmMyInput Box"
End If

MyInputBox = strReturn

Exit_Handler:
Exit Function

Err_Handler:
MsgBox Err.Description , vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Function

Private Function IsFormLoaded(st rName As String)
IsFormLoaded = (SysCmd(acSysCm dGetObjectState , acForm, strName) <> 0)
End Function
' *************** *************** *************** *********