Basing combo one combo box on another

[deanaldo]

Hi,

I have a sub-form on a form that I use 3 combo boxes to filter down choices for a certain field. I have the first combo box that shows 'Type of Source', the next combo box is 'Advertiser Name' and the final box shows 'Campaign '. So the user picks a Type of source, and then the advertiser box only shows advertisers that are the same type of source picked, then the user picks a campaign that has been assigned to that advertiser.



I am having problems with updating the combo boxes. On form current I have all three combo boxes re queried

ComboSource.RequeryComboAdvertiser.Requery
ComboCampaign.Requery

On form load I have the following

ComboSource = NullComboSource.Requery
ComboSource = ComboSource.ItemsSelected(0)
Call ComboAdvertiser_AfterUpdate

On the comboSource box I have the following event after update

ComboAdvertiser = NullComboAdvertiser.Requery
ComboAdvertiser = ComboAdvertiser.ItemData(0)


ComboCampaign = Null
ComboCampaign.Requery
ComboCampaign = ComboCampaign.ItemData(0)

On the comboadvertiser box, I have the following event after update

ComboCampagin.Requery
ComboCampaign = ComboCampaign.ItemData(0)

When I create a new entry and fill in all the data, the criteria works ok, but when I navigate to an old record, the combosource and comboadvertiser boxes stay the same and dont change, and when I go back to the record I created only the comboCampaign box shows the correct information, the other two are blank. Can someone help me please?

Thanks

[June7]

ComboCampaign in the last code snip is misspelled. Some of the lines are running into each other. I assume these are typos in the post.

Are combosource and comboadvertiser unbound?

Want to provide db for analysis? Follow instructions at bottom of my post.

[deanaldo]

ComboCampaign in the last code snip is misspelled. Some of the lines are running into each other. I assume these are typos in the post.

Are combosource and comboadvertiser unbound?

Want to provide db for analysis? Follow instructions at bottom of my post.

Ye that is a typo in the post.

ComboSource is unbound, ComboAdvertiser is unbound and ComboCampaign is bound to a field.

Unfortunately I can't send the DB over as it is full of confidential data, and also over 100MB.

Thanks for your reply.

[June7]

Probably wouldn't need the entire db. An extract of the objects relevent to this issue and a reduced dataset (without confidential data) might be enough to test with.

When I create a new entry and fill in all the data, the criteria works ok, but when I navigate to an old record, the combosource and comboadvertiser boxes stay the same and dont change, and when I go back to the record I created only the comboCampaign box shows the correct information, the other two are blank.

What behavior do you want?

Maybe in the Current event need to set the first two comboboxes to null.

I have never mixed bound/unbound dependent comboboxes. Usually I use dependent comboboxes just to input filter criteria and those comboboxes would all be unbound. One case I did use bound dependent comboboxes, both values had to be saved. It was a weird data relationship.

[deanaldo]

What behavior do you want?

I want the user to have to filter down which campaigns are shown by only showing those that match criteria in the first two boxes.

So the first box the user has is Type of Source(Newspaper, TV, Magazine, Internet etc.), then the next box will be Advertiser Company (Google, BBC, GQ, Financial Times etc.), but only the relevant advertisers will show for the type of source selected(for example, BBC will only show when TV has been selected as type of source, Google will only shown when internet has been selected as type of source etc.)

Finally the last box will only show campaigns that have been used with that Advertiser(usually referenced by date), so for example the advertiser selected is GQ. There may have been two adverts placed in GQ over a year, there would be two entries in this box showing the date(which is the unique identifier).

Now when a user naviagates to the next customer record, all the boxes should either reset if no information is present, or if the customer has previously had all this information entered, it should show it.

Do you understand a bit more about what I'm trying to achieve?

[June7]

Can use the form Current event to 'reset' the comboboxes. This event will execute when the form first opens and when navigating records. I would set the second and third comboboxes as disabled and value null. Then code in the first combobox AfterUpdate would enable and requery the second. Code in the second AfterUpdate would enable and requery the third.

[deanaldo]

I understand what your trying to say and can see that this would work for any new records created, but what about when you go back over old records to view information? Would it not just reset all comboboxes thus lose the data saved?

[June7]

Comboboxes used to enter criteria to filter records should be unbound and thus will not affect the data in table.

If you are using the comboboxes to aid in data entry (they are bound to a field), 'resetting' the RowSource of each combobox will not affect the data saved, just don't set the combobox Value by code.

[deanaldo]

Comboboxes used to enter criteria to filter records should be unbound and thus will not affect the data in table.

If you are using the comboboxes to aid in data entry (they are bound to a field), 'resetting' the RowSource of each combobox will not affect the data saved, just don't set the combobox Value by code.

The comboboxes are unbound at the moment. When I go back to a previous record, the comboboxes show the entrys from whatever values I last entered into them, I would like them to show the values for whatever the data value is, is that possible?

[June7]

If purpose of comboboxes is just to filter records, they should not be bound and I recommend code simply null the boxes after the filter action and use other boxes to display the field values.

If you want the combos to show the data in table the boxes must be either bound or use code to populate the box with value from the current record.

Me.comboboxname = Me!fieldname