Combo box to control subform records

[Robmls]

Hi all,

ClientTable = Table of client details


BrandTable = Table of brands

ClientForm = Form for enterting client details
BrandForm = Subform for entering brand details

Relationship ClientTable (1 ------- many) BrandTable

I have a Form ClientForm and a subform called BrandForm. BrandForm displays records based on Client_ID from the ClientForm. When I create a combo box which controls the records in the subform, it displays all items in BrandTable, not just the records related to Client_ID.

How do I get it to only display the records from the subform? or those Brands that have the ClientForm record's ID?

[orange]

There's a free video tutorial here that may help.

http://www.datapigtechnologies.com/f...combobox1.html

There are 2 other related tutorials also that you may want to watch.
http://www.datapigtechnologies.com/f...combobox2.html
http://www.datapigtechnologies.com/f...combobox3.html

[Robmls]

Hi, Thanks for those.
I still cannot get it to work though, I created a query that looks like:
SELECT Brand.brand_Name FROM Brand WHERE (((Brand.brand_Client_ID)=[Forms]![Client_Form]![Client ID])) GROUP BY Brand.brand_ID, Brand.brand_Name;
But now nothing appears in the combo box and its asks for Client ID (even appears blank when I enter a correct ID)

[orange]

Another link
http://office.microsoft.com/en-us/ac...010098674.aspx

If you has the NorthWind database installed you may wan tto look at it. I think the Customer Orders form has subform(s) that are linked to the main form. The Subform(s) are "synced" so that subform only shows related records.

Good luck.

[Robmls]

Thanks. I am afraid I dont have Northwind.
I can create subforms fine, its just this combobox issue, that is giving me grief!

[orange]

Thanks. I am afraid I dont have Northwind.
I can create subforms fine, its just this combobox issue, that is giving me grief!

See jdraw response on AWF
http://www.access-programmers.co.uk/...d.php?t=209366

If there is something special about the combobox, please describe what it is.

I'm not sure why you'd have combos on the form and subform.

Here's a link to a Northwind download, there may be other sites as well.
http://www.microsoft.com/downloads/e...6-c632d66c529c

[cluelesscoder]

Hey Robmls, fellow newbie here. I have a combobox with a subform (which itself contains a subform) and I can get the combobox to filter the subforms. Mine isn't quite right though.

Basically, my parent form Combobox is based on a single table (let's call it ProductType), and it has no control source. Then I have a key which matches that table in my subform. I set the Subform's Master Key to the value in the parent table (pkProdTypeID) and the Child Key to the corresponding foreign key in the subform (fkProdTypeID).

One problem you'll notice is that you can't click on the Combobox to filter. I have to use the buttons at the bottom to move through the options in the Combobox.

The other problem, which is a bigger deal to me, is that I can't edit the subforms with this method, which is confusing.

Let me know if that helped. Anyone have any idea why I can't edit my subforms or use the dropdown for my comboboxes?

[Robmls]

If there is something special about the combobox, please describe what it is.

I'm not sure why you'd have combos on the form and subform.

Nothing really special about the comboboxes, they just select which record to display. However the subforms combobox, displays all database entries, not just the relative records in the subform (which only contains those linked to the main form record).

I have comboboxes on the form and subform because if there are like 100 records for each, it is not very user friendly to click through all records to the one that is needed. It is much easier and quicker just to select the required record from a list.

[orange]

Did you look at ?
See jdraw response on AWF
http://www.access-programmers.co.uk/...d.php?t=209366

This shows the related records only on the subform.

Or the Northwind database? There are examples in this database for various Forms, subforms etc.

It seemed to me that your first issue was to only show records in the subform that were associated with the record in the mainform. That was in the sample at AWF. You can now have a Form with a combo to select the main record, and then call this current form to show the main form and associated records on the subform.