Display message when no records in Search 1

[vfisher]

I'm using Access 97. I have a main form that contains unbound fields used for search criteria and a search button. The search results will be displayed in a subform. The subform has a query as it's data source with the criteria matching to the unbound fields on the main form. When the search button is clicked, the subform is requeried.

This works correctly. However, I'd like to have a msgbox display if the result of the query is empty. I've tried doing this in the load and currents of the subform, but it appears that after the requery, these events don't fire.

any ideas how I can check the result of the query to see if the result is empty?

Thanks.

 

[Rolliee]

If you are doing an rs.OpenRecordset, then the rs.EOF and rs.BOF = true says there is not records in the set.

You should show some code to let people help you better.

rollie@bwsys.net

 

[vfisher]

Well, I'm not using code. I'm using an access query as the record source for my subform. The query is set up with the criteria matching the fields on the main form.

The problem is that when I requery the subform, it doesn't seem to go thru any of the events in the subform because no data is returned.

So, I'd like to check the subform from the search button on the mainform after the requery, but I can't figure out what to look at in the subform to determine that no records were returned.

If it helps, the sql code behind the query is:

SELECT tblPolicyMaster.PolicyNumber, tblPolicyMaster.PolicySuffix, tblPolicyMaster.RecordType, tblPolicyMaster.PolicyBranch, tblPolicyMaster.IssDeclineInd, tblPolicyMaster.CaseHeadOffice, tblPolicyMaster.CaseNumber, tblPolicyMaster.WriteTimestampDate, tblPolicyMaster.WriteTimestampTime, tblPolicyMaster.PrevPolNo, tblPolicyMaster.PrevPolSuffix, tblPolicyMaster.PrevRecordType, tblPolicyMaster.PrevIssueDate, tblPolicyMaster.PrevIssueAge, tblPolicyMaster.PrevPlanCode, tblPolicyMaster.PrevFaceAmount, tblPolicyMaster.HeadOfficeCode, tblPolicyMaster.PolAccount, tblPolicyMaster.AgreePrintInd, tblPolicyMaster.PlanCodeLiteral, tblPolicyMaster.ReinsuranceType, tblPolicyMaster.CompanyCode, tblPolicyMaster.InsuredLastName, tblPolicyMaster.InsuredFirstName, tblPolicyMaster.InsuredMiddleName, tblPolicyMaster.CessionStatusInd, tblPolicyMaster.AdminDiscrepancyInd, tblPolicyMaster.PurrChangeDate, tblPolicyMaster.PolicyStatus, tblPolicyMaster.PolicyStatusDate, tblPolicyMaster.PlanCode, tblPolicyMaster.OrigRatingClass, tblPolicyMaster.AgreePrintCtr, tblPolicyMaster.UlaUl2TradPolicyStatus, tblPolicyMaster.NaarEntryType, tblPolicyMaster.ReinDeathDate, tblPolicyMaster.ReinPaidToDate, tblPolicyMaster.LobSubsegment, tblPolicyMaster.AbrInd, tblPolicyMaster.LtcrInd, tblPolicyMaster.NfAmount, tblPolicyMaster.ReinstatementDate, tblPolicyMaster.TerminationDate, tblPolicyMaster.FirLtrDuration, tblPolicyMaster.LoadInd, tblPolicyMaster.GerGrr, tblPolicyMaster.CessionsCtr
FROM tblPolicyMaster
WHERE (((tblPolicyMaster.PolicyNumber) Like [Forms]![frmPolicyNoSearch]![PolicyNumber] Or (tblPolicyMaster.PolicyNumber)=[Forms]![frmPolicyNoSearch]![PolicyNumber]) AND ((tblPolicyMaster.PolicySuffix) Like [Forms]![frmPolicyNoSearch]![Suffix] Or (tblPolicyMaster.PolicySuffix)=[Forms]![frmPolicyNoSearch]![Suffix]));

 

[Rolliee]

I believe the only way to use 'unbound fields' is with code. If the datasource on a field on your form is blank, it is unbound. If not, it is bound.

rollie@bwsys.net

 

[vfisher]

the search fields on the main from are unbound. The user enters the values, then the query has as the criteria: Like [Forms]![frmBillingSearch]![EnteredPolicy].

 

[CosmoKramer]

I think this should work. In the On Click event of the search button, add a variable assignment:

Dim intCount as integer
[code]
Assign to it the count of records your query returns:
[code]
intCount = DCount("[PolicyNumber]","YourQueryName")
If intCount > 0 Then
   MsgBox ("No records were found....")
End If

 

[vfisher]

CosmoKramer - Thank you, Thank you!!!!

Seems so simple now - but I just couldn't figure out how to get to the query results. This works great!

 

[CosmoKramer]

Sorry about the typo.....

It should be:

If intCount = 0 Then

 

[upplepop]

What if you aren't using a button to do the search... I use a form that has a query as the Record Source and the query has an input box in the Criteria so the user enters the search value when the form loads. Any way I can have a "No results found" message box too?

 

[vfisher]

I couldn't get this to work quite the same way. However, if you have a field that should always have a value on your form and it is null, then display the message and close the form - or whatever you might want to do. I testing it in the "on current" event and it worked fine. Just need something like:

If IsNull(Me!field1) Then
MsgBox "no records were returned"
docmd.close
end if