Help I Need to replace a Text table with a lookup table

[Randy]

OS= WinXP
Access= 2002

I'm NOT a Programmer, but I have used VB in the past to do some things
( Spaghetti Code King) so I have some understanding of Coding

I need to replace a text field (teacher) in Table (attendance) with a
lookup fileld. the lookup field will point to a newly created table
(teachers)

the original DB was never intended to do the job that it is doing, but
now is nessessary.
the (teacher) field is a TEXT field, and the uses continue to enter
wrong names, misspelled names, wrong Data, Etc.

So I created a (teachers) table with two fields (ID) which is the
Primary Key, and Name which is text.

Now in the attendance table, I have added a lookup field called
TeachID, using the teacher table as it's source.

I need a ONETIME macro to look at the current attendance!teacher field
search the teachers!Name field,find a match, get the ID and enter it
into the Attendance!teachID field...

then I can delete the Attendance!teacher field...

The teachers table had already been populated, but there are over
25000 records in the attendance table and would be too much to do by
hand...

here is what I was trying...

Function UpdtID()

DoCmd.OpenTable "Attendance", acViewDesign, acEdit <----- ???
DoCmd.OpenTable "Teachers", acViewDesign, acEdit <---- ???

Attendance.MoveFirst
teachers.MoveFirst
Do While Not Attendance.EOF


teachers.MoveFirst
TeachTxt = UCase(Trim(Attendance!Teacher))
With teachers
Do While Not .EOF
If UCase(Trim(.FName)) = TeachTxt Then
Attendance!TeachID = .id
Exit Do
End If
Loop

End With
Attendance.MoveNext
Loop


End Function


Thanks for any Help

Randy

 

[Steve Schapel]

Randy,

Trash the idea of the new field TeachID being a Lookup field... for more
information on this see http://www.mvps.org/access/lookupfields.htm.

On a backup copy of your database, make a query that includes both
tables Teachers and Attendance, and join them on the Teacher-Name
fields. Add the new TeachID field to the query design grid. Make this
an Update Query (select Update from the Query menu). In the Update To
row of the TeachID column in the grid, enter [ID]. Run the query (click
the toolbar button with the red (!) icon). Voila. After this, you will
then want to delete the Teacher field from the Attendance table as it is
no longer required.

And, as a side issue, the word 'name' has a special meaning in Access,
and is classified as a "reserver word", and as such should not be used
as the name of a field or control or database object.