1

I have a VBA project that runs into an error:

Dim db
Dim rstOrders As Recordset
Dim rstTabOrders As Recordset
Dim strOrders As String

Set db = CurrentDb
strOrders = "SELECT SQY_ImportOrders_Basis.[Nummer inkooporder] FROM SQY_ImportOrders_Basis"
Set rstTabOrders = db.OpenRecordset("Tab_InkoopOrders", dbOpenDynaset)
Set rstOrders = db.OpenRecordset(strOrders, dbOpenDynaset)

This code hangs on the last line (Set rstOrders =...) ginving an error saying that there are too few parameters and it expects 1.

Furthermore: the strOrders query, works fine when pasted in an SQL query.

The query "SQY_ImporOrders.Basis" has a part of a field that is linked tot a form. The form is open as the code is started y a button on that form. The reference to the form is in a part a of a query field: criteria of that field Field

I can't seem tot find where this goes wrong?

Scoobie
  • 11
  • 4
  • 1
    Not sure, but I would assume you need a space before the `FROM` in your SQL statement. – FunThomas Sep 18 '20 at 09:54
  • you really got me confused with your variable names `rstOrders` and `strOrders` – horst Sep 18 '20 at 09:58
  • I corrected the code: added a space before FROM. Problem not solved and still the same... – Scoobie Sep 18 '20 at 10:03
  • 1
    is there any parameter in `SQY_ImportOrders_Basis`? any criteria? – Foxfire And Burns And Burns Sep 18 '20 at 10:58
  • yes. in that query is a criterium to a form field... – Scoobie Sep 18 '20 at 11:30
  • 2
    That's it. You must pass that parameter value. – Gustav Sep 18 '20 at 11:39
  • I didn't have enough information to replicate the problem, but have you tried running the query when that form is open vs that form is closed. If query works when the form is open then wrap the form field in a public get function and decide what value it will have when the form is closed. – mazoula Sep 18 '20 at 11:40
  • the form is open. i added this info just now to the problem description – Scoobie Sep 18 '20 at 12:11
  • It doesn't matter. `db.OpenRecordset` cannot evaluate this parameter. As explained in the linked duplicate. – Andre Sep 18 '20 at 14:02
  • See also https://stackoverflow.com/questions/49509615/how-do-i-use-parameters-in-vba-in-the-different-contexts-in-microsoft-access – Andre Sep 18 '20 at 14:03
  • What kind of form is this? And could you maybe set a breakpoint before the last line of code, and then set a watch for strOrders? If I were you, I would like to see what the actual SQL statement that is executed looks like. – NXP5Z Sep 19 '20 at 08:55

1 Answers1

0

If you have a typo in the select statement you would get this error. Try SELECT * FROM SQY_ImportOrders_Basis.

Jörgen R
  • 366
  • 1
  • 10