condition table union for record search

[sparkbyte]

How can I create a button on a form that will search the current table for a record and iff the record is not found automagically search the archive table?

Can this be done with a query??

select * from shipping
where TrackingID = 'something'
if count = 0 then
select * from shippingArchive
where TrackingID = 'something'


Thanks

John Fuhrman

 

[MajP]

use a dlookup. If it returns null then use a dlookup in the archive. If it returns null then no record in either.

 

[sparkbyte]

Well I have been playing with Dlookup for awhile now and am stuck.

Here is what I have so far.

Private Sub btnTestLookup_Click()
Dim strInput As String
strInput = InputBox("Enter Tracking Number", "User Input Box")

   MsgBox "DLookup([FileNumber], TrackingTable, [FileNumber]='" & strInput & "'"
    
    If DLookup("[FileNumber]", "TrackingTable", _
        "[FileNumber]='" & strInput & "'") Is Null Then
        DoCmd.OpenForm "BoxNumberLookup", acNormal, , , acFormEdit, acWindowNormal
        Else
        DoCmd.OpenForm "BoxNumberLookupArchive", acNormal, , , acFormEdit, acWindowNormal
    End If
End Sub

It is bombing on the Dlookup line.

the msgbox prompt is showing the correct info.

strInput = InputBox("Enter Tracking Number", "User Input Box"):debug.print "DLookup([FileNumber], TrackingTable, [FileNumber]='" & strInput & "'"

DLookup([FileNumber], TrackingTable, [FileNumber]='12345678'

any insight??



Thanks

John Fuhrman

 

[randy700]

How about...

If IsNull(DLookup("FileNumber", "TrackingTable", "FileNumber = '" & strInput & "'")) Then

Randy

 

[sparkbyte]

OK, got it working.

Now I need a way to pass a variable to a report that it's control source prompts the user.

Can the varible be passed to the report form which in turn passes it to the query? Or should I be changing the query to not prompt the user and have the filter on the form?

Current code:

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

strInput = InputBox("Enter Tracking Number", "User Input Box")
    
    If IsNull(DLookup("[FileNumber]", "TrackingTable", _
        "[FileNumber]='" & strInput & "'")) Then
        strResult = DLookup("[FileNumber]", "TrackingTableArchive", _
        "[FileNumber]='" & strInput & "'")
        MsgBox "Found " & strResult & " in archive data"
        DoCmd.RunSQL "Select BoxNumber, FileNumber, TrackingDate from TrackingTableArchive"
        Else
        strResult = DLookup("[FileNumber]", "TrackingTable", _
        "[FileNumber]='" & strInput & "'")
        MsgBox "Found " & strResult & " in current data"
    End If
    
End Sub

Thanks

John Fuhrman

 

[PHV]

I'd create an UNION query, eg:
SELECT BoxNumber, FileNumber, TrackingDate FROM TrackingTableArchive
UNION SELECT BoxNumber, FileNumber, TrackingDate FROM TrackingTable

You may then use this query as recordsource for the report.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sparkbyte]

PH, can you explain a little further? I understand the UNION, but how should I incorporate it to achieve the over all goal of a user button to prompt the user for the search critera (tracking number) and display a printable report once the data is located.



Thanks

John Fuhrman

 

[PHV]

Use the 4rth argument of the DoCmd.OpenReport method.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886