How to avoid Null's in VBA

[PNGBill]

Hi Forum,

Have had issues with Null's in VBA code and wonder if the following can be used to avoid some of these occurrences.

This Code finds a matching record in a table.

Private Sub ADPK_AfterUpdate()

    Dim DB As Database, TData As Recordset
    Dim ADPK As Integer
    
    ADPK = Me.ADPK
    
    
        Set DB = DBEngine.Workspaces(0).Databases(0)
        Set TData = DB.OpenRecordset("TblPointsTraded", DB_OPEN_TABLE)
    TData.Index = "MemberADPK"
                TData.Seek "=", ADPK
                If Not TData.NoMatch Then
                    MsgBox TData![MemberADPK]
                End If
    TData.Close
    
End Sub

Can we put this, or similar, ahead of the next code so that if a record exits, the 2nd code is actioned and if no record exits, then GetMemPointsTraded = 0.

Public Function GetMemPointsTraded(RecordID As Variant) As Long
            'RecordID is the Control Value on the Form or Report
            
            Dim rst As DAO.Recordset
            Dim sqlString As String
            
              'Points Earned Single Loan on Report or Form
            sqlString = "SELECT Sum(ClubPointsTraded) AS Traded " & _
                "FROM TblPointsTraded " & _
                "WHERE (((ADPK)=" & RecordID & "));"
      
    Set rst = CurrentDb.OpenRecordset(sqlString)

    If rst.RecordCount <> 0 Then
         'Assign SQL result to Variable
         GetMemPointsTraded = rst!Traded
    Else
         GetMemPointsTraded = 0
    End If

    rst.Close
    Set rst = Nothing
            
End Function

Appreciate any guidance

 

[vbaInet]

Are you still having Null issues?

 

[PNGBill]

Only on the one code listed above.

The rest work fine. had 13 hour blackout yesterday so couldn't do any posting

I resolved this by making a query that listed all ADPK records with zero where no record exists and this replaced the Table in the code. (changed back for this posting)

Just seems to me if a check could be done to determine if any record exists first then we can just jump to the end result "=0" and avoid the code that evaluates the records if they do exist.

 

[vbaInet]

My street is getting disconnected for 13 hours as well in 4 hours time to allow for new installations. I'll be out of the house by then

All that needs to be changed is this bit:

              'Points Earned Single Loan on Report or Form
            sqlString = "SELECT Sum(ClubPointsTraded) AS Traded " & _
                "FROM TblPointsTraded " & _
                "WHERE (((ADPK)=" &[COLOR=Red] [B]Nz([/B][/COLOR]RecordID[COLOR=Red][B],0)[/B][/COLOR] & "));"

The Nz() will handle the input of Null, the RecordCount will handle the recordset returning nothing. No need for incorporating that function in there.

 

[PNGBill]

Added Nz but still only works where data exists.

 

[vbaInet]

What is the error message and where does it point to?

 

[PNGBill]

Run-time error '94'

Invalid use of Null

Points to: GetMemPointsTraded = rst!Traded

Only errors where no record exists.

 

[vbaInet]

Actually that's pointing to where record count is greater than 0 so that means there are records. Use MoveFirst:

    If rst.RecordCount <> 0 Then
[COLOR=Red][B]           rst.MoveFirst[/B][/COLOR]
         'Assign SQL result to Variable
         GetMemPointsTraded = rst!Traded
    Else
         GetMemPointsTraded = 0
    End If

 

[PNGBill]

Still fails where no record exists.

The failure is where a Customer has not Traded their Club Points which means in the table TblPointsTraded there is no record with a matching MemberADPK to the forms ADPK (RecordID)

Where there has been Points Traded and a record exists, the code completes.

 

[vbaInet]

I see. This should do it:

    If rst.RecordCount <> 0 Then
     rst.MoveFirst
         'Assign SQL result to Variable
         GetMemPointsTraded = [COLOR=Red][B]Nz([/B][/COLOR]rst!Traded[COLOR=Red][B], 0)[/B][/COLOR]
    Else
         GetMemPointsTraded = 0
    End If

 

[PNGBill]

My street is getting disconnected for 13 hours as well in 4 hours time to allow for new installations. I'll be out of the house by then

This will mean you will miss an episode of The Bill or Coronation Street?

 

[vbaInet]

I don't mind because I don't watch Soaps anyway

 

[PNGBill]

I see. This should do it:

    If rst.RecordCount <> 0 Then
     rst.MoveFirst
         'Assign SQL result to Variable
         GetMemPointsTraded = [COLOR=Red][B]Nz([/B][/COLOR]rst!Traded[COLOR=Red][B], 0)[/B][/COLOR]
    Else
         GetMemPointsTraded = 0
    End If

Resolved - Many thanks vbaInet

Just in time before you are Off The Air.

Our Power just it self.

We have an Emergency battery system that kicks in and should give us 12 hrs but often is moaning at 2 or 3 hours.

 

[PNGBill]

I don't mind because I don't watch Soaps anyway

We liked the BBC programmes such as Hornblower and of course Miss Marple and her friends @ Agatha Christie.

No TV now. Just watch movies (DVD's) and internet.

With travel, we were paying for a cable tv (MMDS wireless ) that was only used 1/2 of the time.

 

[vbaInet]

Still have another 3 hours of light. Yeepee!

Just out of interest, could you do an Msgbox of rst.RecordCount for when it normally throws the null problem. What value does it return.

 

[PNGBill]

Still have another 3 hours of light. Yeepee!

Just out of interest, could you do an Msgbox of rst.RecordCount for when it normally throws the null problem. What value does it return.

With the latest change, it returns one and works for records with and without data in the table.

Without the latest update, the count is still 1 but the procedure crashes when no data present and code line highlighted yellow is
GetMemPointsTraded = rst!Traded

 

[PNGBill]

Because we are near the equator, sunset is 6pm or 6:30pm all year depending if Summer or Winter - down south, not that we notice any difference in temperature

 

[vbaInet]

I meant use this and tell me what the msgbox shows when no data is present:

    msgbox rst.RecordCount
    exit function
    If rst.RecordCount <> 0 Then
     rst.MoveFirst
         'Assign SQL result to Variable
         GetMemPointsTraded = Nz(rst!Traded, 0)
    Else
         GetMemPointsTraded = 0
    End If

Ah, I see. Always hot I would imagine?

 

[PNGBill]

I meant use this and tell me what the msgbox shows when no data is present:

    msgbox rst.RecordCount
    exit function
    If rst.RecordCount <> 0 Then
     rst.MoveFirst
         'Assign SQL result to Variable
         GetMemPointsTraded = Nz(rst!Traded, 0)
    Else
         GetMemPointsTraded = 0
    End If

Ah, I see. Always hot I would imagine?

This is the code I used:

Public Function GetMemPointsTraded(RecordID As Variant) As Long
            'RecordID is the Control Value on the Form or Report
            
            Dim rst As DAO.Recordset
            Dim sqlString As String
            
              'Points Earned Single Loan on Report or Form
            sqlString = "SELECT Sum(PointsTraded) AS Traded " & _
                "FROM TblPointsTraded " & _
                "WHERE (((MemberADPK)=" & NZ(RecordID, 0) & "));"
      
    Set rst = CurrentDb.OpenRecordset(sqlString)
    
    MsgBox rst.RecordCount
    
    If rst.RecordCount <> 0 Then
        rst.MoveFirst
         'Assign SQL result to Variable
         'GetMemPointsTraded = NZ(rst!Traded, 0)
         GetMemPointsTraded = rst!Traded
    Else
         GetMemPointsTraded = 0
    End If

    rst.Close
    Set rst = Nothing
            
End Function

In all cases it sent a message box with 1 and when I clicked on same the Report displayed except where no data was present and the procedure crashed.

 

[PNGBill]

I meant use this and tell me what the msgbox shows when no data is present:

    msgbox rst.RecordCount
    exit function
    If rst.RecordCount <> 0 Then
     rst.MoveFirst
         'Assign SQL result to Variable
         GetMemPointsTraded = Nz(rst!Traded, 0)
    Else
         GetMemPointsTraded = 0
    End If

Ah, I see. Always hot I would imagine?

With this code, there is no crash even when no data is present and the message is 1 even when no data present.

daily temp 29, 30 or 31deg C

gets cold at night, can be as low as 18 deg C