Styjun

I am currently trying to upgrade an old ADP project from Access 2010 x64 to Access 2019 x64. I have managed to convert it to an .accdb file, but are now running into errors with my VBA code.

Please consider the following function:

Public Function GetSystemSetting(sKey As String, vValue As Variant) As Boolean
 Dim cnTemp As ADODB.Connection, rsTemp As ADODB.Recordset
 Dim sSQL As String
 On Error GoTo LAB_Error
 sSQL = "SELECT T_Value FROM INT_SystemSettings WHERE (T_Key = '" & sKey & "')"
 Set cnTemp = New ADODB.Connection
 Set rsTemp = New ADODB.Recordset
 cnTemp.CursorLocation = adUseServer
 cnTemp.Open CurrentProject.BaseConnectionString
 rsTemp.Open sSQL, cnTemp, adOpenForwardOnly, adLockReadOnly
 If (rsTemp.EOF) Then GoTo LAB_Error
 vValue = Nz(rsTemp![T_Value])
 rsTemp.Close
 cnTemp.Close
 On Error GoTo 0
 GetSystemSetting = True
 Exit Function
LAB_Error:
 vValue = Null
 If (rsTemp.State <> adStateClosed) Then rsTemp.Close
 If (cnTemp.State <> adStateClosed) Then cnTemp.Close
 GetSystemSetting = False
End Function

I know that this code is questionable in many aspects, but would like to focus on the line

vValue = Null

When this line gets executed, a runtime error is raised:

Invalid use of Null

I have read dozens of articles about that error message on various sites, including this one, but it always boiled down to that the OP hadn't made the destination variable a variant. But in my case, the destination variable, vValue, is of type variant. Furthermore, that code ran since 8 years without any problem in Access 2010 x64.

What is the reason for that error, and how can I prevent it?

It is important to remember that with functions like this:

Public Function GetSystemSetting(sKey As String, vValue As Variant) As Boolean
 vValue = Null

unless you specify ByVal, the parameters are passed ByRef, and so you are actually writing to the variable that is used as parameter when calling the function.

If that variable isn't a variant, the error is triggered.

Dim str As String
If GetSystemSetting("non-existing", str) Then ' KA-BOOM!

An alternative with DLookup would be the following. It should behave exactly the same, unless you have valid SystemSettings that are NULL.

Public Function GetSystemSetting(sKey As String, vValue As Variant) As Boolean
 ' DLookup returns NULL if no record is found
 vValue = DLookup("T_Value", "INT_SystemSettings", "T_Key = '" & sKey & "'")
 GetSystemSetting = Not IsNull(vValue)
End Function

DLookup is a read-only operation, so it should be the same regarding locking.