Using query results in If ...Then

[Kurt]

I have a parameter query which shows all records
containing a null value for a particular ID (the
parameter). For example, the query shows that ID 00068
has three missing values (Rspns):

RspnsID QstnID Rspns
00068 4
00068 9
00068 12

When the user tries to leave a subform, I would like to
run this query and, if there are any records with null
values for the ID he's on, show a message telling the
user to complete the missing information, prevent the
record from being saved, and keep the form open on the
record (e.g., if he tried to close the form or move to
another record).

I assume the code would go in the Unload or BeforeUpdate
event of the subform, but I'm not sure of the syntax.
Here's what I have so far:

------------------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)

DoCmd.OpenQuery "qryNullResponses"

IF *query shows any records with null values for this
ID* THEN
MsgBox "You cannot save the data until you fill
in all of the questions. " & _
"Use the missing value (-9) if needed.",
vbCritical, "Missing Data"

DoCmd.RunCommand acCmdUndo
Else
'do nothing
End If
End Sub
-----------------------------------------------

What's the proper syntax to use to make use of the
results of the query? Thanks.

Kurt

 

[Steve Schapel]

Kurt,

One way would be to use a domain function (by the way, in any case it
does not achieve anything to open the query)...

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("*","qryNullResponses") > 0 Then
Cancel = True
MsgBox "You cannot save the data until you fill
in all of the questions. " & _
"Use the missing value (-9) if needed.",
vbCritical, "Missing Data"
End If
End Sub

- Steve Schapel, Microsoft Access MVP

 

[Kurt]

Perfect. The only thing I changed was the Cancel = True
to DoCmd.CancelEvent. (Cancel = True was giving me
a "Variable not defined" error.) Thanks.

Kurt

 

[Steve Schapel]

Kurt,

Glad you've got it working. Jost for the record, Cancel = True is the
correct method. The only way I can think of that you would get the
error you mentioned is a mis-spelling.

- Steve Schapel, Microsoft Access MVP

 

[Kurt]

Jost for the record, Cancel = True is the correct

method. The only way I can think of that you
would get the error you mentioned is a mis-spelling.

That's what I thought, and I'm using Cancel = True
elsewhere in the database without problem. But for some
reason if I use it in the code I've come up with I get
the "Variable not defined" compile error. Debugging
highlights the Cancel = True line.

If you'd like to see the full code, see my other recent
post in Forms Programming (as of now it's the my recent
reply to the thread):

Subject: Re: Prevent save if all fields not answered
From: "Kurt" <[email protected]>
Sent: 12/15/2003 11:52:18 AM

You'll see that I use the DoCmd.CancelEvent line three
times in my code. If I replace any of these with Cancel =
True, I'll get the error. Strange. Thanks for your help.

Kurt

 

[Steve Schapel]

Kurt,

Oh, I see, you have moved the code to another event! In the
BeforeUpdate event, the Cancel variable is defined, i.e.
Private Sub Form_BeforeUpdate(**=>> Cancel <<=** As Integer)

- Steve Schapel, Microsoft Access MVP