Solved VBA loop update table with field from another table - multiple criteria

[Mandy2]

Hi all, I am quite new to VBA and in need of help.
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:

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!

 

[theDBguy]

Hi. Welcome to AWF!

If you can produce that information using a query, then you shouldn't need to store that information. If so, then there's no need to update Table1.

 

[Mandy2]

Hi. Welcome to AWF!

If you can produce that information using a query, then you shouldn't need to store that information. If so, then there's no need to update Table1.

Thank you!

The thing is I'd like to update the table one record at a time. The reason being I cannot use a School Name more than once; two students cannot be sent to the same school. There's more to the table but unfortunately I cannot reveal more info.
Hope that helps!

 

[Cronk]

.... two students cannot be sent to the same school.

This to me means each school has only one student. Or do you mean a particular student can only be sent to one school?

 

[June7]

Why duplicate school max age and school area data into table1?

I agree with DBGuy - this data can be calculated when need and no need to save into table1. But if you really want, consider:

Do While Not rs.EOF
    rs2.FindFirst "[School Area]='" & rs![Residence Area] & "' AND [School Max Age]>" & rs!Age
    If Not rs2.NoMatch Then
        rs.Edit
        rs.fields("Future School Name") = rs2.fields("School Name")
        rs.Update
        rs2.MoveFirst
    End if
    rs.MoveNext
Loop

Instead of recordsets can use Execute and DLookup

CurrentDb.Execute "UPDATE Table1 Set [Future School Name] = DLookup(""[School Name]"", ""Table2"", ""[School Area]='"" & [Residence Area] & ""' AND [School Max Age]>"" & [Age])"

 

[Mandy2]

This to me means each school has only one student. Or do you mean a particular student can only be sent to one school?

Correct! This means each school can only accept ONE new student. I was thinking of adding a Yes/No field or a checkbox in Table_2 where it gets updated with a "Yes" each time a school is occupied. That way I can add a third condition where a student cannot be sent to a school with a checked off checkbox.

 

[Mandy2]

Why duplicate school max age and school area data into table1?

I agree with DBGuy - this data can be calculated when need and no need to save into table1. But if you really want, consider:

Do While Not rs.EOF
    rs2.FindFirst "[School Area]='" & rs![Residence Area] & "' AND [School Max Age]>" & rs!Age
    If Not rs2.NoMatch Then
        rs.Edit
        rs.fields("Future School Name") = rs2.fields("School Name")
        rs.Update
        rs2.MoveFirst
    End if
    rs.MoveNext
Loop

Instead of recordsets can use Execute and DLookup

CurrentDb.Execute "UPDATE Table1 Set [Future School Name] = DLookup(""[School Name]"", ""Table2"", ""[School Area]='"" & [Residence Area] & ""' AND [School Max Age]>"" & [Age])"

That worked!! Thank you so much! I wanted to save this data into table1 as it's going to be useful in the future.
DLookup is a great idea too, but I went with the recordset approach.
Thanks again!

 

[theDBguy]

That worked!! Thank you so much! I wanted to save this data into table1 as it's going to be useful in the future.
DLookup is a great idea too, but I went with the recordset approach.
Thanks again!

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[Mandy2]

Hi. Glad to hear you got it sorted out. Good luck with your project.

Thank you, much appreciated!