Updating Combo Boxes in a Form 1

[mirgss]

Hi all:

I have a form for ordering parts. My combo boxes are Manufacturer, Part#, and Part Description. When selecting a Manufacturer, I want only Part #s from those manufacturers to appear in the combo box. Ditto for part description. When updating either Part Description or Part#, I want the other box to be updated with the proper information.

When I select a manufacturer from the first combo box, I get a popup box that asks me to "Enter Parameter Value" for Me.Mfgr. Here is my code:

Private Sub Mfgr_AfterUpdate()
 Me.PartNumber.RowSource = "SELECT PartNumber FROM tblParts WHERE mfgr = " & "Me.Mfgr" & ""
              
End Sub

Previous to this, I had Me.Mfgr without quotes. However, mfgr is a text field, so I thought I had to have quotes.

Can anyone help?

Thanks,
Miranda

 

[Golom]

You do need quotes but you have them in the wrong place. If you use "Me.Mfgr" then that treats it as a text string and not as a reference to the control. Try

Private Sub Mfgr_AfterUpdate()
   Me.PartNumber.RowSource = _
        "SELECT PartNumber FROM tblParts WHERE mfgr = '" & Me.Mfgr & "'"
End Sub

 

[mirgss]

Awesome. Thanks!

Is it a faux pas to ask another question in the same thread that is related? For some reason, whenever I select a manufacturer, my part number drop down is blank. My row source for part number is:

SELECT tblParts.PartID, tblParts.PartDescription 
FROM tblParts 
WHERE (((tblParts.Mfgr)=[Forms].[frmOrderLine subform].[mfgr]));

I have the same row source for the part description box and that works perfectly - and when I update the part description, it updates the part number. What am I doing wrong?

 

[Golom]

Just taking a SWAG (Scientific Wild-A** Guess), I assume that PartID is the primary key. The default action when creating a combo-box is to hide the primary key when there are multiple columns. Check that your combo-box has only one column; it is not hidden and drop the PartDescription from your SQL. You do need it in the other combo-box but not in this one.

 

[TheAceMan1]

How are ya mirgss . . .

Try the following instead:

SELECT PartID, PartDescription FROM tblParts WHERE ([Mfgr]=Forms![[b][COLOR=#204A87]MainFormName[/color][/b]![frmOrderLine subform].Form![mfgr]);

I query the control logic your trying to install here. In the real world ...
[ol][li]A Part# is tied to a manafacturer.[/li]
[li]A Part Description is tied to the Part# it belongs to ... not the manafacturer.[/li][/ol]
So you should have a cascade of control (typical enough in access).

Your Thoughts? . . .

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[mirgss]

Hi Golom:

I removed PartDescription from my SQL for the Control Source for Part# and checked my query in Datasheet view to make sure the appropriate columns were checked/not checked to be visible. I also checked the Property Sheet for the combo box and messed around with the column count and width...no dice.

AceMan:

I'm doing alright but I would be a lot better if I could get this thing to work! I tried your query for my row source and that didn't work either.