.Openreport method NOT printing ALL records from a filtered form, why?

[Qtip23]

I am using Access 2003 on XP Professional 2003 platform. I have been

struggling with what appears to be a relatively easy coding problem.

I am unable to get my VBA code to show all the records and allow me to

view the filtered responses from user input, once my cmd_click() event

fires.

The form displays the 19 fields from database in an AD HOC form, using a

continuous form format so it allows the user to select what fields they want

to filter.

To aid the user, I have two unbound combo boxes and four unbound text

boxes for user input:

Program name (Combo)
Source (Text)
Liaison (Text)
Status (Combo)
Due Date - From (Text)
Due Date - To (Text)

The filter works great. However, it only displays the FIRST record on the
report. What am I doing incorrectly?

Dim stDocName As String
Dim strWhere As String 'The criteria string.

stDocName = "rptActionItems_Filtered"
DoCmd.OpenReport stDocName, acViewPreview, strWhere

Thanks,

[TheSmileyCoder]

Welcome to Bytes

If you open the report directly, does it show all the records? If not, problem is likely in the recordsource of the report.

If it does, problem might be in the strWhere. Do you actually use this? From the code you posted I dont see anything about it.

[Qtip23]

@TheSmileyOne
Hey there,

Well the report pulls the data from the criteria string based on the table. I do not have any issues with that as the user can easily see his/her selection when they scroll down on the continuous form.

I modified code I found from Adapted from: http://allenbrowne.com to suit my requirement.

I have a button that fires the filtering,here is the code associated:

Private Sub cmdFilter_Click()
On Error GoTo Err_cmdFilter_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.


If Not IsNull(Me.txtFilterLiaison) Then
strWhere = strWhere & "([Liaison_Contact_Information] = """ & Me.txtFilterLiaison & """) AND "
End If

If Not IsNull(Me.txtFilterSource) Then
strWhere = strWhere & "([Source] Like ""*" & Me.txtFilterSource & "*"") AND "
End If

If Not IsNull(Me.cboFilterProgram) Then
strWhere = strWhere & "([Program] Like ""*" & Me.cboFilterProgram & "*"") AND "
End If

If Not IsNull(Me.cboFilterStatus) Then
strWhere = strWhere & "([Status] Like ""*" & Me.cboFilterStatus & "*"") AND "
End If

If Not IsNull(Me.txtStartFilterDate) Then
strWhere = strWhere & "([Due_Date] >= " & Format(Me.txtStartFilterDate, conJetDate) & ") AND "
End If

If Not IsNull(Me.txtEndFilterDate) Then 'Less than the next day.
strWhere = strWhere & "([Due_Date] < " & Format(Me.txtEndFilterDate + 1, conJetDate) & ") 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


Exit_cmdFilter_Click:
Exit Sub

Err_cmdFilter_Click:
MsgBox Err.Description
Resume Exit_cmdFilter_Click



End Sub

Any advise would be greatly appreciated. Thx.

[TheSmileyCoder]

Your report and form is based on the same recordsource?

Please use [code][/code] tags around your code.

[Qtip23]

Thanks. I will definitely use the code tags in the future to delineate the start so I am reposting with the tags.

Private Sub cmdFilter_Click()
On Error GoTo Err_cmdFilter_Click
 
Dim strWhere As String [comment]'The criteria string.[/comment]
Dim lngLen As Long [comment]'Length of the criteria string to append to.[/comment]
Const conJetDate = "\#mm\/dd\/yyyy\#" [comment]'The format expected for dates in a JET query string.[/comment]
 
 
If Not IsNull(Me.txtFilterLiaison) Then
strWhere = strWhere & "([Liaison_Contact_Information] = """ & Me.txtFilterLiaison & """) AND "
End If
 
If Not IsNull(Me.txtFilterSource) Then
strWhere = strWhere & "([Source] Like ""*" & Me.txtFilterSource & "*"") AND "
End If
 
If Not IsNull(Me.cboFilterProgram) Then
strWhere = strWhere & "([Program] Like ""*" & Me.cboFilterProgram & "*"") AND "
End If
 
If Not IsNull(Me.cboFilterStatus) Then
strWhere = strWhere & "([Status] Like ""*" & Me.cboFilterStatus & "*"") AND "
End If
 
If Not IsNull(Me.txtStartFilterDate) Then
strWhere = strWhere & "([Due_Date] >= " & Format(Me.txtStartFilterDate, conJetDate) & ") AND "
End If
 
If Not IsNull(Me.txtEndFilterDate) Then 'Less than the next day.
strWhere = strWhere & "([Due_Date] < " & Format(Me.txtEndFilterDate + 1, conJetDate) & ") AND "
End If
 
 
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then [comment]'Nah: there was nothing in the string.[/comment]
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the " AND " at the end.
strWhere = Left$(strWhere, lngLen)
 
[comment]'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).[/comment]
 
Debug.Print strWhere
 
[comment] 'Finally, apply the string as the form's Filter.[/comment]
Me.Filter = strWhere
Me.FilterOn = True
End If
 
 
Exit_cmdFilter_Click:
Exit Sub
 
Err_cmdFilter_Click:
MsgBox Err.Description
Resume Exit_cmdFilter_Click
 
 
 
End Sub
 
 

The form is based on the recordsource. I believe the report is also based on
the recordsource as it does successfully display the criteria search in the form. When it is time to print, only one record is visible at preview (depending where the arrow is pointing from the continuous-form.)

I even tried changing the sorting and grouping field expression to point to the InternalID field (in ascending order) since that is the primary key of the table.

[Qtip23]

@Qtip23
While the report appeared to be based on the same recordsource, I started from scratch and went to the report design wizard to ensure all fields where pointing to the correct control source. Voila! It works now.

I tell you...most times, it's the little things. Thx TheSmileyOne.

[TheSmileyCoder]

IF I had to guess, you probably had a WHERE clause in the recordsource of the report limiting it by a single field in the form, thus only showing 1 record.

Anyways, glad you got it to work.

[Qtip23]

@TheSmileyOne
Hello TheSmileyOne,

Wow, did I goof big time?! I actually had time to review my code and realized that while the form still filters properly (according to the criteria), I cannot view ALL the variables in the report once I fire the click button. Please help!