Query

[Accessless]

Hi, in a previous post, I was helped to design a form that retrieves Client info from a table using a query.

SELECT [MASTER KEY LIST].[Key No], [MASTER KEY LIST].[Client Name], [MASTER KEY LIST].Address, [MASTER KEY LIST].Comments, [MASTER KEY LIST].[M-1], [MASTER KEY LIST].[M-2]
FROM [MASTER KEY LIST]
WHERE ((([MASTER KEY LIST].[Key No]) Like [Enter Client Number] & "-*"));

What do I need to add so if a Number that doesn't exist is entered, the query loops back Stating "Not Found - Enter Client Numbet" or something similar.

Thanks

 

[Smart]

re query

I am assuming the Client number is entered on a form, you then click a button and the query you have produces the results.

when you click the button you can check if the client number exists
Write a select query that selects Key No from the master key list table
in the criteria field specify the client no entered on the form

forms![Yourformname].[yourfieldname]

on the on click property of the button on your form choose Event Procedure
And place the following code there

Dim Invalid_Num as integer

Invalid_num = nz(dlookup("columnname from your query","yourqueryname"),0)

If Invalid_num = 0 then
msgbox("Client Number Does Not Exist",vbokonly,"Invalid Client Number"
clientnum field on your form.setfocus
exitsub
end if

Let me know how you go on

 

[neileg]

Alternatively, use a combo box based on a query that returns the valid numbers. Make sure the Limit to List property is set to yes. This will prevent the user entering a number that doesn't exist.

 

[Accessless]

It's actually a query with a pop up message that says "Enter Client Number"

Then, once the number is entered and you press OK, it displays the info in a form. If the number is not found, it just goes to a blank scree (which is what I'm trying to avoid!)

The initial thread started here Query +Form

All the info is available in a table and nothing can or needs to be modified.

Thanks for your help!