Sql string problem

[Bob Groger]

Hello,

I have a form "timesheets" I am trying to validate data entry in looking for
possible duplicate records. I am trying to open a recordset to see if there
is another table entry with the same employee and date. The simplified code
I am using is:

Dim rst As DAO.Recordset, strEmpID As String
Set rst = CurrentDb.OpenRecordset(" SELECT * FROM timesheet WHERE
me.driver_id = timesheet.driver_id AND me.date = timesheet.date ")
Do Until rst.EOF
The Set rst line returns an error "Too few parameters. Expected:2"
As I understand it, this should return records where the form driver_id and
date = an existing record in the table.

Where am I going wrong here?

Bob Groger

 

[Jeff Boyce]

Bob

I think you have it turned around. Aren't you looking for records (in
[timesheet]) where timesheet.driver_ID = what's on your form?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Bob Groger]

Jeff, you are exactly right. I have reversed the statements and still get
the same error, so I will continue to work with this.

Thanks,
Bob G

Bob

I think you have it turned around. Aren't you looking for records (in
[timesheet]) where timesheet.driver_ID = what's on your form?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello,

I have a form "timesheets" I am trying to validate data entry in looking
for possible duplicate records. I am trying to open a recordset to see if
there is another table entry with the same employee and date. The
simplified code I am using is:

Dim rst As DAO.Recordset, strEmpID As String
Set rst = CurrentDb.OpenRecordset(" SELECT * FROM timesheet WHERE
me.driver_id = timesheet.driver_id AND me.date = timesheet.date ")
Do Until rst.EOF
The Set rst line returns an error "Too few parameters. Expected:2"
As I understand it, this should return records where the form driver_id
and date = an existing record in the table.

Where am I going wrong here?

Bob Groger

 

[Dirk Goldgar]

Hello,

I have a form "timesheets" I am trying to validate data entry in
looking for possible duplicate records. I am trying to open a
recordset to see if there is another table entry with the same
employee and date. The simplified code I am using is:

Dim rst As DAO.Recordset, strEmpID As String
Set rst = CurrentDb.OpenRecordset(" SELECT * FROM timesheet WHERE
me.driver_id = timesheet.driver_id AND me.date = timesheet.date ")
Do Until rst.EOF
The Set rst line returns an error "Too few parameters. Expected:2"
As I understand it, this should return records where the form
driver_id and date = an existing record in the table.

Where am I going wrong here?

You have to take your references to "me.<controlname>" out of the string
literal and, instead, build the values of the controls into the string.
Try this:

Set rst = CurrentDb.OpenRecordset( _
"SELECT * FROM timesheet " & _
"WHERE timesheet.driver_id = " & Me.driver_id & _
" AND timesheet.date = " & Format(Me.date, "\#mm/dd/yyyy\#")

That's assuming that driver_id is a numeric field, not text, and that
timesheet.date is a date/time field.

 

[TC]

Or in the unlikely but possible case where driver_id is text (not
numeric):

"WHERE timesheet.driver_id = """ & Me.driver_id & """" & _

HTH,
TC (MVP Access)
http://tc2.atspace.com