Carry the last record over

[DMT Dave]

Hi Guy's i am not sure what's going on with me, i know you can't help on that one but this so simple procedure should be simple to my standards even!!

I am trying to carry over in the example in the image 19.4 from MSNo 4746 to MSNo 4747

So this is changed on StartTime Update

Example of carrying over





StartTime AfterUpdate

Dim LastNo As Integer, HoursLeft As Variant, myDriver As StringDim myStart As Date, myEnd As Date


myDriver = Me.Driver




If Not IsNull(LastNo = DMax("MSNo", "tblDriverHours", "[Driver] = '" & myDriver & "'")) Then
LastNo = DMax("MSNo", "tblDriverHours", "[Driver] = '" & myDriver & "'") - 1
HoursLeft = DLookup("HoursLeft", "tblDriverHours", "[MSNo] = " & LastNo)
Me.HoursLeft = HoursLeft


End If

[pbaldy]

You haven't said what the problem is (error, wrong result, etc). Do you know how to set a breakpoint and check the values being returned at each step? If not:

http://www.baldyweb.com/Debugging.htm

[Gicu]

If MSNo is an autonumber field (and even if it is a custom counter that you increment yourself) I would not rely on simply substracting 1 to get the previous record; I would use a Top 1 select statement to get the MSNo < Me.MSNo sorted descending on MSNo. Also you declare the LastNo as Integer, should be long as it will error out soon. And you don't show us the Driver field, you treat it as a text (string), if it is a numeric driver ID you don't need the single quotes.

Cheers,

[DMT Dave]

Hi Guy's yes sorry should have siad MSNo is Not autonumber, ahhhhh just noticed i have it set as text, sorry chap's i think i need to regroup my mind

[Gicu]

No worries Dave! Still, if you delete a row and have a gap in your MSNo sequence your subtraction logic will fail so you should go with the ordered recordset to get the previous value.

Cheers,