Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-16-2019, 10:04 AM   #1
Punice
Newly Registered User
 
Join Date: May 2010
Location: Middletown, VA
Posts: 103
Thanks: 46
Thanked 1 Time in 1 Post
Punice is on a distinguished road
Help opening a report to display a group of records

Access 2007: I have a form with a combobox that is configured as value list. I want to pick a trip number (ie., D_TN) from the list, like trips 1 thru 10, and open a report.

I, also, have a query that contains the leg numbers for each trip, like: 3.01, 3.02, . . . . 3.05., which are all under trip 3, where D_TN = 3.

I already have written afterupdate code that gets the max & min leg numbers after a trip number is selected, which for this example are maxT_TN = 3.05 & minT_TN = 3.01.

I need help with the code that uses those three parameters and opens a report displaying only the records for the selected trip number, 3 for this example.

I know how to do that for date ranges, from Allen Browne's paper, but couldn't get a variation of it to work.


Last edited by Punice; 09-16-2019 at 10:12 AM. Reason: To simplify explanations
Punice is offline   Reply With Quote
Old 09-16-2019, 10:16 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,905
Thanks: 57
Thanked 1,291 Times in 1,272 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Help opening a report to display a group of records

Hi. If the value from your list is say a "3", have you tried using Like? For example:
Code:
DoCmd.OpenReport "ReportName", acViewPreview, , "[FieldName] Like '" & Me.ComboboxName & "*'"
Hope it helps...
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 09-16-2019, 11:18 AM   #3
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,269
Thanks: 15
Thanked 1,593 Times in 1,513 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Help opening a report to display a group of records

The problem seems to be caused because you have mushed Trip number and leg into a single, text field. The solution suggested by theDBGuy may work because it compares the trip to the first part of trip.leg. But it only works if you want all legs of a trip. If you want a subset of legs, then the best solution is to fix the data problem and store the tripID and the Leg in two separate fields. You can print them as trip.leg if you want but you don't want to keep them as a mushed text field if you need to pull out a range of values.

Here's an example of why what you are doing will eventually fail.
Code:
print iif("3.01" < "10.01", true, false)
False
In the immediate window, I compared two text fields which humans would always say should return true. However, computers are dumb as rocks and they would disagree when the fields are defined as text. The reason is that text fields are compared character by character, left to right. Therefore since "3" is NOT less than "1", "3.01" is NOT less than "10.01".

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is online now   Reply With Quote
Old 10-06-2019, 05:46 AM   #4
Punice
Newly Registered User
 
Join Date: May 2010
Location: Middletown, VA
Posts: 103
Thanks: 46
Thanked 1 Time in 1 Post
Punice is on a distinguished road
Re: Help opening a report to display a group of records

I used theDBGuy's code and it does what I wanted done for single reports. Thanks for you help.
Punice is offline   Reply With Quote
Old 10-06-2019, 06:08 AM   #5
Punice
Newly Registered User
 
Join Date: May 2010
Location: Middletown, VA
Posts: 103
Thanks: 46
Thanked 1 Time in 1 Post
Punice is on a distinguished road
Re: Help opening a report to display a group of records

Your code did what I wanted. Now, I need to know how to have it do it for a main report and a sub-report, with the code in a form's combobox where an integer that represents a trip number and the main report displays the trip leg records (date, mileage, city, etc.) and the sub-report displays the fueling records (date, gal, vendor, city & state) for the trip. The following works for individual reports, but not for both
In the main report: DoCmd.OpenReport strReport, acViewPreview, , "[T_TN] Like '" & Me.cboBoth_lbl & "*' "
Don't know how to include this: DoCmd.OpenReport strReport, acViewPreview, , "[F_SerNo] Like '"& Me.cboF_SerNo_lbl & " * '".
Currently, only the main report works right; the sub displays all records, because it's now in my sub-routine.

Last edited by Punice; 10-06-2019 at 06:12 AM. Reason: Thought clarification was necessary.
Punice is offline   Reply With Quote
Old 10-06-2019, 06:34 AM   #6
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,905
Thanks: 57
Thanked 1,291 Times in 1,272 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Help opening a report to display a group of records

Quote:
Originally Posted by Punice View Post
Your code did what I wanted. Now, I need to know how to have it do it for a main report and a sub-report, with the code in a form's combobox where an integer that represents a trip number and the main report displays the trip leg records (date, mileage, city, etc.) and the sub-report displays the fueling records (date, gal, vendor, city & state) for the trip. The following works for individual reports, but not for both
In the main report: DoCmd.OpenReport strReport, acViewPreview, , "[T_TN] Like '" & Me.cboBoth_lbl & "*' "
Don't know how to include this: DoCmd.OpenReport strReport, acViewPreview, , "[F_SerNo] Like '"& Me.cboF_SerNo_lbl & " * '".
Currently, only the main report works right; the sub displays all records, because it's now in my sub-routine.
Hi. Can you post a screenshot of your report? If we're talking about a true subreport here, you cannot use the OpenReport method to open it inside a main report. You simply open the main report and make sure the subreport is properly linked to it.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 10-06-2019, 06:25 PM   #7
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Help opening a report to display a group of records

on the subreport you add Link Master Fields (PK of main report) and Link Child Fields (FK of subreport).


__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Reply

Tags
combobox , group by vba , report

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Limiting Access report group to 10 records per group when printed? Access_Help Reports 1 02-05-2019 11:30 PM
Formatting certain records on opening report jmt90404 Reports 4 04-08-2018 06:03 PM
Report group by 500 records irade92 Reports 9 12-31-2010 10:02 AM
How to display report header with each group change KRel Reports 5 07-15-2009 02:06 PM
Opening specific records with a report? red91 Reports 0 11-07-2002 03:35 PM




All times are GMT -8. The time now is 05:31 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World