Filter combobox continuous form and print

[foshizzle]

Hi,

I have almost completed creating a continuous form which I want users to be able to filter though the records based on to/from date, badgenum and a response string.

I then want to have a button, btnSelect, that will select the remaining records and open a report based off only the remaining records.


1.) Everything on the continuous form works except when trying to filter a combo box, cboBadge, field name "BadgeNum". BadgeNum is data type "short text". I have modified the code below from the following Allen Browne link. If someone could please take a look at it and see what I am doing wrong. It keeps throwing a debug on me.Filter = strWhere at the bottom of the btnFilter sub. (Im sure its something simple)

2.) As far as the btnSelect and printing remaining records, I guess I'm not sure where to start.. I currently have another print button that will print individual records only which works fine.

thanks in advance


http://allenbrowne.com/ser-62code.html

Private Sub btnFilter_Click()
    Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria string to append to.
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
 
    '***********************************************************************
    'Look at each search box, and build up the criteria string from the non-blank ones.
    '***********************************************************************
    'Text field example. Use quotes around the value in the string.
    If Not IsNull(Me.txtResponse) Then
        strWhere = strWhere & "([Response] = """ & Me.txtResponse & """) AND "
    End If
 
    'Number field example. Do not add the extra quotes.
    'If Not IsNull(Me.cboBadge) Then
    '    strWhere = strWhere & "([BadgeNum] = " & Me.cboBadge & ") AND "
        strWhere = strWhere & " AND BadgeNum = " & Me.cboBadge
    'End If
 
    'Yes/No field and combo example. If combo is blank or contains "ALL", we do nothing.
    'If Me.cboBadge = -1 Then
    '    strWhere = strWhere & "([BadgeNum] = True) AND "
    'ElseIf Me.cboBadge = 0 Then
    '    strWhere = strWhere & "([BadgeNum] = False) AND "
    'End If
 
 
    'Date field example. Use the format string to add the # delimiters and get the right international format.
    If Not IsNull(Me.txtStartDate) Then
        strWhere = strWhere & "([EntryDate] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "
    End If
 
    'Another date field example. Use "less than the next day" since this field has times as well as dates.
    If Not IsNull(Me.txtEndDate) Then   'Less than the next day.
        strWhere = strWhere & "([EntryDate] < " & Format(Me.txtEndDate + 1, conJetDate) & ") AND "
    End If
 
    '***********************************************************************
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    '***********************************************************************
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    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

 

[spikepl]

'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
'Debug.Print strWhere

Well why don't you?

 

[foshizzle]

Well, never noticed that
I used

strWhere = strWhere & "[BadgeNum] ='" & Me.cboBadge & "' AND "

instead of the original line due to the data type

 

[foshizzle]

By chance would you have any idea how to run a report based off the remaining records on my continuous form?

 

[foshizzle]

Uh oh - well, works mostly works...

cboBadge (combo box) works when filtering by itself or with txtResponse (string), but it does not work with the txtStartDate and txtEndDate filters...?