subform combo box control problem (1 Viewer)

RobMls

New member
Local time
Today, 13:02
Joined
May 6, 2011
Messages
6
Hi all,

I have a a form 'clientForm' based on a database called 'clientDB'. In this 'clientForm', I have a subform, that lists all the 'brands' related to the 'client' record being displayed (linked by a one to many relationship).

brandDB.Client ID (foreign key) many - clientDB.Client ID (primary key) one

I created a combo box that lists the 'brands', which controls which record is being displayed, however the combo box shows all 'brands' not just the 'brands' related to the 'client' record.
IE: the subform displays 2 records, but the combo box within the form, displays the whole list of records.

How can I get the combo box, to only display the records that are determined by the client record? IE: only records that equal the client ID (like the rest of the subform)

Thank you!
 

John Big Booty

AWF VIP
Local time
Today, 22:02
Joined
Aug 29, 2005
Messages
8,263
Welcome to the forum.

You can use;
Code:
Forms!NameofForm!NameofSubform.Form.NameOfControl
as criteria in the Row Source of your Combo.

You will also need to force the combo to requery when you move between records and when a new record is added, to ensure it remains synchronised with with the current record.
 

RobMls

New member
Local time
Today, 13:02
Joined
May 6, 2011
Messages
6
Hi, Thanks for your reply.
What goes in the 'NameOfControl' bit?
I added the field name, it is two words, is that why I get an error?
 

John Big Booty

AWF VIP
Local time
Today, 22:02
Joined
Aug 29, 2005
Messages
8,263
Hi, Thanks for your reply.
What goes in the 'NameOfControl' bit?
I added the field name, it is two words, is that why I get an error?
Yes "Name of Control" will be your field name, given that you have embedded spaces in the name enclose it in square brackets;
Code:
[Your Filed Name Here]
For future refrence avoid using spaces and other special characters in control and object names, limit yourself to alpha and numeric characters and the underscore (_). Consider using a naming protocol along the lines of; FRM_FormName, TBL_TableName, QRY_QueryName, etc.
 

RobMls

New member
Local time
Today, 13:02
Joined
May 6, 2011
Messages
6
I have tried:

Forms!ClientForm!BrandForm.Form.Brand_Name
BrandForm.Form.Brand_Name
BrandForm.Brand_Name
BrandForm.[Brand Name]


Where:
ClientForm = the main form
BrandForm = the subform
Brand_Name = the data field

All throw up error saying that the item does not exist?
 

RobMls

New member
Local time
Today, 13:02
Joined
May 6, 2011
Messages
6
I have attached a copy of the database (converted from 2007 to reduce file size). Hopefully this will help.

Thanks!
 

Attachments

RobMls

New member
Local time
Today, 13:02
Joined
May 6, 2011
Messages
6
Hi everyone, I still have not been able to solve this problem. Any help much appreciated, thanks!
 

jdraw

Super Moderator
Staff member
Local time
Today, 08:02
Joined
Jan 23, 2006
Messages
13,264
RobMls, I looked at your database. I'm not exactly sure what your overall goal is, however, I made a new Form and used your brandSubform as the base.

I added a record to the Client table and a couple of Brand records.

The new form shows a synchronized mainform and subform.

If you go to client3, you will see the associated products.

I didn't understand your comboboxes so removed them.

Hope this is helpful.
 

Attachments

RobMls

New member
Local time
Today, 13:02
Joined
May 6, 2011
Messages
6
Thanks jdraw, but I'm afraid that is not what I am looking for.
I can create linked subforms, no problem. The problem is the subform's combobox, I only want it to display brands that are linked to the client.

It is more user friendly with them.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom