Prevent opening a blank form (table)

B

Bonnie

I have a command button on a form that opens a different form (filtered data)
which works fine, but when there are no records in the filtered table I would
like to say so and not open the form. Can you tell me how?

Thanks in advance.
 
S

Sergey Poberezovskiy

Before opening the form, open a recordset with the same filter and check for
RecordCount. Then if no records - just call MsgBox function, otherwise - open
form.
 
L

Larry Linson

"Bonnie" wrote
I have a command button on a form
that opens a different form (filtered data)
which works fine, but when there are no
records in the filtered table I would
like to say so and not open the form.
Click to expand...

In the Command Button's click event, a DoCmd.OpenForm is executed (if the
Button was built it with the Wizard, and likely, even if it was not). Add
VBA code to make execution of that statement conditional on a DCount domain
aggregate statement, using the same conditions as the filter you will use on
the Form -- if it returns a count of 0, do not open the Form.

I gather this must _not_ be the Form that would also be used to enter new
Records.

Larry Linson
Microsoft Access MVP
 
B

Bonnie

That's what I'm talking about. But could you be so kind as to show me the
syntax/code pretty please.

Thanks so much.
 
J

John Vinson

That's what I'm talking about. But could you be so kind as to show me the
syntax/code pretty please.
Click to expand...

Please post your current code (knowing how you're setting up the
filter will make it much easier to answer).

John W. Vinson[MVP]
 
B

Bonnie

Here it is (I converted a macro). I am not doing any data entry on this form
- just for taking a look at back-ordered items. I'm just not sure how to use
DCount in a sentence! Thanks for your help.

Function OpenBO()
On Error GoTo OpenBO_Err

DoCmd.OpenForm "LogOutBO", acFormDS, "",
"[LogOutBO]![Department]=[Forms]![SelectOut]![Combo8]", acReadOnly, acNormal


OpenBO_Exit:
Exit Function

OpenBO_Err:
MsgBox Error$
Resume OpenBO_Exit

End Function
 
J

John Vinson

Here it is (I converted a macro). I am not doing any data entry on this form
- just for taking a look at back-ordered items. I'm just not sure how to use
DCount in a sentence! Thanks for your help.
Click to expand...

Try this:

Function OpenBO()
On Error GoTo OpenBO_Err
Dim strCrit As String

strCrit = "[LogOutBO]![Department]='" _
& [Forms]![SelectOut]![Combo8] & "'"
If DCount("*", "<tablename>", strCrit) > 0 Then
DoCmd.OpenForm "LogOutBO", acFormDS, "", strCrit, _
acReadOnly, acNormal
Else
MsgBox "No records found"
End If

OpenBO_Exit:
Exit Function

OpenBO_Err:
MsgBox Error$
Resume OpenBO_Exit

End Function

Replace <tablename> with the name of the table upon which LogOutB0 is
based. I'm assuming that Department is a Text field; if it's not leave
off the ' and the "'" in the expression setting strCrit.

John W. Vinson[MVP]
 
Top