1 combobox populating another

[camelman]

I have a bound continous form with 2 comboboxes on it.
A [company] combobox and a [company_member] combobox.

If you select a company it should update the rowsource of the [company_member] box to only show the members in that company.

Both combo boxes have
BOUND COLUMN = 2 (So that I can display the names, but bind the comboboxes the ID fields)
LIMIT TO LIST = true (mandatory if using multiple fields in combobox)

Problem! I have tried 2 things here.
1)
I have tried making a rowsource for company_member which is dependant on the value of the company field.

2)
I have tried placing an event on after_update of company
which updates the rowsource of company member.

Both of these solutions work except for 1 thing.
Company_member is set for limit to list.
Because you are changing the rowsource of company_member each time you update company, some of the company_member boxes on the continous form will now contain values which they are "not allowed".
All the instances of the combobox control on the form contain the same rowsource.

When this happens it causes the values in the company_member box not to show.

If I make the bound column = 1 and put the company_memberID as column 1 then I dont get this problem.
Unfortunately I need to display the company_member_name.

Help ?
Struggling here in a big way.

ps: I can put a text box on the screen and use dlookup to find the value based on the ID in the combobox but this makes the form horribly slow and is really not a decent solution.

 

[batteam]

Doesn't your bound column value from the company name combo box send a parameter to a query that, when runs, only results in company members who belong to that company (they have that chosen company ID in their record in the table)? If so, when ever you choose a company name, just requery the company member combo box - e.g. Company_Member.Requery - and you will be running the rowsource query for the company member combo box - and that should only show the members whose company ID matches the choice in the company name combo box.

My experience here is that you must 'REQUERY' your member combo box. Do this on the Click event or Lost_Focus event or both, of the Company_Name combo box. Then, if your send your bound column company name Id over to your member combobox recordsource correctly, your member combo box should only show the names you want.

Hope this helps. Good Luck

 

[TheAceMan1]

How are ya camelman . . . .

It sounds as if both CB's are bound to the ID field. Hard to tell whats going on without further info!

You need to post the code in the [blue]AfterUpdate[/blue] event of the Company CB. This will clear things quite a bit.

Are the CB's bound to fields?

Some info for ya:

[blue]LIMIT TO LIST = true (mandatory if using multiple fields in combobox)[/blue]

Not so! You have the wrong idea of Limit To List here. The function of limit to list (if true), is to limit data entry to the values in the field tied to the dropdown list. This is the value that displays in the textbox portion of the CB, and is the first field not set to zero in the [blue]Column Widths Property[/blue]. If Limit In List is set to false, your free to enter what ya like.

[blue]Both of these solutions work except for 1 thing.
Company_member is set for limit to list.)[/blue]

[blue]Limit To List[/blue] has nothing to do with the problem.

[blue]Because you are changing the rowsource of company_member each time you update company, some of the company_member boxes on the continous form will now contain values which they are "not allowed".[/blue]

When you change the [blue]RowSource[/blue] of a CB, it automatically requeries and thats all! If other things are changing, this is the indicator that the CB is probably bound to a field.

[blue]All the instances of the combobox control on the form contain the same rowsource.".[/blue]

I have no idea what this means! Can you be more specific?

See Ya! . . . . . .

 

[KenReay]

Hi
See FAQ on Combo boxes in this forum, it explains how to do this

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[camelman]

Hey guys. Thanks for all your speedy responses.
I think you are all missing the point of why this is a problem.
This is not as simple as it looks.

CmboCompany: select company.companyName, company.companyID from company

CmboCompanyMember: select member.surname, member.memberID from member where member.companyID = forms![sales]![companyID]

Take note that the first fields selected are NOT the ID fields and that the bound column is column 2 in both comboboxes. Both comboboxes are bound to fields because this is a continous form.

Aceman: If the bound column is not column 1 then you can't set limit to list = false. Try it, you will see that access tells you that it cant be done.

Ken: Your examply is different because you are selecting the ID's first and then the rest of the values. That is why you aren't having the same problem as I am. The problem with your example is that the guy working on the form see's the manufacturer as a number. This isn't readable enough for my project so I can't do it that way.
I want them to see the name but have the field bound to the number.

Try Kens example but binding to the 2nd column and requerying the 2nd combo box each time the 1st box changes.
Do this on a continous form where you have filled values in for 5 or 6 records.
record 1: company: mcdonalds member: Jon
record 2: company: ceres member: Bob
record 3: company: I&J member: Fred

If limit to list is set (which it has to be as explained above), then if the combobox has been updated to contain all the members of Ceres, the member Jon is not a valid entry into the combobox. When this happens, Jon and Fred dissappear. They re-appear when you set the rowsource back to a SQL statement that contains their values.
Its important to remember that the 3 comboboxes on the form (in the example above) cannot have their rowsources set individually.

Any ideas ?

 

[KenReay]

Hi

To bind to the number, but show the name you simply make teh number the first column, set bound column to 1, and set column width to 0, user sees the name, but value of the combo is teh number (or id in my example)

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[camelman]

Ahhhh.
ok ... 2 ways to skin a cat.
That works the same as binding to the 2nd column like I was doing.

I tried it your way and the problem remains the same.
The values still dissapear.
I have put an "on enter" event on CmboCompanyMember to requery it. Otherwise you can select a company in 1 row and then click the combobox in another row and end up with the wrong data (if you used after update on CmboCompany)

Easy way to show you what I am talking about.
Try your example using a continous form instead of a single form and you will see what the problem is.

Otherwise I can mail you my ms access file with 1 form that shows the problem.

I really appreciate the time you are taking to read my long explanations. I have this problem in so many places that solving it would really make my life easier.

Camelman

ps: I do have a workaround but it makes the forms very slow and clumsy. It isn't really a proper solution.