VBA code for "No records found" in search

[CrostonScottish]

Can anybody help? I have this code i have put together so that when i select values for my combo boxes which are linked to values in lookup tables, it opens my "frmriskass" with only the corresponding records based on a query i have built in to the code below which is applied to the search command button in my search form.

Private Sub Command4_Click()
DoCmd.OpenForm "frmriskass", acNormal, "qrysearchriskass", , acFormReadOnly
Me.Area = ""
End Sub

It works fine when there is a matching record, but the problem i have is when there is no corresponding record. All i get is a blank form whith no fields or command buttons on it.

How do i change the code so that if there are no corresponding records i will get a message box which says something like "No corresponding record, please search again" and instead of a blank form being opened i can click Ok and go back to my original search form to try again?

[CrostonScottish]

Sorry. Just realised i had code as acFormReadOnly but have changed this to acFormEdit so i can still input in to the appropriate records found, however if there is no corresponding record i know just get an empty form ready to be completed. Still need code so this doesn't happen.

Can anybody help? I have this code i have put together so that when i select values for my combo boxes which are linked to values in lookup tables, it opens my "frmriskass" with only the corresponding records based on a query i have built in to the code below which is applied to the search command button in my search form.

Private Sub Command4_Click()
DoCmd.OpenForm "frmriskass", acNormal, "qrysearchriskass", , acFormReadOnly
Me.Area = ""
End Sub

It works fine when there is a matching record, but the problem i have is when there is no corresponding record. All i get is a blank form whith no fields or command buttons on it.

How do i change the code so that if there are no corresponding records i will get a message box which says something like "No corresponding record, please search again" and instead of a blank form being opened i can click Ok and go back to my original search form to try again?

[MMcCarthy]

Can you post the sql for the filter.

[CrostonScottish]

The sql i have for this filter is below:

SELECT tblRiskAss.ReferenceNumber, tblRiskAss.RiskAssessorsName, tblRiskAss.[Hospice Premesis], tblRiskAss.Area, tblRiskAss.Date, tblRiskAss.[Hazard Group Name], tblRiskAss.[State Problem and Risk], tblRiskAss.[Risk Level], tblRiskAss.[Action Taken], tblRiskAss.[Management Status], tblRiskAss.[Person at Risk], tblRiskAss.[Risk Communicated To], tblRiskAss.[Assessment Review Period], tblRiskAss.[Frequency of Monitoring], tblRiskAss.AssessmentReviewDate, tblRiskAss.[Last Reviewed], tblRiskAss.NextReviewDate, tblRiskAss.Completed, tblRiskAss.ysnSentByMailToStaff
FROM tblRiskAss
WHERE (((tblRiskAss.RiskAssessorsName)=[Forms]![frmsearchriskass]![RiskAssessorsName]) AND ((tblRiskAss.Area)=[Forms]![frmsearchriskass]![Area]));

It filters for matching [Risk Assessors Name] and [Area].

Just not sure why it still brings up a blank form ready for data input if no matches for this filter or if there are matches you get the matching record plus a blank one to complete if you scroll with the mouse wheel.

Hope you can help.

Thanks

[MMcCarthy]

For the mousewheel problem try this ...

http://www.lebans.com/mousewheelonoff.htm

This will disable the mousewheel

[CrostonScottish]

Thanks for that. It will come in handy in other parts of my database.

I can't apply it to this as my search may find more than one record so being able to scroll is useful.

I just can't get my head around why the code with the filter i have either turns up all matching records plus one blank record or if there are no matches for the filters i have used to search for then all i get is a blank record.

Any ideas?

[MMcCarthy]

Thanks for that. It will come in handy in other parts of my database.

I can't apply it to this as my search may find more than one record so being able to scroll is useful.

I just can't get my head around why the code with the filter i have either turns up all matching records plus one blank record or if there are no matches for the filters i have used to search for then all i get is a blank record.

Any ideas?

Set the forms Allow Additions property to No

[CrostonScottish]

Thanks. I tried this but it doesn't make any difference. Grrrh! I still get the corresponding records displayed as the forms when i run the code but with addition of this one blank form ready for data entry. Or if there are no matches, just one blank form, ready for data entry.

Thanks for any more help you can give with this. Is it my code? or is it my filter?

[MMcCarthy]

Thanks. I tried this but it doesn't make any difference. Grrrh! I still get the corresponding records displayed as the forms when i run the code but with addition of this one blank form ready for data entry. Or if there are no matches, just one blank form, ready for data entry.

Thanks for any more help you can give with this. Is it my code? or is it my filter?

Honestly don't know why this is happening

[CrostonScottish]

I am a bit lost with it also. Thanks for all the bits of advice though. I suppose i can't solve every problem i encounter.

Thanks again

[Lysander]

I am a bit lost with it also. Thanks for all the bits of advice though. I suppose i can't solve every problem i encounter.

Thanks again

Just tried this, and I think it solves your problem. Set your form to not allow additions and then put the following in the onopen event of your form

Private Sub Form_Open(Cancel As Integer)
Dim rs As Recordset
Set rs = Me.RecordsetClone
If rs.RecordCount = 0 Then
    MsgBox "No Data, Try again"
    DoCmd.Close
End If
End Sub
 

[CrostonScottish]

Thanks for this. I have given it a try but no luck.

Thanks anyway