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
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Brian" <(E-Mail Removed)> wrote in message
news:7357FD83-D55A-4695-8735-(E-Mail Removed)...
>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.
Similar Threads
