combobox using table field descriptions as source 1

[sub5]

Hi all, does anyone know how to get a combobox to list the text from the "descriptions" column found in table design view?

 

[lupins46]

I think you would have to create & append the Description property to the field in the tabledef, and then you could access its value.

Can't imagine a use for this though - but that's just me I guess!

 

[sub5]

Hi lupins46
How would I do that?

What I'm trying to do:
A user wants to update a piece of data.
They don't know (or care) what table it is in.
It can be in one of two tables.
I have two forms, one per table for updating that table and associated other tables (different in each case).

I place two combo boxes on a third form (the user decision form) and two command buttons. The source for one combobox is the description info from table 1, and the source for the other is from the other table description info.

The user looks at each combobox, finds the data they want to change. They click the command button beneath that combo box and it takes them to the appropriate form.

Any alternate process suggestions would be gratefully received.

 

[lupins46]

I assume the user must know which record they want to update? And if they do know then why can't the user select this record and see all the fields on the form?

 

[sub5]

I don't want the combobox to list the records, I just want it to list the field descriptions ie what data fields are in that table.

The user doesn't need to see the record that they will update. I (the programming) just needs to know which field they want to update because that determines what happens next. (there are two different processes - one for each combobox)

 

[PHV]

Are you aware of solutions like QBF (Query By Form):

http://download.microsoft.com/download/access2000/Sample/1.0/WIN98Me/EN-US/Qbf2k.exe

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sub5]

hi, thanks for your input, but from my understand of QBF, I don't think one would help here. I am trying to list a Structure element of a table, nothing to do with the records in that table.

If you open a table in design view, it shows the columns "Field Name", "Data Type", "Description". It is a list of these "Descriptions" which I want to appear in a combobox.

 

[PHV]

So, have a look at the Properties collection of a DAO.Field object:
MsgBox CurrentDB.TableDefs("Table1").Fields(1).Properties("Description")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sub5]

Hi, that gives me the description for field 1, how do I make it return a list of all the fields in a format that I can use to populate a combobox?

 

[PHV]

You have to know how to travel through a collection in VBA.
Feel free to play with the F2 and F1 key when in VBE.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sub5]

Hi, I can get the a list to print in the debug window, using code:

Dim tdf As TableDef, db As Database, fld As Field

Set db = CurrentDb
Set tdf = db.TableDefs("T-ContractDetl")
For Each fld In tdf.fields
Debug.Print fld.Properties("Description") & ";"
Next fld

My problem is getting that list into a combobox without creating a table.
I have tried to find / use something like an additem method to create a list somewhere in VBA and then populate the row source property, but can't find a method that seems to work, any other recommendations on where to look?

 

[rubbernilly]

You've got what you need right there.

Public Sub RefreshRowSource()
dim tdf as dao.tabledef, db as dao.database, fld as dao.field
dim strSource as string

For Each fld in tdf.fields
   [red]strSource = strSource & fld.Properties("Description") & ";"[/red]
Next

strSource = left(strsource, len(strsource)-1)

me.combobox.rowsource = strsource

Your combo or listbox will have to have a row source type of "Value List". This should then give you what you want.

To make it even more robust, you could add two arguments to the code: one for the control whose RowSource you want to set, and one for the table from whom you want to take the Descriptions. Then you could call it for multiple different boxes.

HTH

 

[sub5]

Brilliant, that works perfect, thanks!