DLookup Help (1 Viewer)

[rhounsome]

I am using a DLookup script in a Form to insert a figure into a field on the form in the event of an office being closed due to regional holidays.

It was working fine and I thought everything was fine.

The script was as follows

If Me.AmericasHoliday = True Then

Me.AmericasBacklog = DLookup("[AmericasBacklog]", "[tblBookingsBacklog]", "[AutoID]= Forms![frmBookingsBacklog]![AutoID]-1")
Else

End If

However the AutoID is the standard sequential number which I thought was fine. However after putting in a new script on a date field that notifies the user if the information they are entering is in advance of todays current date and telling them to contact their manager and press Escape to cancel the record it then buggers up this script. As the script looks at the current forms AutoID field and then subtracts 1 from it to get the last record from the relevant table or thats what I thought.

AutoID now on Form = 9 but last record AutoID in Table = 7. So NO values are being selected as the DLookup is looking for record AutoID = 8 and there isn't one.

I don't think I can use the Date field as a lookup as this is also affected by there being 7 days in a week but only 5 working days but being a novice VBA/Database writer I may be wrong.

Can someone help me out and offer a solution of some sorts.

 

[Travis]

'Using the Formst Recordset check the value of the previous record
Dim rst As Object

Set rst = Me.RecordsetClone
'Places the recordset clone on the current record
rst.Bookmark = Me.Recordset.Bookmark
rst.MovePrevious
If rst.BOF then
  'Trap for not having a previous record
Else
  Me.AmericasBacklog = rst.Fields("[AmericasBacklog]").value
End if

Set rst=Nothing

 

[rhounsome]

Travis. Your a star. It works well sort of.

Can you explain why it sort of needs to be prompted into life to get it working. For me to get it to work I need to check the box go to the previous record and then back to the record I was entering before the value appears. However once I have done that I don't need to do it again until I close and reopen the form.

The script you provided is in the AfterUpdate item.

Would it help in me refreshing the data as in theory this it what I am doing by switching between previous and current record to get it to update. What is confusing is why I need to only do this once.

Access 2000 User (don't know if that helps at all)

 

[Travis]

Place this in the Current Event of the Form. That way everytime you switch records it runs.