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.
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.