SQL-query in VB with Data1.recordset 1

[Perra]

Why doesn´t this code work?

Private Sub cmdSok_Click()

Dim sql As String
skivnr = 0
sql = "SELECT Titel FROM Skiva WHERE Snr = '" & Val(txtsnr.Text) & "'"
Data1.Refresh
Data1.RecordSource = sql
With Data1.Recordset
Do While Not .EOF
.FindFirst (sql)
If .NoMatch Then
MsgBox ("Ingen träff!&quot
Exit Do
Else
List1.AddItem !Titel
.MoveNext
End If
Loop
End With

End Sub

The run-time compiler/debugger says: "Syntax error in expression"



Grateful for tek-tips

/Perra

 

[flaubert]

I assume that the error is occurring on the forllowing line:

sql = "SELECT Titel FROM Skiva WHERE Snr = '" & Val(txtsnr.Text) & "'"

Is Snr a text field or a numeric field? If it's numeric then you will get the above error because you have put quotes around the value you are searching for. ie.

use:
WHERE Snr = 6
when Snr is a numeric field, and:
WHERE Snr = '6'
when Snr is a text field.

Furthermore, consider whether you actually need the Val command (you don't if txtsnr.Text is just a number) and whether you have the lines

Data1.Refresh
Data1.RecordSource = sql

the right way round.

Hope this helps - it's been a while since I used the data control...