Multiple choices in field with same key??? 1

[MrsTFB]

I have a database of part numbers with vendor, drawing number etc. information included in the database.

Recently we have started receiving parts (the same part number) from different vendors. Now I need to set the vendor field to allow more than one choice while the part number is the same.

Most part numbers will only have the one vendor so I don't need an entire list of vendors, only the ones for that part.

I'm no expert at Access so this is stumping me.

I know we have an expert out there that will help me with this.

Thanks in advance,

Remember the newbie status with your replies. (|>

Bsimmons

 

[scking]

Lots of ways to handle this, but, it MUST fit in with your database design. First of all, I assume that when you say key that you mean the part number is the primary key and there are no other concatenated parts of it.

The correct way to handle this would be to add a vendor table with a vendorid as the primary key. Then use the vendorid as a part of the new primary key. Therfore the primary key would be a concatenated key of the following type: vendorid\part number. This is making the assumption that no individual vendors will have identical part numbers which is a very easy assumption to make, otherwise they would be having severe configuration control problems with their product. This alternative guarantees uniqueness for the vendor/part number.

An alternative method would be to add an autonumber field to the table and make it the primary key. This would allow you to have multiple copies of a single part number, which is an advantage. But it could also be a disadvantage for the same reason.

Or, you need to determine from you own data how to combine two or more fields to make a unique key.

All of these alternatives also require you to modify other tables which contain foreign keys to your part number column. Depending on the size of the database this may take a little effort.

Steve King

 

[MrsTFB]

Thanks for the reply, I tried the alternative method of adding an autonumber field and making that the primary key. This did allow me to enter both the records with the same part number but with different vendors.

My goal is that on the form where inspection is entered when there are multiple vendors, the user would be allowed to chose the vendor that applies to that particular inspection.

As I have it now, a form with a subform for the vendor information. When the part number is entered on the form, the vendor, description etc. appears on the subform. This comes from a seperate table from the inspection records. When I entered a second vendor for one part number the subform only gave the first vendor name. I have changed the vendor name to a combobox to allow the down arrow.

I'm a rookie as you can tell.

Thanks for any help anyone can give.

Bsimm GO TITANS!

 

[scking]

If you want a combobox to show you a list of only those vendors which are associated with a particular part number then you need a table which contains a list of the part number/vendor pairs. This table would be the RecordSource for your combobox. For instance, if you have a combobox which provides a list of valid part numbers the AfterUpdate event would have code that would assign the RecordSource property of the cboVendor combobox. Something along these lines:

cboPartNumber_AfterUpdate

Me.cboVendor.RecordSource = "SELECT * FROM vtblVendors WHERE pn = " & """" & Me.cboPartNumber & """" & ";"
Me.cboVendor.Requery

End Sub

You can even be a little fancier by disabling the cboVendor control until a valid part number has been selected from the cboPartNumber control and then enable the control. Also, if the cboPartNumber control is on the main form and the cboVendor control on the subform then the assignment would be for the subform control.

Steve King

Steve King