Too Few Parameters in Access VBA but works in Query builder

Question

I am using the below SQL, it works fine if I run it from query builder but once I have put it in VBA it throws out an error:

Code:

With CurrentDb.CreateQueryDef("", "SELECT [_tbl_Structure].[User Name], tbl_Genesys_Daily.Field32, [_tbl_Structure].[Supervisor Emp Num], [_tbl_Structure].Supervisor FROM _tbl_Structure RIGHT JOIN tbl_Genesys_Daily ON [_tbl_Structure].[User ID] = tbl_Genesys_Daily.Field5 WHERE ((([_tbl_Structure].Supervisor)=?));")
            .Parameters(0) = [Forms]![frm_Manager_Stats_NEW]![Text279]  
            Set lvxObj = AvailabilityCap.Object
                Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
        End With

Error:

Too few parameters. Expected 1. (Runtime 3061)

Any help on understanding why this works for one but not another is appreciated

Please don't do Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset) if I answer you should use Set rs = .OpenRecordset. That's totally not the same. — Erik A
– Erik A, Commented Apr 3, 2018 at 11:04

Erik A · Accepted Answer · 2018-04-03 10:59:22Z

1

Form- and report-based parameters are only available in the GUI context (queries run using the GUI, forms, reports, macros and DoCmd.RunSQL). You're probably executing this through CurrentDb, and need to use a querydef instead.

With CurrentDb.CreateQueryDef("", "SELECT [_tbl_Structure].[User Name], tbl_Genesys_Daily.Field32, [_tbl_Structure].[Supervisor Emp Num], [_tbl_Structure].Supervisor FROM _tbl_Structure RIGHT JOIN tbl_Genesys_Daily ON [_tbl_Structure].[User ID] = tbl_Genesys_Daily.Field5 WHERE ((([_tbl_Structure].Supervisor)=?));")
    .Parameters(0) = [Forms]![frm_Manager_Stats_NEW]![Text279]
    Set rs = .OpenRecordset
End With

You can learn more about the different types of parameters, and when to use which one, in this answer

answered Apr 3, 2018 at 10:59

Erik A

32.8k13 gold badges48 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dmorgan20 Over a year ago

wow that was quick - Thank you. I have updated my question to incorporate your answer but still not working unfortunately.

Collectives™ on Stack Overflow

Too Few Parameters in Access VBA but works in Query builder

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related