Share via

Access VBA error "too few parameters expected 3"

Anonymous
2020-04-07T15:16:34+00:00

hello,

I have a bit of VBA code written to execute a parameter query but i keep getting "too few parameters expected 3." 

i made the query in query design and it works, so i changed the code to SQL and pasted into VBA.  i'm not very familiar with using SQL in VBA, so any help is greatly appreciated!

Set db = CurrentDb

Set rst = db.OpenRecordset("SELECT tbl_TireGroupsA.[Test Plan#], tbl_TireGroupsA.Group, tbl_LoadDeflectionA.[Deflection ID], tbl_TireGroupsA.[Cat#], tbl_TireGroupsA.Size," & _

"tbl_TireGroupsA.Brand, tbl_TireGroupsA.Line, tbl_TireGroupsA.[LI/PR], tbl_TireGroupsA.[Unique Features], tbl_TireGroupsA.[Tire Engineer], tbl_TireGroupsA.[Tire Serial#]," & _

"tbl_TireGroupsA.[Design SW], tbl_TireGroupsA.[Design OD], tbl_TireGroupsA.[Spec Weight], tbl_TireGroupsA.[Tire Location], tbl_TireGroupsA.[Preferred Wheel]," & _

"tbl_TestingA.[Test Type], tbl_TestingA.[Wheel Received], tbl_TestingA.[Tire Order#],tbl_TestingA.[Mount Date], tbl_TestingA.[Test Supervisor],tbl_TireGroupsA.[Tire Engineer]," & _

"tbl_LoadDeflectionA.[Test Load], tbl_LoadDeflectionA.[Test Inflation], tbl_TestingA.[Test Location], tbl_LoadDeflectionA.[Load Type], tbl_LoadDeflectionA.[Required Testing]," & _

"tbl_LoadDeflectionA.[Job#] , tbl_LoadDeflectionA.Machine, tbl_LoadDeflectionA.[% of Load_1], tbl_LoadDeflectionA.[Test Load_1], tbl_LoadDeflectionA.Deflection_1," & _

"tbl_LoadDeflectionA.[% of Load_2], tbl_LoadDeflectionA.[Test Load_2], tbl_LoadDeflectionA.Deflection_2, tbl_LoadDeflectionA.[% of Load_3], tbl_LoadDeflectionA.[Test Load_3]," & _

"tbl_LoadDeflectionA.Deflection_3, tbl_LoadDeflectionA.[% of Load_4], tbl_LoadDeflectionA.[Test Load_4], tbl_LoadDeflectionA.Deflection_4, tbl_LoadDeflectionA.[% of Load_5]," & _

"tbl_LoadDeflectionA.[Test Load_5], tbl_LoadDeflectionA.Deflection_5, tbl_LoadDeflectionA.[% of Load_6], tbl_LoadDeflectionA.[Test Load_6], tbl_LoadDeflectionA.Deflection_6," & _

"tbl_LoadDeflectionA.[% of Load_7], tbl_LoadDeflectionA.[Test Load_7], tbl_LoadDeflectionA.Deflection_7, tbl_LoadDeflectionA.[% of Load_8], tbl_LoadDeflectionA.[Test Load_8]," & _

"tbl_LoadDeflectionA.Deflection_8, tbl_LoadDeflectionA.[% of Load_9], tbl_LoadDeflectionA.[Test Load_9], tbl_LoadDeflectionA.Deflection_9, tbl_LoadDeflectionA.[% of Load_10]," & _

"tbl_LoadDeflectionA.[Test Load_10], tbl_LoadDeflectionA.Deflection_10, tbl_LoadDeflectionA.[% of Load_11], tbl_LoadDeflectionA.[Test Load_11], tbl_LoadDeflectionA.Deflection_11," & _

"tbl_LoadDeflectionA.[% of Load_12], tbl_LoadDeflectionA.[Test Load_12], tbl_LoadDeflectionA.Deflection_12, tbl_LoadDeflectionA.[% of Load_13], tbl_LoadDeflectionA.[Test Load_13]," & _

"tbl_LoadDeflectionA.Deflection_13, tbl_LoadDeflectionA.[Inflated Profile], tbl_LoadDeflectionA.[Full Measurements], tbl_LoadDeflectionA.[ECE Measurements], tbl_LoadDeflectionA.Footprint," & _

"tbl_LoadDeflectionA.[LD Comments], tbl_LoadDeflectionA.Revision, tbl_LoadDeflectionA.[Edited By], tbl_LoadDeflectionA.[Date Edited], tbl_LoadDeflectionA.EditReason" & _

"FROM ((tbl_WorkOrders INNER JOIN tbl_TireGroupsA ON tbl_WorkOrders.[Test Plan#] = tbl_TireGroupsA.[Test Plan#]) INNER JOIN tbl_TestingA ON (tbl_TireGroupsA.Group = tbl_TestingA.Group) AND (tbl_TireGroupsA.[Test Plan#] = tbl_TestingA.[Test Plan#])) INNER JOIN tbl_LoadDeflectionA ON (tbl_TestingA.Group = tbl_LoadDeflectionA.Group) AND (tbl_TestingA.[Test Plan#] = tbl_LoadDeflectionA.[Test Plan#]) AND (tbl_TestingA.[Test Type] = tbl_LoadDeflectionA.[Test Type])" & _

"WHERE (((tbl_TireGroupsA.[Test Plan#])=[Forms]![frm_ReviseWO]![Test Plan ID]) AND ((tbl_TireGroupsA.Group)=[Forms]![frm_ReviseWO]![cmbo_EditGroupSelect]) AND ((tbl_LoadDeflectionA.[Deflection ID])=[Forms]![frm_ReviseWO]![cmbo_TestEditSelect]) AND ((tbl_TestingA.[Test Type])='Load / Deflection'));")

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Duane Hookom 26,820 Reputation points Volunteer Moderator
    2020-04-07T16:47:37+00:00

    Change your where clause:

    "WHERE tbl_TireGroupsA.[Test Plan#]=" & [Forms]![frm_ReviseWO]![Test Plan ID] & " AND tbl_TireGroupsA.Group ='" & _

         [Forms]![frm_ReviseWO]![cmbo_EditGroupSelect] & "' AND tbl_LoadDeflectionA.[Deflection ID]= " & _

         [Forms]![frm_ReviseWO]![cmbo_TestEditSelect] & " AND tbl_TestingA.[Test Type]='Load / Deflection'"  )

    The exact amount of double and single quotes depends on the data type of the fields. I assumed Test Plan# and Deflecition ID are numeric and Group is text. 

    Also, it makes more sense to assign your SQL string to a variable like strSQL and then use:

    strSQL = "SELECT ....."

    Set rst = db.OpenRecordset(strSQL)

    1 person found this answer helpful.
    0 comments
  2. ScottGem 68,810 Reputation points Volunteer Moderator
    2020-04-07T15:40:47+00:00

    Hi Riley, I'm an independent adviser and will try to help.

    This is actually a common error when trying to generate a SQL statement in code. It usually means a error in the WHERE clause. The way I debug a situation like this is as follows.

    First, change your code. Add a Dim statement:

    Dim strSQL As String

    Second, assign your SQL statement to that variable:

    strSQL = "SELECT ...

    Third, after that add

    Debug.Print strSQL

    This will copy the generated SQL statement into the Immediate Window.

    Finally, set a breakpoint after the Debug line. So, when the breakpoint is reached, the VBE will open and you can copy and paste the SQL statement into SQL View in Query Design mode. When you switch to Design View, your will get an error indicating more clearly what the problem is. You can then fix the generated SQL statement. It may take a couple of times to get it right.

    If you need an further clarification on this please feel free to ask.

    1 person found this answer helpful.
    0 comments
  3. Tom van Stiphout 40,201 Reputation points MVP Volunteer Moderator
    2020-04-07T15:25:04+00:00

    Oh boy, I get dizzy from just looking at that Repeating Group - a violation of relational database design.

    That aside, the classic way to debug this is as follows.

    You should have code similar to:

    dim qd as dao.querydef

    dim rs as dao.recordset

    dim sql as string

    sql = "big sql statement"

    set qd=db.createquerydef("", sql)

    qd!param1 = value1

    qd!param2 = value2

    'etc.

    set rs = qd.openrecordset(dbOpenSnapshot)

    Now what are those parameters?

    In its simplest incarnation you can find out in the immediate window, running the code to a breakpoint you set on the "set rs =" line.

    ?qd.parameters.count

    3

    ?qd.parameters(0).name

    'etc.

    In your case the form references are the likely culprits. There is a technique using Eval you may be able to use to evaluate those parameters. Search for it online.

    1 person found this answer helpful.
    0 comments
  4. George Hepworth 22,765 Reputation points Volunteer Moderator
    2020-04-07T15:46:04+00:00

    Like Tom and Scott, I like to follow the "trial and error" method for troubleshooting.

    Problems like this are often the result of trying to concatenate strings inappropriately, and the debug.print makes it easier to spot those problems.

    Note, however, that you have to provide for spaces between things in your SQL. I suspect that's not happening here.

    0 comments