Parameter query that returns no records

[Roncc]

I have a form that uses a parameter query as its record source. The end user will enter a client number (the parameter) and the form will open displaying that clients information on the form. If the user enters an non-existent client number, the form still opens but it is empty. Is there anyway to advise the user with a message box that he has entered a number that is invalid and to please try again.

Thanks for all the help. You guys and gals are terrific and much appreciated.



Ron Cheshier

[isladogs]

Use a DLookup together with Nz similar to this

If Nz(DLookup("ClientNumber","MyTableName"),"")="" Then
     MsgBox "This client number does not exist"
     Exit Sub
Else
     DoCmd.OpenForm "MyFormName"
End if

However, a much better method would be for users to select the client number from a combo box or listbox.
That would mean they can only select numbers that do exist.

[Roncc]

Thank you for the response. I'll give it a go. Regarding the combo/listbox; there will be upward of 5000 records in the table so such an approach isn't very practical.
Thanks again.

[pbaldy]

Ridder's code would need a criteria using the textbox:

http://www.theaccessweb.com/general/gen0018.htm

I'd also use a combo; the user can still type in a value, they don't have to scroll and select.

[isladogs]

Depending on how your client numbers are constructed, could you split it down into chunks using two cascading combos?
For example, I have a postcodes database with almost two million UK postcodes.
To make searching manageable, I break it down into 5 sections
E.g. BS => BS7 => BS7 8 => BS7 8H =>BS7 8HP

[Roncc]

I tried the code but because the query parameter precedes the opening of the form, it doesn't work I still get a blank form. I've tried moving the code lines around but that didn't work. As suggested by other responders, I'm now pursuing the use of a combo box. Thanks for the help.

Ron

[Micron]

you should be able to determine the form recordset count upon opening the form and cancel if it's zero, or get the count based on a DLookup before opening the form and don't execute the DoCmd.OpenForm line if it's zero. The former will require you to handle error 2501 (?) in the calling code. Saying you tried the code isn't very helpful. Quite possibly it's just a matter of a syntax or procedural error on your part, but we can't tell without seeing what you tried.

[pbaldy]

I tried the code but because the query parameter precedes the opening of the form, it doesn't work I still get a blank form. I've tried moving the code lines around but that didn't work.

Did you try adding a criteria to the DLookup()? It won't work without.

[isladogs]

Whilst still much preferring the combo approach if feasible, here is a modified version of my previous code including criteria as Paul correctly pointed out were needed
The parameters for filtering the form are only supplied after the check has been done

The following code assumes a field name 'Company', table name tblClients and the ClientNumber is the number value entered in textbox txtClient

If Nz(DLookup("Company", "tblClients", "ClientNumber=" & Me.txtClient), "") = "" Then    
    MsgBox "This client does not exist"
    Exit Sub
Else
    DoCmd.OpenForm "frmClientInfo", , ,"ClientNumber=" & Me.txtClient
End If

If the ClientNumber is a text field, instead use text delimiters

If Nz(DLookup("Company", "tblClients", "ClientNumber='" & Me.txtClient & "'"), "") = "" Then    
    MsgBox "This client does not exist"
    Exit Sub
Else
    DoCmd.OpenForm "frmClientInfo", , ,"ClientNumber='" & Me.txtClient & "'"
End If

[Roncc]

Micron, thank you for the suggestion but my coding knowledge is insufficient to be able to follow your instructions. I am finding that the combobox suggestion will probably meet my needs. When I said I tried the code what I meant was that I went to the code written into the on-click for a button that opens my form (whose data source is the parameter query) inserted the code as presented with the modifications necessary to fit my table and field titles but it did not work, the form open with no data. I tried re-arranging the code placement so that the
If Nz(DLookup("ClientNumber","MyTableName"),"")="" Then
cmd.close
MsgBox "This client number does not exist"
Exit Sub
and this did not work either. I am not familiar enough with coding to use the error handling you suggest.

[Roncc]

Thank you ridders52, I will give this a try.

[Roncc]

ridder52, I just posted that I was having issues with the coding you provided but upon reviewing it I may have misunderstood what you were saying. I'll keep you posted.

[pbaldy]

Your original response indicates you are using a bracketed parameter in your query. Most of us would use a form to gather user input, which is what the code solutions assume. You have much more control that way.

[Roncc]

The only way I know how to use parameter queries is to use the bracketed parameter.

[pbaldy]

Instead of that you can have a form reference:

Forms!FormName.TextboxName