CurrentDb.Execute Error 3061 and DAO.Recordset Error

Question

On a form, a user is able to select a value of either 1 or 2. This number is on an unbound control called CountVal.

When the user selects the submit button, an update query is ran. The following is the query.

UPDATE UserData_T SET UserQuantity = Forms!MainUser_F!CountVal.value;

The query, when run separately, runs as should. The issue comes when I call it in the submit button click event. I use the CurrentDb.Execute method. This method throws a

3061 error of "Too few parameters'.

I have found through much research that control based parameters do not work with the execute method. It works when I set the value as either 1 or 2 but not through the control. I need to have this dynamic.

I have also tried the DAO.Database.OpenRecordset() method, however I am struggling with looping through each record. Logically, I would gravitate to a for each but I'm not finding any references on using this loop with a DAO recordset. Also, wouldn't looping through the recordset be extremely slow? Could I also add an if statement to the query itself?

Parfait Parfait · Accepted Answer · 2021-06-09T14:26:47

In MS Access, stored queries can refer to open form or report controls as parameters. However, queries called via DAO methods like CurrentDb.Execute, do not see the GUI interface and hence cannot evaluate form or report controls.

So to continue using form controls as is, simply save your SQL statement as a stored query and call it with DoCmd.OpenQuery (which do not need to be closed for action queries like UPDATE, INSERT, DELETE). This is the coding counterpart to clicking the stored query via Navigation Pane.

DoCmd.OpenQuery "mySavedUpdateQuery"

Do note the above will raise prompts of data changes. To suppress such prompts, use DoCmd.SetWarnings:

DoCmd.SetWarnings False
DoCmd.OpenQuery "mySavedUpdateQuery"
DoCmd.SetWarnings True

CurrentDb.Execute Error 3061 and DAO.Recordset Error

2 Answers