Help with my SQL statement!!!!!!!!

[THEAF]

Im using vb and access to for my project. Im trying to use this code in order to show all the payments made to a specific staff.

Private Sub Command1_Click()
 
Dim strSQL As String
 
    strSQL = "SELECT * FROM Staff "
    strSQL = strSQL & "WHERE Firstname = " & txtName.Text
 
 Set dbmyDB = OpenDatabase("E:\skol\A2 Computing\Speedy Pizza database.mdb")
 Set rsStaff = dbmyDB.OpenRecordset(strSQL, dbOpenDynaset)
 
    If Not rsStaff.EOF Then rsStaff.MoveFirst
    lstRecord.Clear
 
    Do Until rsStaff.EOF
    lstRecord.AddItem rsStaff!Firstname & " " & rsStaff!Lastname & vbTab & rsStaff!StaffID
    lstRecord.ItemData(lstRecord.NewIndex) = rsStaff!StaffID
    rsStaff.MoveNext
    Loop
End Sub

I keep getting an error on the line " Set rsStaff = dbmyDB.OpenRecordset(strSQL, dbOpenDynaset)". It says 'Too few parameters. Expected one'
Im not very good with SQL statements so it's hard for me to solve. I would appreciate it if anyone has any good answers.
THANK U

[QVeen72]

Hi,

I guess, your First Name Field in Database must be text.. Wrap the name with Single Quotes:

strSQL = "SELECT * FROM Staff " 
strSQL = strSQL & " WHERE Firstname = '" & txtName.Text & "'"
 

Regards
Veena

[THEAF]

Hi,

I guess, your First Name Field in Database must be text.. Wrap the name with Single Quotes:

strSQL = "SELECT * FROM Staff " 
strSQL = strSQL & " WHERE Firstname = '" & txtName.Text & "'"
 

Veena

sorry i forgot to mention that i wanted to put txtSname.text after txtname.text so that it will show 1 person with that name and not anyone with the same first name. sorry for the trouble but how do i put this in. your improvement worked. but when i tried to fix it so that Sname comes up the list box just clears. Could you please help me.
strSQL = strSQL & " WHERE Firstname = '" & txtName.Text & "'"

[QVeen72]

Hi,

Is it that you want to query with both Firstname and secondname...?
Try this :

strSQL = "SELECT * FROM Staff " 
strSQL = strSQL & " WHERE Firstname = '" & txtName.Text & "'"
strSQL = strSQL & "  And LastName = '" & txtSName.Text & "'"
 
 

Regards
Veena

[THEAF]

Hi,

strSQL = "SELECT * FROM Staff " 
strSQL = strSQL & " WHERE Firstname = '" & txtName.Text & "'"
strSQL = strSQL & "  And LastName = '" & txtSName.Text & "'"
 
 

Regards
Veena

Again sorry about this but I'm getting the same error again on the line

 Set rsPayment = dbmyDB.OpenRecordset(strSQL, dbOpenDynaset)

that its too few parameters

[QVeen72]

Again sorry about this but I'm getting the same error again on the line

 Set rsPayment = dbmyDB.OpenRecordset(strSQL, dbOpenDynaset)

that its too few parameters

Hi,

Check the FieldNames in Database..

Regards
Veena