How to loop through every record in query called "estimates"

[behnam]

Hi,




I found the following code online and it allows me to loop through each record based on the results of a query and make changes to the field which is exactly what i want to do.

 Dim rs As DAO.Recordset
Dim db As Database
Dim strSQL as String

Set db=CurrentDB

strSQL = "select * from table where some condition"

Set rs = db.OpenRecordset(strSQL)

Do While Not rs.EOF

    rs.Edit
    rs!SomeField = "Abc"
    rs!OtherField = 2
    rs!ADate = Date()
    rs.Update

    rs.MoveNext
Loop

how can i define strSql to a query builder query I created. Thanks.

[thebigthing313]

Instead of using SQL code in a string, you can open your query directly:

Dim rs As DAO.Recordset
Dim db As Database

Set db = CurrentDB

Set rs = db.OpenRecordset("nameofqueryincludingquotes", dbOpenDynaset)

Do While Not rs.EOF
    rs.Edit
    rs!SomeField = "Abc"
    rs!OtherField = 2
    rs!ADate = Date()
    rs.Update
    rs.MoveNext
Loop

[behnam]

Set rs = db.OpenRecordset("Redeployed-Estimate", dbOpenDynaset)

I get an error message on this line saying, to few parameters, expected 1.
Do you have any idea why?

thanks

[thebigthing313]

Does your query have any parameters? As in, does it request for user input before running the query?

Also, is there a specific reason you are doing this in VBA? I just read the code in the Do...Loop and it looks like something you can do with an Update Query.

[behnam]

it dosent request for input, it however uses the field in a textbox for comparison.

[thebigthing313]

I haven't tried something like this before, but in any case, if a saved query requires data from a form control, that form needs to be open for the query to run, or else, the query wouldn't know where to get the information, and you usually would get prompted for them when you run the query. Did you try running the VBA code while your form was open?