Access JumpStart 2.0 | Blog

A Rapid Development Framework for Microsoft Access

Using a function and a modal form to retrieve user data

by | Sep 7, 2020 | MS Access Forms | 0 comments

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.

Submit a Comment