MS Access - Invalid use of null

[malibu65k]

I am getting an Invalid use of null with this part of my code

vMaxId = Rec1("MaxId")

The recordcount is showing 1 but there are no records in the table yet. I have used this in other applications and it has worked fine. Am I missing something or just not seeing it?

Here's my code:

Private Sub AddRecord()

Dim rec1 As DAO.Recordset
Dim db1 As DAO.Database
Dim vMaxId As Integer
Dim rec2 As DAO.Recordset
Dim db2 As DAO.Database
Dim strSQL As String


Set db1 = CurrentDb()
strSQL = "SELECT MAX(ID)AS MaxID FROM Meetings"
Set rec1 = db1.OpenRecordset(strSQL)

Set db2 = CurrentDb()
Set rec2 = db2.OpenRecordset("Meetings")

With rec1
MsgBox rec1.RecordCount <-- here shows me 1 record
If rec1.RecordCount = 0 Then

vMaxId = 1

With rec2

rec2.AddNew
rec2!Meeting = Me.txtMeeting
rec2!Id = vMaxId
rec2.Update

End With
Else

vMaxId = rec1("MaxID") <-- Errors out here

With rec2

rec2.AddNew
rec2!Meeting = Me.txtMeeting
rec2!Id = vMaxId + 1
rec2.Update

End With

End If

End With

rec2.Close

End Sub

Thanks in advance for any help or suggestions from anyone!

 

[PHV]

An aggregate query never returns empty recordset, so rec1.Recordcount should always equal 1.
Take a look at the IsNull or Nz functions.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TonyJollans]

Hi malibu65k,

It's a bit since I've done anything with Access but won't your SELECT MAX will always return 1 record. If there are no records underlying the query then that record will have a null value in the field. I would expect the result you get and am surprised you say you have done this before (but happy to be shown wrong). I would use ..

[blue][tt] vMaxId = [highlight]Nz([/highlight]rec1("MaxID")[highlight])[/highlight][/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[TonyJollans]

You're too fast for me, PH

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[malibu65k]

Thanks! I'll try that. I will let you know either way.

I was probably wrong about my other 2 databases, it may not even be falling into that part of the code. I better check those out also.

Thanks again.

 

[malibu65k]

Perfect! Thanks PHV and TonyJollans