help

[ppoole16]

I removed all of the cirteria in the query. I also changed the column ChargeID to Booking Sheet number (using the Charges table).

I have the following behind the command button:

DoCmd.OpenReport stDocName, acPreview, , "[Booking Sheet Number]=" & Forms![BookingSheet]![ID] & " AND [ChargeID]=" & Forms![BookingSheet]![Charges].Form![ChargeID]

Now I get an error that says that "[Booking Sheet Numer]" could refer to more than one table.


I know I'm working way too hard on this. I voulunteered to do much more than I'm capable of. -Way Stressed!

 

[WayneRyan]

Phil,

No, no, no

You don't have to put all of that stuff in the command:

Try just:

DoCmd.OpenReport "Booking Report", AcPreview

As I said last time, Your report is defined by the query, the
relationship between the data in your tables, defines which
charge and car records go with the main booking record.
That's why you have such a nice relationship picture on
the tools menu.

The only thing the query needs is the Booking Sheet Number
from the main form. There is only one criteria in the query.

Because of how you've defined the relationships, and put the
criteria in the query, you're working too hard to try to put
the criteria in the DoCmd line.

Wayne

 

[ppoole16]

right, that works. But I need to limit the report to show only the current form and subform. Right now it shows the current form, but all of the "subrecords" associated with that subform are also shown.

My actual dbase doesn't have the car info. I was only using that at home for an example. I had that one working. I was hoping to use what I learned from that to apply it to this. I'm sure I'm just missing something simple again.

 

[WayneRyan]

Phil,

I didn't notice that.

It's probably the joins in the query. If you right-click on the
line between the Booking and Charge tables it will show how
the query will view the table relationships.

I'll check it, but I've move to a different computer.

Wayne

 

[ppoole16]

I have to repost with the changes.

 

[WayneRyan]

Phil,

I just gave your report a good look, what you need to do is
very similar to how you set up your forms. Your main form has
subforms for cars and charges. The master-child link takes
care of displaying the appropriate ones for the Booking Number.

To use a subform, you just go to it.

In the report, you have a report for the Main booking information.
On that same report though, you have separate reports for
the charges and cars. What they need to be is SubReports.
That way you can use the Master-Child link of the "new"
subreports to keep things in synch. Right now, their data
source is not the "other" reports query, it is the WHOLE cars
or charges table.

What we will end up with is:

1) The DoCmd.OpenReport, with no criteria.

2) The Booking Report query keeps its criteria to show which
booking number, but it loses the charges and cars (test)
tables.

3) The report replaces the cars and charges reports with
subreports, with the Master-Child defined. The source still
remains the "whole" table, but the Master-Child links will
pick just the right records.

4) Access will take care of the rest.

Wayne

 

[ppoole16]

Well geocities is a little slow (I guess what can I expect for free?) I've ended up just redesigning the db that you helped my with earlier to fix my needs. I still don't know why this one works and not my main one.


I can't begin to thank you enough for all your help. I wish there was some way I could repay you.

Thanks.

 

[WayneRyan]

Phil,

Actually, most of this was just trying to hard to define things.
Your underlying things were pretty solid, as were your
forms. The relationships are OK. I see that you are starting
to "transplant" things like the cars and charges to other
tables. More will follow, but you can evolve this one. Once
you get your report done, it'll be pretty stable.

see ya,

Wayne

 

[ppoole16]

I know you've got to be getting tired of this by now. I completed the report using a subreport for the charges. Everything seemed to be working, but when I tested it I'm back to the same problem as before. I've defined the Master/Child field but it's displaying all of the subreports for the record rather than just the current one.

For example: in the db, the second record (Jones) has been arrested twice. I want the booking sheet (Report) to only show the arrest that is on the screen.

I've gone through the query and deleted all the criteria but the Booking sheet Number.

If you still have time, I've reposted the updated version.

Thanks.

http://www.geocities.com/philpoole16