?
Solved

How to create Access Check Box Pick List Form then populate report based on selection

Posted on 2010-01-08
9
Medium Priority
?
281 Views
Last Modified: 2013-11-28
I am not going to lie. I am an Access Newbie and will most likely need a lot of hand holding through this. Basically, I inherited this modification from another department in my company, so here goes.

I have an Access 2000 Report which displays data from MS SQL Tables based on a DATE Parameter entered into a form which appears when the end user runs the report from within Access.

What I need to do is to expand on this existing [or start from scratch] process and add an additional step where the end user will be able to select which data is displayed on the form, rather than all data based on the date.

The logic would be:

1. Run the report
2. Prompt user for Date Parameter [single date not range]
3. Prompt user with a form which displays all 'routes' for that date. Each route will have a check box next to it. Once the end user selects all the desired routes using the check boxes, they will click a button which will:
4. Display the report as defined by the parameters above

I have the report design [as it was given to me].
I have the query which displays ALL the 'routes' data in the report.
I know which server, db, and tables will be used.

I am not sure how to tie it all together, especially the check box form [which has yet to be created].

I also am not sure how the existing Date Parameter is functioning. I am unable to locate where this parameter is being called from -- I don't know where it is in the code.

Please let me know what i can post to this question so that it is more clear and also what will be needed for you to assist me.

Again, this is in Access 2000 and it is relatively foreign to me. And as always, thank you for your help.

PS - If I could give more than 500 points, I would.

Jason
SELECT ROUTE_SETS.ROUTING_DATE, ROUTES.ROUTE_NAME, DRIVER.NAME, DRIVER_1.NAME AS DRIVER2NAME, DRIVER_1.EMPLOYEENUMBER AS EMPLOYEENUMBER2, VEHICLE.NAME, VEHICLE.ODOMETER, CUSTOMER.WHOLE_ACCOUNT_ID, ORDERS.ORDER_ID, CUSTOMER.NAME, CUSTOMER.STREET_ADDRESS, CUSTOMER.PHONE_NUMBER, ORDERS.HW_OPEN_TIME, ORDERS.HW_CLOSE_TIME, ROUTE_STOPS.ARRIVAL_TIME, ROUTE_STOPS.DEPARTURE_TIME, CUSTOMER.STOP_ID, VEHICLE_1.NAME, VEHICLE.DRIVER_ID1, VEHICLE.VEHICLETYPE, VEHICLE_1.VEHICLETYPE, DRIVER.EMPLOYEENUMBER, ROUTE_STOPS.STOP_NUMBER, ORDERS.COMMENT_ROUTE_SHEET, ORDERS.ORDER_SIZE_VOLUME, ROUTES.DISPATCH_TIME, ROUTES.DELIVERY_VOLUME, VEHICLE_1.DRIVER_ID2, DRIVER.TEAMMATEID, ROUTES.DRIVER_ID2, ROUTES.VEHICLE_TYPE3, VEHICLE_2.NAME, CUSTOMER.NICKNAME, STOP.STOP_NAME, STOP.STREET_ADDRESS, STOP.CITY, STOP.STATE, STOP.COMMENTS, STOP.PHONE_NUMBER, STOP.ZIP
FROM (((((ROUTE_STOPS INNER JOIN (((VEHICLE INNER JOIN (ROUTE_SETS INNER JOIN ROUTES ON ROUTE_SETS.ROUTE_SET_INDEX = ROUTES.ROUTE_SET_INDEX) ON VEHICLE.VEHICLE_ID = ROUTES.VEHICLE_ID1) LEFT JOIN VEHICLE AS VEHICLE_1 ON ROUTES.VEHICLE_ID2 = VEHICLE_1.VEHICLE_ID) INNER JOIN DRIVER ON ROUTES.DRIVER_ID1 = DRIVER.INTERNALID) ON ROUTE_STOPS.ROUTE_INDEX = ROUTES.ROUTE_INDEX) INNER JOIN RSTOP_ORDER ON ROUTE_STOPS.STOP_INDEX = RSTOP_ORDER.STOP_INDEX) INNER JOIN (CUSTOMER INNER JOIN ORDERS ON CUSTOMER.CUSTOMER_ID = ORDERS.CUSTOMER_ID) ON RSTOP_ORDER.ORDER_ID = ORDERS.ORDER_ID) LEFT JOIN DRIVER AS DRIVER_1 ON ROUTES.DRIVER_ID2 = DRIVER_1.INTERNALID) LEFT JOIN VEHICLE AS VEHICLE_2 ON ROUTES.VEHICLE_ID3 = VEHICLE_2.VEHICLE_ID) INNER JOIN STOP ON ROUTE_STOPS.STOP_ID = STOP.STOP_ID
GROUP BY ROUTE_SETS.ROUTING_DATE, ROUTES.ROUTE_NAME, DRIVER.NAME, DRIVER_1.NAME, DRIVER_1.EMPLOYEENUMBER, VEHICLE.NAME, VEHICLE.ODOMETER, CUSTOMER.WHOLE_ACCOUNT_ID, ORDERS.ORDER_ID, CUSTOMER.NAME, CUSTOMER.STREET_ADDRESS, CUSTOMER.PHONE_NUMBER, ORDERS.HW_OPEN_TIME, ORDERS.HW_CLOSE_TIME, ROUTE_STOPS.ARRIVAL_TIME, ROUTE_STOPS.DEPARTURE_TIME, CUSTOMER.STOP_ID, VEHICLE_1.NAME, VEHICLE.DRIVER_ID1, VEHICLE.VEHICLETYPE, VEHICLE_1.VEHICLETYPE, DRIVER.EMPLOYEENUMBER, ROUTE_STOPS.STOP_NUMBER, ORDERS.COMMENT_ROUTE_SHEET, ORDERS.ORDER_SIZE_VOLUME, ROUTES.DISPATCH_TIME, ROUTES.DELIVERY_VOLUME, VEHICLE_1.DRIVER_ID2, DRIVER.TEAMMATEID, ROUTES.DRIVER_ID2, ROUTES.VEHICLE_TYPE3, VEHICLE_2.NAME, CUSTOMER.NICKNAME, STOP.STOP_NAME, STOP.STREET_ADDRESS, STOP.CITY, STOP.STATE, STOP.COMMENTS, STOP.PHONE_NUMBER, STOP.ZIP
HAVING (((ROUTE_SETS.ROUTING_DATE)=[Enter Routing Date]) AND ((ROUTES.ROUTE_NAME)=[ENTER ROUTE_NAME]))
ORDER BY ROUTES.ROUTE_NAME;

Open in new window

1-8-2010-11-18-05-AM.png
1-8-2010-11-18-40-AM.png
1-8-2010-11-47-02-AM.png
0
Comment
Question by:jsvb1977
  • 5
  • 4
9 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 26211767
<
I also am not sure how the existing Date Parameter is functioning. I am unable to locate where this parameter is being called from -- I don't know where it is in the code.>

see the [Enter Routing Date]  in the criteria of your query from image
1-8-2010-11-47-02-AM.png

0
 

Author Comment

by:jsvb1977
ID: 26212150
Cool thanks. Now, is there a way to use this so that the user is prompted to select from a list of 'routes'?

Column 2 in that same screen shot prompts the user to enter the name of the route. But it is only for one route.

Maybe we could mod the code to accept multiple parameters like:

route 1, route 2, route 3, etc...

Let me know your thoughts,
Jason
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 26212227
Nope.. use a form with a listbox/ textbox for that..
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Comment

by:jsvb1977
ID: 26212255
capricorn1,

Are you able to assist with the creation of the form with a listbox/textbox? I question the use of the listbox/textbox though, since i need to give the end user the ability to choose multiple 'routes' and not just one.

Make sense?

Jason
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 26212296
i suggest that you divide your requirements into several post. that way, more experts can attend to your problem.

i can assist you, why not.
0
 

Author Comment

by:jsvb1977
ID: 26212389
ok, i am happy to split this one up. what is your suggestion to do this?

Shall i accept your post above as the solution to the second question i asked, then enter this question again but be more specific to the first question, which was asked in the following sentence?

"What I need to do is to expand on this existing [or start from scratch] process and add an additional step where the end user will be able to select which data is displayed on the form, rather than all data based on the date."

Let me know how to proceed,
Jason
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 26212452
yes you can, and in your next q it will be best if you can upload a copy of your db with the form you are designing and related objects.
0
 

Author Comment

by:jsvb1977
ID: 26212483
OK. Not sure about uploading the db.

1. Our company has a policy about such things [which is not to do it]
2. but more importantly, this db uses ODBC connections and linked tables, so it would next to worthless located on someone else's machine.

But I will do my best. Thank you,
Jason
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 26212516
create a blank db, import objects related to this problem. use dummy data
do not upload proprietary items.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question