End Milage to Edit Start milage Record

[rkellogg518]

I have a data base that tracks milage, working time, according to rep. I am trying to get the stop milage form to check to see if a record with the same date and rep exists if so then edit the existing record if not then create a new record.

I have tried it with the following code in VB but continually get errors.

Private Sub Combo7_AfterUpdate()
Dim strSQL As String
Dim lngKey As Long

lngKey = Nz(DLookup("Firstname", "rep", "[RepID] = " & Me.Combo7 & " AND [Mileage_Date] = #" & DATE & "#"), 0)

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

Any help would be appreciated.

 

[lespaul]

what errors do you get?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[rkellogg518]

Run time error 2001
You canceled previous operation
end or debug

I click on debug and it displayes this line in yellow
lngKey = Nz(DLookup("Firstname", "rep", "[RepID] = " & Me.Combo7 & " AND [Mileage_Date] = #" & DATE & "#"), 0)

Don't know what the exact problem is.

rkellogg

 

[Prattaratt]

With Combo boxes, it's easier to set the LimitToList prop to true, which then causes the OnNotInList event to fire when you attempt to add a new entry. You can then use the event handler to add the new entry.

You also might want to try your search in the BeforeUdate event, rather than the AfterUdate event.

Lastly, the Help files I have suggest using a query instead of DLookup.

The simplest solution is the best!