For each record in table syntax

[buratti]

I am trying to create a for loop, looping through each record in a particular table. Then I want to set the value of each field to a variable and do other calculations with them, then repeat (loop).


I know the code below is way wrong, but I made it "literal" to make it easier to explain what I am trying to do: Items in red are what I am having trouble with

Dim ID As Integer
Dim cbName As String
Dim Position As Integer
Dim Temporary As Boolean
 
for each [COLOR=red]record in "Table1"
[/COLOR]    ID = "[COLOR=red]field1".value
[/COLOR]    cbName = [COLOR=red]"field2".Value
[/COLOR]    Position = [COLOR=red]"field3".Value
[/COLOR]    Temporary = [COLOR=red]"field4".value
[/COLOR]    
[COLOR=lime]    'Do some other code here[/COLOR]
   
        For [COLOR=red]each record in "Table2"
[/COLOR]            If "Field1".value = ID then
                  
                 [COLOR=lime]'Do more code here[/COLOR]
[COLOR=#00ff00][/COLOR] 
            End If
        Next
Next

 

[boblarson]

You don't use For Each Record. You would use a recordset:

Dim ID As Integer
Dim cbName As String
Dim Position As Integer
Dim Temporary As Boolean
[B][COLOR=red]Dim rst As DAO.Recordset[/COLOR][/B]
 
Set rst = CurrentDb.OpenRecordset("Table1")
 
Do Until rst.EOF
    ID = [B][COLOR=red]rst(0)[/COLOR][/B]
    cbName = [COLOR=red][B]rst(1)[/B][/COLOR]
    Position = [COLOR=red][B]rst(2)[/B][/COLOR]
    Temporary = [COLOR=red][B]rst(3)[/B][/COLOR]
 
[COLOR=red]  'Do your other stuff with the variables[/COLOR]
 
[COLOR=red]rst.MoveNext[/COLOR]
 
[COLOR=red]Loop[/COLOR]
 
[COLOR=red]rst.Close[/COLOR]
 
[COLOR=red]Set rst = CurrentDb.OpenRecordset("Table2")[/COLOR]
[COLOR=red]'    ....etc. - the same as above.[/COLOR]
 
 
[COLOR=red]rst.Close[/COLOR]
 
[COLOR=red]Set rst = Nothing[/COLOR]

 

[Poppa Smurf]

Bob

From my understanding of the question is that the user wants to compare ID in table1 to ID in table2 and if they are the same then do the calculation.

See the following line in the user's code If "Field1".value = ID then


This will require two loops similar to this air code

Set rst = CurrentDb.OpenRecordset("Table1")
Set rst1 = CurrentDb.OpenRecordset("Table2")
 
Do Until rst.EOF
         rst1.movefirst
         Do until rst1.eof
           if rst.(0)=rst1.(0) then
             do calculations here
           end if
 
        rst1.MoveNext
 
        loop
[COLOR=black]rst.MoveNext[/COLOR]
 
loop

 

[boblarson]

Yep, missed that point.

 

[buratti]

Thanks to both of you. I originally thought of using recordsets, however I did not know the syntax for referencing fields in the recordset. Now I know! Thanks

 

[boblarson]

Thanks to both of you. I originally thought of using recordsets, however I did not know the syntax for referencing fields in the recordset.

Just an FYI for you - there are a few other ways you can reference them as well:

If rst.Fields("FieldNameHere").Value = xxxx
 
 
If rst("FieldNameHere") = xxxx
 
 
If rst!FieldNameHere = xxxx

and there may be others I missed.

 

[Poppa Smurf]

Here is another alternative.

Use a query to join the ID in table1 to ID in Table2. If you have field names in both tables with the same name then you will need to use aliases. Suppose you have a field named "field1" in table1 and "field1" in table2 then you will need aliases by using fld1a:field1 for table1 and fld1b:field1 for table2

Set rst = CurrentDb.OpenRecordset("query name")
Do Until rst.EOF
 
     do calculations here
 
 
rst.MoveNext
 
loop