Returning a message box if a form is blank 2

[Drisconsult]

Hello All

I have a parameter query that opens a report. I have a parameter query that opens a form.

I am aware of the On No Data property available for providing a message box when a user enters data into a parameter query and the report is empty.

Is there such a procedure for providing a message box when the user enters data into a parameter query and the form is empty?

Kind Regards
Terence
London

 

[maxhugen]

No. However, you could check in code first to see if there are any records, and throw up a msgbox if none.

Max Hugen
Australia

 

[Drisconsult]

Hello Maxhugen

Could you help me with the code for such a procedure?

Regards
Terence

 

[Remou]

You can check the form's recordcount in the Open Event.

 

[CharlieT302]

On the "On Open" event, you could try something like:

If Dcount("field","RecordSource")<1 then
Msgbox "No Data"
DoCmd.Close
else
goto Exit_sub

 

[Drisconsult]

Hello CharlieT302

Many thanks for your reply. I am getting an error with this code:

Private Sub Form_Open(Cancel As Integer)
If DCount("field", "RecordSource") < 1 Then
MsgBox "No Data"
DoCmd.Close
Else
GoTo Exit_sub
End If
End Sub

The error is:

Compile error
Label not found

What am I doing wrong here. I am just beginning my long path to acquiring VBA skills, so go gently with me.

Regards
Terence

 

[CharlieT302]

Drisconsult,

What exactly is the code you are using? Don't take the code literally. See below.

Code Syntax Example:
If DCount("field", "RecordSource") < 1 Then
MsgBox "No Data"
DoCmd.Close
Else
GoTo Exit_sub
End If

Function Parameters:
Field = is the field you are counting.
Recordsource = is the table or query containing the field.

Example:
Dcount("Emp_ID","tbl_Staff")

Also:
Msgbox "No Data"
You can replace the words No Data with any message you wish.

Does this help?

 

[Drisconsult]

Hello CharlieT302

Yes it does, and thank you so much for your understanding.

Regards
Terence

 

[Drisconsult]

Hello CharlieT302

This is the code I have used:

Private Sub Form_Open(Cancel As Integer)
If DCount("Class", "qrySUBJECT 01 DATA ENTRY") < 1 Then
MsgBox "You have entered an incorrect Class or the form is empty"
DoCmd.Close
Else
GoTo Exit_sub
End If
End Sub

But I am still getting an error with the line

GoTo Exit_sub

I am informed that the label has not been defined.

Regards
Terence

 

[PHV]

What about this ?

Private Sub Form_Open(Cancel As Integer)
If Me.Recordset.Count < 1 Then
  MsgBox "You have entered an incorrect Class or the form is empty"
  Cancel = True
End If
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Drisconsult]

Hello PHV

No, unfortunately not. I get an error message:
Object doesn't support this property or class

On the line:
If Me.Recordeset.Count < 1 Then

But many thanks for your help
Regards
Terence

 

[PHV]

You have a typo.

 

[Drisconsult]

Hello PHV

Many thanks for your help in this matter.

I pasted your suggestion and still obtained the same error message.

Private Sub Form_Open(Cancel As Integer)
If Me.Recordset.Count < 1 Then
MsgBox "You have entered an incorrect Class or the Form is empty"
Cancel = True
End If
End Sub

Where is the typo?

I have also tried the following:

Private Sub Form_Open(Cancel As Integer)
If Me.Recordset.Count < 1 Then
MsgBox "You have entered an incorrect Class or the Form is empty"
Cancel = -1
End If
End Sub

And get the same error message.
Regards
Terence

 

[CharlieT302]

Drisconsult,

I looked at your code. Two suggestions:

From your code:
If DCount("Class", "qrySUBJECT 01 DATA ENTRY") < 1 Then

Place brackets around the query name:
If DCount("Class", "[qrySUBJECT 01 DATA ENTRY]") < 1 Then

Also, if I understood your message, the Goto Exit_sub line gave you an error?

That line directs the code to a label called Exit_sub:, which should appear at the end of your code. If it doesn't exist, you will get an error. Be sure to place a colon at the end.

Try this:

Private Sub Form_Open(Cancel As Integer)
If DCount("Class", "[qrySUBJECT 01 DATA ENTRY]") < 1 Then
MsgBox "You have entered an incorrect Class or the form is empty"
DoCmd.Close
Else
GoTo Exit_sub
End If
End Sub

Exit_sub:

 

[PHV]

Terence, what happens if you remove the code from the Open event procedure and type the following in the Load event procedure ?
If Me.Recordset.Count < 1 Then
MsgBox "You have entered an incorrect Class or the Form is empty"
DoCmd.Close
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Drisconsult]

Really do appreciate the time taken over my problem from you all.

Hello CharlieT302
I pasted your suggestion and received the following error message:
"Compile error label not defined".
Regards
Terence

Hello PHV
I pasted your suggestion in the Load Event Procedure and received the following error message:
"Object dosen't support this property or method".
Regards
Terence

Is it something that I am doing wrong here? I have the feeling that the fault is mine and not yours.

 

[PHV]

OOps, sorry for the typo:
If Me.Recordset.[!]Record[/!]Count < 1 Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[CharlieT302]

Assuming you do not already have this corrected, could you paste the entire code you have entered in this post?

I am wondering if you do not have duplicate lines in your code. For example: all code will automatically start with a header such as:

Private Sub Form_Open(Cancel As Integer)
and end with
End Sub

(The exact line is dependent upon which event you are working with.)

If you paste the entire code line that appears here, including the Private Sub... line or End sub (and so forth) you may receive errors.

 

[Drisconsult]

Hello PHV

After I corrected the typo, bingo - success. This is the code I now have in the Load Event procedure:

Private Sub Form_Load()
If Me.Recordset.RecordCount < 1 Then
MsgBox "You have entered an incorrect Class or the Form is empty"
DoCmd.Close
End If
End Sub

When I enter the correct class such as 1A, I get the form with all its data. When I enter a class that doesn't exist, I get the message box.

Gentleman, may I thank you both for your contributions to my education. You have both given me insights into the wonderful world of VBA.

Regards
Terence
London

 

[Drisconsult]

Hello PHV

It never rains but it pours. I may have to abandon the procedure that uses the On Load procedure.

I have menus that open and close forms and reports. There is now a problem when loading a form that I have recently edited to cope with the incorrect class problem. The macros that I use to open and close the form are being bypassed.

I will return one of the forms back to its original state to see if I can load the form and at the same time close down the previous form. It's all good fun though!!

Regards
Terence