Printable Version of Topic
Click here to view this topic in its original format
UtterAccess Forums _ Access Reports _ Printing Continuous Roll Label [censored]
Posted by: Sarah4uk Oct 18 2019, 10:33 AM
Hi
I've spent all day trying to creat a report, and failed. I'm not Access savvy, and our database was made years ago by someone who i can't ask for help....
Basically i print labels for plants, using the data below
 |
When the database was set up, it was made with an interface like
 |
to select what plants/how many labels i need
making
 |
Now; i would like to set up a semi-automated process for printing some labels.
CatGenusIDRef and CatSpeciesIDRef are number linked to words (which i need the words printer, not the numbers)
But i also need VarietyName and Description to be printed... so far so good...
 |
BUT i would like it to print the number of labels for each (2020), and order them by Auricula Let - there are several different letters, and i want to print all the 'a's on one coloured lable, all the 'd's on another etc...
I have no idea how to write code, and everything i've tried has failed....
I'm sure its a super simple fix; but i just can't get my head around it!!!!
Many Thanks if you got this far..
Sarah
Posted by: GroverParkGeorge Oct 18 2019, 10:51 AM
Welcome to UtterAccess.
Your labels need to be based on a query that joins the appropriate tables and the appropriate fields from those tables.
BTW: when you use the phrase "number linked" and when I see that screenshot, it gives me the impression whoever created this Access Relational Database Application used Lookup Fields in the table. This is generally considered a sub-optimal approach, but remedying that problem might be more trouble than it's worth.
To confirm this assumption, please open the table in design view and look at the property sheet for the field called CatGenusIDRef. I'll bet it looks like this, with a reference to a different table in your Access Relational Database Appplication:
 |
To make your labels work, therefore, you'll need to include that referenced table in this lookup field in the query that provides records to the label, and make sure the text value is included.
Posted by: GroverParkGeorge Oct 18 2019, 10:55 AM
The other part of your question, regarding sorting and conditional formatting can be done with properties of the report that creates the labels.
Sorting is done here:
 |
This screenshot shows only one level of sorting, but you can sort on multiple fields, e.g. on Genus, then on Species within that genus
Conditional formatting is done here:
 |
The exact formula will be dependent on the value of the first letter of the names, i.e. Left([Genus],1) for "A", "B", etc.
Posted by: Sarah4uk Oct 21 2019, 09:04 AM
Yes; you're right with the lookUp - the database was set up in about 1998; and with imput from the website man, who uses the data to generate the website; very complicated - and Bloody Merlin is still lurking there too...
But the lookup can't be simple, can it...
 |
Posted by: GroverParkGeorge Oct 21 2019, 10:54 AM
Okay, a qualification first.
Normally I urge, implore and nag people NOT to use lookup fields in tables and to revert back to the standard approach instead.
In this case, I'm not so sure. You've been working with this data for a long time, and perhaps it's safer to make the smallest change needed to get the result. In other words, don't necessarily make the change.
Instead, include the table "Genus" in the query that provides records for the label. Open the query in design view and drag Genus into it from the navigation pane. The query designer should automatically find the join between Genus.GenusID and Catalogue.CatGenusIDRef, but if not, add it.
Now, drag the field Genus.GenusName down into the query design grid so that it is now available to the query and the needed field on the label. The same process can be used to add other related fields from other tables, if needed.