Get list of values from a field that uses a lookup combobox 1

[RSfromCO]

If a field in MS-Access, has a lookup defined which is a combo-box which uses a Value List... Is it possible to obtain the list of available choices for that field programatically?

The list has to be stored somewhere... but I don't see anyway to access it. I thought maybe it could be accessed through the TableDef object but that doesn't apear to be there.

The reason, I want to have access to the value list is to output BOTH the numeric value (stored in the table) and the text assigned to that value from the value list...

So if the Value List is defined something like...
1;Item One; 2;Item Two; 3;Item Three

I would like to be able to output a query result that includes both the number and the text...
1 Item One
3 Item Three
1 Item One
5 (item not found in Value List)
2 Item Two
... etc.


Please save replies about proper database design and using related tables for defining lookup values instead of using this lookup/combobox feature in the table design. I already know thats how it should have been designed. I am just trying to utilize this database structure that someone else designed 10 years ago. The situation doesn't warrant re-design of the existing data model.

 

[TheAceMan1]

How are ya RSfromCO . . .

[blue]The list has to be stored somewhere... but I don't see anyway to access it.[/blue]

The listing is stored in the [blue]RowSource[/blue] of the control. It all depends on wether the source of the combobox
is a [blue]table or form[/blue].
[ol][li]If the source is a form:

[blue]Me![purple][b][i]TextboxName[/i][/b][/purple] = Me![purple][b][i]ComboboxName[/i][/b][/purple].RowSource[/blue]

[/li]
[li]If the source is a table:

[blue]Me!TextboxName = CurrentDb.TableDefs("[purple][b][i]TableName[/i][/b][/purple]") _
                          .Fields("[purple][b][i]FieldName[/i][/b][/purple]") _
                          .Properties("RowSource")[/blue]

[/li][/ol]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[CautionMP]

RSfromCO,
If you were to do it in code (like I assume you would a [tt]TableDef[/tt]) you could use something like this. It will iterate through all the forms in your database and pick out any ComboBox controls that have the [tt]RowSourceType[/tt] set to "[tt]Value List[/tt]" and output the some useful information to the Immediate window in the VBE.

Sub Discover_Combo_Source()
Dim aobForm As AccessObject
Dim ctlCombo As Control
For Each aobForm In CurrentProject.AllForms
  DoCmd.OpenForm aobForm.Name, acDesign
  For Each ctlCombo In Forms(aobForm.Name)
    If ctlCombo.ControlType = acComboBox Then
      If ctlCombo.RowSourceType = "Value List" Then
        Debug.Print aobForm.Name & "." & ctlCombo.Name, ctlCombo.BoundColumn & "(" & ctlCombo.ColumnCount & ")", ctlCombo.RowSource
      End If
    End If
  Next ctlCombo
  DoCmd.Close acForm, aobForm.Name, acSaveNo
Next aobForm
End Sub

I would like to be able to output a query result that includes both the number and the text...

Since I can't visualize how your going to do this I hope the snipet above will get your moving in the right direction.

As to replies about database design: The code above could be used to create a completely 'normalized' database structure by creating a couple of tables, populating them, then changing the [tt]RowSourceType[/tt] and [tt]RowSource[/tt] of all the [tt]ComboBox[/tt]es in a minute or two with one button click. But remember, just because it can be done doesn't mean it's a good idea.

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[RSfromCO]

Thanks TheAceMan1. I didn't realize that a table.field had a property called RowSource. I thought that was just a form control property. Your example for the table was right on as no forms are involved here.

 

[PHV]

Have a look here:

http://www.mvps.org/access/lookupfields.htm

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

[blue] I didn't realize that a table.field had a property called RowSource. [/blue]

A table field setup as a [blue]lookup combobox[/blue] does!

In table design view when adding a new field, if under the [blue]Data Type[/blue] column you select [blue]Lookup Wizard[/blue] . . . you'll make a combobox. All comboboxes have a [blue]RowSource![/blue] which prescribes what appears in their listing!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884: