VBA SQL query with different actions depending on result

Question

I am trying to do a query of a linked table. After I get the results, I need to do 1 of 2 things. Either say it passed or failed. Here is the code I have written. It doesn't give me any errors, nor does it run correctly. When I input a SN I know doesn't have any entries it doesn't run the second part (the fail part).

Private Sub SQL_Check()
Dim rs As DAO.Recordset
Dim sqlMax As String
Dim result As String

sqlMax = "Select count(1) FROM dbo_Event WHERE [AssemblyNo] = 'SYSCHATESTE'   and [SerialNo] = '" & Me.txtUnitNo & "' and [ProcessTyp] = 'SF1';"
Set rs = CurrentDb.OpenRecordset(sqlMax)

If rs.Fields.Count = 1 Then
        txtECCT.BackColor = vbGreen
        txtECCT.ForeColor = vbBlack
        txtECCT.Value = "Passed"
        GoTo la
        End If
If rs.Fields.Count = 0 Then
Set rs = Nothing
Set db = CurrentDb
Set rec = db.OpenRecordset("Select * from tblScannedParts")
rec.AddNew
rec("Inspector") = Me.txtUserId
rec("PO Number") = Me.txtWorkOrderNo
rec("Assembly") = Me.txtAssem
rec("SERIAL Number") = Me.txtUnitNo
rec("DateScanned") = CStr(Now())
rec("Result") = "Failed"
rec("Defect Type") = "Missing ECCT"
rec("Comments") = strFileContent
rec("Qty") = "1"
rec.Update
txtStatus.BackColor = vbRed
txtStatus.ForeColor = vbBlack
txtStatus.Value = "Failed"
MsgBox "Please take unit to NCM Cart for review.", vbCritical, "Unit Not Ready for DOF QA"
txtAssem.SetFocus
End If

la:
Set rs = Nothing
End Sub

The recordset rs is based on a SELECT which returns one field, so your If condition, rs.Fields.Count = 1, will always be True. — HansUp
– HansUp, Commented Apr 7, 2016 at 14:09
Thank you. I added in a variable. If rs.Fields.Count = 1 Then result = rs.Fields(0) End If and had the check done based off that. Seems to be working now. — Tim Eckmann
– Tim Eckmann, Commented Apr 7, 2016 at 14:15

HansUp · Accepted Answer · 2016-04-07 14:33:00Z

The issue is that the recordset rs is based on a SELECT which returns one field, so your If condition, rs.Fields.Count = 1, will always be True.

Conversely, the code within the If rs.Fields.Count = 0 Then block could never run because Fields.Count would never be zero. And actually, Access does not even evaluate that condition because you included GoTo la in the previous If block ... which quickly exits the procedure. Be wary of GoTo

If you want to base the action on a count of matching records, I think the logic should be simpler with DCount().

Dim strCriteria As String
Dim lngMatchCount As Long

strCriteria = "[AssemblyNo] = 'SYSCHATESTE' AND [SerialNo] = '" & Me!txtUnitNo.Value & "' AND [ProcessTyp] = 'SF1'"
Debug.Print strCriteria '<- inspect this in Immediate window; Ctrl+g will take you there
lngMatchCount = DCount("*", "dbo_Event", strCriteria)

If lngMatchCount > 0 Then
    ' do the 'Passed' thing
Else
    ' do the 'Failed' thing
End If

Collectives™ on Stack Overflow

VBA SQL query with different actions depending on result

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related