1st of month problem

[mon]

The code below is to show the last job number in the new
record job number field. I had a lot of trouble with this
and got a lot of help from you all. Kept getting a
runtime erreor. Then suddenly it worked. Now amazingly I
am getting the same runtime error. After looking back at
my original messages I note that it occurred on the 1st of
October - it is now the first of November. Help, please.
Thanks
Mon
Also the number shows when I tab into one of the fields in
that new record. Can I make it show immediately the new
record appears?

Dim LastEntry As Date
Dim LastJob As Long
LastEntry = DMax("[DateEntered]", "tblInstructions")
LastJob = DLookup
("[JobNumber]", "tblInstructions", "[DateEntered]=#" &
LastEntry & "#")
Me.JobNumber = LastJob
End Sub

 

[John Vinson]

LastJob = DLookup
("[JobNumber]", "tblInstructions", "[DateEntered]=#" &
LastEntry & "#")

I'm not sure how LastEntry is formatted: try

"[DateEntered] = #" & Format(DateValue([LastEntry]), "mm/dd/yyyy") &
"#"

 

[mon]

date entered is Now(). Still try what you said??

-----Original Message-----

LastJob = DLookup
("[JobNumber]", "tblInstructions", "[DateEntered]=#" &
LastEntry & "#")

I'm not sure how LastEntry is formatted: try

"[DateEntered] = #" & Format(DateValue ([LastEntry]), "mm/dd/yyyy") &
"#"



.

 

[John Vinson]

date entered is Now(). Still try what you said??

Now() returns the current date and time, accurate to microseconds. So
no - if [Date Entered] contains a date and time portion the lookup
won't find it if you use DateValue, because DateValue strips off the
time portion!

I don't see why the code as written should be affected by the first
day of the month at all. I'd suggest setting a Breakpoint in the code
(open the code in the VBA editor and click in the grey bar at the
left), then add a record. The code should stop at the breakpoint and
you'll be able to step through the code, checking to see what each
variable is at each program step.

 

[DanK]

Try running it as a Query, filtering on either populated
information or prompted info.