Error 3061 to few parameters expected 1

[Guest]

This is driving me potty ! Can anyone tell me what is going wrong please

SQLstr = "SELECT * FROM tblDrawingsIssued WHERE
tblDrawingsIssued.ContractNumber" _
& "=" & TarContractNumber & _
"AND tblDrawingsIssued.DrawingNumber" _
& "=" & TarDrawingNumber & ";"

variables are set from open form viz TarContractNumber = Me!ContractNumber

any help appreciated, this is part of my Open University course

 

[Guest]

Hi John,

Try inserting:
Debug.Print SQLstr

just after the line of code that sets this variable. Run the code and
examine the result in the Immediate window (open with Ctrl G). It appears as
if the inserted TarContractNumber is not separated from the keyword "AND"
properly. For example, if I set TarContractNumber = 3 and TarDrawingNumber =
4 (for demonstration purposes only) and then print your SQL statement to the
Immediate window, I get the following result:

SELECT * FROM tblDrawingsIssued WHERE tblDrawingsIssued.ContractNumber =3AND
tblDrawingsIssued.DrawingNumber=4;

The "3AND" part is not correct. Try this instead:

SQLstr = "SELECT * FROM tblDrawingsIssued WHERE " _
& "tblDrawingsIssued.ContractNumber=" _
& TarContractNumber _
& " AND tblDrawingsIssued.DrawingNumber" _
& "=" & TarDrawingNumber & ";"


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Thank you for your help
--
John

Hi John,

Try inserting:
Debug.Print SQLstr

just after the line of code that sets this variable. Run the code and
examine the result in the Immediate window (open with Ctrl G). It appears as
if the inserted TarContractNumber is not separated from the keyword "AND"
properly. For example, if I set TarContractNumber = 3 and TarDrawingNumber =
4 (for demonstration purposes only) and then print your SQL statement to the
Immediate window, I get the following result:

SELECT * FROM tblDrawingsIssued WHERE tblDrawingsIssued.ContractNumber =3AND
tblDrawingsIssued.DrawingNumber=4;

The "3AND" part is not correct. Try this instead:

SQLstr = "SELECT * FROM tblDrawingsIssued WHERE " _
& "tblDrawingsIssued.ContractNumber=" _
& TarContractNumber _
& " AND tblDrawingsIssued.DrawingNumber" _
& "=" & TarDrawingNumber & ";"


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Hi John,

You're welcome. Using the Debug.Print statement in code is a very useful
debugging technique. For example, with your SQLstr statement, you should be
able to print the completed SQL statement to the immediate window, copy it,
and then paste it into the SQL view of a brand new query. If you've created
it properly in code, it should work just fine when run as a new query.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Tom

regretably still get the same error !

Here is the whole fragment

Private Sub DrawingNumber_Exit(Cancel As Integer)

Dim db As DAO.Database
Dim rec As DAO.Recordset

Dim SQLstr As String
Dim TarContractNumber As String
Dim TarDrawingNumber As String

TarContractNumber = Me!contractNumber
TarDrawingNumber = Me!DrawingNumber
Set db = CurrentDb

SQLstr = "SELECT * FROM tblDrawingsIssued WHERE " _
& "tblDrawingsIssued.ContractNumber=" _
& TarContractNumber _
& " AND tblDrawingsIssued.DrawingNumber" _
& "=" & TarDrawingNumber & ";"

Set rec = db.OpenRecordset(SQLstr, dbOpenDynaset)

End Sub

 

[Guest]

Hi John,

This part is a bit confusing to me, because Me!ContractNumber would likely
change as you scrolled through records. However, it is only set once, when
the form opens per a statement in your first post:

"variables are set from open form viz TarContractNumber = Me!ContractNumber"

I just threw together a quickie sample, using the indicated tablename and
fieldnames, and it seems to work fine for me. So, perhaps I can be of more
help if you can send a compacted and zipped copy of your database to me. If
you are interested, send your sample with a valid reply to address. My e-mail
address is
available at the bottom of the contributor's page indicated in my signature
below. Whatever you do, please do not post your e-mail address (or mine) to a
newsgroup message.

Also, if you click on the link on the page I mentioned, please change the
subject to "Error 3061 to few parameters expected 1" and include this
hyperlink in the body of the message:

http://www.microsoft.com/office/com...cess&mid=3d820397-b68e-469a-bf4f-1e3d987ff83d

That way, I'll know exactly which posting is referenced in your message.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________