loop through records on a form, run code on each record

[nickrugado]

thread705-544592

I have a form that is based off a query (using certain criteria).
It displays 1 record at a time.

I want it to do the following.

Run queries (These update queries use criteria from each record on the form. Queries already created)

Move to the next record.
Run the queries again, this time it will use the next records criteria to do the update.
Move to the next record, Move to the next record. (There could be 0, 1 or 100 records)

Close when done.

If anyone can help with this I would appreciate it. I have searched for hours and not found the right solution. The above referenced thread is a situation that mirrors mine but the person was trying to change how it was being done and it was never solved. I need that solution that they never got to.

Thanks,
Nick

 

[Blorf]

Use the button wizard, make a button that moves to the next record. Examine the properties, and open the code behind the button and you will see the syntax of telling it to move to the next record.

Use the wizard again, but this time to run a query, same steps, see the syntax.

Put this in a do loop for a for loop, and this should take care of it.



There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

http://www.thelegacyreturns.com/

 

[MajP]

Do you need to interface with each record on the form, or can you just use the underlying data? If the latter then something like

dim rs as dao.recordset
dim strSql as string
set rs = me.recordset
rs.movefirst
do while not rs.eof
strSql = "do something based on the fields of the current record"
'example: StrSql = "Insert into tblOne (fieldOne, fieldTwo) values (" & rs!fldOne & ", " & rs!fldTwo & ")"
currentdb.execute strsql
rs.movenext
loop

 

[nickrugado]

I do need to interface with each record. The queries use criteria provided in fields to perform updates.

So the form is based off of a table that has new information that will update other tables.
The key of that form is used as criteria in the update query. So after the updates are run, I need to move to the next record until all of the records have been processed.

Thanks,
Nick

 

[MajP]

What you describe does not sound like you need to interface with each record.

Are you going to the first record making manual updates to the values then running the query then going to the next record and making making updates and running the query?
or do you just need to automatically read the first record, run a query, read the next run a query ...

 

[Golom]

If you have stored queries that you want to run for each record and you know the names of the parameter fields that you want to set then

Dim qdf As DAO.QueryDef
Dim db  As DAO.Database
Set db = CurrentDb


Do Until rs.EOF
   [blue]' Inside the loop to process records in Recordset rs[/blue]
   Set qdf = db.QueryDefs("SomeQuery")

   qdf.Parameters("First Parameter").Value = rs![Field1]
   qdf.Parameters("Second Parameter").Value = rs![Field2]
   [blue]etc...[/blue]

   qdf.Execute  [red]' Only for UPDATE, INSERT, DELETE, MAKE TABLE queries[/red]

   ' Use the same pattern to process another query
   rs.MoveNext
Loop

Where "SomeQuery" is the name of a stored query that you want to process and "First Parameter", "Second Parameter", etc. are the parameters as they appear in the query's SQL.

 

[nickrugado]

MajP
Like you said, "do you just need to automatically read the first record, run a query, read the next run a query ... "

Yes, I have a form and I want to run a set of queries based on that record, then go to the next record on the form and run the queries, and so on until it's done. That's the only solution I am wanting to implement. If you can help me with the code that's great, I would totally appreciate it.
Nick

 

[nickrugado]

I found the answer.

Private Sub Form_Current()
On Error GoTo Err_Form_Current_Error

DoCmd.GoToRecord , , acNext 'advances you to next record
docmd.openquery "yourqueryname" 'the commands that you want to run
docmd.openquery "yourqueryname2"

Exit Sub
Err_Form_Current_Error:'this does nothing and holds back the error
End Sub