Access VBA in Access confusion.

[Ian Anderson]

Hello community,

this is absolutely baffling me. It should work, and it doesn't and i'm at the end of my limited troubleshooting ability and really need some help. Please.... i'm begging... *sobs*

so the issue is...

Private Sub cmdprint_Click()
Dim strFilter As String

strFilter = Me.Filter

DoCmd.OpenReport "rptfilter", acViewPreview, , strFilter

End Sub

Private Sub FD_Click()
Me.Filter = "[Date_Entered] Between " & _
 Format$(startdate, "\#mm\/dd\/yyyy\#") & _
 " And  " & _
 Format$(enddate, "\#mm\/dd\/yyyy\#")
Me.FilterOn = True

End Sub

This code works, in its own separate Access database. Essentially there are 2 date fields entered via date picker & user entered. Then the button (FD) takes those two fields and filters the form accordingly. The other button (cmdprint) takes that form filter and prints it to a specific report (rptfilter).

Thats all well and good, until I take that code above and enter it into the company database, which has the following code.

Option Compare Database
Option Explicit
Private Sub cmdFilter_Click()
    Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria string to append to.
   
    If Not IsNull(Me.mtr) Then
        strWhere = strWhere & "([materialtrackingnumber] = """ & Me.mtr & """) AND "
    End If
    
    If Not IsNull(Me.filterdia) Then
        strWhere = strWhere & "([diameter] = """ & Me.filterdia & """) AND "
    End If
    
    If Not IsNull(Me.filtersch) Then
        strWhere = strWhere & "([schedule] = """ & Me.filtersch & """) AND "
    End If
    
    If Not IsNull(Me.filterjob) Then
        strWhere = strWhere & "([jobnumber] = """ & Me.filterjob & """) AND "
    End If
    
    If Not IsNull(Me.filterpo) Then
        strWhere = strWhere & "([ponumber] = """ & Me.filterpo & """) AND "
    End If
    
    If Not IsNull(Me.filterheat) Then
        strWhere = strWhere & "([heatnumber] Like ""*" & Me.filterheat & "*"") AND "
    End If
    
    If Not IsNull(Me.filterplate) Then
        strWhere = strWhere & "([platenumber] Like ""*" & Me.filterplate & "*"") AND "
    End If
    
    If Not IsNull(Me.filtermtr) Then
        strWhere = strWhere & "([materialtrackingnumber] >= '" & Me.filtermtr & "' AND [materialtrackingnumber] <= '" & Me.filtermtr2 & "') AND "
    End If
           
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        'Debug.Print strWhere
        
        'Finally, apply the string as the form's Filter.
        
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
End Sub
Private Sub cmdReset_Click()
    'Purpose:   Clear all the search boxes in the Form Header, and show all records again.
    Dim ctl As Control
    
    'Clear all the controls in the Form Header section.
    For Each ctl In Me.Section(acHeader).Controls
        Select Case ctl.ControlType
        Case acTextBox, acComboBox
            ctl.Value = Null
        Case acCheckBox
            ctl.Value = False
        End Select
    Next
    
    'Remove the form's filter.
    Me.FilterOn = False
End Sub
Private Sub Command152_Click()
    'Purpose:   Clear all the search boxes in the Form Header, and show all records again.
    Dim ctl As Control
    
    'Clear all the controls in the Form Header section.
    For Each ctl In Me.Section(acHeader).Controls
        Select Case ctl.ControlType
        Case acTextBox, acComboBox
            ctl.Value = Null
        Case acCheckBox
            ctl.Value = False
        End Select
    Next
    
    'Remove the form's filter.
    Me.FilterOn = False
End Sub
Private Sub cmdOpenReport_Click()
      DoCmd.OpenReport "materialreceiving_rpt", acViewReport, , Me.Filter
    
    End Sub

Private Sub datefilter_Click()

End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
    'To avoid problems if the filter returns no records, we did not set its AllowAdditions to No.
    'We prevent new records by cancelling the form's BeforeInsert event instead.
    'The problems are explained at http://allenbrowne.com/bug-06.html
    Cancel = True
    MsgBox "You cannot add new clients to the search form.", vbInformation, "Permission denied."
End Sub
Private Sub Form_Open(Cancel As Integer)
    'Remove the single quote from these lines if you want to initially show no records.
    Me.Filter = "(False)"
    'Me.FilterOn = True
End Sub
[B]
Private Sub cmdprint_Click()

Dim strFilter As String
strFilter = Me.Filter
DoCmd.OpenReport "rptfilter", acViewPreview, , strFilter
End Sub

Private Sub FD_Click()

Me.Filter = "[Date_Entered] Between " & _
 Format$(startdate, "\#mm\/dd\/yyyy\#") & _
 " And  " & _
 Format$(enddate, "\#mm\/dd\/yyyy\#")
Me.FilterOn = True

End Sub[/B]

I have created the report and buttons identical to the working copy database. The filter button works, essentailly giving me a screen with exactly what i'm trying for, but as soon as i try to print the form to the report via the cmdprint button

The form pops up a dialog box asking me to enter Date_entered.

So anyone with some time, please help me understand why the pop up dialog happens and what kind of solution I have available.

Thanks in advance

Ian Anderson

 

[Ian Anderson]

Im trying to upload a copy of my DB, 4.9 MB but i keep getting a Server error. Ill be happy to email it to anyone with the desire to see it. =D

Thanks again in advance.

Ian