Use button to record combo box PK's to table (1 Viewer)

[vbaInet]

Now for the setup I suggested:

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


tblDesignName
-------------------
DesignNameID  [COLOR="blue"] - PK[/COLOR]
DesignName
JewelryTypeFK  [COLOR="blue"]- PK  [/COLOR]  


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


tblCollectionDesign (junction)
-------------------
CollectionNameFK [COLOR="blue"]- PK[/COLOR]
DesignNameFK     [COLOR="blue"]- PK
[/COLOR]

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

With the tblDesignName table the PK highlighted above isn't strict. It can be determined by the composition of DesignNameID field.

 

[vbaInet]

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.

Sure!

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.

It's best to put this memo field in a separate table. Memos are prone to corruption and having it in an isolated table ensures that it doesn't affect the entire record. Sometimes, it's even put in a separate db altogether but it's not necessary in your case.

 

[bigalpha]

Just when I thought I had this all figured out!

I knew memos were not preferred but didn't know I should separate them out. I'll make sure to do that, thanks!

I really appreciate your time and help! I'll report back, but may not be until Monday.

 

[vbaInet]

You may never experience any problems with your current setup, but you might as well do it since you're still in the design stage.

Sure... till next week!

 

[bigalpha]

Okay, so I've set this up according to your post and this is what I've come up with.

I don't understand the point in linking tblCollection and tblDesignName together and tblDesignName if we are not using the composite key from tblCollectionLink.

Also, why create unique combinations of tblDesignName and tblJewleryType inside of tblDesignName if we are creating those combinations again in tblJewelryInventory?

 

[vbaInet]

It was pretty late over here when I posted that

Here's an amendment:

tblCollectionDesign (junction)
-------------------
[COLOR="blue"]CollectionDesignID[/COLOR]
CollectionNameFK - PK
DesignNameFK     - PK


tblJewelryInventory
-------------------
JewelryInventoryID
JewelryTypeFK    - PK
[COLOR="Blue"]CollectionDesignFk - PK[/COLOR]
DateSold
NoCenterStone

 

[bigalpha]

It was pretty late over here when I posted that

Okay, well as long as I'm not crazy I redid the relationships but I don't quite understand the logic of it. What makes this set-up better than the simpler version (three FK's in tblJewelryInventory only)?

Thanks again for your help!

 

[vbaInet]

It's up to you how you want to set it up, I'm just giving you alternatives. Will you have a form for entering just collections and designs? If you do then this is where this setup becomes useful. It's mainly to enforce this criteria "Each of these collections can have many Designs (design 1, design 2, etc)".

If it's still confusing you can go with the setup in post #19.

 

[bigalpha]

It's up to you how you want to set it up, I'm just giving you alternatives. Will you have a form for entering just collections and designs? If you do then this is where this setup becomes useful. It's mainly to enforce this criteria "Each of these collections can have many Designs (design 1, design 2, etc)".

If it's still confusing you can go with the setup in post #19.

I originally had a form for entering all the different combinations. So I would just select from the combos and it'd filter the subform because I thought that was easiest. Then I tried to move over to a system that I could use the combos to filter and add data.

If I use the system in post #19, can I still create cascading combos just based on that one table?

 

[vbaInet]

Both setups will allow you to cascade the combo boxes. With #19 setup all the cascading will need to be done from the Inventory table. But like I said much earlier in the thread, don't let the need for cascading determine how the table should be setup. Your form should work around properly structured tables, not the other way round. Maybe that's what is causing the confusion!

 

[bigalpha]

No, I definitely understand that proper table structure is #1 priority. I just don't have much experience with cascading combo boxes and wasn't sure if the tables had to be nested like I first had them. Is #19 structure considered proper?

 

[vbaInet]

I trust you do.

It's a good setup. Just remember to include validation code at form level to ensure that you have a one to many relationship between collections and designs.

 

[bigalpha]

I trust you do.

It's a good setup. Just remember to include validation code at form level to ensure that you have a one to many relationship between collections and designs.

Right. So I can just use some code to see if CollectionFK, DesignFK and TypeFK exist in the inventory table. If so, filter the subform; if not, then open an input form.

 

[vbaInet]

You would normally validate it before a new inventory record is inserted, and if the combination doesn't exist then the insert will be allowed, otherwise you cancel the insert action. The event where this will happen is the Before Insert event of the form.

 

[bigalpha]

You would normally validate it before a new inventory record is inserted, and if the combination doesn't exist then the insert will be allowed, otherwise you cancel the insert action. The event where this will happen is the Before Insert event of the form.

Could I stick it in the On Click event of a command button? So I click the button and it runs a DCount. If <1, then open a form to insert a new record into the inventory table. If <>0, then it filters the subform.

 

[vbaInet]

But where is the DCount() getting its parameter values from?

 

[bigalpha]

But where is the DCount() getting its parameter values from?

Can't I set it to look up the value of each combo box (which is the PK from each of the three tables) and compare it to the Inventory table?

 

[vbaInet]

Ok, let's step back a bit. The cascading combo boxes are supposed to be used on a search form right? So you want to add a button that would allow the user add that combination if the search doesn't return any matching records?

 

[bigalpha]

Ok, let's step back a bit. The cascading combo boxes are supposed to be used on a search form right? So you want to add a button that would allow the user add that combination if the search doesn't return any matching records?

Preferably yes, though I'm not adverse to having it open a 'new entry' form for this purpose.

 

[vbaInet]

That's up to you! You design whatever you see fit for your users.