If no results returned via search to stay on search module (1 Viewer)

[joe789]

Hi Folks,

I have a search form that opens up as soon as the database is opened, if the search renders results those results are displayed in a different (results) form otherwise the "results" form opens but it is blank/void of any records but still useful in that the command buttons on the top of the form can be used to perform another search, open reports, enter a new record, etc...

My problem is that if nothing is returned in the search and that blank results form is opened and a user selects a command button from that results form in order to perform another search or enter a new record or browse records, the following message is returned:

Microsoft Access: You entered a expression that returns no value

This error NEVER happens if anything is returned in the search.

In attempting to figure out this issue, I noticed that I could perhaps use a DCount and if the DCount is greater than zero to open the results form otherwise I would simply open a prompt stating nothing is found to the user and to remain on the search module because all other functions can be gotten to via that module anyways ...

I am running into a problem with my code, not sure how to state this ... I tried a dozen different ways:


The original code is the following:

Private Sub SearchViaSSNCommandButton_Click()

DoCmd.OpenForm "MainForm", , , "[SSN] = Forms!SEARCHRECORDS!SSNTEXTBOX"

DoCmd.Close acForm, "SEARCHRECORDS"

End Sub

Again, the above works fine if result returned, but if no results returned I am trying to get a prompt to come up stating that fact to the user ... so I tried the following code and similar ones like it to no avail:


Private Sub SearchViaSSNCommandButton_Click()

IF DCount("*","maintable") = 0 Then
MsgBox ("No Records Match Your Search Criteria, Please Try Again!")
else
DoCmd.OpenForm "MainForm", , , "[SSN] = Forms!SEARCHRECORDS!SSNTEXTBOX"

DoCmd.Close acForm, "SEARCHRECORDS"
end if

End Sub

I think my issue is that I am not quite sure how to tell DCount exactly how to get the records I want it to count. The table in question is "maintable" but that isn't even what I want, I just want to determine if any results would be returned via the "DoCmd.OpenForm "MainForm", , , "[SSN] = Forms!SEARCHRECORDS!SSNTEXTBOX" portion.

Is there some easy way to do this? Or can I disable the warning of the "... you entered an expression with no value" ... I tried set warnings to off, it didn't work. The prompt doesn't harm or hurt anything or make anything work wrong per se, it is just I don't want the user to see it anytime no results are returned and they use command/navigation buttons of the main form that opens with no records returned as none meet the search criteria....


Thanks a lot for any help!

Joe

 

[pr2-eugin]

I am not sure If I am understanding this correctly.. As there is too much information, but at a quick glance.. So you want to tell the user if there is some records inside, if nothing in there a friendly statement.. but anyway close the SearchForm.. I am not entierly sure if this will work.. and I am finishing work so have no time to test this.. Try using..

DoCmd.OpenForm "MainForm", , , "[SSN] = Forms!SEARCHRECORDS!SSNTEXTBOX"
If Forms!MainForm.Count=0 Then MsgBox("NOTHING TO SEE HERE, move along people")
DoCmd.Close acForm, "SEARCHRECORDS"