HOW do I make selections and carry only those selections thru proc

[kealaz]

Hello,

I hope someone can help me with this. THANK YOU in advance for any and all
tips and advice.


I have many tables. The ones that I think are pertinent to this issue are....

tblPOTODO
PART_NO
MANUF1
MANUF1_PN
MANUF2
MANUF2_PN
MANUF3
MANUF3_PN

which represents the part number and 3 possible manufactures of the part and
their respective part numbers.


tblVENDOR
VENDORNAME
ADDRESS
PHONE
FAX
VENDORNO

which lists the vendors that we use


tblMANUF
MANUF
VENDORNAME
VENDORNO

which lists the manufactures that each vendor carries. In this table,
manufacturers are listed multiple times because more than one vendor carries
each manufacturer AND vendors are listed multiple times because vendors carry
multiple manufacturers.

For example, a manuf001 part may be sold by vendor01, vendor 02 and vendor 03.
Additionally, vendor01 may carry parts by manuf001, manuf002 and manuf003.


so, if I have a part number with the following info. (and that part number
is in tblPOTODO)

(our) PART_NO: 1234-5678
MANUF1: manuf001
MANUF1_PN: abc001
MANUF2: manuf002
MANUF2_PN: lmn001
MANUF3: manuf003
MANUF3_PN: xyz001


What I need to do is create a form, or some other format (if there is a
better way than a form) to have my user CHOOSE a manufacturer from the 3
listed as possible options. Once they choose a manufacturer, then I need to
look at my tblMANUF to see which vendor supports that product and give them
those vendor choices (this very likely will be more than 3 options, could be
many vendors)... so that they can select which vendor to purchase this part
from. All of this information needs to be written to tblPOTODO, which is the
table that my Purchase Order gets it's information from.

What would be the best way to capture the selections (information) and carry
that on to my ultimate destination, which would be tblBUY (not tblPOTODO as
stated in the original post; oops!).

tblBUY
PART_NO
VENDORNAME
MANUF
MANUF_PN

which is the final information after all of the selections have been made.

Can I do this with a form? If so, how would the users make the selections?
Which controls would be best suited for this application. Once the
Manufacturer is selected, can I have them make another selection for the
vendor, based on their first selection? The vendor list, would be different
depeneding on which manufacturer is selected. If there is a better way to do
this, other than a form, like a query, or other.... please let me know also.




Thank you so much for your help!

 

[Dale Fye]

Well, I hate to tell you this, but your "best" solution involves modifying
your table structures.

1. Start out with tblParts, which would contain your part number, and some
fields describing the part.

2. tblPartMan: This table should only contain 3 fields, your part number
(Part_No), Man_ID, and Man_PN. This provides you the opportunity to
identify as many manufacturers that carry your parts as you want, not just
three. The other advantage of this is that the table, when structured this
way, can easily be joined to your Manufacturer table with a single join, not
multiples.

3. tblVENDOR: The structure of this table is generally workable, although
I'm surprised this table does not contain multiple phone numbers and email
addresses. Realisticly, I would probably have another table
(tblVendorPhone) for your vendor phone numbers with fields: VendorNO,
PhoneType, and PhoneNum, where PhoneType might include Office, Cell, Fax.

4. tblManufacturers: This table should only contain information about your
Manufacturers, and should be organized similar to tblVendor. The
tblManPhone would have the same fields as VendorPhone, but the PhoneTypes
might be different to reflect the different offices within the manufacturer
that you might contact.

5. tblVendorMan: This table would contain the VendorNO, ManNo, and the
Man_PN. This would identify which vendors handled which parts from which
manufacturer.

6. Once you have a structure similar to this, you can create your form
with:
a. A control for your part number. This could be a textbox, or maybe a
combo box. If you use a combo box, you could use a query that looks like
the following as the rowsource for that control.

SELECT Part_NO, Part_Desc FROM tbl_Parts Order By Part_NO

Set the columnCount to 2, and column widths to 1.5, 0 (where the first # is
the # of inches wide needed to display the longest part number. Then, add a
textbox next to the combo box, and in the combo boxes AfterUpdate event, put
the data from the Part_Desc field in the textbox.

Private Sub cbo_Part_No_AfterUpdate

me.txt_Part_Desc = me.cbo_Part_No.column(1)

'to be explained in "6.b"
me.cbo_Man.requery
me.cbo_Man = Null

'to be explained in "6.c"
me.cbo_Vendor.requery
me.cbo_Vendor = Null

End sub

b. Next, create another combo box (cbo_Man) or listbox (lst_Man) which uses
a query that looks something like the following as the rowsource:

SELECT Man_ID, Man_PN FROM tblPartMan
WHERE Part_NO = me.cbo_Part_No

The requery of this combo box in the cbo_Part_No_AfterUpdate event described
in "6.a" above will requery this combo box so that it only displays the
manufacturers that carry the part number you selected above. In the
AfterUpdate event of this control, and in the Part_No combo, you will need
to requery the vendor combo.

Private Sub cbo_Man_AfterUpdate

me.cbo_Vendor.requery
me.cbo_Vendor = Null

c. Next, create another combo box or listbox (cbo_Vendor) with a rowsource
similar to the following. This will ensure that your Vendor combo box only
contains the vendors that carry the manufacturer and part that you selected
in cbo_Man

SELECT DISTINCT VendorNo
FROM tblVendorMan
WHERE Man_No = me.cbo_Man.column(0)
AND Man_PN = me.cbo_Man.column(1)

d. Finally, you will need a command button that actually adds the
information from these controls to your PO_Details table, which I would
probably display in a subform, on your main form.

7. There is a whole lot more to a purchasing and inventory management
database, but I this is a good start for the purchasing portion. You might
want to include a Price column in the VendorMan table, so that you can track
and display each vendors current price for each part when you display the
vendors (if you do this, I'd use a listbox rather than a combo box for
readability). In which case you might want to sort from lowest to highest
cost, or use some other OrderBy mechanism to display the vendors in some
sort of priority order.

HTH
Dale