Search code examples
databasevbams-accessopenargs

Me.OpenArgs is always returning null when trying to open a new form in access

Okay, so I have a database in Access, and when I have one form open (for a job application), and click a button it runs this code:

Private Sub cmdAddDemo_Click()

DoCmd.OpenForm "sfrmAppDemographics", , , , , , txtApplID.Value

End Sub

Where txtApplID is a text box filled with a numerical value for "application ID" number in a table for applications. I want this value to be passed to the "sfrmAppDemographics" form so it will open the correct demographics information for the user displayed in the previously mentioned form for the application.

So, I did this in the code for the demographics form: 

Private Sub Form_Open(Cancel As Integer)

Dim rs As DAO.Recordset
Set rs = Me.Recordset
If rs.RecordCount = 0 Then
    rs.AddNew
    rs!ApplID = Me.OpenArgs
    rs.Update
Else
    MsgBox Me.OpenArgs
End If
Me.Requery

End Sub

So, the idea is, if there's no demographic info, it will create a new one using the ApplID from the passed openargs, and if there IS demographic data for this user, it pops up a message box with the openargs (as a test to see if it's working). I ALWAYS get an error "Run-time error '94': Invalid use of Null" - on the line for the MsgBox. (Because the database DOES have records for demographics). When I take out the Else MsgBox Me.OpenArgs, it just shows the first demographics info in the db, with ApplID 1. Strange. It seems I am not able to pass or access the ApplID through the OpenArgs functionality in the Form_Open code. Any help?

Solution

  • OpenArgs is a string that is passed to the form, nothing more. If you want to filter the called form, you use the WhereCondition parameter:

    Private Sub cmdAddDemo_Click()

    DoCmd.OpenForm "sfrmAppDemographics", WhereCondition:="ApplID = " & txtApplID.Value

End Sub

    If you want to create the record if it doesn't exist, it makes sense to pass the ID additionally as OpenArgs:

        DoCmd.OpenForm "sfrmAppDemographics", WhereCondition:="ApplID = " & txtApplID.Value, _
                   OpenArgs:=txtApplID.Value

    and then

    Private Sub Form_Open(Cancel As Integer)

' No need for a separate recordset here
If Me.RecordsetClone.EOF Then
    ' We are already in a new record, just set the bound ID control to create it
    Me!ApplID = Me.OpenArgs
    ' if you want to save the new record immediately
    Me.Dirty = False
End If

End Sub

    P.S. if Me.OpenArgs is Null in your code, then txtApplID was Null. I see no other reason.