Supress error message

Ken,

What does the query do (Select, Append, Delete, Update)?

Generally, I use controls on a form for entering these types of parameters, usually on the same form with the button that launches the query, so that the button actually checks for a value before executing the query.  Sometimes, I will raise my own popup form to capture the data and allow the user to cancel.  If the user clicks Cancel on my popup, then the query never gets executed.

Thanks for reply, Dale.  It's  a simple Select query.

Queries have no event trapping so if you want to trap errors, you need to follow Dale's advice and use a form to capture the argument and validate it and a button to run the query.

Ken, I think you're going to have to use VBA code, rather than a macro, for this to work.  :)  If you open your form in Design view, and click the Design tab, you'll see a "Convert Form's Macros to Visual Basic" button.  Click that, and the macro will be translated into VBA.   Select the button, and in the Properties window you can click the Event tab, find the OnClick event, and see that the macro has been changed to an Event Procedure.  Click the Builder button (three dots) next to the event procedure, and you'll be taken to the Code Window, where you can see and edit the VBA.

Now to your actual problem:  you need to capture and validate the user's response before the query runs.  My suggest is that you create a Variant variable, and use the InputBox to drop a value into it.  You can test the user's response, and if it passes the test, create a query using the variable as criteria.

This code assumes that Packing Slip Number is an text field, so the criteria is set off with single quotes.  If it's a numeric field, you'll need to remove the single quotes, so the SQL statement looks like this:  strSQL = "Select * from YourTableName where PackingSlipNumber = " & varResponse

The code also assumes that you want all the fields from the table you're querying;  if not, you'll need to modify the SQL to select only the fields you're interested in.

The first time you run this code, it should work.  The second time, you'll need to uncomment the DeleteObject statement, so the query can be dropped before you create a new version of it.

If you're new to VBA and like playing with computers, you're going to love this;  I think it's the best toy ever.  :)

Private Sub btnYourButtonName_Click()
Dim KensDB As Database, KensQD As QueryDef, strQueryNa As String, varResponse as Variant

strQueryNa = "QueryPackingNumber"

'DoCmd.DeleteObject acQuery, strQueryNa

'get user input:
varResponse = InputBox("Enter Packing Slip Number:", "Ken's Database")

'test user's response
If varResponse = "" Then Exit Sub

'compose SQL
strSQL = "Select * from YourTableName where PackingSlipNumber = '" & varResponse & "'"

'create query:
Set KensDB = CurrentDb
Set KensQD = KensDB.CreateQueryDef(strQueryNa)
'populate query's SQL
KensQD.SQL = strSQL

'open query:
DoCmd.OpenQuery strQueryNa

End Sub

Select allOpen in new window

Can you share the query to see if it can be handled on query level...

Thanks for all the replies and assistance!

Glad we could help.  :)  I was thinking about this on my drive to work this morning;  it occurred to me that you could put a drop-down box on your form that retrieves the packing slip numbers, and modify the query to get the number from the dropdown box, rather than asking the user to enter it.

Driving under the influence (DUI) of Resolving EE questions is bad ....

LOL