Still needing help with code for message box when no records found

[JackieEVSC]

I am not fluent in VBA, so coding is very difficult for me! I could not figure out how to use the DCount, so I tried using DLookup to pass the value in a query field, but it's not working.

---In this example, "frm_RepairInfo-Asset" is a from based on the query "RepairInfo-SearchByAsset".
---I added a second query "RepairInfo-SearchByAsset2" that counts the records retrieved by the first query (the field is called "TestData").
---If "TestData" is zero, I want the form to go back to the Repair Menu on the Search tab and display a box that says "No matching records found."
---If "RepairInfo-SearchByAsset" HAS results, the value in the "TestData" in will be one or more, and I want the "frm_RepairInfo-Asset" form to open.

Here is my code:

Private Sub Command40_Click()
DoCmd.Close acForm, "frm_RepairMenu"
On Error GoTo fErr
DoCmd.OpenQuery "RepairInfo-SearchByAsset2", acViewNormal, acEdit
Dim SearchVal As String
SearchVal = DLookup("TestData", "RepairInfo-SearchByAsset2")
If SearchVal = "0" Then
DoCmd.Close acQuery, "RepairInfo-SearchByAsset2"
DoCmd.OpenForm "frm_RepairMenu", View:=acNormal, OpenArgs:="SearchRep"
MsgBox "Your search criteria found no matching records."
Else
DoCmd.OpenForm "frm_RepairInfo-Asset"
End If
Exit Sub
fErr:
DoCmd.OpenForm "frm_RepairMenu", View:=acNormal, OpenArgs:="SearchRep"
End Sub

This code is not working. I keep playing around with it, but I can't figure it out. (I know this is a comical effort on my part, but with limited VBA knowledge, this is all I can come up with.)

Can anyone point out my mistakes? Any help would be greatly appreciated.
Jackie

 

[pbaldy]

Try

If DCount("*", "[RepairInfo-SearchByAsset]") = 0 Then

 

[JackieEVSC]

I tried that, then tested the form. I first entered an asset number I knew existed, but it never opened the repair info form ... it stayed on the repair menu/search form. After that, clicking "Search by Asset" just caused the form to blink.

I know my novice workaround is clunky, and that someone who knows what they are doing would go about this in an entirely different (and much easier) way!

P.S. I'm now wishing I had gotten a degree in programming rather than networking!!

 

[pbaldy]

Can you attach a db that exhibits the problem? That should test the query for no records.

I often wish I had more networking knowledge!

 

[JackieEVSC]

I'm a little hesitant to post it. It contains student data (names, ID numbers, etc.), which we are sworn to protect with our lives. I don't want the black helicopters showing up at work!

I can tell you the query that "counts" the number of records (RepairInfo-SearchByAsset2) shows correctly (because it never closes ... and it should!). When I enter an asset number I know is in the db, the record count is 1 (or more). When I enter one that I know doesn't exist, the record count is 0. Regardless, the form with the repair info or the message box never come up ... I stay on the same screen and can't run the code on that button again. (The other buttons work after I run that code, but not that one in question.)

I changed 'If SearchVal = "0" Then' to 'If DCount("*", "[RepairInfo-SearchByAsset]") = 0 Then', but it still doesn't work. I tried changing the 'DLookup' to 'DCount', too, and that didn't work either.

 

[JackieEVSC]

(P.S. At this point, Paul, I would trade ALL my networking knowledge for a tenth of your programming knowledge!)

 

[pbaldy]

I'm confused (not unusual); the query returns 0 records or returns a record with a value of 0? If that's the case, try without the quotes:

If SearchVal = 0 Then

Your concern is well-placed, you shouldn't post anything with confidential info in it. If it comes to it, perhaps one with a few dummy records.

 

[JackieEVSC]

I'm explaining it poorly. I'll try again.

The form "frm_RepairInfo-Asset" is based on a query (RepairInfo-SearcyByAsset) that asks the user to enter an asset number. If it's never been sent in for repair, the query will have no results. If it HAS been sent in for repair, the results of this query will be 1 or more.

I created a second query (RepairInfo-SearchByAsset2) that has one field (TestData), a count of the records in RepairInfo-SearchByAsset.

What I need it to do is if TestData is NOT 0, open "frm_RepairInfo-Asset" (with the info from the RepairInfo-SearcyByAsset query.

If TestData IS 0, I need it to go back to the RepairMenu on the Search tab and display a message box that says "No records found."

Does that make more sense?

 

[pbaldy]

Have you tried without the quotes?

 

[JackieEVSC]

My stupidity is going to show here ... without quotes on which phrase?

 

[pbaldy]

As shown here:

If that's the case, try without the quotes:

If SearchVal = 0 Then

 

[pbaldy]

Oh, and

http://www.excelguru.ca/node/7

 

[JackieEVSC]

I figured it out, using an onload event on the form:

Dim rs As dao.Recordset
Set rs = Me.RecordsetClone
If rs.RecordCount < 1 Then
DoCmd.OpenForm "frm_RepairMenu", View:=acNormal, OpenArgs:="SearchRep"
MsgBox "No records found"
DoCmd.Close acForm, "frm_RepairInfo-Asset"

Else
txtDamageDesc = Replace(Nz(txtDamageDesc, ""), "'", "")
DoCmd.OpenForm "frm_RepairInfo-Asset"
rs.Close
End If

Set rs = Nothing

Thank you for all your suggestions