runtime error

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

mon

This code gives me a runtime error 94, invalid use of Null.
It seemed to work a couple of times, but not since then.


It is in the before insert of the form.
Dim LastEntry As Date
Dim LastJob As Long
LastEntry = DMax("[DateEntered]", "tblActivityList")
LastJob = DLookup
("[JobNumber]", "tblActivityList", "[DateEntered]=#" &
LastEntry & "#")
Me.JobNumber = LastJob
Thanks Mon
 
Try putting a condition around your lookup.. IE: if the date wasn't found,
because there was no record in tblActivityList, then you will get an error
94 when you do the DLookup...
 
but there are records in the table. (The default value of
DateEntered is Now())


-----Original Message-----
Try putting a condition around your lookup.. IE: if the date wasn't found,
because there was no record in tblActivityList, then you will get an error
94 when you do the DLookup...


mon said:
This code gives me a runtime error 94, invalid use of Null.
It seemed to work a couple of times, but not since then.


It is in the before insert of the form.
Dim LastEntry As Date
Dim LastJob As Long
LastEntry = DMax("[DateEntered]", "tblActivityList")
LastJob = DLookup
("[JobNumber]", "tblActivityList", "[DateEntered]=#" &
LastEntry & "#")
Me.JobNumber = LastJob
Thanks Mon
Click to expand...


.
Click to expand...
 
Monika,

I suspect the problem here has got to do with a disparity beween
US/Other date formats. I am guessing that your code will work
correctly if your date is unambiguous, i.e. the day is after the 12th
of the month, but otherwise the comparison is not working. Am I
right?

There may be more elegant ways of doing it, but try it like this...

Dim LastEntry As Long
Dim LastJob As Long
LastEntry = CLng(DMax("[DateEntered]", "tblActivityList") * 1000
LastJob = DLookup("[JobNumber]", "tblActivityList",
"CLng([DateEntered]*1000)=" & LastEntry)
Me.JobNumber = LastJob

- Steve Schapel, Microsoft Access MVP
 
Just thinking, Mon, that multiplying by 1000 as I did in my example
will narrow the time to the nearest 1.5 minutes or so. If there is a
chance that records will be added more frequently than this, you might
want to make it 10000, which will take it to 8 or 9 seconds accuracy.

- Steve Schapel, Microsoft Access MVP


Monika,

I suspect the problem here has got to do with a disparity beween
US/Other date formats. I am guessing that your code will work
correctly if your date is unambiguous, i.e. the day is after the 12th
of the month, but otherwise the comparison is not working. Am I
right?

There may be more elegant ways of doing it, but try it like this...

Dim LastEntry As Long
Dim LastJob As Long
LastEntry = CLng(DMax("[DateEntered]", "tblActivityList") * 1000
LastJob = DLookup("[JobNumber]", "tblActivityList",
"CLng([DateEntered]*1000)=" & LastEntry)
Me.JobNumber = LastJob

- Steve Schapel, Microsoft Access MVP


This code gives me a runtime error 94, invalid use of Null.
It seemed to work a couple of times, but not since then.


It is in the before insert of the form.
Dim LastEntry As Date
Dim LastJob As Long
LastEntry = DMax("[DateEntered]", "tblActivityList")
LastJob = DLookup
("[JobNumber]", "tblActivityList", "[DateEntered]=#" &
LastEntry & "#")
Me.JobNumber = LastJob
Thanks Mon
Click to expand...
Click to expand...
 
Hi Steve
I've almost given up on this. I get the runtime error 94
again.
The debugging goes to the line below:
LastJob = DLookup("[JobNumber]", "tblActivityList",
Click to expand...
"CLng([DateEntered]*1000)=" & LastEntry)
Me.JobNumber = LastJob

- Steve Schapel, Microsoft Access MVP


This code gives me a runtime error 94, invalid use of Null.
It seemed to work a couple of times, but not since then.


It is in the before insert of the form.
Dim LastEntry As Date
Dim LastJob As Long
LastEntry = DMax("[DateEntered]", "tblActivityList")
LastJob = DLookup
("[JobNumber]", "tblActivityList", "[DateEntered]=#" &
LastEntry & "#")
Me.JobNumber = LastJob
Thanks Mon
Click to expand...

.
Click to expand...
 
Mon,

I am very sorry you are having a headache with this. I regret to say
that doing stuff with dates in VBA is often difficult.

I have replicated what I imagine to be your situation, and it is
working correctly for me. Could you please post back with exact code
you are now using, just to check, and if I can't spot the problem, I
would be willing to take a look at the database if you like.

- Steve Schapel, Microsoft Access MVP


Hi Steve
I've almost given up on this. I get the runtime error 94
again.
The debugging goes to the line below:
LastJob = DLookup("[JobNumber]", "tblActivityList",
Click to expand...
"CLng([DateEntered]*1000)=" & LastEntry)
Me.JobNumber = LastJob
Click to expand...
Click to expand...
 
Back
Top