Don't open form if no data (1 Viewer)

[RichO]

I have been able to find information on this regarding reports but not for forms...

I have a form based on a query. This form is opened from another form with a DoCmd.OpenForm command. If the query returns no data the form opens with an error message.

I would like to have the form not open if the query returns no records but is there an easy way to determine this? I could open the query as a recordset and check for EOF, but is there a better way?

Thanks

 

[ChrisO]

G’day RichO.

Don’t really know if it would be better but one thing you could try...

The second form is opened by some action on the first form.
It may look better to disable that action if the second form has no data and only enable it if it has data.

That could be done with something like this: -

Me.MyControl.Enabled = DCount("[FieldName]", "QueryName")

Hope that helps.

Regards,
Chris.

 

[ghudson]

Chris has a good suggestion but the user might be confused as to why they button will not work.

I use the following in my forms OnOpen event to test if the record source is empty, if true then I provide a message box to expain the problem then I close the form.

Private Sub Form_Open(Cancel As Integer)
        
    If Me.RecordsetClone.RecordCount = 0 Then
        MsgBox "There are zero records in the data source!", vbInformation, "No Records Found"
        DoCmd.Close acForm, Me.Name
    End If
    
End Sub

 

[ChrisO]

G’day ghudson and excellent point.

If going for the disabling of the control method I posted, it would be much better to change the control caption to something like “No Records To Display” and then disable it.

Thanks ghudson, I now have to go and make a few changes.

Edit:
Might change the Control Tip Text as well…
“Button disabled due to lack of interest on the part of the respondent.”
(Or words to that effect. )

Regards,
Chris.

 

[RichO]

That sounds like a better idea. I didn't realize you could DCount a query. I thought it was stricty for tables.

Thanks for the help guys!

 

[RichO]

I tried both suggestions with no luck.

When I use the DCount method I get an error: "Data type mismatch in criteria expression" and I have tried DCounting each of the 4 fields in the query as well as "*" with the same results.

When I try ghudson's method in the On Open event I get the same error I originally had, "Runtime error 2501, the OpenForm action was canceled." The On Open event of the second form does not even fire.

Any ideas?

Thanks

 

[ChrisO]

G’day RichO.

Can you cut the db down and post it in A97 please.

Regards,
Chris.

 

[Rich]

Use the OnLoad event, and don't use Cancel=True

 

[RichO]

The OnLoad event did not fire either.

ChrisO, I don't think I can successfully zip an example here. The query references a good number of tables and these tables are in a SQL server back end. It would take a load of work import the records and then scale down the tables to make a working example.

I will try opening the query as a recordset and checking EOF and see if that works.

Thanks again.

 

[Rich]

The OnLoad event did not fire either.


Thanks again.

How do you know? what's the code to open the form?

 

[RichO]

I put a MsgBox into the On Load and On Open events as a test to see if they are firing and the MsgBoxes never appear.