Re-prompt search criteria if no result

[mhk]

I have a form that is based on a search query where a prompt is displayed for the user to enter their search criteria. The form then displays the associated records with that search criteria. This is working great with one exception. If the user enters no criteria or searches for something that does not exist in the database, the form opens and displays no record.

Ideally I would like to handle the case where the search criteria doesn't exist, the form does not load, but pop-up box saying no records found to match the criteria, and requesting new search criteria to be entered.

I'm not sure how/where to handle this. Can anyone make a suggestion?

Thanks
MK

 

[wazz]

nice thing about stored queries is they're always up-to-date. you could do a dcount on a field in the query; if it's > 0 you're good to go.

 

[mhk]

Thanks Wazz...I'm still fairly new to VBA. Would I do this on the form load event? Any chance you have some sample code that would be helpful?

Thanks
MK

 

[namliam]

You can use the "on open" event of the form you are opening to retrieve the count of your query...
Like Wazz said you can use a DCOunt or a recordsetcount. In this case a Dcount will do.

Lookup DCount in the Access help and see how far you can get.

 

[Rich]

Private Sub Form_Open(Cancel As Integer)
If (RecordsetClone.RecordCount) = 0 Then

MsgBox "There are no records to view"
DoCmd.Close acForm, Me.Name
DoCmd.OpenForm (YourCriteriaForm)
End If
End Sub

 

[mhk]

Hi Rich,

I finally had a chance to use this code but when I'm getting a run time error. The error states "The Action Method requires a Form Name Argument. Here is a cody of my code.


Private Sub Form_Open(Cancel As Integer)
If (RecordsetClone.RecordCount) = 0 Then
MsgBox "There are no records to view"
DoCmd.Close acForm, Me.Name
DoCmd.OpenForm (SearchQuery)
End If
End Sub

The SearchQuery is the name of my form.

Am I missing something?

Thanks
MK

 

[namliam]

DoCmd.OpenForm "SearchQuery"

 

[mhk]

That worked...Thanks for the help.

MK