Passing multiple values to a second form

[WithoutPause]

I see a number examples of passing a single value, but I would like to pass multiple values instead. There are two parts to this as one is the initial data entry and the second is where people can look up previously entered records.



Here's what each one needs:

#1
1. Validate that the key field is filled in.
2. Pass the needed values into the second form so the user can complete the process on the second form.

#2
1. Validate that the key field is filled in.
2. Do a DB search based on the key field to make sure the record in there first place exists.
2. Pass the needed values into the second form.

[pbaldy]

You mean with OpenArgs?

http://www.baldyweb.com/OpenArgs.htm

[ItsMe]

The value can be passed to a second form by referencing the first form before it is closed.

Something like

Me.txtFieldForm2.value = Forms!frmOne.txtField.value

To check if the value exists in a table already, I would us DAO and .Findfirst with an If Then statement using .Nomatch.

Dim db As DAO.Database
Dim rcdFindMatch As DAO.Recordset
Set db = CurrentDb
Set rcdFindMatch = db.OpenRecordset("tblName", dbOpenDynaset)
    Dim strWhere As String
    
    strWhere = "[CustID] = " & intCustId & " AND [ItemNum] = '" & strItem & "'"
    
    
    rcdFindMatch.FindFirst strWhere
    
        If Not rcdFindMatch.NoMatch Then
        
            MsgBox "This Item number is already listed for this customer", vbInformation, "Matching Item Found"
            
        rcdFindMatch.Close
        Set rcdFindMatch = Nothing
        Exit Sub
        Else
        
            MsgBox "This Item number does not exist for this customer", vbInformation, "Item Not Found"
        
        rcdFindMatch.Close
        Set rcdFindMatch = Nothing
        Exit Sub
        End If

[WithoutPause]

I got the values I wanted to get passed in example #1. The form I'm passing the data to has additional fields and those fields have "#Name?" in them when I open the second form. Is there an easy way to clear those out, or do I have to do them on an individual basis?

[ItsMe]

Clear out the "#Name?" by binding the control to a valid field within the form's recordset. You could also type "Unbound" inside the control to cause it to be an Unbound control.

[WithoutPause]

I got the values I wanted to get passed in example #1. The form I'm passing the data to has additional fields and those fields have "#Name?" in them when I open the second form. Is there an easy way to clear those out, or do I have to do them on an individual basis?

Either something wonky happened with my table or I forgot to add some fields to the table. As I add the fields to the table the #Name? issue goes away. I'll fix this and work on the query aspect.

[WithoutPause]

Ok, I had the passing of the data correct and then I began fixing an issue where if a user cleared out the key field after entering data and it still was going to the second form. At some point my data stopped being transferred to the second form. This code works for validation, but fails at actually passing the data.

Private Sub cmdFactSheet_Click()
    C01.SetFocus
    If Trim(C01.Text) = "" Then
        MsgBox "Please enter grievant number."
        Forms!frmComplaint!C01.SetFocus
    Else
        If Not IsNull(C01) Then
            DoCmd.OpenForm "frmFactSheet", , , , , , Me.C01 & ";" & Me.C02 &.... ' There are more fields passed
        End If
    End If
End Sub

[ItsMe]

So you tested the open args string in your second form? It works when used outside of your If Then Else statement?

[WithoutPause]

Well it was but now only the first variable is passed if I take out the if statement. I haven't touched the On Load code so that should be set.

If Not IsNull(Me.OpenArgs) Then
        strOpenArgs = Split(Me.OpenArgs, ";")
        Me.C01 = strOpenArgs(0)
        Me.C02 = strOpenArgs(1)
        Me.C03 = strOpenArgs(2)

That's the begining of the On Load code.

[pbaldy]

I'd use this method to make sure OpenArgs contains what you expect:

http://www.baldyweb.com/ImmediateWindow.htm

[WithoutPause]

It doesn't like the strWhere line saying there is a data type mismatch.

If Not IsNull(Me.C01) Then
    '
    Dim db As DAO.Database
    Dim rcdFindMatch As DAO.Recordset
    Set db = CurrentDb
    Set rcdFindMatch = db.OpenRecordset("Complaint", dbOpenDynaset)
        Dim strWhere As String
        
        strWhere = "[C01] = " & C01
        
        rcdFindMatch.FindFirst strWhere
        
        If rcdFindMatch.NoMatch Then
            MsgBox "The grievant number does not exist", vbInformation, "Grievant Number Not Found"
            rcdFindMatch.Close
            Set rcdFindMatch = Nothing
            C01.SetFocus
            Exit Sub
        Else
            DoCmd.OpenForm "frmFactSheet", acNormal, , , , , C01.Value
        End If
    '
Else
    Debug.Print "y"
    MsgBox "Please enter grievant number."
    Me.C01.SetFocus
End If

[pbaldy]

If the field is text you need delimiters:

http://www.baldyweb.com/wherecondition.htm

[ItsMe]

When you reference a control in VBA you should use the collection name

Forms!frmName![ControlName]

You can also use the Me shortcut if the control is on the form's class module you are working in

Me.ControlName

try
strWhere = "[C01] = " & Me.C01

What data type is Me.C01?

[WithoutPause]

Me.C01 is a text field. I tried passing more than one field and I got an error message. I had though it just needed to add & ";" & between what fields were being passed.

Works:

DoCmd.OpenForm "frmFactSheet", , , "C01 = '" & Me.C01 & "'"

Doesn't work:

DoCmd.OpenForm "frmFactSheet", , , "C01 = '" & Me.C01 & "'" & ";" & "C02 = '" & Me.C02 & "'"

[pbaldy]

A wherecondition wouldn't be separated by ";", rather by " AND ".