Here is what I'm trying to do:
The form "frm_RepairInfo-Asset" is based on a query (RepairInfo-SearchByAsset) that asks the user to enter an asset number. If it's never been sent in for repair, the query will have no results. If it HAS been sent in for repair, the results of this query will be 1 or more.
I created a second query (RepairInfo-SearchByAsset2) that has one field (TestData), a count of the records in RepairInfo-SearchByAsset query.
What I need it to do is if TestData is NOT 0, open "frm_RepairInfo-Asset" with the info from the RepairInfo-SearchByAsset query. (The ID key for this is "TicketNum".)
If TestData IS 0, I need it to go back to the RepairMenu on the Search tab and display a message box that says "No records found." Clicking "Ok" would close the message box and they would be a the screen where they can select another search.
I have tried two different ways, with no luck. In both cases the "RepairInfo-SearchByAsset2" query returns the correct number of results. Also in both cases, no form ever opens up, and I never get a message box when there are no matching records. AND, after I click the button to run the code once, I am never asked to enter an asset number ... the screen just flickers, but doesn't move to another screen or display the message box.
_____________________________________
CODE #1:
Private Sub Command40_Click()
DoCmd.Close acForm, "frm_RepairMenu"
On Error GoTo fErr
DoCmd.OpenQuery "RepairInfo-SearchByAsset2", acViewNormal, acEdit
Dim SearchVal As String
SearchVal = DLookup("TestData", "RepairInfo-SearchByAsset2")
If SearchVal = "0" Then
DoCmd.Close acQuery, "RepairInfo-SearchByAsset2"
DoCmd.OpenForm "frm_RepairMenu", View:=acNormal, OpenArgs:="SearchRep"
MsgBox "Your search criteria found no matching records."
Else
DoCmd.OpenForm "frm_RepairInfo-Asset"
End If
Exit Sub
fErr:
DoCmd.OpenForm "frm_RepairMenu", View:=acNormal, OpenArgs:="SearchRep"
End Sub
_____________________________________
CODE #2:
DoCmd.Close acForm, "frm_RepairMenu"
On Error GoTo fErr
DoCmd.OpenQuery "RepairInfo-SearchByAsset2", acViewNormal, acEdit
Dim SearchVal As String
If SearchVal = 0 Then
DoCmd.Close acQuery, "RepairInfo-SearchByAsset2"
DoCmd.OpenForm "frm_RepairMenu", View:=acNormal, OpenArgs:="SearchRep"
MsgBox "No records found."
Else
DoCmd.OpenForm "frm_RepairInfo-Asset"
End If
Exit Sub
fErr:
DoCmd.OpenForm "frm_RepairMenu", View:=acNormal, OpenArgs:="SearchRep"
End Sub
_____________________________________
Someone who knows VBA can probably spot the problem in seconds, but I'm stymied! Any help you can give will be appreciated.
Jackie