No Records for Form

[Bonnie]

Hello everyone! Using A02 on XP. Not a programmer, but learning every day.

I can use the OnNoData event on reports but how do I check for records when
a form opens? I have the following in my button that opens a form (no
subform, just the mainform called fViewOldAdminBill). It doesn't work. I
copied it from a 'no records in table' post. qViewOldAdminBill is the form's
query. I would like to click the button and either get the message 'no
records' or open the form with the records. Am I on the right track? The best
track?

If DCount("*", "qViewOldAdminBill") = 0 Then
MsgBox "There are no Admin Bill records for this contract."
DoCmd.CancelEvent
Exit Sub
Else: 'open the form language
End If

Thanks in advance for any help or advice on this!

 

[Ofer]

What doesn't work?
Put a code break and see where the code goes to.
It always return 0 records?
What the query qViewOldAdminBill based on, what is the filter?
Do you get an error message?

You don't need the line "DoCmd.CancelEvent", if this code placed on the
onclick event of a button then its enough writing exit sub.

 

[Jeff Conrad]

in message:

Hello everyone! Using A02 on XP. Not a programmer, but learning every day.

I can use the OnNoData event on reports but how do I check for records when
a form opens? I have the following in my button that opens a form (no
subform, just the mainform called fViewOldAdminBill). It doesn't work. I
copied it from a 'no records in table' post. qViewOldAdminBill is the form's
query. I would like to click the button and either get the message 'no
records' or open the form with the records. Am I on the right track? The best
track?

If DCount("*", "qViewOldAdminBill") = 0 Then
MsgBox "There are no Admin Bill records for this contract."
DoCmd.CancelEvent
Exit Sub
Else: 'open the form language
End If

Use the form's open event to test the recordset returned.
Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "There are no Admin Bill records for this contract." _
, vbInformation, "No Records"
Cancel = True
End If
End Sub


You will of course need to trap for and ignore Error 2501
in the code routine that opens the form.

 

[Bonnie]

Jeff! You are da bomb! EXACTLY what I was looking for. I knew it was
simple, just needed to be pulled back to the correct avenue. I have notes for
trapping errors so will use them to stop the 'open form action cancelled'
message. THX!!!

 

[Jeff Conrad]

in message:

Jeff! You are da bomb! EXACTLY what I was looking for. I knew it was
simple, just needed to be pulled back to the correct avenue. I have notes for
trapping errors so will use them to stop the 'open form action cancelled'
message. THX!!!

Sweeeeeet.
High-five back at you Bonnie.


Good luck with your project.

 

[richard harris]

hi bonnie, i have used the same code and it works, how can i get rid of the
message 'open form action cancelled'

really grateful thanks richard

 

[Bonnie]

Hi Richard,

Was running through old posts and saw your inquiry.

You run an if:

IIF Error = "3075" (ErrorNumber),"",MsgBox Err.Description