3061 - Too Few Parameters. Expected 2

Stormrider83

Computer Science Student
Local time
Today, 20:59
Joined
Jul 16, 2008
Messages
26
Hi,

I have looked for solutions to this but can't find one that exactly matches it so here goes. With the following code I get the error 3061 - Too few parameters. Expected 2:

Code:
Private Sub Command22_Click()
Dim lngPaint_Order_ID As Long
Dim strSQL As String
Set rs = New ADODB.Recordset
strSQL = "INSERT INTO Paint_Orders (Paint_Order, Supplier_Name, Paint_Color) VALUES ('" & Me.Text18 & "'," & Combo20 & ",'" & Combo24 & "')"
CurrentDb.Execute strSQL
strSQL = " SELECT Paint_Orders.Paint_Order_ID, Paint_Order.Paint_Orders FROM Paint_Orders WHERE Paint_Order_ID.Paint_Orders = '" & [Forms]![Paint_Order]![Text18] & "';"
Set rs = CurrentDb.OpenRecordset(strSQL)
lngPaint_Order_ID = Paint_Orders.Paint_Order_ID
rs.Close
Set rs = Nothing
strSQL = " INSERT INTO Paint_Order_Parts (Part_No, Paint_Order, Qty) " _
    & "SELECT Part_No, " & lngPaint_Order_ID & ", 0 " _
    & " FROM [Parts];"
CurrentDb.Execute strSQL
Text18.SetFocus
Text18.Text = ""
Me.Combo26.Requery
End Sub

The offending lines are:

Code:
strSQL = " SELECT Paint_Orders.Paint_Order_ID, Paint_Order.Paint_Orders FROM Paint_Orders WHERE Paint_Order_ID.Paint_Orders = '" & [Forms]![Paint_Order]![Text18] & "';"
Set rs = CurrentDb.OpenRecordset(strSQL)

I have no idea why its happening...
 
You've got a typos

Code:
strSQL = " SELECT Paint_Orders.Paint_Order_ID, [COLOR="Red"]Paint_Order[/COLOR].Paint_Orders FROM Paint_Orders WHERE [COLOR="red"]Paint_Order_ID.Paint_Orders[/COLOR]

Should be

Code:
strSQL = " SELECT Paint_Orders.Paint_Order_ID, Paint_Orders.Paint_Orders FROM Paint_Orders WHERE Paint_Orders.Paint_Order_ID
 
Code:
strSQL = " SELECT Paint_Orders.Paint_Order_ID , Paint_Order.Paint_Orders FROM Paint_Orders WHERE Paint_Order_ID.Paint_Orders = '" & [Forms]![Paint_Order]![Text18] & "';"
Set rs = CurrentDb.OpenRecordset(strSQL)

I have no idea why its happening...
This is largely due to your code beeing unreadable!!! Thus hard to maintain/debug.

Lets make the code readable and see what we can find!
Code:
strSQL = "" 
strSQL = strSQL & " SELECT Paint_Orders.Paint_Order_ID " 
strSQL = strSQL & " ,      [B]Paint_Order[/B].Paint_Orders " 
strSQL = strSQL & " FROM Paint_Orders " 
strSQL = strSQL & " WHERE [B]Paint_Order_ID[/B].Paint_Orders = '" & [Forms]![Paint_Order]![Text18] & "';"
Set rs = CurrentDb.OpenRecordset(strSQL)

Now I can see atleast 2 problems, the Tables Paint_Order and Paint_Order_ID do not excist in your query.... Therefor appear to be parameters to access.
 
I had the table and fields the wrong way round! :o That fixed I still have the same error message but much tidier code:

Code:
strSQL = ""
strSQL = strSQL & " SELECT Paint_Order_ID.Paint_Orders "
strSQL = strSQL & " ,      Paint_Order.Paint_Orders "
strSQL = strSQL & " FROM Paint_Orders "
strSQL = strSQL & " WHERE Paint_Order_ID.Paint_Orders = '" & [Forms]![Paint_Order]![Text18] & "';"
Set rs = CurrentDb.OpenRecordset(strSQL)
lngPaint_Order_ID = Paint_Order_ID.Paint_Orders

The table is Paint_Orders and has the fields, Paint_Order and Paint_Order_ID
 
strSQL = strSQL & " SELECT Paint_Order_ID.Paint_Orders "

that should be strSQL = strSQL & " SELECT Paint_Orders.Paint_Order_ID "

also you have the same problem in the WHERE part

the format is tablename.fieldname and not the other way round.
 
strSQL = strSQL & " FROM Paint_Orders "
[Forms]![Paint_Order]![Text18]
I am noticing two things here! That are NOT good practice.

1) You are not using a naming convention, your form and table have the same name. Forms should be prefixed by frm and tables by tbl. Likewise queries by qry. Etc.
This makes things much more clear

2) Controle names ! Give your controles usefull names and dont leave them as "Text18" again this is maintenance-HELL
 
Youre right I am confusing myself! Right I have got the select statement working correctly as follows for anyone who is interested. One of the problems was I used ' instead of # for a date field!

Code:
strSQL = ""
strSQL = strSQL & " SELECT Paint_Orders.Paint_Order_ID, "
strSQL = strSQL & " Paint_Orders.Paint_Order "
strSQL = strSQL & " FROM Paint_Orders "
strSQL = strSQL & " WHERE Paint_Orders.Paint_Order = #" & [Forms]![Paint_Order]![Text18] & "#;"
Set rs = CurrentDb.OpenRecordset(strSQL)

I am now getting object required for this line:

Code:
lngPaint_Order_ID = Paint_Orders.Paint_Order_ID

The whole code being:

Code:
Private Sub Command22_Click()
Dim lngPaint_Order_ID As Long
Dim strSQL As String
Set rs = New ADODB.Recordset
strSQL = ""
strSQL = strSQL & "INSERT INTO Paint_Orders"
strSQL = strSQL & "(Paint_Order, Supplier_Name, Paint_Color) VALUES ('"
strSQL = strSQL & Me.Text18 & "'," & Combo20 & ",'" & Combo24 & "')"
CurrentDb.Execute strSQL
strSQL = ""
strSQL = strSQL & " SELECT Paint_Orders.Paint_Order_ID, "
strSQL = strSQL & " Paint_Orders.Paint_Order "
strSQL = strSQL & " FROM Paint_Orders "
strSQL = strSQL & " WHERE Paint_Orders.Paint_Order = #" & [Forms]![Paint_Order]![Text18] & "#;"
Set rs = CurrentDb.OpenRecordset(strSQL)
lngPaint_Order_ID = Paint_Orders.Paint_Order_ID
rs.Close
Set rs = Nothing
strSQL = ""
strSQL = strSQL & " INSERT INTO Paint_Order_Parts (Part_No, Paint_Order, Qty) "
strSQL = strSQL & " SELECT Part_No, " & lngPaint_Order_ID & ", 0 "
strSQL = strSQL & " FROM [Parts];"
CurrentDb.Execute strSQL
Text18.SetFocus
Text18.Text = ""
Me.Combo26.Requery
End Sub
 
Every table and form I have added have both but I inherited the db from someone else and its huge! I am slowly going through getting things sorted! I shall do so before posting a request for help though as I forget I only find it easy to read because I have been staring at it for months!

I am noticing two things here! That are NOT good practice.

1) You are not using a naming convention, your form and table have the same name. Forms should be prefixed by frm and tables by tbl. Likewise queries by qry. Etc.
This makes things much more clear

2) Controle names ! Give your controles usefull names and dont leave them as "Text18" again this is maintenance-HELL
 
Last edited:
No need to change anything in posting ONLY... As long as you normaly do it... An inherited DB can be a nightmare sometimes.

Dont just randomly cut up the SQL tho... There is reason to cutting it up, not just random-cutting

i.e.
Code:
strSQL = ""
strSQL = strSQL & "INSERT INTO Paint_Orders"
strSQL = strSQL & "(Paint_Order, Supplier_Name, Paint_Color) VALUES ('"
strSQL = strSQL & Me.Text18 & "'," & Combo20 & ",'" & Combo24 & "')"
Would be more readable IMHO:
Code:
strSQL = ""
strSQL = strSQL & "INSERT INTO Paint_Orders"
strSQL = strSQL & "       (Paint_Order        , Supplier_Name , Paint_Color) " 
strSQL = strSQL & "VALUES ('" & Me.Text18 & "'," & Combo20 & ",'" & Combo24 & "')"

neatness counts !
 
Thanks for the advice!

I am also still desperately struggling with the object required error!
 
Thanks for the advice!

I am also still desperately struggling with the object required error!

Ah sorry didnt spot that problem...

What are you trying to do?? Use the field out of the query right??? So why use the table name??

You have to use the name of the recordset that you opened to retrieve the data within it.
 
I am trying to insert data into a table. The data generates an autonumber.

The code I am having an issue with is meant to find the record I just inserted, get the autonumber and use it to populate a link (reference) table.

Its not based off a query its just a SELECT statement. Would it help basing it off a query?

Ah sorry didnt spot that problem...

What are you trying to do?? Use the field out of the query right??? So why use the table name??

You have to use the name of the recordset that you opened to retrieve the data within it.
 
If you NOT use an Insert query but use ADO to insert the record into the recordset/table. Then you can retrieve that key value right there.
 
How would I Do that?

I tried using ADO but Access 2007 doesnt seem keen on it!

If you NOT use an Insert query but use ADO to insert the record into the recordset/table. Then you can retrieve that key value right there.
 
I am FAR from comfertable with ADO... I much prefer the access native DAO.

But type in "Addnew" into a module and hit F1, read all about it in the access help.
 

Users who are viewing this thread

Back
Top Bottom