VBA code for displaying MsgBoxs in a parameter query

Hello everyone:

This question is neither urgent nor important, but I post it just in case anyone knows (and wants to give a reply, of course XD).
I have little idea of VBA, but I have dared to write a little piece of code. It is as follows:
The query (qry) is a parameter query, with the Parameter query criteria (for the "ID" field): [Enter ID]. What I want is to display a msgbox telling whether the person is present in the database or not, after having launched a parameter query prompting the user to provide an ID (which is to be checked against the IDs already present in the database).
Nevertheless, when launched, the query do fire but there seems to be a problem with the first If statement line (If [BUSQUEDA PACIENTES].[HISTORIA] Is Not Null Then). What is the problem here?

Best regards!

re: VBA code for displaying MsgBoxs in a parameter query

Hello. limperger.

You will definitely obtain more simple and more reliable code, if you just follow Nico's suggestion and use a custom form instead of Access query parameter dialog.

Kind regards,
Fish

re: VBA code for displaying MsgBoxs in a parameter query

While agreeing completely with Nico & Fish, I appreciate there are times when you need to do things in non-standard ways. Also, learning is never misplaced.

You will have two fundamental problems with this approach.

1. Main problem - Opening a query displays it for the operator. This is not accessible programmatically (# records etc). To access this data the query needs to be opened as a recordset (which doesn't display to the operator of course).
2. Checking for a Null value in SQL uses X Is Null, but in VBA you need to use the IsNull(X) function.

re: VBA code for displaying MsgBoxs in a parameter query

Hello!

First and foremost, thank you for your comments. They're much appreciated. As you can see, I have little idea of VBA code. I even forgot about the Isnull function...
Secondly, this web provided (fontstuff.com) is excellent. I recommend it to anyone here.

Best regards