Error 94: Invalid Use of Null (1 Viewer)

[PatAccess]

Hello very smart people,
I have the following code:

Option Compare Database
Option Explicit

Dim db As DAO.Database
Dim rs As Recordset

Private Sub Form_Load()
   Set db = CurrentDb
   Set rs = db.OpenRecordset("Qry_User", dbOpenDynaset, dbSeeChanges)

   Do Until rs.EOF
      Me.LstUser.AddItem rs("UserLogin")
      rs.MoveNext
   Loop
End Sub

It previously worked then all of sudden when I open the form, it gives me Error 94 Invalid use of Null on the Me.LstUser.AddItem rs("UserLogin") Step.

What am I missing here? :banghead::banghead:

Thank you

 

[MarkK]

The field UserLogin as returned by the query "Qry_User" has a null value, which you are attempting to add to a listbox, which fails. Check your data. Also, you could re-query your query to eliminate this kind of data problem, like...

Private Sub Form_Load()
   Const SQL As String = _
      "SELECT UserLogin FROM Qry_User WHERE UserLogin Is Not Null"

   With CurrentDb.OpenRecordset(SQL)
      Do While Not .EOF
         Me.LstUser.AddItem !UserLogin
         .MoveNext
      Loop
      .Close
   End With
End Sub

See what is happening there?
Mark

 

[PatAccess]

WOW! That was exactly it. I have a whole empty record that I didn't see but basically

Const SQL As String = _
"SELECT UserLogin FROM Qry_User WHERE UserLogin Is Not Null"

With CurrentDb.OpenRecordset(SQL)

Would handle the issue in the future? Correct?

 

[MarkK]

Well, my code kind of hides the problem, right? Doesn't really solve it. There should probably not be a null value in that field, so rather than use the code I posted, you should find out why your data has a hole in it, and prevent that hole from occurring again, as a more fundamental solution.
Mark

 

[PatAccess]

Thank you for your help!
I have a better understanding of it