Blank querry makes form vanish

[charliemopps]

So I have a forum based on a Query.
The Query is read only, nothing I can do about that, it is very complex and the order in which the records are displayed is important, so there's nothing I can do.
When the Query reaches 0 records the form blanks out... loses all it's controls. Even subforms.

I need a way around this. does anyone have any tricks for avoiding this problem?

 

[SOS]

In the Subform's Current event you could put a check to see how many records there are and then change recordset to a "bogus" record when the record count is zero.

 

[missinglinq]

I don't think the OP's just talking about the subform, I think he means the main form is empty including the subform control. When the a form is read-only, as yours is because of the underlying, read-only query, and that recordsource is empty, your form is going to exhibit the behavior you're seeing. My approach to solving this would be to check the recordsource for records, and if empty, either pop up a messagebox up informing the user of the problem or maybe closing the form and popping up a form that is identical in appearance, but which is unbound, and have an added label in the middle of the form telling the user that the recordset is empty.

 

[charliemopps]

In the Subform's Current event you could put a check to see how many records there are and then change recordset to a "bogus" record when the record count is zero.

I dunno how to count records in a Query with VB. Could you point me to some example code I can modify?

 

[SOS]

In the subform's Current Event put:

If Me.Recordcount = 0 Then
   Me.Parent.SomeControlToSetFocusOn.SetFocus
   Me.Parent.YourSubformCONTAINERName.Form.RecordSource = "Select * From YourBogusTable Where 1=1"
End If

That is untested, so I'm not 100% sure of how, or if, it will work.

 

[missinglinq]

As I said before, my approach would be to

Copy the main form
Make the copy unbound (i.e. remove the RecordSource in the Properties Sheet)
Go to Properties-Data and set Recordset Type to Snapshot
Remove, via the Properties Sheet, the Control Source for each control
Add a label to the form informing the user that there are no records
Go to Properties-Format and change the Caption to the same Caption as the original form (usually the form name)

Then use this code in the original form

Private Sub Form_Load()
If Me.Recordset.RecordCount < 1 Then
 DoCmd.Close
 DoCmd.OpenForm "LookAlikeForm", , , stLinkCriteria
End If
End Sub

where LookAlikeForm is the name of the form copy.

 

[charliemopps]

As I said before, my approach would be to

Copy the main form
Make the copy unbound (i.e. remove the RecordSource in the Properties Sheet)
Go to Properties-Data and set Recordset Type to Snapshot
Remove, via the Properties Sheet, the Control Source for each control
Add a label to the form informing the user that there are no records
Go to Properties-Format and change the Caption to the same Caption as the original form (usually the form name)

Then use this code in the original form

Private Sub Form_Load()
If Me.Recordset.RecordCount < 1 Then
 DoCmd.Close
 DoCmd.OpenForm "LookAlikeForm", , , stLinkCriteria
End If
End Sub

where LookAlikeForm is the name of the form copy.

I tried this, it doesn't seem to work.
This is exactly what I'm using:

Private Sub Form_Load()
Me.Requery
Debug.Print Me.Recordset.RecordCount
If Me.Recordset.RecordCount < 1 Then
    DoCmd.OpenForm "Frm_Fake"
    DoCmd.Close
End If
End Sub

The debug code does print 0 as the record count. But it just blanks out the current form and does not open the fake form or close the current form.

 

[charliemopps]

ok, now I'm REALLY confused.
I removed the DoCmd.Close thinking maybe it was closing the wrong form.
now the code opens the Main form twice. It's not opening the Frm_Fake at all.

ALSO: My if statements arent working right for some reason.
I have the Debug printing the recordcount is 1, and the if statement set to print from inside the if statement... so it should only print when it's 0... but sometimes it does it when it it's 1?

 

[gemma-the-husky]

use the form open event to stop the form opening

Private Sub Form_Open(Cancel As Integer)

if dcount("*",me.recordsource) = 0 then
 msgbox("no records")
 cancel = true
end if

end sub