Hi all, I am quite new to VBA and in need of help.
I have two tables: Table_1 and Table_2.
Table_1
Table_2
I am basically trying to move the students from their current school into a future school under two conditions:
1. School Area = Residence Area, and
2. Age < School Max Age.
If we follow the example above, St John satisfies the condition therefore the three Null fields will be updated with St John's data from table_2.
I'd like to create a loop that updates table_1 based on the above conditions, table_1 contains 30 records.
Here's what I have so far:
Any help is appreciated!
I apologize if this has been answered before, please redirect me if so.
Thanks!
I have two tables: Table_1 and Table_2.
Table_1
Student Name | Current School | Future School | Age | School Max Age | Residence Area | School Area |
---|---|---|---|---|---|---|
Richard Bell | Little Angels | 3 | Erindale |
Table_2
School Name | School Max Age | School Area |
---|---|---|
St John | 5 | Erindale |
Passion Education | 4 | Barrie |
I am basically trying to move the students from their current school into a future school under two conditions:
1. School Area = Residence Area, and
2. Age < School Max Age.
If we follow the example above, St John satisfies the condition therefore the three Null fields will be updated with St John's data from table_2.
I'd like to create a loop that updates table_1 based on the above conditions, table_1 contains 30 records.
Here's what I have so far:
Code:
Private Sub NewSchool()
Dim i As Integer
Dim db As Database
Dim rs As Recordset
Dim rs2 As Recordset
Dim str As String
Set db = CurrentDb
Set rs = db.OpenRecordset("Table_1")
Set rs2 = db.OpenRecordset("Table_2")
i = 0
Do While Not rs.EOF
If rs.fields("Residence Area") = rs2.fields("School Area") and rs.fields("Age") < rs2.fields("School Max Age") Then
Update rs.fields("Future School Name") = rs2.fields("School Name")
End if
rs.MoveNext
Next i
rs.Close
Set rs = Nothing
db.Close
End Sub
Any help is appreciated!
I apologize if this has been answered before, please redirect me if so.
Thanks!
Last edited: