Blank screen on empty query

[JackieEVSC]

I'll try explaining what I'm doing and hope I do a better job this time.

I have a form that allows users to query the RepairInfo table, which only includes schools they've selected in the MySchools table.

One of the fields they can choose to search on is the repair ticket number (TicketNum). Clicking on the button the search by ticket number has this underlying code:

Private Sub Command162_Click()
DoCmd.Close acForm, "frm_RepairInfo-Asset"
DoCmd.OpenForm "frm_RepairInfo-Ticket"
End Sub



If they enter a ticket number not in the table, it currently goes to a blank screen. Because I've already closed the form they were on and the db structure is hidden, the only way to recover is to close and reopen the program.

If the criteria they enter yeilds no results, I need a message box to pop up, then when they click "ok", to have it go to the frm_RepairInfo form, which is the only form with the search buttons that is not based on a search query.

I've received some help that gave me the code where I could paste the SQL code form the query, but believe a JOIN and an ORDERBY statement in the query is causing it to fail. In the module, the code in the shows red for JOIN and ORDERBY statement.

I know someone who knows vba could accomplish this in seconds, but I'm really struggling with it! I know it's not a difficult thing to do, but I'm lost! Help!

[June7]

Don't let them enter a ticket number not in the table. Use a combobox with RowSource listing ticket numbers.

Or validate the entry in the AfterUpdate event of the box with DLookup() function. Like:
If IsNull(DLookup(...)) Then
Msgbox "No such number."
'open RepairInfo form
Else
'code to open other form - instead of button click
End If

[JackieEVSC]

I changed the form to "Yes" for allow additions which allows the it brings up an empty form. It's not what I was striving for, but it keeps them in the program and allows them to search again, so I'm going to quit banging my head against the wall and call it good enough! Thank you!