printing single record reports

[btappan]

I have a print button on my form using the following code to print a single record report:

Private Sub Command26_Click()
Dim strDocName As String
Dim strLinkCriteria As String

DoCmd.RunCommand acCmdSaveRecord
strDocName = "InventoryTagBusway"
strLinkCriteria = "ID = Forms![BUSWAY]![ID]"
DoCmd.OpenReport strDocName, acViewPreview, , strLinkCriteria
End Sub

Private Sub Form_Current()

I know i'm breaking the Normalization rules but for now (i'm repairing a database line by line) i have about 10 tables each with about 10 fields, ALL 10 tables have 3 fields that are the same, and only those 3 fields are being printed for the report for any of the tables. I have 10 reports that all look the same to go with each table's 3 records. How can I use 1 report for this instead of 1 for each table? each time I adjust the page formatting, i have 2 do it for all 10 . Thanks

 

[stopher]

You can use a UNION query as your source for your report:

SELECT * FROM table1
UNION
SELECT * FROM table2
UNION
SELECT * FROM table3
etc...

If you want to be able to identify which table each record came from then add a column:

SELECT "t1" as [tableID], * FROM table1
UNION
SELECT "t2" as [tableID], * FROM table2
UNION
SELECT "t3" as [tableID], * FROM table3
etc...

hth
Stopher

 

[btappan]

sorry, newb here. Can you help me with implementing this?

 

[stopher]

I might be making too many assumptions about the "shape" of your data. Perhaps you could post your database and we can do it on the real thing.
Stopher

 

[btappan]

here is a stripped down version, you'll notice all of the reports have the same fields and layout. I am trying to have just one report that chooses the fields from the appropriate table based on which form the "print label" button is being pressed from.

 

[stopher]

Are you using Access 2007 ? I'm sorry I don't have that version so I can't examine your file. Perhaps you have the option to convert it to a previous version of Access...
Stopher

 

[btappan]

all right, i replaced the file above with a 2003 version.

 

[stopher]

The first point is that your ID is really just a number despite the fact that you are formatting it. So when you come to join data together, this formatting will be lost and you will just see 1,2,3 etc and you won't be able to tell the difference between the data from different tables. Therefore it is worth looking at a more robust system for capturing/storing your primary key. It there are many posts in the forums on this (do a search on “autonumber”) and you’ll see some discussion on different approaches/views. In order to deal with this for now, I’ve created the query such that the format you want is generated by the query and therefore becomes useable. Also look at Bob Larson's response in a previous post of yours here as it explains how better to model what you are doing i.e. putting all "items" in one table and storing attributes in another.

In the attached d/b I have created a union query (qryCombinedTableView). A query allows you to view tables in different ways and generally reports and forms will be based on queries rather than directly on tables. You can look at it in design mode and also run it and you will see how it has combined your tables. Also, if you look at the new report (InventoryTagALL) and look at the record source, you will see that I’ve reference the new query here (rather than a table). You can use the new query as the source of all your reports for the time being.

It’s worth reading up on queries as they are the workhorse of relational databases.

Stick with it. Access and relational databases isn't something that you learn overnight. Every now and again the penny will drop and you'll find yourself taking a big step forward.

Stopher