Dynamic list... tougher than it sounds

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

I've been bashing my brain all day with this one. Here's what I'm trying to
do.

I have a fixed number of combo boxes on a form. Each combo box row source is
determined by the value of another combo box (table name). These are used to
allow users to match data from imported excel worksheets to a standard data
structure. (The whys and wherefores are a very long story...)

I would like to have a list box as a visual cue to users displaying the
selected tables field names "excluding" those already selected in the combo
boxes, updated as each combo box is modified.

There doesn't seem to be a simple way to do this

I'm guessing I'll have to do something along the lines of creating a table
of Table names / field names and then run a query on the resulting data as a
row source for the listbox updated every time one of the combos is
updated/changed. or is it possible to achieve this without creating a table?

Can someone point me in the right direction code wise to accomplish this?

Regards

Richard Douthwaite
 
"Richard Douthwaite" <Richard (e-mail address removed)>
wrote in message
Hi all,

I've been bashing my brain all day with this one. Here's what I'm
trying to do.

I have a fixed number of combo boxes on a form. Each combo box row
source is determined by the value of another combo box (table name).
These are used to allow users to match data from imported excel
worksheets to a standard data structure. (The whys and wherefores are
a very long story...)

I would like to have a list box as a visual cue to users displaying
the selected tables field names "excluding" those already selected in
the combo boxes, updated as each combo box is modified.

There doesn't seem to be a simple way to do this

I'm guessing I'll have to do something along the lines of creating a
table of Table names / field names and then run a query on the
resulting data as a row source for the listbox updated every time one
of the combos is updated/changed. or is it possible to achieve this
without creating a table?

Can someone point me in the right direction code wise to accomplish
this?
Click to expand...

I haven't tried it, but it sounds as though you might use a list box
with a "Value List" RowSourceType, using its AddItem method to initially
add all the field names for the selected table, and then using its
RemoveItem method to remove those field names one by one as they are
chosen in other combo boxes. There's a maximum size to a list box's
RowSource property, but I'm thinking it may be big enough for this
purpose.

You can easily get the list of fields in a table from the DAO TableDef
object that represents it:

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb
Set tdf = db.TableDefs(Me!cboTableName)

For Each fld In tdf.Fields
Me!lstFields.AddItem fld.Name
Next fld

Set tdf = Nothing
Set db = Nothing
 
Dirk Goldgar said:
"Richard Douthwaite" <Richard (e-mail address removed)>
wrote in message


I haven't tried it, but it sounds as though you might use a list box
with a "Value List" RowSourceType, using its AddItem method to initially
add all the field names for the selected table, and then using its
RemoveItem method to remove those field names one by one as they are
chosen in other combo boxes. There's a maximum size to a list box's
RowSource property, but I'm thinking it may be big enough for this
purpose.

You can easily get the list of fields in a table from the DAO TableDef
object that represents it:

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb
Set tdf = db.TableDefs(Me!cboTableName)

For Each fld In tdf.Fields
Me!lstFields.AddItem fld.Name
Next fld

Set tdf = Nothing
Set db = Nothing

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...

Dirk,

Thanks for the suggestion, I've had a go at it but I get a Run-time error
'438'

Object doesn't support this propery or method.

Thanks anyway
 
"Richard Douthwaite" <[email protected]>
wrote in message
Thanks for the suggestion, I've had a go at it but I get a Run-time
error '438'

Object doesn't support this propery or method.
Click to expand...

What version of Access are you using? I believe the AddItem and
RemoveItem methods were added to the ListBox control in Access 2002. If
you're using an earlier version, you can use your own code to build a
value list as a string and assign it to the list box. I think I'd
probably create a collection or array of field names, so that I could
easily add or remove items from the collection, and then have code to
build a value list string from the collection and assign that to the
list box's rowsource.
 
Back
Top