Updating Table

[Guest]

I'm running the following code in order to insert a field Prod_Date into the
tablt tblRepairRLTemp. Based on the DATE_CODE in tblRepairRLTemp, it should
lookup for that Date Code in the DateCodeLookup table and then substitute the
Prod_Date with the corresponsing Date field present in the DateCodeLookup
table.
It adds the Prod_Date field but does not add any Dates to it.
Anything that I'm doing wrong?
Thanks.
DD

Public Function tblRepairDateCode()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim RecSet As DAO.Recordset
Dim addflag As Integer
Dim sTemp As String
Dim sRes As String
DoCmd.RunSQL ("ALTER TABLE tblRepairRLTemp ADD COLUMN [Prod_Date] Date;")

Set db = CurrentDb
Set rs = db.OpenRecordset("tblRepairRLTemp")
Set rs1 = db.OpenRecordset("DateCodeLookup")
rs.MoveFirst

Do Until rs.EOF
If (rs("DATE_CODE") <> Null) Then

sTemp = rs("DATE_CODE")

rs1.MoveFirst

Do Until rs1.EOF
sRes = rs1("DateCode")
If (sTemp = sRes) Then
rs.Edit
rs("Prod_Date") = rs1("Date")
rs.Update
End If
rs1.MoveNext
Loop

Else

rs.MoveNext
End If
Loop

End Function

 

[MGFoster]

I'm running the following code in order to insert a field Prod_Date into the
tablt tblRepairRLTemp. Based on the DATE_CODE in tblRepairRLTemp, it should
lookup for that Date Code in the DateCodeLookup table and then substitute the
Prod_Date with the corresponsing Date field present in the DateCodeLookup
table.
It adds the Prod_Date field but does not add any Dates to it.
Anything that I'm doing wrong?
Thanks.
DD

Public Function tblRepairDateCode()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim RecSet As DAO.Recordset
Dim addflag As Integer
Dim sTemp As String
Dim sRes As String
DoCmd.RunSQL ("ALTER TABLE tblRepairRLTemp ADD COLUMN [Prod_Date] Date;")

Set db = CurrentDb
Set rs = db.OpenRecordset("tblRepairRLTemp")
Set rs1 = db.OpenRecordset("DateCodeLookup")
rs.MoveFirst

Do Until rs.EOF
If (rs("DATE_CODE") <> Null) Then

sTemp = rs("DATE_CODE")

rs1.MoveFirst

Do Until rs1.EOF
sRes = rs1("DateCode")
If (sTemp = sRes) Then
rs.Edit
rs("Prod_Date") = rs1("Date")
rs.Update
End If
rs1.MoveNext
Loop

Else

rs.MoveNext
End If
Loop

End Function

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Why don't you just use a query to update the table:

UPDATE DISTINCTROW tblRepairRLTemp INNER JOIN DateCodeLookup
ON tblRepairRLTemp.DATE_CODE = DateCodeLookup.DateCode
SET tblRepairRLTemp.Prod_Date = DateCodeLookup.Date
WHERE tblRepairRLTemp.DATE_CODE IS NOT NULL

If you want to use your code, just change

If (rs("DATE_CODE") <> Null) Then

to this

If Not IsNull(rs("DATE_CODE")) Then

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQzrNCYechKqOuFEgEQJiZQCfRoY2x/aThPSCQXHVBxjzPCA64zcAoJlG
+ljQXe04ZtNqvhYylD4IiwQt
=vXzS
-----END PGP SIGNATURE-----