Help writing SQL in VBA

[HLCruz via AccessMonster.com]

I am creating some code for a button on a form and I seem to be having a
problem with the following "SQL" I've written in VBA. I tested it using a
Zip that I knew existed but keep getting the "Zip Not Found" message box.
I'm using some code I've found in a book that I've customized to y database.

Does anyone see a problem with what I've written so far? Thanks to anyone
who will look this over!


Dim conn As ADODB.Connection
Dim rs As New ADODB.Recordset

Set conn = CurrentProject.Connection
ssql = "Select * From MNZIPS Where ZIP=' " & Me.txtZip & " ' "
rs.Open ssql, conn, adOpenKeyset, adLockOptimistic
If rs.RecordCount = O Then
rs.Close
Set rs = Nothing
Set conn = Nothing
MsgBox "Zip Not Found"
Exit Sub

 

[Stefan Hoffmann]

hi,

too many spaces...

Dim rs As New ADODB.Recordset

Don't use this, cause it has a nasty "feature":

Dim rs As New ADODB.Recordset
Set rs = Nothing
'now it should be no more existing
If Not rs Is Nothing Then
MsgBox "dang"
End If

Use

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

instead.

ssql = "Select * From MNZIPS Where ZIP=' " & Me.txtZip & " ' "

Remove the spaces:

ssql = "SELECT * " & _
"FROM MNZIPS " & _
"WHERE ZIP = '" & Replace(txtZip.Value, "'", "''") & "'"


mfG
--> stefan <--

 

[Clifford Bass]

Hi,

The recordcount may not be getting set right away. Better to check for
BOF and EOF.

If rs.BOF and rs.EOF Then
' No records found
End If

Clifford Bass

 

[Albert D. Kallal]

Try:

if dcount ("*","MNZIPS","zip = '" & me.txtZip & "'") = 0 then
MsgBox "Zip Found"
end if


It is a truck load of less code.