No Value Given for one or more required parameters

[LLazuta]

I am fairly new to Vba and have been trying to implement a Select
statement with 2 criterias but i keep getting the same error No value
given for one or more ewuired parameters. Any Help will be much
appreciated. This is my code as follows:

Dim myConnection As ADODB.Connection
Set myConnection = CurrentProject.Connection
Dim RS As New ADODB.Recordset
RS.ActiveConnection = myConnection
RS.Open "SELECT * FROM Attendance WHERE RS!ID = '" &
Form_Attendance.CmbID.Value & "' AND RS!DATE = '" &
Form_Attendance.TxtDate.Value & "'", , adOpenDynamic, adLockOptimistic

 

[Ken Snell \(MVP\)]

Don't use RS as a table name in the query's SQL statement:

RS.Open "SELECT * FROM Attendance WHERE ID = '" &
Form_Attendance.CmbID.Value & "' AND [DATE] = '" &
Form_Attendance.TxtDate.Value & "'", , adOpenDynamic, adLockOptimistic


Also, I note that you're using Date as the name of a field in a table. They
and many other words are reserved words in ACCESS and should not be used for
field names, etc. See these Knowledge Base articles for more information
about reserved words and characters that should not be used:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


See this site for code that allows you to validate your names as not being
VBA keywords:

basIsValidIdent - Validate Names to Make Sure They Aren't VBA Keywords
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=18#18

 

[LLazuta]

Thanks again for spending the time with this. Well I did all your
suggestions and now the error changed to "Data type mismatch in
criteria expression". If I take out the second criteria and execute it
only with ID it works perfectly. This is my revised code as follows:

RS.Open "SELECT * FROM Attendance WHERE ID = '" &
Form_Attendance.CmbID.Value & "' AND [DT] = '" &
Form_Attendance.TxtDate.Value & "'", , adOpenDynamic, adLockOptimistic

 

[Ken Snell \(MVP\)]

You'll need to use # delimiter for a date value, and explicitly format the
date to US format. Try this:

RS.Open "SELECT * FROM Attendance WHERE ID = '" &
Form_Attendance.CmbID.Value & "' AND [DT] = #" &
Format(Form_Attendance.TxtDate.Value, "\#mm\/dd\/yyyy\#"), , adOpenDynamic,
adLockOptimistic


--

Ken Snell
<MS ACCESS MVP>
https://mvp.support.microsoft.com/profile/Kenneth.Snell
www.cadellsoftware.org

 

[Ken Snell \(MVP\)]

Sorry - left in an extra # character -- corrected code line:

RS.Open "SELECT * FROM Attendance WHERE ID = '" &
Form_Attendance.CmbID.Value & "' AND [DT] = " &
Format(Form_Attendance.TxtDate.Value, "\#mm\/dd\/yyyy\#"), , adOpenDynamic,
adLockOptimistic


Also, if ID field is numeric, remove the ' characters that you're using as
delimiters:

RS.Open "SELECT * FROM Attendance WHERE ID = " &
Form_Attendance.CmbID.Value & " AND [DT] = " &
Format(Form_Attendance.TxtDate.Value, "\#mm\/dd\/yyyy\#"), , adOpenDynamic,
adLockOptimistic
--

Ken Snell
<MS ACCESS MVP>
https://mvp.support.microsoft.com/profile/Kenneth.Snell
www.cadellsoftware.org

You'll need to use # delimiter for a date value, and explicitly format the
date to US format. Try this:

RS.Open "SELECT * FROM Attendance WHERE ID = '" &
Form_Attendance.CmbID.Value & "' AND [DT] = #" &
Format(Form_Attendance.TxtDate.Value, "\#mm\/dd\/yyyy\#"), ,
adOpenDynamic, adLockOptimistic


--

Ken Snell
<MS ACCESS MVP>
https://mvp.support.microsoft.com/profile/Kenneth.Snell
www.cadellsoftware.org

Thanks again for spending the time with this. Well I did all your
suggestions and now the error changed to "Data type mismatch in
criteria expression". If I take out the second criteria and execute it
only with ID it works perfectly. This is my revised code as follows:

RS.Open "SELECT * FROM Attendance WHERE ID = '" &
Form_Attendance.CmbID.Value & "' AND [DT] = '" &
Form_Attendance.TxtDate.Value & "'", , adOpenDynamic, adLockOptimistic

 

[LLazuta]

Well I tried that code and now a new error showed up: "Syntax error
(missing operator) in Query expression 'ID = 'A12345' and [DT] =
##01/03/2007#'.

 

[LLazuta]

Well I tried that code and now a new error showed up: "Syntax error
(missing operator) in Query expression 'ID = 'A12345' and [DT] =
##01/03/2007#'.

Ops i didn't see your post. That last part did the trick, thanks a
million.