Count Query OnClose of Form

[Ian]

I'm trying to create an IfThenElse statement for an OnClose command of a form;

The query outputs a count of a field

If the count is =0 I want the vbYESNO msgbox to show.

I assume I need to DIM the results of the query somehow but I don't know how.

If [CountofApptID] = 0 Then
If MsgBox ("Change Recall", vbYESNO) = vbYES Then
........
Else
DoCmd.CloseForm
End If
Else
DoCmd.CloseForm
End If

 

[tkelley via AccessMonster.com]

If I understand correctly, you have a couple of options (maybe more).

1) Create a hidden Listbox with a rowsource of your query, CountOfApptID.
That list box will have only one column and row, which is your count.

On close, you'd:

Me.MyListBox.Requery
If Me.MyListBox.Column(0,0)="0" Then
If MsgBox ("Change Recall", vbYESNO) = vbYES Then
...
Endif
Endif

Or you could:

2) Use a recordset to load your query result into, then read the recordset.

(I like using listboxes)

I'm trying to create an IfThenElse statement for an OnClose command of a form;

The query outputs a count of a field

If the count is =0 I want the vbYESNO msgbox to show.

I assume I need to DIM the results of the query somehow but I don't know how.

If [CountofApptID] = 0 Then
If MsgBox ("Change Recall", vbYESNO) = vbYES Then
.......
Else
DoCmd.CloseForm
End If
Else
DoCmd.CloseForm
End If