By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,587 Members | 1,047 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,587 IT Pros & Developers. It's quick & easy.

VBA code for displaying MsgBoxs in a parameter query

P: 66
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Comando551_Click()
  2.   Dim qry As String
  3.   qry = "BUSQUEDA PACIENTES"
  4.   DoCmd.OpenQuery qry
  5.   If [BUSQUEDA PACIENTES].[HISTORIA] Is Not Null Then
  6.     MsgBox ("This person is present in the DB")
  7.   Else
  8.     MsgBox ("This person is not present")
  9.   End If
  10. End Sub
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!
May 5 '08 #1
Share this Question
Share on Google+
3 Replies


FishVal
Expert 2.5K+
P: 2,653
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
May 5 '08 #2

NeoPa
Expert Mod 15k+
P: 31,485
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.
May 5 '08 #3

P: 66
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
May 6 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.