Lookup settings for a field in a table

[Guest]

I am creating a table based on user input for the fields, but one of the
fields I always need to have contains predefined sets of information decided
by the system. I wanted to change the Display Control to a combo box, set
the Row Source Type, Row Source, etc. programatically, because I was just
going to create a query for data input- eliminating the need to create a
form based on an unknown table from code.

I have all of the fields created and the tabledef appeneded to the db. I
was trying to change the value for the Display Control for one of the fields
in the table with the following line of code;

CurrentDb.TableDefs(tdfNew.Name).Fields("fldName").Attributes(DisplayControl) = "Combo Box"

I get a message saying that I can't assign to a read only property.

Is there any way to make this work from VBA?

Side Note: The other values - Row Source Type, Row Source, etc. do not
cause an error when they try to update, but obviously the Display Control
needs to be changed first. They are not available in design view until the
Display control is changed from Text Box.

Any advice appreciated.

 

[Marshall Barton]

I am creating a table based on user input for the fields, but one of the
fields I always need to have contains predefined sets of information decided
by the system. I wanted to change the Display Control to a combo box, set
the Row Source Type, Row Source, etc. programatically, because I was just
going to create a query for data input- eliminating the need to create a
form based on an unknown table from code.

I have all of the fields created and the tabledef appeneded to the db. I
was trying to change the value for the Display Control for one of the fields
in the table with the following line of code;

CurrentDb.TableDefs(tdfNew.Name).Fields("fldName").Attributes(DisplayControl) = "Combo Box"

I get a message saying that I can't assign to a read only property.

Is there any way to make this work from VBA?

Side Note: The other values - Row Source Type, Row Source, etc. do not
cause an error when they try to update, but obviously the Display Control
needs to be changed first. They are not available in design view until the
Display control is changed from Text Box.

Misleading message. Try this:

CurrentDb.TableDefs(tdfNew.Name).Fields("fldName").CreateProperty("DisplayControl",
dbInteger, acComboBox)

Set db = CurrentDb()
Set tdf = db.TableDefs(tdfNew.Name)
Set fld = tdf.Fields(fldName)
Set prp = fld.CreateProperty("DisplayControl", _
dbInteger,acCheckBox)
fld.Properties.Append prp

 

[Guest]

Thanks Marsh, that did it. I had actually tried something similar, but where
you had (dbInteger, acComboBox) I had tried (dbText, "Combo Box") which did
not work.

Thanks,
Michael

 

[Marshall Barton]

Just when we think we've got something under control, those
little details come along to trip us up ;-)

Glad to help,