printing reports based on # of fields

[complexx]

I have a table that contains records with 25 fields. Each record represents a list of locations. These lists of locations represent a route for a service technician. I want to be able to press 1 button, and print a separate report for each field in the record so that the service tech has an individual report for each stop on his route.

How does one go about doing this?

 

[stopher]

This sounds like a normalisation problem. You should split your table into two tables. The first table is simply a list of routes. The second table will be a list of locations for each route.

e.g.
tblRoutes:
RouteID__RouteDescription
1_________West
2_________Central
3_________North&East

tblRouteDetail:
RouteID___Sequence___Location
1___________1_________TownA
1___________2_________TownB
1___________3_________TownC
2___________1_________TownF
2___________2_________TownG

I added a Sequence field in case you needed to list the locations in a specific order i.e. the order they are visited on route.

Anyway, by having two tables like this you can easily write queries/report to any level of detail e.g. one location per page if you wish

Stopher

 

[complexx]

This table structure is probably a better design for data recall. But it does not provide simple structure for data input. I want my service techs to be able to use a form to create their own routes. In other words, they need drop down boxes, etc. for selecting customers to add to the route. I don't see how this can be easily accomplished this way.

With my initial design, they could easily update routes, change order, add/remove customers. It doesn't seem nearly as easy now.

 

[boblarson]

Actually, when you get it normalized it isn't as bad as it may seem. You just have to get to know how to use it. I'm attaching a quick sample that threw together for you to look at.

 

[complexx]

Ok. I implemented it this way. The routing works nicely.

Back to my original issue. How can I print out a specific route list with each customer on its own page from the Routes form?

 

[boblarson]

I added a sample query and report for you. Check out the attached db.

 

[complexx]

What I'm trying to do is a bit more complicated. I'm only looking to print out 1route at a time. However, if that route has for example, 4 stops, it should print out 4 pages (each page has information for 1 of the 4 stops). The important aspect for me, is that each page only has information for 1 stop on it (these pages will have lots of blank space that the techs will fill out for each customer they visit).

So what I'd like to do is be viewing the form that displays the Routes, when I've scrolled to the route that I want to run, hit a buttom and print out a 1 piece of paper for each stop on the route. Each piece of paper contains the customer info and some blank space.

 

[boblarson]

Okay, modifications made.

 

[complexx]

Bob, your one crazy access programmer! Thank you for the help!

 

[boblarson]

Glad to be of assistance. Now, just a few explanations of what I did, so you can learn a bit.

1. For individual pages for the route stops, I set up a group header for the stops and then in the group footer placed a page break (it's that little, hard to see item in the route stop footer.

2. I used grouping levels to group first by Service Tech, by Route, and then by Stop.

3. To pull only the route you wanted on the form, I created a listbox that has the route id and route description as it's rowsource but hid the route id by setting the column widths to 0";1.5" (or something like that). Then, the bound column of the listbox is set to column 1 (route id) and then in the button that clicks to open the report I set

DoCmd.OpenReport "rptServiceTechRoutes", acViewPreview, , "[RouteID]=" & lngRoute

and the last part: "[RouteID]=" & lngRoute is a WHERE clause so the report will open filtered with only items that match that statement.

Let me know if you have any other questions on how I did things.

 

[complexx]

I'm trying to implement this the way you have shown me in the sample however I'm getting an error when I try to view the report. My database is access 2002/2003 format, I have a feeling that has something to do with the problem.

The error I get is on the line of code that you quoted in your last post.

In addition, I just want to let you know I have not been using a service tech table to specify who is running the route because that information is not important to me. So all of my implementation leaves out anything related to whom the route is related to. I don't think this really effects the design but I can't be positive.

 

[boblarson]

Can you post your database? That way I can try to take a look at what you've got and make some suggestions.

 

[complexx]

I'll try to put something together for you to look at. The DB is split and I haven't been the most organized when it comes to labeling.

 

[complexx]

my file is somewhat large

can you PM me your email please?

I recreated the tables in the backend and tossed them into a copy of the front end for you. I tried to strip as many tables/data as I could to compress the file but its still too large.

Please take a few things into consideration.

1. I did not implement the routing exactly as you did (negated the service tech information because each route is not specific to a person)

2. Each customer on each route has 1 or more systems that need to be checked when the service is done. I'd like the systems associated with each customer to appear on that customers route sheet. So if customer X has 2 systems, the route sheet for customer X would list his location information as well as both systems. I added these system names to the query that the report is based on.

3. Please don't be mad about my "style". I'm fairly new to the access world and have not had the time to go through the database and properly label/comment tables, forms, code, etc. So bare with me in that respect.

If anything is unclear, please, please let me know. I really appretiate your assistance.

 

[boblarson]

I found why your report isn't opening (the error)

You had this:

So you need to highlight this row by clicking on that part to the left (where the arrow is) and hit your delete key.

 

[complexx]

DOH! Thanks for the assistance. I'll make the changes on Tuesday when I get into the office.

 

[complexx]

report not updated if data is changed

if I have already entered a specific route into my data base with customer X in it, and then decide to change some of the data associated with customer X, these changes are not reflected when I preview the report.

However, if I create a new route (different route), add customer X to it, preview the report for the new route, it shows the updated data.

What steps can I take to make sure routes that have already been specified to the database are updated if a change in the customers detailed information occurs.

 

[boblarson]

You should be changing your data with a form that has the applicable fields and is based on an updatable query. The report should change, if the data is actually being saved, when it opens again. If you have the report already open, then the changes won't be seen until it closes and reopens.