R
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
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