Detail_Print

[CamtheChamp]

Hey,



So I am working on a report where I am using code to fill an unbound text field in the detail section of the report. It works great when I first open the report but my code does not work when using the filtering form that someone else wrote a long time ago. I have been working on this for a couple weeks and can't seem to find a way to resolve the issue.

I have stored my code in a function and the problem lies in actually entering the function. I call the function in Detail_Print:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Me.FrameTypes = DisplayFrameTypes(Me.EngineeringCostModelID)
 
End Sub

EngineeringCostModelID is a field in the report query. When I run the code below, it throws up the error: Run-time error '94': Invalid use of Null.

It is saying that EngineeringCostModelID is Null, but that doesn't make any sense. Why would this code work fine when loading a report but fail when filtering? It has something to do with the way I am activating Detail_Print.


I know it has to be something simple that I am not familiar with yet since I am still relatively new to VBA.

Private Sub cmdApplyFilter_Click()
    Dim varItem As Variant
    Dim strFrameType As String
    Dim strSequenceNumber As String
    Dim strCNS As String
    Dim strBuyer As String
    Dim strFilter As String
    Dim strSortOrder As String
     
' Check that the report is open
    If SysCmd(acSysCmdGetObjectState, acReport, "rptECM_VarianceDetail") <> acObjStateOpen Then
        MsgBox "You must open the Report titled 'rptECM_VarianceDetail' first."
        Exit Sub
    End If
    
    
' Build criteria string from lstFrameType listbox
    For Each varItem In Me.lstFrameType.ItemsSelected
        strFrameType = strFrameType & ",'" & Me.lstFrameType.ItemData(varItem) _
        & "'"
    Next varItem
    If Len(strFrameType) = 0 Then
        strFrameType = "Like '*'"
    Else
        strFrameType = Right(strFrameType, Len(strFrameType) - 1)
        strFrameType = "IN(" & strFrameType & ")"
    End If
             
' Build criteria string from lstSequenceNumber listbox
    For Each varItem In Me.lstSequenceNumber.ItemsSelected
        strSequenceNumber = strSequenceNumber & "," & Me.lstSequenceNumber.ItemData(varItem) _
        & ""
    Next varItem
    If Len(strSequenceNumber) = 0 Then
        strSequenceNumber = "Like '*'"
    Else
        strSequenceNumber = Right(strSequenceNumber, Len(strSequenceNumber) - 1)
        strSequenceNumber = "IN(" & strSequenceNumber & ")"
    End If

' Build criteria string from lstCNS listbox
    For Each varItem In Me.lstCNS.ItemsSelected
        strCNS = strCNS & "," & Me.lstCNS.ItemData(varItem) _
        & ""
    Next varItem
    If Len(strCNS) = 0 Then
        strCNS = "Like '*'"
    Else
        strCNS = Right(strCNS, Len(strCNS) - 1)
        strCNS = "IN(" & strCNS & ")"
    End If
' Build criteria string from lstBuyer listbox
    For Each varItem In Me.lstBuyer.ItemsSelected
        strBuyer = strBuyer & ",'" & Me.lstBuyer.ItemData(varItem) _
        & "'"
    Next varItem
    If Len(strBuyer) = 0 Then
        strBuyer = "Like '*'"
    Else
        strBuyer = Right(strBuyer, Len(strBuyer) - 1)
        strBuyer = "IN(" & strBuyer & ")"
    End If

' Build filter string
    strFilter = "[SequenceNumber] " & strSequenceNumber & _
                " AND [CNS] " & strCNS & _
                " AND [FrameType] " & strFrameType & _
                " AND [BUYER] " & strBuyer

' Build sort string
    If Me.cboSortOrder1.Value <> "Not Sorted" Then
        strSortOrder = "[" & Me.cboSortOrder1.Value & "]"
        If Me.cmdSortDirection1.Caption = "Descending" Then
            strSortOrder = strSortOrder & " DESC"
        End If
        If Me.cboSortOrder2.Value <> "Not Sorted" Then
            strSortOrder = strSortOrder & ",[" & Me.cboSortOrder2.Value & "]"
            If Me.cmdSortDirection2.Caption = "Descending" Then
                strSortOrder = strSortOrder & " DESC"
            End If
            If Me.cboSortOrder3.Value <> "Not Sorted" Then
                strSortOrder = strSortOrder & ",[" & Me.cboSortOrder3.Value & "]"
                If Me.cmdSortDirection3.Caption = "Descending" Then
                strSortOrder = strSortOrder & " DESC"
                End If
            End If
        End If
    End If
' Apply filter and sort to report
    With Reports![rptECM_VarianceDetail]
        .Filter = strFilter
        .FilterOn = True
        .OrderBy = strSortOrder
        .OrderByOn = True
    End With
End Sub

[June7]

What is FrameTypes? What is the function code?

Have you step debugged? Follow the code as it executes.

[CamtheChamp]

What is FrameTypes? What is the function code?

Have you step debugged? Follow the code as it executes.

FrameTypes is the unbound textbox that I am filling with data.

I have step debugged, it fails when it attempts to enter the function but everything else works fine.

The function code is:

Public Function DisplayFrameTypes(engCMID As String)
Dim FrameTypeText As String
Dim SQL As String
Dim MyRec As ADODB.Recordset
SQL = "SELECT qryfrmFrameTypeList.EngineeringCostModelID, qryfrmFrameTypeList.FrameType1, qryfrmFrameTypeList.FrameType2, qryfrmFrameTypeList.FrameType3, qryfrmFrameTypeList.FrameType4, qryfrmFrameTypeList.FrameType5, qryfrmFrameTypeList.FrameType6, qryfrmFrameTypeList.FrameType7, qryfrmFrameTypeList.FrameType8, qryfrmFrameTypeList.FrameType9, qryfrmFrameTypeList.FrameType10, qryfrmFrameTypeList.FrameType11, qryfrmFrameTypeList.FrameType12 " & _
"FROM qryfrmFrameTypeList " & _
"WHERE qryfrmFrameTypeList.EngineeringCostModelID =" & engCMID & ""
 
Set MyRec = New ADODB.Recordset
MyRec.Open SQL, CurrentProject.Connection
If MyRec("FrameType1") = -1 Then FrameTypeText = "SGen6-Aux" & vbCrLf
If MyRec("FrameType2") = -1 Then FrameTypeText = FrameTypeText & "SGT6-2000E" & vbCrLf
If MyRec("FrameType3") = -1 Then FrameTypeText = FrameTypeText & "SGT6-5000F4" & vbCrLf
If MyRec("FrameType4") = -1 Then FrameTypeText = FrameTypeText & "SGT6-5000F5" & vbCrLf
If MyRec("FrameType5") = -1 Then FrameTypeText = FrameTypeText & "SGT6-5000F6" & vbCrLf
If MyRec("FrameType6") = -1 Then FrameTypeText = FrameTypeText & "SGT6-8000H(1.3)" & vbCrLf
If MyRec("FrameType7") = -1 Then FrameTypeText = FrameTypeText & "SGT6-8000H(SS)" & vbCrLf
If MyRec("FrameType12") = -1 Then FrameTypeText = FrameTypeText & "SGT6-8000H(1.4)" & vbCrLf
If MyRec("FrameType8") = -1 Then FrameTypeText = FrameTypeText & "SST6-1000A(104-50)" & vbCrLf
If MyRec("FrameType9") = -1 Then FrameTypeText = FrameTypeText & "SST6-1000A(104-55)" & vbCrLf
If MyRec("FrameType10") = -1 Then FrameTypeText = FrameTypeText & "SST6-2000H(110-46)" & vbCrLf
If MyRec("FrameType11") = -1 Then FrameTypeText = FrameTypeText & "SST6-PAC" & vbCrLf
 
DisplayFrameTypes = FrameTypeText
MyRec.Close
End Function

[RayMilhon]

Have you looked at your dataset to see if there are any records where Me.EngineeringCostModelID is null? Is that record part of the dataset that results when you apply the filter? The other possibility is that it's a timing issue. The filter is applied the function runs but the field element isn't set yet. just for the fun of it set the parameter as optional then set a breakpoint on the first line of code execute and see what's in the parameter after you filter.