Solved Issue with Null Value

[PatAccess]

Hello all,

I have a lot of issue with Null Values. Here is the code

Private Sub cboRestaurant_AfterUpdate()
    Dim strSQL As String
    strSQL = SelectFromQryCateringsWhere & "WHERE RestaurantName='" & Me.cboRestaurant.Column(0) & "'"
    
    Me.txtContactName.Value = CurrentDb.OpenRecordset(strSQL).Fields(4).Value
    Me.txtTn1.Value = CurrentDb.OpenRecordset(strSQL).Fields(5).Value
    Me.txtTn2.Value = CurrentDb.OpenRecordset(strSQL).Fields(6).Value
    
    Dim strEmail As String
    strEmail = CurrentDb.OpenRecordset(strSQL).Fields(7).Value
    If Not IsNull(strEmail) Then
        Me.cmdSendEmail.Visible = True
    End If
    
    Dim strSite As String
    strSite = CurrentDb.OpenRecordset(strSQL).Fields(8).Value
    If IsNull(strSite) Then
        MsgBox "No Website is available"
    Else
        Me.cmdGotoSite.Visible = True
    End If
    
    
'    If Not IsNull(strSite) Then
'        Me.cmdGotoSite.Visible = True
'    ElseIf IsNull(strSite) Then
'        MsgBox "No Website is available"
'    End If
    
    Dim strMenu As String
    strMenu = CurrentDb.OpenRecordset(strSQL).Fields(11).Value
    If Not IsNull(strMenu) Then
        Me.Command123.Visible = True
    End If
    
    Me.txtAddress = CurrentDb.OpenRecordset(strSQL).Fields(9).Value
    Me.txtComments = CurrentDb.OpenRecordset(strSQL).Fields(12).Value
    
End Sub

The issue is when strSite is empty, it gives me Error 94 Invalid use of Null but that particular field is not required to have data which is why I put that the if IsNull() but it is not working. What am doing wrong here?

 

[June7]

strSite is declared as string type. Only variant type can hold Null so code errors when attempting to fill strSite with Null or if recordset is empty. Need to test if recordset is empty and/or field Is Null. Same for strMenu.

Open a recordset object and pull values only if recordset is not empty.

Or instead of a recordset, include all required fields in combobox RowSource then reference those columns to pull related data.

Where is SelectFromQryCateringsWhere variable set? Possibly need a space added in front of "WHERE..."

 

[GinaWhipp]

Does your Recordsource only return one line of data? If it does not than that is your Null error. The other issue is it could EMPTY which is not the same as NULL. You might consider adding...

Or strSite <> ""

 

[CJ_London]

have you tried to debug.print strlsql after assigning it a value?

strSQL = SelectFromQryCateringsWhere & "WHERE RestaurantName='" & Me.cboRestaurant.Column(0) & "'"

just wondering if there should be a space before WHERE although it would appear your error occurs on the 5th time of opening.

Why open the recordset numerous times? It's pretty wasteful. Usual method would be to assign it once to a recordset then assign your values

dim rs as dao.recordset
strSQL = SelectFromQryCateringsWhere & "WHERE RestaurantName='" & Me.cboRestaurant.Column(0) & "'"
   
set rs= CurrentDb.OpenRecordset(strSQL)
if not rs.EOF then
     Me.txtTn1= nz(rs.Fields(5))
     Me.txtTn2= nz(rs.Fields(6))
     ....
     ...
end if
rs.close

 

[LarryE]

Instead of using If Not IsNull(strSite) try using
If IsNull(strSite)=False Then
Also, when dealing with Nulls, Empty strings and other unknown values it's sometimes helpful to test a string for its length rather than if it's Null or Empty. So, you could try:
If Len(strSite)<1 Then

 

[PatAccess]

strSite is declared as string type. Only variant type can hold Null so code errors when attempting to fill strSite with Null or if recordset is empty. Need to test if recordset is empty and/or field Is Null. Same for strMenu.

Open a recordset object and pull values only if recordset is not empty.

Or instead of a recordset, include all required fields in combobox RowSource then reference those columns to pull related data.

Where is SelectFromQryCateringsWhere variable set? Possibly need a space added in front of "WHERE..."

Thank you so much. I changed the data type from String to variant and it works. I did not know that bit about only variant being able to hold null values. Thanks again. That did it!!!

 

[PatAccess]

Instead of using If Not IsNull(strSite) try using
If IsNull(strSite)=False Then
Also, when dealing with Nulls, Empty strings and other unknown values it's sometimes helpful to test a string for its length rather than if it's Null or Empty. So, you could try:
If Len(strSite)<1 Then

Good Information. Thank you

 

[PatAccess]

have you tried to debug.print strlsql after assigning it a value?

strSQL = SelectFromQryCateringsWhere & "WHERE RestaurantName='" & Me.cboRestaurant.Column(0) & "'"

just wondering if there should be a space before WHERE although it would appear your error occurs on the 5th time of opening.

Why open the recordset numerous times? It's pretty wasteful. Usual method would be to assign it once to a recordset then assign your values

dim rs as dao.recordset
strSQL = SelectFromQryCateringsWhere & "WHERE RestaurantName='" & Me.cboRestaurant.Column(0) & "'"
  
set rs= CurrentDb.OpenRecordset(strSQL)
if not rs.EOF then
     Me.txtTn1= nz(rs.Fields(5))
     Me.txtTn2= nz(rs.Fields(6))
     ....
     ...
end if
rs.close

Changing the data type from String to variant worked.