Invalid Use of NULL

[JezLisle]

I keep getting an error message Invalid Use of NULL when trying to select data from a list box on another form and then display the results in my main form.

This is my code on the search form...

Private Sub lstSearch_DblClick(Cancel As Integer)
    Call UnLockAll
    If IsNull(Me.lstSearch) Then
        MsgBox "Select from the list", vbExclamation
        Exit Sub
    End If
    Form_frmInputData.Initialise Me.lstSearch
    Form_frmSearchRecords.Visible = False
End Sub

The debugger highlights line
Form_frmInputData.Initialise Me.lstSearch
as the problem.

This is my code on the main form

Sub Initialise(strPatientRef As String)
Dim cnn As ADODB.Connection
Dim sQRY As String
'*************************************************************************************
    Set cnn = New ADODB.Connection
        cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;User Id=Admin; " & _
        "Data Source=" & cTables
'*************************************************************************************
    DoCmd.SetWarnings False
    Call UnLockAll
    Me.cboGender.Enabled = True
    Me.cboReferringAgent.Enabled = True
    Me.cboReferralDestination.Enabled = True
    Me.cboReasonNotAccepted.Enabled = True
    Me.cboChangedDestination.Enabled = True
    Me.cmdSubmit.Enabled = True
    Me.cmdSearchRecords.Enabled = True
    Me.cmdMainMenu.Enabled = True
        
        sQRY = _
            "SELECT PatientID, PatientRef, Forename, Surname, Name, Address1, Address2, Address3, " & _
            "PostCode, DateOfBirth, Age, Gender, ReceivedDate, ReceivedTime, ReferringAgent, ReferralDestination, " & _
            "Comments, ReasonNotAccepted, ChangedDestination, ChangedDestinationComments, ChangedInputBy, " & _
            "ChangedInputDate, InputBy, InputDate, InputFlag " & _
            "FROM tblICReferralRecord " & _
            "WHERE PatientRef = '" & strPatientRef & "' "
            
        Debug.Print sQRY
        
    Me.RecordSource = sQRY
    Me.txtPatientRef = IIf(IsNull(strPatientRef), "", (strPatientRef))
    Me.txtDummy.SetFocus
    Me.txtForename.Value = PCASE(Me.txtForename.Value)
    Me.txtSurname.Value = PCASE(Me.txtSurname.Value)
    cnn.Close
    Set cnn = Nothing
End Sub

Where have I gone wrong?

 

[boblarson]

The debugger highlights line
Form_frmInputData.Initialise Me.lstSearch
as the problem.

I know you are testing for nulls and then trying to bypass, but you might also try doing

    Form_frmInputData.Initialise Nz(Me.lstSearch,"")

 

[MarkK]

Code breaks at the line in question because there's an unhandled Invalid Use of Null error in the Initialise routine somewhere. To get the debugger to highlight the problem in that routine do this...
In a code window menu navigate to Tools->Options->General Tab->Error Trapping Section and set the 'Break in Class Module' option, not 'Break on Unhandled Errors'
Now run the code again and the debugger will break at the error in the Initialise procedure, which is a class module. I bet it's where you PCASE the names, but ...
Cheers,

 

[JezLisle]

I have tried what lagbolt has said. The Initialise sub was where the problem is. I looked at other issue and saw that the connection did not have a recordset. I have now included this in and still have a debug error. I have checked the names in the table to make sure fields are named correctly

runtime error - Item cannot be found in the collection corresponding to the requested name or ordinal.

What does that mean?

this below is my new code

Sub Initialise(strPatientRef As String)
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sQRY As String
'*************************************************************************************
    Set cnn = New ADODB.Connection
    Set rs = New ADODB.Recordset
        cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;User Id=Admin; " & _
        "Data Source=" & cTables
'*************************************************************************************
    DoCmd.SetWarnings False
    Call UnLockAll
    Me.cboGender.Enabled = True
    Me.cboReferringAgent.Enabled = True
    Me.cboReferralDestination.Enabled = True
    Me.cboReasonNotAccepted.Enabled = True
    Me.cboChangedDestination.Enabled = True
    Me.cmdSubmit.Enabled = True
    Me.cmdSearchRecords.Enabled = True
    Me.cmdMainMenu.Enabled = True
        
        sQRY = _
            "SELECT PatientID, PatientRef, Forename, Surname, Name, Address1, Address2, Address3, " & _
            "PostCode, DateOfBirth, Age, Gender, ReceivedDate, ReceivedTime, ReferringAgent, ReferralDestination, " & _
            "Comments, ReasonNotAccepted, ChangedDestination, ChangedDestinationComments, ChangedInputBy, " & _
            "ChangedInputDate, InputBy, InputDate, InputFlag " & _
            "FROM tblICReferralRecord " & _
            "WHERE PatientRef = '" & strPatientRef & "' "
            
        Debug.Print sQRY
        
    Me.RecordSource = sQRY
    Me.txtPatientID = rs.Fields("PatientID")
    Me.txtPatientRef = strPatientRef
    Me.txtForename = rs.Fields("Forename")
    Me.txtSurname = rs.Fields("Surname")
    Me.txtName = rs.Fields("Name")
    Me.txtAddress1 = rs.Fields("Address1")
    Me.txtAddress2 = rs.Fields("Address2")
    Me.txtAddress3 = rs.Fields("Address3")
    Me.txtPostcode = rs.Fields("PostCode")
    Me.txtDOB = rs.Fields("DateOfBirth")
    Me.txtAge = rs.Fields("Age")
    Me.cboGender = rs.Fields("Gender")
    Me.txtReceivedDate = rs.Fields("ReceivedDate")
    Me.txtReceivedTime = rs.Fields("ReceivedTime")
    Me.cboReferringAgent = rs.Fields("ReferringAgent")
    Me.cboReferralDestination = rs.Fields("ReferralDestination")
    Me.txtComments = rs.Fields("Comments")
    Me.cboReasonNotAccepted = rs.Fields("ReasonNotAccepted")
    Me.cboChangedDestination = rs.Fields("ChangedDestination")
    Me.txtChangedComments = rs.Fields("ChangedDestinationComments")
    Me.txtChangedInputDate = rs.Fields("ChangedInputDate")
    Me.txtChangedInputUser = rs.Fields("ChangedInputBy")
    Me.txtInputDate = rs.Fields("InputDate")
    Me.txtInputUser = rs.Fields("InputBy")
    Me.chkInputFlag = rs.Fields("InputFlag")
    
    Me.txtDummy.SetFocus
'    Me.txtForename.Value = PCASE(Me.txtForename.Value)
'    Me.txtSurname.Value = PCASE(Me.txtSurname.Value)
    
    rs.Close
    cnn.Close
    Set rs = Nothing
    Set cnn = Nothing
End Sub

 

[JezLisle]

I have found what the problem was. I had missed out the line of code

rs.Open sQRY, CurrentProject.Connection, adOpenDynamic, adLockOptimistic