Access Cookbook - Ken Getz [191]
' requery the calling form. Designed to be called from
' OnNotInList event procedures.
'
' strAddForm - The form to be opened to add a record
' strControlName - The control on the add form that matches
' the displayed info in the calling combo box
' strNewData - The data as supplied by the calling combo box
On Error GoTo HandleErr
' First, confirm that the user really wants to enter a new record.
If MsgBox("Add new value to List?", vbQuestion + vbYesNo, _
"Warning") = vbNo Then
acbAddViaForm = acDataErrContinue
Exit Function
End If
' Open up the data add form in dialog mode, feeding it
' the name of the control and data to use.
DoCmd.OpenForm FormName:=strAddForm, DataMode:=acAdd, _
WindowMode:=acDialog, OpenArgs:=strControlName & ";" & strNewData
' Before control returns to the calling form,
' tell it we've added the value.
acbAddViaForm = acDataErrAdded
ExitHere:
Exit Function
HandleErr:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
, "acbAddViaForm"
Resume ExitHere
End Function
If the user wants to add the new record, the function opens the pop-up form in dialog mode. This pauses the function at this point (because a dialog-mode form won't give up the focus until it is closed or hidden) and lets the user enter the required data to complete the record:
' Open up the data add form in dialog mode, feeding it
' the name of the control and data to use.
DoCmd.OpenForm FormName:=strAddForm, DataMode:=acAdd, _
WindowMode:=acDialog, OpenArgs:=strControlName & ";" & strNewData
However, this leads to another issue. You can't fill in controls on the form before it's opened, and you can't fill them in after because the form is open in dialog mode. The acbAddViaForm function gets around this by using the OpenArgs property of the form, which allows you to pass a text string to the form. You'll see later in this solution how this property is used by the form to fill in its key field.
After the pop-up form is closed, all you have to do is set the appropriate return value. In this case, acDataErrAdded tells Access that you've added the value to the underlying table and that it can be used as the value for the combo box:
' Before control returns to the calling form,
' tell it we've added the value.
acbAddViaForm = acDataErrAdded
When the pop-up form opens, the OnLoad event property calls the acbCheckOpenArgs function, which takes a form variable from the active form as its only parameter. This function is used to process the OpenArgs property of the form (which is where the form places the parameter that was passed to it when it was opened). Its code is:
Public Function acbCheckOpenArgs(frm As Form)
' Designed to be called on loading a new form.
' Checks OpenArgs and, if it finds a string of
' the form "ControlName;Value", loads that
' value into that control.
Dim strControlName As String
Dim strControlValue As String
Dim intSemi As Integer
On Error GoTo HandleErr
If IsNull(frm.OpenArgs) Then
Exit Function
Else
intSemi = InStr(1, frm.OpenArgs, ";")
If intSemi = 0 Then
Exit Function
End If
strControlName = Left$(frm.OpenArgs, intSemi - 1)
strControlValue = Mid$(frm.OpenArgs, intSemi + 1)
' This OpenArgs property may belong to someone else
' and just look like ours. Set the error handling
' to just ignore any errors on the next line.
On Error Resume Next
frm.Form(strControlName) = strControlValue
End If
ExitHere:
Exit Function
HandleErr:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
, "acbCheckOpenArgs( )"
Resume ExitHere
End Function
The acbCheckOpenArgs function has to be careful to avoid errors because it's called every time the form is opened. First, it's possible that no OpenArgs argument was passed in. Second, the OpenArgs argument might be there for another reason. Thus, if OpenArgs doesn't parse out as expected (in the format ControlName ; Value), it's ignored.
If OpenArgs is in the correct format, the code parses out the value to be placed in the corresponding