Converting lookup text field to number for primary

[Ruegen]

I have a field in a table that another links into - it's from an old database and they didn't use the primary key.



I need to convert the text field to a number so it uses the primary key from the table called 'tblBanks'

Is there a batch way to convert the text in those fields to their corresponding primary key on the other table? There's about 30 and I need to do this a few times for testing reasons and I don't know how to get an update query to update multiples of the same field.

(I require keeping the table data)

If it makes it any easier this is what I hope to do but for more than just one of the same field...

[June7]

Do these tables presently have a common value that can be used to join them?

[Ruegen]

Yeah, there is a text field in "tblBanks" that for the large majority is the same as in the other text field in "tblSchools" that I want to convert to a number field (except less than 10 in that text field are not of any).

So

BanksID (autonumber)	Bank
1	CBA
2	ANZ

and then the tblSchools has a text field with CBA or ANZ in the record that I need to convert to a 1 or 2

[Ruegen]

I'm thinking of making a form of the table with some vba that loops through and updates the text in the field bankid with a corresponding number using an if

hoping that will work

[Ruegen]

I did this
Private Sub Form_Load()


With Me.Recordset
Do Until Me.Auto = 11610
.MoveNext
If txtBankID = "CBA" Then
txtBankID = "1"


ElseIf txtBankID = "Westpac" Then
txtBankID = "2"


ElseIf txtBankID = "NAB" Then
txtBankID = "3"


ElseIf txtBankID = "Bk of SA" Then
txtBankID = "4"


ElseIf txtBankID = "ANZ" Then
txtBankID = "5"


ElseIf txtBankID = "Bendigo Bank" Then
txtBankID = "6"


ElseIf txtBankID = "St George" Then
txtBankID = "7"






End If
Loop
End With


End Sub

works