Help with DAO.Recordset

[businesshippieRH]

I'm having a little trouble figuring something out, but I'm sure some of you experts can help.

I have a table which is basically used to fill in a form (tbl_RateSchedule_NEW) and is spit back out as a new table. This part all works great and code isn't included below. The problem is, there's one column that's not in the "original" table (tbl_RateSchedule_DEFAULT)...

I'm not sure how to do a DLookup on a DAO.Recordset (see the bold and red text), but the ID part is a PK AutoNumber field in a table with the PK and a descriptive name only.

Thanks in advance!

My code is:

Private Sub Form_Load()
'Add all new entries from Default schedule to "temporary" tbl for enty to new schedule
Dim Updatetbl_RS_NEW As String
    Updatetbl_RS_NEW = "INSERT INTO tbl_RateSchedule_NEW (EqTypeID, DefaultDailyRate, DefaultWeeklyRate, DefaultMonthlyRate, AddedBy, DateAdded) " & _
                        "SELECT EqTypeID, DailyRate, WeeklyRate, MonthlyRate, AddedBy, DateAdded " & _
                        "FROM tbl_RateSchedule_DEFAULT " & _
                                        "WHERE NOT EXISTS (SELECT * FROM tbl_RateSchedule_NEW " & _
                                        "WHERE tbl_RateSchedule_NEW.EqTypeID = tbl_RateSchedule_DEFAULT.EqTypeID)"
DoCmd.RunSQL Updatetbl_RS_NEW
'Since tbl_RS_DEFAULT doesn't have an EquipmentType Field, use loop to get each from tbl_EqTypeValidValues and Add it
Dim DBS As DAO.Database
Dim RS As DAO.Recordset
Dim Finder As String
Dim IntI As Integer
    'Set to look at the list created
        'Find empty...
        Set DBS = CurrentDb
        FinderSQL = "SELECT EqTypeID FROM tbl_RateSchedule_NEW WHERE EquipmentType IS NULL"
        Set RS = DBS.OpenRecordset(FinderSQL, dbOpenSnapshot)
        
        'If RS Empty, quit
        If RS.EOF Then Exit Sub
        Else
        'Do Stuff with nulls
        IntI = 1
        With RS
            Do Until .EOF
            'Get correct value
            Dim FindEqType As String
                FindEqType = DLookup("EquipmentType", "tbl_EquipmentTypeValidValues", "EqTypeID = [COLOR="Red"][B]RECORDSETEqTypeID"[/B][/COLOR])
                'Add to table
                Dim AddSQL As String
                AddtoTableSQL = "INSERT INTO tbl_RateSchedule_NEW (EquipmentType) VALUES (" & FindEqType & ")"
                DoCmd.RunSQL AddtoTableSQL
            .MoveNext
            IntI = IntI + 1
            Loop
        End With
        
End Sub

 

[TJPoorman]

FindEqType = DLookup("EquipmentType", "tbl_EquipmentTypeValidValues", "EqTypeID =" & RS!EqTypeID)

 

[businesshippieRH]

Message deleted and moved to new thread.