By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,963 Members | 1,863 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,963 IT Pros & Developers. It's quick & easy.

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

P: 39
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


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

Apr 22 '10 #1

✓ answered by 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.

Share this Question
Share on Google+
7 Replies

Expert Mod 100+
P: 2,321
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.
Apr 23 '10 #2

P: 39
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: 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 Sub

MsgBox Err.Description
Resume Exit_cmdFilter_Click

End Sub

Any advise would be greatly appreciated. Thx.
Apr 23 '10 #3

Expert Mod 100+
P: 2,321
Your report and form is based on the same recordsource?

Please use [code][/code] tags around your code.
Apr 23 '10 #4

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdFilter_Click()
  2. On Error GoTo Err_cmdFilter_Click
  4. Dim strWhere As String [comment]'The criteria string.[/comment]
  5. Dim lngLen As Long [comment]'Length of the criteria string to append to.[/comment]
  6. Const conJetDate = "\#mm\/dd\/yyyy\#" [comment]'The format expected for dates in a JET query string.[/comment]
  9. If Not IsNull(Me.txtFilterLiaison) Then
  10. strWhere = strWhere & "([Liaison_Contact_Information] = """ & Me.txtFilterLiaison & """) AND "
  11. End If
  13. If Not IsNull(Me.txtFilterSource) Then
  14. strWhere = strWhere & "([Source] Like ""*" & Me.txtFilterSource & "*"") AND "
  15. End If
  17. If Not IsNull(Me.cboFilterProgram) Then
  18. strWhere = strWhere & "([Program] Like ""*" & Me.cboFilterProgram & "*"") AND "
  19. End If
  21. If Not IsNull(Me.cboFilterStatus) Then
  22. strWhere = strWhere & "([Status] Like ""*" & Me.cboFilterStatus & "*"") AND "
  23. End If
  25. If Not IsNull(Me.txtStartFilterDate) Then
  26. strWhere = strWhere & "([Due_Date] >= " & Format(Me.txtStartFilterDate, conJetDate) & ") AND "
  27. End If
  29. If Not IsNull(Me.txtEndFilterDate) Then 'Less than the next day.
  30. strWhere = strWhere & "([Due_Date] < " & Format(Me.txtEndFilterDate + 1, conJetDate) & ") AND "
  31. End If
  34. lngLen = Len(strWhere) - 5
  35. If lngLen <= 0 Then [comment]'Nah: there was nothing in the string.[/comment]
  36. MsgBox "No criteria", vbInformation, "Nothing to do."
  37. Else 'Yep: there is something there, so remove the " AND " at the end.
  38. strWhere = Left$(strWhere, lngLen)
  40. [comment]'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).[/comment]
  42. Debug.Print strWhere
  44. [comment] 'Finally, apply the string as the form's Filter.[/comment]
  45. Me.Filter = strWhere
  46. Me.FilterOn = True
  47. End If
  50. Exit_cmdFilter_Click:
  51. Exit Sub
  53. Err_cmdFilter_Click:
  54. MsgBox Err.Description
  55. Resume Exit_cmdFilter_Click
  59. 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.
Apr 23 '10 #5

P: 39
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.
Apr 26 '10 #6

Expert Mod 100+
P: 2,321
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.
Apr 27 '10 #7

P: 39
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!
May 7 '10 #8

Post your reply

Sign in to post your reply or Sign up for a free account.