Lookup Button (current data or archive)

[sparkbyte]

I am trying to create a button on a form that allows the user to enter a file number for lookup that first checks current data that looks in archival data for the file number.

I have a script that seems to successfuly look in each of the tables, but I am not sure how to dynamicaly change a forms source table.

Here is what I have so far.

Private Sub btnTestLookup_Click()
Dim strInput As String
Dim strResult As String

strInput = InputBox("Enter A-File Number", "User Input Box")
    
    If IsNull(DLookup("[FileNumber]", "tblTrackingTable", _
        "[FileNumber]='" & strInput & "'")) Then
        strResult = DLookup("[FileNumber]", "tblTrackingTableArchive", _
        "[FileNumber]='" & strInput & "'")
        
        MsgBox "Found " & strResult & " in archive data"
        DoCmd.OpenForm "frmFileNumberLookup", acNormal, , , acFormEdit, acWindowNormal, Me.Name

    Else
        strResult = DLookup("[FileNumber]", "tblTrackingTable", _
        "[FileNumber]='" & strInput & "'")
        MsgBox "Found " & strResult & " in current data"
        DoCmd.OpenForm "frmFileNumberLookup", acNormal, , , acFormEdit, acWindowNormal, Me.Name

    End If
    
End Sub

Thanks

John Fuhrman

 

[LeonAtRC]

You don't indicate what "Me.Name" is but that would be a good place to put the table name. Then use the On Open event of the form to set the table:

Private Sub Form_Open(Cancel As Integer)
If Nz(Me.OpenArgs, "") <> "" Then
Me.RecordSource = Me.OpenArgs
End If
End Sub

This could use some error trapping.