Display different messages depending on results of query

[jdanieluk]

Hey everyone, thanks in advance for your time and help. It is greatly appreciated to all of us still learning.

I have a problem that I cannot seem to figure out. I want to do a quick record check using a query. The query checks to make sure a record is present in a table based on user entry. I wrote this small test button but I cannot get it to work. When I execute the script the query opens and I enter the required information for looking up records in the table. All of that is working. The query will open and either display a file or not. The problem is the message box always displays the "No Items for this selection" message. I would like display one message if there is a record and another if there is not. Anyone have any suggestions?

Running on Windows Server 8/Access 2013



[
Private Sub Blank1_Click()

DoCmd.OpenQuery "Query1", acViewNormal

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No Items for this selection."
Else
MsgBox "Selection is good."
End If

DoCmd.Close acQuery, "Query1"

End Sub
]

[jdanieluk]

I think I see what one issue might be. I went back and looked at the query I use and it will display the same number of records for both instances. Normally, the query will find only one record based on the input. I just noticed that when it does not find a record the count is also one.

Is there a way to use = null or have the query so it does not show 1 of 1 for the record count when it has no information?

[jdanieluk]

Went back and added this line to the code to see what comes up;

MsgBox Me.RecordsetClone.RecordCount
If Me.RecordsetClone.RecordCount = 0 Then
...
Now when I run the script the initial message box displays zero for both instances. I am not sure why I am getting a zero count when the query is displaying 1 record.

[ssanfu]

Private Sub Blank1_Click()

    DoCmd.OpenQuery "Query1", acViewNormal   '<<-- opens a saved query


    'Me.RecordsetClone.RecordCount gets the record count of the current FORM,
   ' NOT the query record count!!

    If Me.RecordsetClone.RecordCount = 0 Then   '<<-- ME is a reference to the active form
        MsgBox "No Items for this selection."
    Else
        MsgBox "Selection is good."
    End If

    DoCmd.Close acQuery, "Query1"

End Sub

Try this

Private Sub Blank1_Click()
    Dim r As DAO.Recordset
    Dim RC As Long   '<<-- record count

    Set r = CurrentDb.OpenRecordset("Query1")
    r.MoveLast
    
    MsgBox r.RecordCount
    
    RC = r.RecordCount
    If RC = 0 Then
        MsgBox "No Items for this selection."
    Else
        MsgBox "Selection is good."
    End If

    r.Close
    Set r = Nothing
End Sub

[jdanieluk]

Thanks for the help Steve. I get a user defined type not defined for this line.

Dim r As DAO.Recordset

[jdanieluk]

Ok, I added reference to the VB Library and ran the code again.

I now get a 3061 error - too few parameters. expected 2;

Set r = CurrentDb.OpenRecordset("Query1")

My guess is because query1 asks the user two input questions to find if the record is there.

[jdanieluk]

I went back and changed query1 removing the user input questions. It is now a fixed selection resulting in a record found of 1. This verified my thoughts on the 3061 - too few parameters error.

Now running the script with a found record, I will get the correct result and display "selection is good"

But, if I change the query to not find a record, I get a run time 3021 error. No current record.

Stopping at this line.

r.MoveLast

[jdanieluk]

I ended up removing this line and it somewhat works.

r.MoveLast

The logic is sound. If no record the "No Items for this selection." is displayed and if there is a record then "Selection is good." is displayed.

Now my problem is I still need to have my original query1 that can take user input to verify there is actually a record.

[ssanfu]

Oops.... found some errors. Try this one.

You will have to open the IDE, then go to TOOLS/References and replace any MISSING references. I have A2010 (ver 14.0) and you have A2013 (ver 15.0 I think)
(Microsoft Office XX.0 Access database engine Object Library)


There are 3 buttons on the form.

1) "Blank 1" just checks "Query1" records.

2)Enter a value (from the sub form fields) in either Job ID or Period (a date) and click "Blank2".
This modifies "Query2" (a clone of Query1) that uses the 2 text boxes to filter "Query2".

3) If you then click "Blank3", the sub form will change to see what records/record count are returned.


So "Blank2" uses the saved query with criteria like your query1 that asks for input but gets the inputs for the controls on the form.
"Blank3" builds the query with criteria in code.


Entering a 2 in the Job ID and clicking "Blank3" will display 4 records.
Clicking on "Blank2" should have a message box with a 4 (records in the query).

[jdanieluk]

Steve, All three instances work perfectly.

But i still have a slight problem. I can get this working using the entry form. I was hoping to just have the query pop up an input box and ask the user to enter two field items to match an item in a table.

Have [ENTER CRITERIA1] and [ENTER CRITERIA2] in the query. Sorry maybe I wasn't clear enough about that.


Thanks for all the work, I appreciate it. I always get great help when i come to this forum.

[ssanfu]

ave [ENTER CRITERIA1] and [ENTER CRITERIA2] in the query.

I might have used an input box when I first started working with Access (Office 95). but learned it was easier and better to get the criteria from a form. Using controls on a form to enter/select criteria allows you to do some validation before the query executes. After all, the criteria MUST be entered sometime.....


Since I've never seen your dB or query, its the best I can do. At least you have options now..

Good luck with your project.....

[jdanieluk]

Great information Steve. You have me rethinking how i have some of the other queries being executed. Thanks again for your expertise.

[CJ_London]

why not just use

If DCount("*","Query1") = 0 Then '<<-- ME is a reference to the active form
MsgBox "No Items for this selection."
Else
MsgBox "Selection is good."
End If

[jdanieluk]

Thanks for the help Ajax. That will work if I had a fixed criteria that I was searching for in the query. The problem is the way I have it now, the query prompts for the user to enter criteria for two of the fields. As Steve had said it would be a better option to pop up an entry form for the user.

If I run the code you supplied, I get a Runtime error 2471 because it is looking for the user input in Query1 for the two fields; [ENTER CRITERIA1] and [ENTER CRITERIA2]. If I manually enter information in Query1 for in the field criteria that I know is present/or not, that code will work.

I will probably have to bite the bullet and insert a quick form for what i am trying to do.