Fetch!

Let’s say you want to set a text control to a date allowing them to use either a date picker or the keyboard to enter the date using a mask.

Or you may need a dialog box to get a password from a user or a piece of information needed for a report, and you want a little more control over it than the standard options you are given using inputbox.

One solution is to use a modal form.  You can set a form to modal and popup using the properties dialog for the form to set “Pop Up” and “Modal” to Yes.

frmInputBox_Password design view:

frmInputBox_Password code module:

Option Compare Database

Private Sub cmdCancel_Click()
    DoCmd.Close acForm, "frmInputBox_Password"
End Sub

Private Sub cmdOK_Click()
    Me.Visible = False
End Sub

Private Sub Form_Load()

Dim lngLoop As Integer
Dim strTitle As String
Dim strDefault As String
Dim varOpenArgs As Variant
Dim varOpenArg As Variant

  If Len(Me.OpenArgs & vbNullString) > 0 Then
    varOpenArgs = Split(Me.OpenArgs, "~")
    For lngLoop = LBound(varOpenArgs) To UBound(varOpenArgs)
      varOpenArg = Split(varOpenArgs(lngLoop), "=")
      If UBound(varOpenArg) = 1 Then
        Select Case varOpenArg(0)
          Case "Prompt"
            Me!lblPrompt.Caption = varOpenArg(1)
          Case "Title"
            strTitle = varOpenArg(1)
          Case "Default"
            strDefault = varOpenArg(1)
        End Select
      End If
    Next lngLoop
  End If

  If Len(strTitle) = 0 Then
    strTitle = Application.Name
  End If

  Me.Caption = strTitle
  Me!pwdPassword = strDefault
  Me!pwdPassword.SetFocus

End Sub

Then I have another function in a public module that calls the form and retrieves the value like so:

Public Function PasswordInputBox( _
   Prompt As String, _
   Optional Title As String = vbNullString, _
   Optional Default As String = vbNullString _
   ) As String

   Dim strOpenArgs As String

   strOpenArgs = "Prompt=" & Prompt
   If Len(Title) > 0 Then
      strOpenArgs = strOpenArgs & "~Title=" & Title
   End If
   If Len(Default) > 0 Then
      strOpenArgs = strOpenArgs & "~Default=" & Default
   End If

   DoCmd.OpenForm Formname:="frmInputBox_Password", _
      View:=acNormal, _
      WindowMode:=acDialog, _
      OpenArgs:=strOpenArgs

   If CurrentProject.AllForms("frmInputBox_Password").IsLoaded Then
      PasswordInputBox = Forms("frmInputBox_Password")!pwdPassword
      DoCmd.Close acForm, "frmInputBox_Password"
   Else
      PasswordInputBox = vbNullString
   End If

End Function

There are some optional parameters being passed to set different things like the prompt, title of the dialog, etc., but here are the important bits:

1. Clicking OK sets the modal, popup form visibility to false.  This is what allows execution to continue in the PasswordInputBox function.
2. If the form properties are not set correctly, execution of the code will not stop on DoCmd.OpenForm and this function will not work correctly.
3. ‘In addition, if you close the form, we will not be able to retrieve the value in the next line of the calling function, which is why we hide it, then actually close the form from the function after retrieving the value.