Interdependent combo boxes 1

[THWatson]

Using Access 2000

I have a form called frmCustomers. On this form is a subform called fsubProjects, related to frmCustomers by CustomerID field.

On the subform, fsubProjects, there is a subform called fsubMaterials, related to fsubProjects by ProjectID field.

On fsubMaterials, I have 2 combo boxes.
One is called Category and is based on tblCategory which has 2 fields (CategoryID and Category).
The second is called Item and is based on tblItems which has 3 fields (ItemID, CategoryID and Item).
The two tables are related, one to many, on the CategoryID field.

In fsubMaterials, when I click on an entry in Category, I want the Item combo box to show only those items which have the same CategoryID as the entry selected in the Category combo box.

What I have done to try and accomplish this is place code on the AfterUpdate event for the Category combo box. That code is

Me.Item.RowSource = "SELECT tblItems.ItemID, tblItems.CategoryID, tblItems.Item FROM tblItems WHERE (((tblItems.CategoryID) = [Forms]![fsubMaterials]![Category]![CategoryID]));"

What happens is that a parameter box appears asking for a CategoryID, and when I place the correct matching CategoryID the correct items appear in the Item combo box, but it won't run without this parameter intervention.

I have tried other formulations of the code, but can't get it to work correctly.

Can someone point me in the right direction.

Thanks.

Tom

 

[straybullet]

Try something along the lines of:

Me.Item.RowSource = "SELECT tblItems.ItemID, tblItems.CategoryID, tblItems.Item FROM tblItems WHERE tblItems.CategoryID) = " & Me.Category & ";"

The Me.Category refers to the control "Category". The reason it was prompting for a value for "CategoryID" is that there is no control with that name on the form.

Let them hate - so long as they fear... Lucius Accius

 

[TheAceMan1]

How are ya THWatson . . .

[blue]straybullet[/blue] beat me to it but here's a formatted view:

[blue]   Dim SQL As String
   
   SQL = "SELECT ItemID, CategoryID, Item " & _
         "FROM tblItems " & _
         "WHERE ([CategoryID] = " & Me!Category.Column(0) & ");"
   Me!Item.RowSource = SQL[/blue]

When the SQL is assigned to the RowSource, if the CategoryID = 5 the Where clause reads:

[blue]"WHERE ([CategoryID] = 5);"[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997

 

[THWatson]

Hey, AceMan... I haven't been on this site for a while. Nice to hear from you. And your method works really well. THANKS!

straybullet
For whatever reason, even though your solution looks as if it should work it doesn't. Access must be looking for something more.

Thanks to both of you for replying.

May 2008 bring many great things your way!

Tom

 

[TheAceMan1]

How are ya straybullet . . .

I too thought you had it until I saw the error (only reason I posted):

[blue]"SELECT tblItems.ItemID, tblItems.CategoryID, tblItems.Item FROM tblItems WHERE tblItems.CategoryID[red][b])[/b][/red] = " & Me.Category & ";"[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997

 

[straybullet]

lol thanks AceMan - that'll teach me to be more careful when deleting parentheses (sp?)

Let them hate - so long as they fear... Lucius Accius

 

[TheAceMan1]

straybullet . . .

We all do it at one time or another! . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886