Same Part number, two different vendors

[MFS]

Good morning,
I've built a small database for our MRO inventory which has been working quite well. I'm now running into a request from the data entry employee requesting that when he queries a part number something should inform him that he has two + vendors that carry the same part number. I allow the database to have duplicate part numbers so that is okay but he is requesting a message box appear when he searches one of those part numbers to inform him of 'X' amount of vendors. I would have to then show who.
Could someone help me with this?



Mike

[jzwp11]

Technically speaking, if a part can be supplied by more than one vendor and a vendor can supply many parts to you, that describes a many-to-many relationship which is routinely handled with 3 tables as follows:

tblParts (ony 1 record per part number in this table)
-pkPartID primary key, autonumber
-PartNo

tblVendors
-pkVendorID primary key, autonumber
-txtVendorName

tblPartVendors (each part can have many records in this table--one for each supplier)
-pkPartVendorID primary key, autonumber
-fkPartID foreign key to tblParts
-fkVendorID foreign key to tblVendors
-PartCost

With the above structure, what you user want to be able to do is possible

[Missinglinq]

To determine if there is more than one supplier ofr a given part, assuming that there is one Record per Vendor per Part,

If the part number is defined as a Number Datatype:

If DCount("*","TableName", "PartNum = " & Me.SearchBoxName)> 1 Then
 Msgbox "There are Multiple Suppliers for this Item!"
End If

If the part number is defined as a Text Datatype:

If DCount("*","TableName", "PartNum = '" & Me.SearchBoxName & "'")> 1 Then
 Msgbox "There are Multiple Suppliers for this Item!"
End If

replacing TableName with the actual name of the Table that holds the data, PartNum with the actual name of the Field, in the Table, that holds that piece of data, and SearchBoxName with the name of the Control, on the Form, that holds the number to be searched for.

You could then have, perhaps, a small Subform, or perhaps a Listbox, sourced by a SQL statement, using the same Criteria, to return all appropriate Records.

Linq ;0)>