Combobox setting value of another combobox

[camelman]

I have a bound CONTINUOUS 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.

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

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

the column width of column 1 in both boxes is 0 so that they show meaningful names and not ID numbers to the user.

Limit to list is set to true because access doesn't allow it to be false if you have multiple columns in your select statement.
The "On enter" event on the CmboCompanyMember says "CmboCompanyMember.requery" so that it always shows the records for the correct company.

You are probably thinking "This all sounds well implemented and should have no problems!"
Well ... here's the problem.

On a single form this works perfectly. On a continuous form we have a slight problem.

PROBLEM:
Lets say the continous form looks like this.

COMPANY MEMBER
row1 mcdonalds Jon
row2 Ceres Bob
row3 I&J Fred
row4 mcdonalds Frank

These 3 records are shown at the same time (being a continous form)
CmboCompanyMember, as a form object, can only contain 1 rowsource, yet on our screen we have 3 instances of CmboCompanyMember.
This means that all 3 instances have the same rowsource.

Therefore when the rowsource contains all the members from Ceres
(select member.surname, member.memberID from member where member.companyID = 2)

Bob and Fred become invalid records for the combobox.
ie: the limit to list causes access to go "Hey, Bob and Fred aren't in the list for this combobox, what the hell are they
doing on this form"
What then happens is the comboboxes all show blank values (although the underlying bound value still remains).
As you click into a box, it shows its correct value (because the rowsource is updated).
If you click into the row1 box, both row1 and row4 show their values because the rowsource is valid for both McDonalds records.

NB: If you give the 1st column in CmboCompanyMember a width then this whole problem doesnt happen.
Unfortunately you are then stuck looking at member no 154 trying to work out who he is so this is not really any good.

At the moment when I go into my form, almost all of the combo boxes are showing blanks even though they have values.

Workaround: I have used a dlookup text box (looking up the guys name) next to a combobox which shows you the member_id.
This works but the form becomes incredibly slow. Especially if you have a few of these on the same form.
I can't use that as my solution because it is just too slow.

Help ?
Please ?

 

[Rich]

Post a 97ver

 

[camelman]

huh ?

I didst seeketh for many an hour and yet I didst not findest.
Could you pointeth me in the right direction before I killeth myself ?

ps: I am using Access 2002.

Camelman

 

[camelman]

attachment

My file which I attached with this post has only 1 form.
This form is a simple illustration of what I am trying to achieve.

 

[adam_fleck]

I think this might be a usable work around

It's a bit difficult getting this to work. One of the problems is that if you use a hidden ID column in a combo box, (Company_MemberID was hidden) then you're stuck with "Limit to List= Yes". Because of the hidden ID column (the bound column), "Limit to List" cannot be set to "No".

If you're in continuous forms or datasheet view, this means that when the combo box Rowsource is requeried, the Member Name will go blank for a lot of rows. All the lists requery at once, as they're all just clones. These clone combos will be blank in rows for companies other than the current company because "Limit to List" is set to "Yes", and so folks from other companies can't show up.

It's a nuisance. One way round, however, is to create a dummy field in the Sales table that will store the Member's name, and - well, see the zip file.

The basic idea is this: use the Member's Name as the bound column. Because this isn't a hidden column, "Limit to List" can be set to "No", so other rows won't mess up and go blank when this combo is requeried.

However, you've now got the problem of getting the Member_ID and writing this back to the underlying table whenever the value in the Member column is changed. This isn't too difficult, though.

You use a second (hidden column) to store this Id, and on AfterUpdate, you write the value of this hidden column into the Member_ID field.

It's not perfect, as there will be problems if two people from the same company turn out to have exactly the same name. This has to be disallowed in some way. And also, users will be able to type entries that are not in the list, but this can be trapped on the BeforeUpdate event.

The References you'd set in your code seemed to be inconsistent (with my settings at any rate) and were preventing combo requery events from running as they should. I took these references out, and put in a ref to DAO 3.6.

c2.mdb is an Access 2000 file.