Can't Quite figure out what I'm missing here...

[z_bryant]

The database consists of 9 tables. Each table represents a different aspect of the equipments' configuration. In it's most simple form, equipment would consist of a motor and a directly attached, driven piece of equipment. In other cases they'd be attached by a connecting device that could be a coupling, a belt assembly, or a chain assembly. Still others could have a gear reducer in the middle and then a second connecting assembly. Typically the equipment is associated with an equipment #.

So that's how I built the database. 9 tables each with the primary key being the [Equipment Number], of which anywhere from 2 to 5 tables will contain data for that particular piece of equipment. My Equipment_Form is the main form with a tab control to subforms for each of the other equipment attributes (see picture).

How it works is:
On the Equipment tab, the arrangement is selected with the check boxes. It only allows 1 check box to be selected per zone, and the tabs appear and disappear according to what boxes are checked. After entering all data on each tab, the record can be locked with a check box on the equipment tab. This helps to keep the data from being accidently changed. But it can be unchecked at any time to allow for editing of the record.

All these parts appear to work as needed. But I want to be able to create a report of the current record, but I can't seem to get my head around how to set it up completely.

I have a report [Datasheet] built with all of the possible fields layed out where I want them to print (a template, if you will). I have a [Datasheet_Query] with all of the fields of each of the 9 tables. I have a button control on the Equipment tab of [Equipment_Form]. The [Equipment Number] is on each tab, but I'm thinking about moving it to the Header section of the main page and taking out all the other references to it.

I've tried everything I can think of trying with the coding and have had various degrees of luck.

My code is currently:

DoCmd.OpenReport "Datasheet", acViewPreview, , "Forms!Equipment![Equipment Number]='" & Me![Equipment Number] & "'"

BTW: The primary key is set as text because sometimes it may have a
letter at the end of the number.

The report previews as it's layed out. But various attempts at fixing it have given the following results:

Sometimes only the Equipment portion gets data. Usually the data fields are blank. Sometimes it says "#NAME?" in most of the data fields and a few are blank. It just doesn't seem to pass the [Equipment Number] value
to wherever it needs to go.

What should the record source for the report (Datasheet) be? I've tried it as blank, as Datasheet_Query, as Equipment_Form, and I've tried the Query Build wizard.

With the current code, and the Record Source set by the Query Builder, when I press the button:

It asks for input to Forms!Equipment![Equipment Number], then generates the report, as per the layout, but without the data.

 

[JHB]

Try:

DoCmd.OpenReport "Datasheet", acViewPreview, , "[Equipment Number]='" & Me![Equipment Number] & "'"

 

[z_bryant]

I think that's one I tried. And it gives me:

"Run-time error '3079':
The specified field '[Equipment Number]' could refer to more than one table listed in the FROM clause of your SQL statement"

which is where I'm having difficulty. I want it to get data from the applicable tables, but can't figure out how to set that up.

 

[JHB]

Ok, then try:

DoCmd.OpenReport "Datasheet", acViewPreview, , "[applicable]![Equipment Number]='" & Me![Equipment Number] & "'"

 

[z_bryant]

Now it asks for applicable!Equipment Number Then previews the report without the data.

 

[Beetle]

If you can create a sanitized copy of your application and upload it here we can take a look at it and perhaps help you find the problem.

 

[MSAccessRookie]

I think that should be "[applicable].[Equipment Number]='"

 

[z_bryant]

OK, I've attached a zipped copy. What you'll see is three different print options that I was working on, Because I wasn't sure what I was going to want in the end.

The one labeled "Print Pages" was my first attempt and it works, in that it makes preview versions of each current tab. It wasn't what I was going for but saw potential use for it down the road, so I kept it.

My second attempt "Print Combined" was supposed to fix the previous and put everything on one document. I did it by hiding the parts that had no data (unused tabs). It worked as far as hiding the blank stuff, but didn't limit itself to just the current record.

Which got me to the third attempt "Print Datasheet". I attempted to fill in a preformatted datasheet with the current data and didn't even attempt to filter out unused tabs.

But I'm in over my head now. Not knowing what should be a record source for what, linked child and master fields, etc.

It sure seemed like this should've been the easy part of the job, and I'm sure it is for some. But between this and attempting to incorporate a tree structure navigation control, I'm not making much headway. I sure hope one of you can locate the printing issue for me.

Thanks

 

[JHB]

This is the correct code, but you also have to chose a Equipment Number who will give data to the report, (only number 82007 and 81402 will do that).
You've a lot of controls (32) in your report which doesn't have a ControlSource!!!

DoCmd.OpenReport "Datasheet", acViewPreview, , "[Equipment]![Equipment Number]='" & Me.[Equipment Number] & "'"

 

[z_bryant]

I've got the "Print Combined" button working correctly. I hadn't entered the 'Link Master Field' and 'Link Child Field' info. It now just leaves white space where the 'invisible' sections would have resided, but I'm confident I can get rid of that. One thing I noticed though, is I can build a report by dragging forms or reports into it. And if I use a form I can get to the visible property using Form.Coupling_1_Subform.Visible, but how would I address it if the file I dragged in was a report called Coupling_1_Datasheet?

Form.Coupling_1_Datasheet.Visible doesn't seem to work.
Report.Coupling_1_Datasheet.Visible doesn't seem to work.

Even though the record source displays as Report.Coupling_1_Datasheet and it has a Visible property it doesn't seem to address it.

I could generate a report with either method, but only using the 'forms' was I able to make the unused data 'invisible'.


Now for the "Print Datasheet" issue: Since my report "Datasheet" is printing info from multiple tables, should the record source list each of the tables, each of the querys, or should it list a query that includes all of the tables and their fields?

 

[JHB]

...
...
Now for the "Print Datasheet" issue: Since my report "Datasheet" is printing info from multiple tables, should the record source list each of the tables, each of the querys, or should it list a query that includes all of the tables and their fields?

I find so as you have done it, (with a query is ok).

 

[z_bryant]

It didn't make sense to me that my two statements were identical (except for the report name) and only one worked.

This one didn't work:

DoCmd.OpenReport "Datasheet", acViewPreview, , "[Equipment Number]='" & Me.[Equipment Number] & "'"

This one worked:

DoCmd.OpenReport "Combined", acViewPreview, , "[Equipment Number]='" & Me.[Equipment Number] & "'"

So right or wrong I began experimenting:

The report "Datasheet" has a record source called "Datasource_Query", which contains all the fields of all the tables involved. It contains no criteria.

I went to every control on the report and added "<Table>." in front of each control source.

At this point I got the error:

"The specifird field '[Equipment Number]' could refer to more than one table listed in the FROM clause of your SQL statement."

I went back to the OpenReport statement and added the "<Table>." to it

acViewPreview, , "[Equipment.Equipment Number]='"

Now no errors, but all it previews is the report template with blank text boxes again. I don't know if I'm any closer or not, but at least there are no errors or asking for values.

 

[JHB]

It didn't make sense to me that my two statements were identical (except for the report name) and only one worked.

This one didn't work:

DoCmd.OpenReport "Datasheet", acViewPreview, , "[Equipment Number]='" & Me.[Equipment Number] & "'"

This one worked:

 DoCmd.OpenReport "Combined", acViewPreview, , "[Equipment Number]='" & Me.[Equipment Number] & "'"

So right or wrong I began experimenting:

For me it gives pretty good sense.
For the "Datasheet" report, the [Equipment Number] occurs more as one time in the source, therefor you've to point out, from which table you want to use [Equipment Number].

In "Combined" report the [Equipment Number] occurs only one time, then you don't need to tell which one to use.

Now no errors, but all it previews is the report template with blank text boxes again. I don't know if I'm any closer or not, but at least there are no errors or asking for values.

For which equipment no are you trying to open the "Datasheet" report?
I've mention it before, only number 82007 and 81402 will show data in your report, for the rest the report will be blank.

I went back to the OpenReport statement and added the "<Table>." to it

acViewPreview, , "[Equipment.Equipment Number]='"

The right code is so, (you are missing ] and [ between the table and the field ):

DoCmd.OpenReport "Datasheet", acViewPreview, , "[Equipment]![Equipment Number]='" & Me.[Equipment Number] & "'"

I've attached a picture to show you the report works ok.

 

[z_bryant]

JHB, it brings up an interesting point that it only works on a couple of numbers. That's something I'll have to look into.

But even with those brackets added, I'm not getting the result I wanted. Your picture only shows the first table populating the form, when it should be each table that a tab is visible. It should have been 5 sections of the report filled with data.

That's my difficulty. How to get the one report to query each of the relative tables. The other two buttons collect the correct information, they're just not putting it onto one neat, concise report.

 

[JHB]

Oh - now I think I know what you trying to preform.
But then your report is wrong build up, you need to use sub reports.
I've attach the your database with the "new" report.
And also a picture which showed the report's output.
Now the report is also showed for each equipment no.!

 

[z_bryant]

Yes, that sure looks like it! Excellent, Thanks a lot! I'll have to dig into it a little later so I know where I went wrong.

Couple quick questions:

How do you go about building that type of query? I've seen a couple like that online here lately and wondered how they were constructed. And you only used the fields from the Equipment Table. I take it that's because the subforms referenced their respective tables directly? Is there a name for this type of query? I'd like to read up on it some.

In Datasheet_Query Alle tabeller you added belt_1.*, belt_2.*, Chain_1.* and chain_2.*. Is that a wildcard way of adding all the fields?

It looks like the *_Old and *_Alle tabeller are no longer needed.

I see why only two equipment numbers worked in my method. I only built my Datasheet_Query using 5 tables, and it would only list the ones that included info on all five.

Thanks again, I appreciate the time and effort that you put into helping me out.

 

[JHB]

... I'll have to dig into it a little later so I know where I went wrong.

You did add to many tables in the main report, only one should be there, the Equipment Table.

And you only used the fields from the Equipment Table. I take it that's because the subforms referenced their respective tables directly?

Yes, so it is..

In Datasheet_Query Alle tabeller you added belt_1.*, belt_2.*, Chain_1.* and chain_2.*. Is that a wildcard way of adding all the fields?

Yes

It looks like the *_Old and *_Alle tabeller are no longer needed.

Exactly, delete them.

I see why only two equipment numbers worked in my method. I only built my Datasheet_Query using 5 tables, and it would only list the ones that included info on all five.

Yeah, and in Your case, it was wrong.

Thanks again, I appreciate the time and effort that you put into helping me out.

You're welcome, luck with your project.

It took really long time, before I realized what you wanted to do, it can sometimes help if you make a small sketch, how you want the result, instead of just write, it does not work .