By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,573 Members | 938 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,573 IT Pros & Developers. It's quick & easy.

VBA code for "No records found" in search

P: 36
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?
Apr 23 '07
Share this Question
Share on Google+
11 Replies


P: 36
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?
Apr 23 '07

MMcCarthy
Expert Mod 10K+
P: 14,534
Can you post the sql for the filter.
Apr 24 '07

P: 36
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
Apr 24 '07

MMcCarthy
Expert Mod 10K+
P: 14,534
For the mousewheel problem try this ...

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

This will disable the mousewheel
Apr 25 '07

P: 36
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?
Apr 25 '07

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Apr 25 '07

P: 36
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?
Apr 25 '07

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Apr 25 '07

P: 36
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
Apr 26 '07

Expert 100+
P: 344
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
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2. Dim rs As Recordset
  3. Set rs = Me.RecordsetClone
  4. If rs.RecordCount = 0 Then
  5.     MsgBox "No Data, Try again"
  6.     DoCmd.Close
  7. End If
  8. End Sub
  9.  
Apr 26 '07

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

Thanks anyway
Apr 27 '07

Post your reply

Sign in to post your reply or Sign up for a free account.