Command Button error with query

[TC]

Ansers inline.

Hi All,

I create a button to grab the value from a FirstName and
LastName textboxes and do a search to see if the record
exits. But I'm having an error with this code.

The code is:

Dim rs As DAO.Recordset
Dim db as DAO.Database
Set db = currentdb
query = "SELECT User.FirstName,User.LastName FROM User
WHERE User.FirstName =" & Me.FirstName &" AND
User.LastName" & Me.LastName

Say the name is Fred Smith. Here is what your generated SQL will be
(omitting all the User. alias references, which are not required):

SELECT FirstName, LastName FROM User
WHERE FirstName =Fred AND LastNameSmith
^error ^error

What you need is:

SELECT FirstName, LastName FROM User
WHERE FirstName ="Fred" AND LastName ="Smith"

So:

query = "SELECT FirstName, LastName FROM User WHERE FirstName = """ &
Me.FirstName & """ AND LastName = """ & Me.LastName & """"

Also, you really should use rs.EOF (or even, rs.BOF AND rs.EOF) to determine
when the recordset is empty. rs.recordCount is not defined to be, "the
number of records in the recordset". Read-up in the online help, to see what
it really is. It >will work< here, but that is just by chance, as it were.

HTH,
TC

 

[mike]

Hi All,

I create a button to grab the value from a FirstName and
LastName textboxes and do a search to see if the record
exits. But I'm having an error with this code.

The code is:

Dim rs As DAO.Recordset
Dim db as DAO.Database


Set db = currentdb

query = "SELECT User.FirstName,User.LastName FROM User
WHERE User.FirstName =" & Me.FirstName &" AND
User.LastName" & Me.LastName
Set rs = db.OpenRecordset(query) // Error right here
It says too few
argument
If rs.RecordCount <=0 then
Msgbox "No record found ", vbokonly
rs.close
set rs = nothing
Exit Sub
End If

Thanks for the help...

mike

 

[mike]

Thanks.

mike

-----Original Message-----
Ansers inline.




Say the name is Fred Smith. Here is what your generated SQL will be
(omitting all the User. alias references, which are not required):

SELECT FirstName, LastName FROM User
WHERE FirstName =Fred AND LastNameSmith

^error ^error