Use button to record combo box PK's to table

[bigalpha]

I have three cascading combo boxes. How can I program a button to record the PK's from these combos into a table, but only if those values don't already exist.

The 3 combos are circled in red and I want to record those values into tblJewelryInventory (circled in dark blue).

 

[vbaInet]

You can perform a search using DCount() with three criteria that would involve those three fk fields. If it returns 1 then it exists, otherwise it doesn't so it's ok to save.

By the way, you need relationships set up between tblJeweleryInventory and (tblCollection and tblDesignName).

 

[bigalpha]

You can perform a search using DCount() with three criteria that would involve those three fk fields. If it returns 1 then it exists, otherwise it doesn't so it's ok to save.

By the way, you need relationships set up between tblJeweleryInventory and (tblCollection and tblDesignName).

Okay, I'll look into setting up a DCount for this.

Do I need to delete the relationships between tblCollection and tblDesignName? I have a set of 3 nested forms set up that I'm going to use to enter the data into to ensure that they still cascade properly. Will that be a problem?

 

[vbaInet]

Do I need to delete the relationships between tblCollection and tblDesignName? I have a set of 3 nested forms set up that I'm going to use to enter the data into to ensure that they still cascade properly. Will that be a problem?

You've got CollectionName as an FK in tblDesignName so no it should stay related. And perhaps CollectionID would be a more suitable name since it's not the name that it's linked to. Same goes for your other FKs - not that important anyway bigalpha.

 

[bigalpha]

You've got CollectionName as an FK in tblDesignName so no it should stay related. And perhaps CollectionID would be a more suitable name since it's not the name that it's linked to. Same goes for your other FKs - not that important anyway bigalpha.

No, I appreciate all the input. Want to build it right the first time!

The reason for the FK in those tables is because I thought I needed that to ensure they would cascade correctly.

Do I need to keep my current relationships and also create the two new ones you recommended?

 

[vbaInet]

I was actually talking about the name, in tblDesignName you called it CollectionNameFk but you're linking to the ID field in tblCollection so a befitting name would be CollectionIDFk. You get what I mean? It's only aesthetics and I'm not condemning your relationships

However, if they're only there just for cascading then perhaps you need to rethink. It's only justified if it meets your business requirements on table level.

 

[bigalpha]

I was actually talking about the name, in tblDesignName you called it CollectionNameFk but you're linking to the ID field in tblCollection so a befitting name would be CollectionIDFk. You get what I mean? It's only aesthetics and I'm not condemning your relationships

However, if they're only there just for cascading then perhaps you need to rethink. It's only justified if it meets your business requirements on table level.

Oh, I see what you're saying now. Yes, I need to fix the naming so it's consistent.

Yes, I set it up this way so I could cascade them. Is this not the correct way to structure the tables for this purpose? I thought that since they are cascaded, then JewelryTypePK would end up being the unique value for the combination from the three tables.

 

[vbaInet]

I don't completely understand your business model so I'm only going by the screenshot.

* Does each jewellery type have an associated design name?
* Does each design have an associate collection?
I'm guessing that they don't and that you don't even need to cascade in the first place. It looks like those three tables are only required in the Inventory table.

 

[bigalpha]

I don't completely understand your business model so I'm only going by the screenshot.

* Does each jewellery type have an associated design name?
* Does each design have an associate collection?
I'm guessing that they don't and that you don't even need to cascade in the first place. It looks like those three tables are only required in the Inventory table.

Yes, they actually have associated records in each table. It's the same as country > state > city.

I attached an example of how the data is related. The left column is what I'm storing in tbljeweleryinventory and the right column is what I'm storing in tblInventoryLink.

 

[vbaInet]

Can you upload sample records of all three tables. Copy and paste to a spreadsheet and upload that.

 

[bigalpha]

Okay, here's the spreadsheet. I also uploaded the db in case you needed to see it for any reason.

 

[vbaInet]

Actually it only had the db, but that will do just fine

I've attached a screenshot of the Jewellery table, can you explain the relation between the JewelryType and DesignName fields there? I.e. what is Ring-Tears?

 

[bigalpha]

Actually it only had the db, but that will do just fine

I've attached a screenshot of the Jewellery table, can you explain the relation between the JewelryType and DesignName fields there? I.e. what is Ring-Tears?

So there are many collections (collection 1, collection 2, etc). Each of these collections can have many Designs (design 1, design 2, etc). Each of these designs will have a type of jewelry (ring, necklace, etc). What's in there is just junk data to help me figure things out.

An example of real life data is (From most generic to most specific)
Collection | Design | Type
Fluted | Tears | Ring
Fluted | Tears | Necklace
Fluted | Tears | Broach
Hollein | Hollein | Ring
Hollein | Hollein | Necklace

The other half of the db is the details of each variation of what I listed above. That's the right column on the hand written screen shot I posted above.

 

[vbaInet]

Ok, based on your description, this is how those three tables (i.e. Collections, Designs and Jewellery) should be structured:

* Collections table
* Designs table
* Jewellery table
* Junction table between Collections and Designs (one collection to multiple designs)
* JewelleryTypefk field in the Designs table (one design to one jewellery type)

 

[bigalpha]

Ok, based on your description, this is how those three tables (i.e. Collections, Designs and Jewellery) should be structured:

* Collections table
* Designs table
* Jewellery table
* Junction table between Collections and Designs (one collection to multiple designs)
* JewelleryTypefk field in the Designs table (one design to one jewellery type)

With this set up, I'm stating that Tears | Ring can be apart of multiple Collections, right? Why wouldn't I put tblCollection, tblJewelryType and tblDesignName all together with a link table?

 

[vbaInet]

Because you could end up with the wrong combination of Collection to Design.
Your Inventory table will now reference the Junction table and the Jewellery table and the PK (or composite key) in the Inventory table will be the IDs from both tables. That's one way of enforcing strict referential integrity at table level.

 

[bigalpha]

Because you could end up with the wrong combination of Collection to Design.
Your Inventory table will now reference the Junction table and the Jewellery table and the PK (or composite key) in the Inventory table will be the IDs from both tables. That's one way of enforcing strict referential integrity at table level.

I understand what you're saying, but it just looks weird to me. I'm certainly not disputing what you say!

I attached a screenshot of how I have it set up now.

 

[vbaInet]

Set it up on a backup of your db and see how it looks. Ask if you have any questions.

If you want to set it up your way, then all three tables need to be linked to the Inventory table and all three FKs should be the PKs (normally called composite keys). It would ensure that the combination of all three is unique.

 

[vbaInet]

For clarity:

tblCollection
-------------------
CollectionPK
CollectionName
CollectionDescription
CollectionDiscontinued
CollectionHistory


tblDesignName
-------------------
DesignNamePK
DesignName


tblJewelryType
-------------------
JewelryTypePK
JewelryType


tblJewelryInventory
-------------------
JewelryInventoryID
JewelryTypeFK    [COLOR="Blue"]- PK[/COLOR]
DesignNameFK     [COLOR="blue"]- PK[/COLOR]
CollectionNameFK [COLOR="blue"]- PK[/COLOR]
DateSold
NoCenterStone

... all based on your current field names.

By the way, I don't know what the CollectionHistory field is.

 

[bigalpha]

For clarity:

tblCollection
-------------------
CollectionPK
CollectionName
CollectionDescription
CollectionDiscontinued
CollectionHistory


tblDesignName
-------------------
DesignNamePK
DesignName


tblJewelryType
-------------------
JewelryTypePK
JewelryType


tblJewelryInventory
-------------------
JewelryInventoryID
JewelryTypeFK    [COLOR="Blue"]- PK[/COLOR]
DesignNameFK     [COLOR="blue"]- PK[/COLOR]
CollectionNameFK [COLOR="blue"]- PK[/COLOR]
DateSold
NoCenterStone

... all based on your current field names.

By the way, I don't know what the CollectionHistory field is.

I'm going to set it up both ways. I don't particularly care the way in which its set up, as long as it won't cause me trouble and its straightforward to manipulate.

The collection history field is a memo that has a paragraph or two that describes the background of how the design was imagined. You can see it in the demo db I posted earlier.