1st of month problem

  • Thread starter Thread starter mon
  • Start date Start date
M

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
 
LastJob = DLookup
("[JobNumber]", "tblInstructions", "[DateEntered]=#" &
LastEntry & "#")
Click to expand...

I'm not sure how LastEntry is formatted: try

"[DateEntered] = #" & Format(DateValue([LastEntry]), "mm/dd/yyyy") &
"#"
 
date entered is Now(). Still try what you said??
-----Original Message-----
LastJob = DLookup
("[JobNumber]", "tblInstructions", "[DateEntered]=#" &
LastEntry & "#")
Click to expand...

I'm not sure how LastEntry is formatted: try

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



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

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