DLookup too soon after record insert?

[Brian]

I am getting an "Invalid use of null" when performing a DLookup of a record
immediately after inserting it.

'capture date & time
Dim BatchDate As Date
Dim BatchTime As Date
BatchDate = Date
BatchTime = Time
'create invoice batch
Dim strSQL As String
strSQL = "INSERT INTO InvoiceBatch (BatchDate, BatchTime)SELECT Date() AS
BatchDate, Time() AS BatchTime;"
DoCmd.RunSQL strSQL
'capture current batch number to insert into each invoice
BatchIDTemp = DLookup("[BatchID]", "[InvoiceBatch]", "[BatchDate] = #" &
BatchDate & "# and [BatchTime] = #" & BatchTime & "#")

[Lots more code below here]

I get an "Invalid use of Null on the last line (BatchIDTemp = ...), even
though I have verified that the record is correctly inserted into the table
via on the RunSQL statement above. This happens only only on two
workstations. Two other workstations do not have this problem.

Front end is an MDE distributed to each of five WinXP Pro workstations, each
running Access 2003 runtime. Back end is MDB housed on Windows 2003 server.

For what it is worth, before I started debugging to pinpoint the location of
the problem by inserting MsgBoxes at key points, the users were getting a
"3027: Cannot Update. Database or object is read-only error." They were also
receiving a 3027 error on another form that does not write to the back end
(just runs several select SQL statements). However, they had no problem
making any other changes in the database.

The application has been running for eight months prior to this without a
problem. I just rebooted the server to see if there is some network
share/security issue but will not know the effect of that until tomorrow &
thought I should post here in the meantime.

 

[Allen Browne]

There could be several things going on here, Brian.

When you concatenate a date value into a string, you need to format it the
way JET expects. Otherwise it will appear in keeping with the user's
regional settings, which might explain why it works on some machines and not
others:
Dim strWhere As String
strWhere = "([BatchDate] = #" & Format([BatchDate], "mm\/dd\/yyyy") & _
"#) AND ([BatchTime] = #" & Format([BatchTime], "hh\:nn\:ss") &
"#)")
BatchIDTemp = DLookup("[BatchID]", "[InvoiceBatch]", strWhere)

Date/Time fields are a particular kind of floating point time, so there is
always a chance that they don't match exactly. You may have to change it to
handle 1 second either way (Between ... And ...) if it still doesn't match.

We don't see where you declared BatchIDTemp, but presumably it's a Long
since it cannot handle the null. There's always a chance that DLookup()
returns a null, so it should be declared as a Variant (or use Nz() to handle
the null case.)

But a better solution might be to ask Access to give you the new ID number.
This kind of thing:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = DBEngine(0)(0)
strSQL = "INSERT INTO InvoiceBatch ...
db.Execute strSql, dbFailOnError

Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
BatchIDTemp = rs!LastID
rs.Close

Set rs = Nothing
Set db = Nothing
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am getting an "Invalid use of null" when performing a DLookup of a record
immediately after inserting it.

'capture date & time
Dim BatchDate As Date
Dim BatchTime As Date
BatchDate = Date
BatchTime = Time
'create invoice batch
Dim strSQL As String
strSQL = "INSERT INTO InvoiceBatch (BatchDate, BatchTime)SELECT Date() AS
BatchDate, Time() AS BatchTime;"
DoCmd.RunSQL strSQL
'capture current batch number to insert into each invoice
BatchIDTemp = DLookup("[BatchID]", "[InvoiceBatch]", "[BatchDate] = #"
&
BatchDate & "# and [BatchTime] = #" & BatchTime & "#")

[Lots more code below here]

I get an "Invalid use of Null on the last line (BatchIDTemp = ...), even
though I have verified that the record is correctly inserted into the
table
via on the RunSQL statement above. This happens only only on two
workstations. Two other workstations do not have this problem.

Front end is an MDE distributed to each of five WinXP Pro workstations,
each
running Access 2003 runtime. Back end is MDB housed on Windows 2003
server.

For what it is worth, before I started debugging to pinpoint the location
of
the problem by inserting MsgBoxes at key points, the users were getting a
"3027: Cannot Update. Database or object is read-only error." They were
also
receiving a 3027 error on another form that does not write to the back end
(just runs several select SQL statements). However, they had no problem
making any other changes in the database.

The application has been running for eight months prior to this without a
problem. I just rebooted the server to see if there is some network
share/security issue but will not know the effect of that until tomorrow &
thought I should post here in the meantime.

 

[Brian]

Thank you, Allen.

This is not the first time that differences in the Regional Settings have
caught me!

I always try to make sure that the chance of confusion is actually zero,
which is why I went through all the code to guarantee that the user captured
the same ID that was just created.

However, given the fact that the time lapse between the time it is created &
the time I look it up will be, in reality, a very small fraction of a second,
the chance of capturing the wrong BatchID is virtually zero.

That all meanst that it will probably be simpler to just capture a DMax on
BatchID rather than all of that!

Thank you.

There could be several things going on here, Brian.

When you concatenate a date value into a string, you need to format it the
way JET expects. Otherwise it will appear in keeping with the user's
regional settings, which might explain why it works on some machines and not
others:
Dim strWhere As String
strWhere = "([BatchDate] = #" & Format([BatchDate], "mm\/dd\/yyyy") & _
"#) AND ([BatchTime] = #" & Format([BatchTime], "hh\:nn\:ss") &
"#)")
BatchIDTemp = DLookup("[BatchID]", "[InvoiceBatch]", strWhere)

Date/Time fields are a particular kind of floating point time, so there is
always a chance that they don't match exactly. You may have to change it to
handle 1 second either way (Between ... And ...) if it still doesn't match.

We don't see where you declared BatchIDTemp, but presumably it's a Long
since it cannot handle the null. There's always a chance that DLookup()
returns a null, so it should be declared as a Variant (or use Nz() to handle
the null case.)

But a better solution might be to ask Access to give you the new ID number.
This kind of thing:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = DBEngine(0)(0)
strSQL = "INSERT INTO InvoiceBatch ...
db.Execute strSql, dbFailOnError

Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
BatchIDTemp = rs!LastID
rs.Close

Set rs = Nothing
Set db = Nothing
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am getting an "Invalid use of null" when performing a DLookup of a record
immediately after inserting it.

'capture date & time
Dim BatchDate As Date
Dim BatchTime As Date
BatchDate = Date
BatchTime = Time
'create invoice batch
Dim strSQL As String
strSQL = "INSERT INTO InvoiceBatch (BatchDate, BatchTime)SELECT Date() AS
BatchDate, Time() AS BatchTime;"
DoCmd.RunSQL strSQL
'capture current batch number to insert into each invoice
BatchIDTemp = DLookup("[BatchID]", "[InvoiceBatch]", "[BatchDate] = #"
&
BatchDate & "# and [BatchTime] = #" & BatchTime & "#")

[Lots more code below here]

I get an "Invalid use of Null on the last line (BatchIDTemp = ...), even
though I have verified that the record is correctly inserted into the
table
via on the RunSQL statement above. This happens only only on two
workstations. Two other workstations do not have this problem.

Front end is an MDE distributed to each of five WinXP Pro workstations,
each
running Access 2003 runtime. Back end is MDB housed on Windows 2003
server.

For what it is worth, before I started debugging to pinpoint the location
of
the problem by inserting MsgBoxes at key points, the users were getting a
"3027: Cannot Update. Database or object is read-only error." They were
also
receiving a 3027 error on another form that does not write to the back end
(just runs several select SQL statements). However, they had no problem
making any other changes in the database.

The application has been running for eight months prior to this without a
problem. I just rebooted the server to see if there is some network
share/security issue but will not know the effect of that until tomorrow &
thought I should post here in the meantime.

 

[dch3]

If you need to add a record and then immediately work with the Autonumber
assigned to it - or get the exact date/time, you can use DAO to add the
record. Once you execute the .Update method of the recordset the Autonumber
will be available to be read.

There could be several things going on here, Brian.

When you concatenate a date value into a string, you need to format it the
way JET expects. Otherwise it will appear in keeping with the user's
regional settings, which might explain why it works on some machines and not
others:
Dim strWhere As String
strWhere = "([BatchDate] = #" & Format([BatchDate], "mm\/dd\/yyyy") & _
"#) AND ([BatchTime] = #" & Format([BatchTime], "hh\:nn\:ss") &
"#)")
BatchIDTemp = DLookup("[BatchID]", "[InvoiceBatch]", strWhere)

Date/Time fields are a particular kind of floating point time, so there is
always a chance that they don't match exactly. You may have to change it to
handle 1 second either way (Between ... And ...) if it still doesn't match.

We don't see where you declared BatchIDTemp, but presumably it's a Long
since it cannot handle the null. There's always a chance that DLookup()
returns a null, so it should be declared as a Variant (or use Nz() to handle
the null case.)

But a better solution might be to ask Access to give you the new ID number.
This kind of thing:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = DBEngine(0)(0)
strSQL = "INSERT INTO InvoiceBatch ...
db.Execute strSql, dbFailOnError

Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
BatchIDTemp = rs!LastID
rs.Close

Set rs = Nothing
Set db = Nothing
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am getting an "Invalid use of null" when performing a DLookup of a record
immediately after inserting it.

'capture date & time
Dim BatchDate As Date
Dim BatchTime As Date
BatchDate = Date
BatchTime = Time
'create invoice batch
Dim strSQL As String
strSQL = "INSERT INTO InvoiceBatch (BatchDate, BatchTime)SELECT Date() AS
BatchDate, Time() AS BatchTime;"
DoCmd.RunSQL strSQL
'capture current batch number to insert into each invoice
BatchIDTemp = DLookup("[BatchID]", "[InvoiceBatch]", "[BatchDate] = #"
&
BatchDate & "# and [BatchTime] = #" & BatchTime & "#")

[Lots more code below here]

I get an "Invalid use of Null on the last line (BatchIDTemp = ...), even
though I have verified that the record is correctly inserted into the
table
via on the RunSQL statement above. This happens only only on two
workstations. Two other workstations do not have this problem.

Front end is an MDE distributed to each of five WinXP Pro workstations,
each
running Access 2003 runtime. Back end is MDB housed on Windows 2003
server.

For what it is worth, before I started debugging to pinpoint the location
of
the problem by inserting MsgBoxes at key points, the users were getting a
"3027: Cannot Update. Database or object is read-only error." They were
also
receiving a 3027 error on another form that does not write to the back end
(just runs several select SQL statements). However, they had no problem
making any other changes in the database.

The application has been running for eight months prior to this without a
problem. I just rebooted the server to see if there is some network
share/security issue but will not know the effect of that until tomorrow &
thought I should post here in the meantime.