VBA SQL Error Handling 3

[accessisnew]

I want to run an SQL Select query in VBA to fetch one field in a row from the table using the primary key in the WHERE clause then perform an validation as to whether the Select found the record or didn't find the record.

Is there a simple If statement I can use to perform the validation?

CODE:
Private Sub Text34_Exit(Cancel As Integer)

On Error GoTo Err_Text34

Dim dbs As ADODB.Connection
Dim selectSQL As String

Set dbs = CurrentProject.Connection

selectSQL = "SELECT Tracking_Number
FROM t_CIOSP2_Awards WHERE" & _
"Tracking_Number=" & _
"'" & Text34 & "';"
dbs.Execute selectSQL
If (THIS IS WHERE I NEED HELP with record found) Then
msgbox "Record Exists"
Else
msgbox "Record not found"
End If

Exit_Text34:
Exit Sub

Err_Text34
MsgBox Err.Description
Resume Exit_Text34

End Sub

 

[snyperx3]

How about just using DLookup.

replace the entire sub with this

Private Sub Text34_Exit(Cancel As Integer)
     If Not IsNull(DLookup("[Tracking_Number]","[t_CIOSP2_Awards]","[Tracking_Number] = '" & Text34.Value & "'") Then
          MsgBox "Record Exists"
     Else
          MsgBox "Record not found"
     End If
End Sub

gotta love those domain aggregate functions =]

-Pete

 

[Golom]

The EXECUTE method should be used only for ACTION queries like INSERT, UPDATE, DELETE, ALTER, etc.
Try

Dim rs As New ADODB.Recordset
rs.Open selectSQL, dbs
If Not rs.EOF Then
    msgbox "Record Exists"
Else
    msgbox "Record not found"
End If
Set rs = Nothing

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[SkipVought]

Hi,

Try something like this

dim rst as recordset

set rst = new recordset

rst = dbs.Execute(selectSQL)

If (not rst.bof and not rst.eof) Then
    msgbox "Record Exists"
Else
    msgbox "Record not found"
End If

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]