No data found in query pop up 2

[Xzibit7]

I have a query that prompts the user for a part number when run. The problem is I need an error message that comes up when the user enters a wrong part number or one that does not exist. Now the user is just directed to a blank form with no data

 

[Remou]

I think you should use a form for the part number, rather than just the standard prompt from a query.

 

[Xzibit7]

Do you mean for a drop down menu?

 

[dhookom]

Use a combo box control on a form for the criteria.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Remou]

What I was thinking of is a small dialog form, as described in:

http://office.microsoft.com/en-ca/assistance/ha011170771033.aspx

There may be other possibilities, if you say what happens with the query. Is it for a form or report, for example?

 

[Xzibit7]

Okay I think I'm confused if there are thousands of part numbers do you mean to have like a drop down box with each or can you just have a form that has all part numbers and if someone enters the wrong one this would cause an error message automatically.

 

[Melagan]

Are you using query parameters to prompt the user or controls on a form? If you're using query parameters, there is no way to accomplish this; judging by your post above, it sounds like you are indeed using query parameters. Almost everyone here would suggest running queries from a form when you want to prompt the user for parameters. It takes a little extra effort, but adds much more flexibility.

Create a new, unbound form.
Add text boxes, name and lable accordingly (these are for the data that will be passed through to your query).
Add a command button that will be used to open the query. The code behind your command button should be something like this:

Private Sub cmdOK_Click()
Dim strQueryName as String
  strQueryName = ""             'Enter your query name here

DoCmd.OpenQuery strQueryName, acViewNormal
End Sub

Now for the query, instead of having prompts with the brackets []'s, you'll want to reference the controls on your form. In the design grid, for example, under the column where you're asking for part number, type in
Forms![YourFormName]![YourControlName].

As long as [YourFormName] is open, the query will take whatever the user has typed in [YourFormName]![YourControlName] and use that as criteria!

To keep things simple for your data verification, you can do a DLookUp function in code behind your text box. Test to see if that DLookUp evaluates to false, then prompt the user with a messagebox. If you need further help with the DLookup and Msgbox functions, let me know!


~Melagan
______
"It's never too late to become what you might have been.

 

[Xzibit7]

Thanks Melagan I think that is what I was trying to ask (sorry about the confusion everyone) how using a form you can test if the part number is in your database. So I am unsure how to do a DLookUp function and then have a MsgBox come up if not in the database.

 

[randy700]

If IsNull(DLookup("PartNumber", "tblParts", "PartNumber = '" & txtPartNumber & "'")) Then
   MsgBox "Invalid part number."
Else
   DoCmd.OpenQuery strQueryName
End If

Randy

 

[Xzibit7]

Thanks Randy I was looking through some of the old posts on dlookups and this is definetly the most straight-forward I have seen

 

[Melagan]

The best way to learn how to utilize DLookup, or any other domain aggregate function, is to read through the helpfile in access. If you hit ctrl-g, Access should open a visual basic window with the Immediate window in focus. Type in DLOOKUP then hit F1. The help files for functions and methods in VBA are very...well...helpful =)

~Melagan
______
"It's never too late to become what you might have been.