runtime error

[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

 

[Ruskin Hardie]

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]

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

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

.

 

[Steve Schapel]

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

 

[Steve Schapel]

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

 

[mon]

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",

"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

.

 

[Steve Schapel]

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",

"CLng([DateEntered]*1000)=" & LastEntry)
Me.JobNumber = LastJob