Using combo boxes with settings table

[vicissitude]

Hi all,

Is it possible to have 2 combo boxes on the one form having the same table field control source but be looking at different rows?

I am using the combo boxes to look up setting value in a 'settings' table which has a fields like this:

SettingID         SettingName          SettingValue
      0               Name 1                  1
      1               Name 2                  20    
      3               Name 3                  2

etc.

Thanks.

 

[John Big Booty]

If both combo boxes are bound to the same control source, a change in one combo will be reflect in the other combo box.

You could have two combos bound to different control sources but reading from the same table. You could set these up as a variation on the cascading combo box theme, but in this case the contents of the second combo exclude the selection of the first combo box.

 

[vicissitude]

That is the bit i don't understand. How to have 2 combo boxes bound to different control sources but still accessing the one field. Are you not bound by what record source the form is set to?

The only way i can work out how to get round this is to put each combo box in a different subform so that i can set the record source independently for each combo.

Or something similar to what you are suggesting with the cascading combo box theme but using multiple option buttons to select desired row and just using one combo box. This sounds like the wisest solution don't you think?

 

[John Big Booty]

Have a look at the attached sample. The major difference between this one and the one I linked to in my previous post, is that both queries are link fed by the same table. Check out the query and the form's On Current event and combo1's On Change event.

 

[vicissitude]

Thanks for that but i am not sure i am meaning the same thing. I think i am wanting to do the impossible.

I am wanting to have multiple combo boxes on a form and have them bound to different records/rows on the same table. The combo boxes have simple row source value lists.

As far as i can understand your db has multiple combo boxes looking up different tables (Row source) but all bound to the same table record(control source).

 

[John Big Booty]

There seems to be a misunderstand somewhere in here.

In my DB the two combos are bound to separate fields (Control Source) in the same table, and they draw their data from the same table (Row Source). The second combo's Row Source is filtered so that the same selection can not be made in both combos.

You can not have two combos bound to the same Control Source and hope to have them show different results. It just won't work. You will have to store their data in separate fields (Control Sources).

 

[vicissitude]

Yes I think i understand you now....

It is not possible to have multiple combos set to the same control source and expect to see different rows on the one form.

So the only solution is to have the settings table have different settings fields to accommodate the combos

I ended up with a settings table with SettingValueInteger1, SettingValueInteger2, SettingValueBoolean1, SettingValueBoolean2, SettingValueString1 etc like you had on your db.

I had wanted to keep it clean and have the flexibility of the settings table but I wanted to be able to have a 'settingName' field corresponding to the 'settingValue' so it would be easy to see what that value represented in the table. Instead of just SettingValueInteger1 for example (Hope this makes sense)

I suppose I could put in a name field after each settingValue to describe it.

 

[John Big Booty]

You could easily reflect the setting name in the field's label. If for example as part of the Row Source for the combo contains the setting name you could use some code like;

Me.LabelName.Caption = Me.ComboName.Column([B][COLOR="Red"]X[/COLOR][/B])

Where X is the column number containing the setting name. Remember that the columns in a combo are zero rate, that is they are numbered from zero up.

 

[vicissitude]

Will have a look into that, thanks again for your help.

 

[kesmith]

Have a look at the attached sample. The major difference between this one and the one I linked to in my previous post, is that both queries are link fed by the same table. Check out the query and the form's On Current event and combo1's On Change event.

The db2.mdb example you provided is exactly the type of cascading combo box problem I am trying to deal with, except I need to take it two more times (ie. two more combo boxes that use the same source list but do not allow the user to reselect previously selected items.) I have tried to do it with the db2 sample but cannot achieve the desired result. Can you expand the sample database to include at least one more combo box selection for Setting Three (and maybe Four).