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

Manifest retrieval database.

P: 1
Hello everyone, this is my first post here and hopefully the first of many.

I have an issue with a database i'm trying to setup. I have manifests that I distribute out to drivers that do some Tech work for us and this database keeps all the locations, addresses, tech numbers, etc in order... Everything works fine except with a multi select list box.

The list box retrieves the information correctly into the report... The problem is that I want it to split the actual pages of the report into seperate pages by route.

There are several routes to one driver, but each page should be divided by its respective route name. Instead what it does, is merges all routes into the first route it picked up, so all stops would show under ROUTE 1 instead of being distributed to ROUTE 1, 2, 3 etc...

Below is the code for the command button that retrieves the selected items in the listbox...

Any help is appreciated. If you need the whole db let me know.

Expand|Select|Wrap|Line Numbers
  1. On Error GoTo Err_ReportManifest_Click
  2.  
  3.     Dim stDocName As String, stSelectedItems As Variant, stResult As String, iCounter As Integer, EmptyCheck As String
  4.     iCounter = 1
  5.  
  6. If List0.ItemsSelected.Count <> 0 Then
  7.     For Each stSelectedItems In List0.ItemsSelected
  8.     If iCounter < List0.ItemsSelected.Count Then
  9.     stResult = stResult & "'" & List0.ItemData(stSelectedItems) & "', "
  10.     iCounter = iCounter + 1
  11.     Else
  12.     stResult = stResult & "'" & List0.ItemData(stSelectedItems) & "'"
  13.     End If
  14.     Next stSelectedItems
  15. Else
  16.     MsgBox "Nothing was selected.", vbCritical + vbOKOnly
  17.     Exit Sub
  18. End If
  19.  
  20. MsgBox stResult
  21.  
  22.     stDocName = "Manifest"
  23.     DoCmd.OpenReport stDocName, acPreview
  24.  
  25. With Reports![Manifest]
  26.     .Filter = "TechQuery.Route IN (" & stResult & ")"
  27.     .FilterOn = True
  28. End With
  29.  
  30.  
  31. Exit_ReportManifest_Click:
  32.     Exit Sub
  33. Err_ReportManifest_Click:
  34.     MsgBox Err.Description
  35.     Resume Exit_ReportManifest_Click
  36.  
Oct 9 '07 #1
Share this Question
Share on Google+
2 Replies


Jim Doherty
Expert 100+
P: 897
Hello everyone, this is my first post here and hopefully the first of many.

I have an issue with a database i'm trying to setup. I have manifests that I distribute out to drivers that do some Tech work for us and this database keeps all the locations, addresses, tech numbers, etc in order... Everything works fine except with a multi select list box.

The list box retrieves the information correctly into the report... The problem is that I want it to split the actual pages of the report into seperate pages by route.

There are several routes to one driver, but each page should be divided by its respective route name. Instead what it does, is merges all routes into the first route it picked up, so all stops would show under ROUTE 1 instead of being distributed to ROUTE 1, 2, 3 etc...

Below is the code for the command button that retrieves the selected items in the listbox...

Any help is appreciated. If you need the whole db let me know.

Expand|Select|Wrap|Line Numbers
  1. On Error GoTo Err_ReportManifest_Click
  2.  
  3. Dim stDocName As String, stSelectedItems As Variant, stResult As String, iCounter As Integer, EmptyCheck As String
  4. iCounter = 1
  5.  
  6. If List0.ItemsSelected.Count <> 0 Then
  7. For Each stSelectedItems In List0.ItemsSelected
  8. If iCounter < List0.ItemsSelected.Count Then
  9. stResult = stResult & "'" & List0.ItemData(stSelectedItems) & "', "
  10. iCounter = iCounter + 1
  11. Else
  12. stResult = stResult & "'" & List0.ItemData(stSelectedItems) & "'"
  13. End If
  14. Next stSelectedItems
  15. Else
  16. MsgBox "Nothing was selected.", vbCritical + vbOKOnly
  17. Exit Sub
  18. End If
  19.  
  20. MsgBox stResult
  21.  
  22. stDocName = "Manifest"
  23. DoCmd.OpenReport stDocName, acPreview
  24.  
  25. With Reports![Manifest]
  26. .Filter = "TechQuery.Route IN (" & stResult & ")"
  27. .FilterOn = True
  28. End With
  29.  
  30.  
  31. Exit_ReportManifest_Click:
  32. Exit Sub
  33. Err_ReportManifest_Click:
  34. MsgBox Err.Description
  35. Resume Exit_ReportManifest_Click
  36.  

Have you looked at sorting and grouping in your report?

Jim
Oct 9 '07 #2

nico5038
Expert 2.5K+
P: 3,072
I concur to Jim's remark, but wanted to add that all sorting for a report is done in the grouping menu (Press the [= looking Grouping button).
The ORDER BY in a report query won't have any effect !

Nic;o)
Oct 9 '07 #3

Post your reply

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