Selecting Part Number Based on Manufacturer

[mirgss]

Hi:

I have a table and a form with Part Number and Part Description. Right now, I have the form set to autofill the part number and price when a particular description is selected, and autofill the description and price when the part number is selected (using combo boxes). My customer now wants to add a "Manufacturer" field to the database. Is it possible to design the form so when a manufacturer is selected, the combo box only shows the part (numbers and descriptions) that are available for that manufacturer?

Thanks for your help!

Miranda

 

[SkipVought]

hi,

Is it possible to design the form so when a manufacturer is selected, the combo box only shows the part (numbers and descriptions) that are available for that manufacturer?

YES, if you have a table that relates manufacturer with part number.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mirgss]

Awesome. I do have tblParts, which has the manufacturer, part #, and part description. Can you point me in the direction of a tutorial or something so I can figure it out?

 

[SkipVought]

A simple query like...

select [part number]
from [your table]
where [manufacturer]=me.yourform.selectedmfg.text

as source for your combobox control

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dhookom]

Slight correction/clarification to Skip's post. The "Text" property is not available if the control doesn't have the focus. Substitute "Value" or simply leave it off. Also, the "Me" is only available in VBA.

SELECT [PartNumber]
FROM [YourTableName]
WHERE [Manufacturer] = Forms.yourform.selectedmfg;

Since you hadn't provided any information about your table and field names, our replies need some transformation.

Duane
Hook'D on Access
MS Access MVP

 

[mirgss]

This points me in the right direction but I have a lot going on in this DB right now. I added the code you suggested (to the best of my ability) and now I get an error when I try to choose a part description: I can't assign a value to the object. The code that the error directs me to is this:

Private Sub PartDescription1-AfterUpdate()

  PartNumber1 = PartDescription1

End Sub

I'm not sure if this is because I entered the control source wrong or if it's something else. Do you have any suggestions?

Also, should I be using a separate table for the manufacturer and put a foreign key in tblParts instead of simply having the manufacturer in tblParts?

 

[dhookom]

We might have a suggestion if you provided other code, row sources, etc. Also, please copy and paste your code so it doesn't include errors.
This code

Private Sub PartDescription1-AfterUpdate()

I expect should be

Private Sub PartDescription1[red][b]_[/b][/red]AfterUpdate()

Any time your code references a control, you should use the "Me." prefix.

Duane
Hook'D on Access
MS Access MVP

 

[mirgss]

You're right; I apologize for mistyping. For each part number and description, I have something like what I posted above:

Private Sub PartNumber2_AfterUpdate()

    PartDescription2 = PartNumber2
       
End Sub

Row sources for the part numbers and descriptions look like this:

SELECT [Parts].[PartNumber], [Parts].[PartDescription], [Parts].[Price] FROM Parts ORDER BY [PartNumber];

Row source for Manufacturer is:

SELECT [Parts].[ID], [Parts].[Manufacturer] FROM Parts ORDER BY [Manufacturer];

although I now think I should have a separate mfgr table.

Control source for manufacturer is:

=[Parts]![Manufacturer]

but it doesn't work.

Control source for Part # is:

=(Select [PartNumber]
from [Parts]
Where [manufacturer] = Forms.Estimates.mfgr1)

as suggested above.

Control source for Part Description is simply:

PartDescription1

which is similar to what Part # was before I changed it.

Does this info help? Thanks again for your replies; I've got a couple of books and I've been using Google but they are only useful up to a point.

Miranda

 

[dhookom]

Miranda,

Your code suggests PartNumber2 and references PartDescription2 but you haven't told us anything about if these are controls or whatever.

Also, a control source can't be a SQL statement.

Do you have a combo box to select the manufacturer?

Duane
Hook'D on Access
MS Access MVP

 

[mirgss]

Oh, then Skip must have meant the statement for the row source. Sorry...I am still pretty new to all this (obviously).

In my form I have Mfgr 1-8, Part Numbers 1-8 and Part Descriptions 1-8 as combo box fields (for ordering of up to 8 parts). I don't know if there is a better way of doing this, so that's how I designed it. So my form looks somewhat like this:

Qty  Mfgr  Part#  PartDesc  Price  Ext Price
Qty  Mfgr  Part#  PartDesc  Price  Ext Price
Qty  Mfgr  Part#  PartDesc  Price  Ext Price
etc...

Thanks for your patience with this noob.

 

[dhookom]

Personally, I wouldn't go any further until your table structure was normalized. Check Description of the database normalization basics.
Each part should really create a new record in a related table.

If you have any questions, please come back and ask...

Duane
Hook'D on Access
MS Access MVP

 

[mirgss]

I will take a look at that; thanks! I do have tblEstimates which stores each record created in the form. My customers are not tech wizards (even less than me!) so I thought a form would be best for entry instead of asking them to enter information into tblEstimates. I'll definitely take another look at my table structure and see what I can do to normalize it.

Thanks!