Tracking My mileage

rkellogg · September 2nd, 2006, 05:45 PM

I would like to track my milage.

Table
MilageId - Autonumber Primary Key
RepId - Number
Milage_Date
Milage_Start_Time
Milage_Start_Milage
Milage_Stop_Time
Milage_Stop_Milage

I have a from for start milage and a form for Stop milage. I would like to be able to have the stopMilage form lookup on the table to see if there is a record with this date and RepId and add the current information to the same record. If nothing is present (I forgot to start my milage for the day) just add a new record.

I have been trying to write some code in vb. This is what I have so far.
Private Sub Combo7_AfterUpdate()

Dim strSQL As String
Dim lngKey As Long

lngKey = Nz(DLookup("milageid", "milage", "RepID = " & Me.Combo7 & " AND [Milage_Date] = #" & DATE & "#"), 0)

If lngKey <> 0 Then
Me.Undo
Me.RecordsetClone.FindFirst "[milageId] = " & lngKey
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

End Sub

I get an error on the last line
Me.Bookmark = Me.Recordsetclone.Bookmark
It says no record set. The line just above it has the correct value for the lngKey but I don't know how to get the book mark feature to work correctly.

Any help would be appreciated.
Rkellogg

BrianWren · September 4th, 2006, 04:03 PM

I might not have this right, but here goes: Recordset clone returns a recordset. It is not a thing to be manipulated. I think this is what you need:

Code:

Private Sub Combo7_AfterUpdate()

    Dim strSQL As String
    Dim lngKey As Long
    Dim tmpRs  As Recordset

    Set tmpRs = Me.RecordsetClone

    lngKey = Nz(DLookup("milageid", _
                        "milage", _
                        "RepID = " & Me.Combo7 & _
                        " AND Milage_Date = #" & DATE & "#"), _
                0)

    If lngKey <> 0 Then
        Me.Undo
        tmpRs.FindFirst "milageId = " & lngKey
        Me.Bookmark = tmpRs.RecordsetClone.Bookmark
    End If

End Sub

rkellogg · September 6th, 2006, 11:19 PM

I am still getting the error in the same place... on the last line with me.bookmark = tmpRs.recordsetClone.bookmark

Can't seem to get it to work.

rkellogg