InputBox()

[Albert D. Kallal]

The best solution would be to make a small form and use that in place of
place of the input box. You can actually design a form that looks identical
to the input box, but is only a form. (just turn off all the junk like
navigation buttons etc.). The end result is that you can use a text box.

The trick you need is how do you wrap a form into a function?

The soltion is as follows:

Of course, if code opens a form, it does NOT wait, the calling code
continues to run. Thus, what we need is the call code (the docmd.Openform)
to halt, and wait while the user enters some value from the prompt form.
When done, then your calling code can examine the form' values.

Ok, here is how you do it. You open the form in acDialog mode. This will
halt the calling code, and wait. The user then enters some data into fields,
and the hits the ok button. The trick here is that the ok button DOES NOT
close the form, but sets the forms visible property to false. The will cause
the form to drop out of acDialog mode, and then the calling code will
continue!. If the user does in fact hit cancel, or closes the form, you
consider that as a CANCEL!! (ie: the cancel, or even the "X" in the upper
right corner is thus considered a close/cancel).

Hence, the code looks as

' lets open a prompt form
strF = "frmGetComboName"
docmd.OpenForm strF,acNormal,,,,acDialog

' the above code opens our form "frmGetComboName", and the waits until the
user
enters some data, and then hits ok. The code behind the ok button in the
GetName form does NOT close the form, but does

me.Visiable = false

' at this point, either the user hit ok, or closed the form. If the form is
still open, then user did not cancel, and we assume he hit OK

if isloaded(strF) = true then
' code goes here to example values
strName = forms(strF)!ThenameField
strSex = fomrs(strF)!GenderField
' ok, got our data...lets close the form
docmd.Close acForm,strF
else
' if the form is not open, then user hit the "x", or the cancel
' button on the GetName form. Note that the cancel button on
' the GetName from simply does a docmd.close

msgbox "user canceled"
end if

You also need the code for isloaded. It can be found in tons of examples,
and also in the northwind traders. It is reproduced below just in case
you don't have it:

You can also thus wrap the whole acDialog form code in a function that
returns a value if you want. (I do this for date calendar prompts, and
you thus get re-usable form that can be used anywhere by a simple function).

You could create a function called MyInput

Function fIsLoaded(ByVal strFormName As String) As Integer
'Returns a 0 if form is not open or a -1 if Open
If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> 0 Then
If Forms(strFormName).CurrentView <> 0 Then
fIsLoaded = True
End If
End If
End Function

Just remember, that logically you consider that if the form gets closed via
the X, or by the user's cancel button, the form is NOT loaded.

--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
kal...@msn.com
http://www.attcanada.net/~kallal.msn