Checking for no rows returned on continuous form

[dejfatman]

Hi,

I am writing an application in which a continuous form is used to search for records using filters based on search parameters you supply. The continuous form provides you with a list of records of which you can hilight one and press a "Select" button on the bottom which fills in fields on a separate form.

My problem arises when you specify search parameters which return no rows and the search form's detail section is empty. I would like to close the form immediately with a "No rows found" warning message in such a case. I thought I should be able, in a load routine, to check whether any details have been returned. However, the form was created with the form wizard, so I am not using sql to fill the form with data, and there is no recordset (that I know of anyway) to check for EOF. I do not know how Access handles retrieving the information and don't know how to check for an EOF condition. Any suggestions?

 

[hsitz]

Access properties of the form's recordset object:

me.recordset.recordcount

will be 0 if no records, positive number if there are records.

Also, you could simply test

me.recordset.eof

-- Herb

 

[dejfatman]

Thanks,

OK, it sort of works now, but not quite. I have an event procedure on the form load which checks if no records were returned and, if so, displays a message and closes the form. But for some reason it doesn't actually execute when the form loads. If no rows are returned, the form stays open, but if I press the "Select" button, then my load event (display messagebox and close form) occurs. Do you know why? Here's the code -

Private Sub Form_Load()
If Me.Recordset.EOF = True Then
MsgBox "No records found", vbInformation, "Not Found"
DoCmd.Close acForm, "frmFind", acSaveNo
End If
End Sub

 

[hsitz]

I'm guessing you can't close the form within the middle of the load event. You might try putting the same code in the form's "on current" event. If you don't want the form to be visible, then adjust the form's visible property in the "on load" event: me.visible = false, and then change visibility to true in the "on current" event.

If you do that, you'd probably want to check the recordcount property in the current event, because otherwise user would be booted when they went to eof while browsing in form.

-- Herb

 

[dejfatman]

Thanks,

I'm really a mainframer who got stuck developing an Access application. I never had done anything in Access or VB until a week ago. Anyways, I'll try out your suggestion.