Linking different MS Access tables together to show a text on the label based on product number.

Issues related to label design (working with databases, data processing, RFID encoding etc.) and printing (from NiceLabel Express, NiceLabel Pro, NiceForm and NicePrint)

Moderators: Georges, milos, NiceLabel Support Team

Post Reply
mmp
Newcomer
Posts: 1
Joined: Wed Apr 22, 2020 11:42 am

Linking different MS Access tables together to show a text on the label based on product number.

Post by mmp » Thu Apr 23, 2020 11:40 am

Dear Nice Label forum,

I am a total rookie in working with labels and the program NiceLabel Designer 2019. So need a little help 😊

I have connected a MS Access database with multiple tables. My problem is to connected information from one table in Access to another.

In one of the tables is information about the product including different product numbers. When a product number is available for a given product should NiceLabel pull information from another table to display the matching text on the label.

Example from attached photo. In screenshot A is shown table 1 from the database. The information is product name and two columns with product codes (arrow Green and Black). These product codes should then be linked to table 2 (screenshot B) and search for a match in the product code column (Red arrow). On the label should be displayed the text from the column “Shown text 2” (Blue Arrow).

How to link these together within Nice Label designer?

And btw Table 1 is continues being updated whereas table 2 is static. This is the reason for having the information in two tables.

/Best
Magnus
Attachments
illustration til hjælp af database link.png
illustration til hjælp af database link.png (180.4 KiB) Viewed 743 times

highfrost
Valued Contributor
Posts: 34
Joined: Tue Aug 19, 2014 10:51 am

Re: Linking different MS Access tables together to show a text on the label based on product number.

Post by highfrost » Thu Apr 23, 2020 3:45 pm

Hi magnus,

The main problem you have is that you don't have exact keys for you tables to easily link them.
If you don't want to change your second db to also have prodcode 1 and prodcode 2 you will have to combine the 2 fields of your first db when selecting a row. We can do that :)

Make 3 variables
PD1 - for product code 1
PD2- for product code 2
PDCombined - for the codes combined

On your table object in your niceform (picture D) go to settings and attach a variable to a column. "PD1" for "Prod code1" and "PD2" for "prod code2".
And also on the table object set an event on selection change.
Throw a VB or python script on there.
Make it something like
if PD1 = "" then
PDCombined = PD2
elseif PD2 = "" then
PDCombined = PD1
else
PDCombined = PD1 + "+" + PD2
end if

Next in your form go to your Data tab and edit your second db.
To keep things easy use the filter option. Add a rule, column Productcode = PDCombined (use datasource)

Now your tables are connected.
What I usually do is also put the second db somewhere on a hidden layer so I can attach my label variables to those columns

Hope it's clear and it helps you out

PS if you just change your second database to have the 2 columns for the product code you don't need the script part and you can just filter on prodcode1 = PD1 and prodcode2 = PD2

Post Reply