Checking value on Open

[Taxcop]

I have a form that is tied to a query that searchs on the last name. I would like to have a meesage box appear if the last name is not found. Thanks for the help.

 

[jwindon]

From the event on a form that runs the query or on the OnLoad Event of your form that is based on:

Dim CountLastNameQuery As Integer
CountLastNameQuery = DCount("*","qryLastName")

If CountLastNameQuery = 0 Then
Msgbox "There are not matching Last Names"
Else
Exit Sub
End If

 

[Taxcop]

I get a error message when I try this. Am I correct to assume that the "*" is the name of the last name field and the "qryLastName" should be the name of the qry that the form is based on? When I go to debug, this is the line that is highlighted.

 

[jwindon]

The * (asterik) tells Access to count ALL the records in that query.

DCount("*","YOURQUERYNAME")


Include the quotations as illustrated.

Also, how does your query get its criteria?

A form? Or are you using brackets in the query grid?

[This message has been edited by jwindon (edited 11-05-2001).]

 

[Taxcop]

Using brackets in the query grid ([Enter last name].

 

[Jack Cowley]

Use DLookup before you run your query. Something like this:

If Not IsNull(DLookup("[LastName]", "YourQueryName", "[LastName] = '" & Me.[LastName] & "'")) Then
Run query
Else
Msgbox "That name does not exist."
End if

Change the names in the code to those that you are actually using and put this code in place of the code you have now.



[This message has been edited by Jack Cowley (edited 11-05-2001).]

 

[Taxcop]

Thanks for the help. It works except for one
glitch. After clicking the "OK" button on the
msg box the form still opens. Is there any way to stop the form from opening?