Programmatically creating text field with combobox

[Stephen J. Levine MD]

Using visual basic, I am trying to programmatically
create, in a table, a text field using a combobox for
lookup with the following properties:

Row Source Type = Value List
Row Source = "F;fixed;M;Mobile"
column count = 2
bound column = 1
column widths = 0.5;1.5
list width = true
limit to list = yes

I have tried to do this by setting the properties. They
appear to be set correctly by looking sequentially at all
properties for the field.
DisplayControl 111
Rowsourcetype ValueList
Rowsource F;Fixed;M;Mobile
ColumnCount 2
BoundColumn 1
ColumnWidths .5;1.5
LimitToList true
ListWidth 2.0
GUID ????????

However, when I go into the field in design mode, lookup
is still a text box, and when I change it to a combobox,
my other properties get changed as follows:

DisplayControl 111
Rowsourcetype Table/Query
ColumnCount 1
BoundColumn 1
LimitToList False
ListWidth 0twip
GUID ????????
ColumnWidth -1
ColumnOrder 0
ColumnHidden False
ColumnHeads False
ListRows 8
IMEMode 0
IMESentenceMode 3
UnicodeCompression False

How do I set the field in a table to be a combobox
programmatically with the properties I desire?

sjl

 

[John Vinson]

How do I set the field in a table to be a combobox
programmatically with the properties I desire?

Well... DON'T.

See http://www.mvps.org/access/lookupfields.htm for a critique of the
all-but-useless table Lookup field.

The *only* good thing table lookup fields do is to make it a couple of
mouseclicks easier to create a combo box on a report. One of the *bad*
things they do is encourage the use of table datasheets for routine
interaction with data. Just use Forms instead.

 

[Guest]

John

Read the article. Good points.

However, the reason I wanted to use the combobox in the
table was to constrain the field to a fixed set of values
(two to be exact) and document the constraint within the
table. I already have a form using an option group with
radio buttons to maintain the field, but I do not like to
depend on forms to enforce constraints.

In contemplating your reply, however, I realized that
there was another way to constrain the field. Thus I used
the ValidationRule property to define the constraint and
ValidationText property to document what the defined
values mean. And that worked in creating the field
programmatically.

Thanks

sjl

 

[John Vinson]

However, the reason I wanted to use the combobox in the
table was to constrain the field to a fixed set of values
(two to be exact) and document the constraint within the
table. I already have a form using an option group with
radio buttons to maintain the field, but I do not like to
depend on forms to enforce constraints.

The typical way to enforce constraints is via the Relationships
window: establish a relationship from your lookup table's Primary Key
to the foreign key in your main table, and select "Enforce Referential
Integrity". This does exactly the same constraint-setting as the
lookup field, more flexibly (you can't do Cascade Updates with a
lookup field, I don't think!), and without the downsides of the Lookup
Wizard.

 

[Stephen J. Levine]

John

You are not reading my posts closely enough.

I know about building relationships through the
relationships window. In this case, because there are
only two legal values for this field, I am trying to avoid
another table and just use a value list.

And if you are wondering how I can be so sure that there
will always only be two values for this field, while no
one can guarantee that, for the past 50 years, there have
only been two values for this data item.

In any case, the use of programmatic updating here is to
upgrade a production database and ensure the upgrade has
been done correctly. Obviously, the program that will be
used to do the upgrade will be validated and will provide
lots of output to establish what its correct behavior is.

Furthermore, prior to upgrading the production database, a
copy thereof will be made. All of the output of the
upgrade program will be examined after upgrading the
database, as well as examination of the database itself,
before releasing the upgraded database back into
production.

John, believe me, this is a much better way than having to
perform the several dozen individual steps manually to
upgrade the production database and then verify manually
that each and every step has been done correctly.

sjl

 

[Marshall Barton]

Using visual basic, I am trying to programmatically
create, in a table, a text field using a combobox for
lookup with the following properties:

Row Source Type = Value List
Row Source = "F;fixed;M;Mobile"
column count = 2
bound column = 1
column widths = 0.5;1.5
list width = true
limit to list = yes

I have tried to do this by setting the properties. They
appear to be set correctly by looking sequentially at all
properties for the field.
DisplayControl 111
Rowsourcetype ValueList
Rowsource F;Fixed;M;Mobile
ColumnCount 2
BoundColumn 1
ColumnWidths .5;1.5
LimitToList true
ListWidth 2.0
GUID ????????

However, when I go into the field in design mode, lookup
is still a text box, and when I change it to a combobox,
my other properties get changed as follows:

DisplayControl 111
Rowsourcetype Table/Query
ColumnCount 1
BoundColumn 1
LimitToList False
ListWidth 0twip
GUID ????????
ColumnWidth -1
ColumnOrder 0
ColumnHidden False
ColumnHeads False
ListRows 8
IMEMode 0
IMESentenceMode 3
UnicodeCompression False

How do I set the field in a table to be a combobox
programmatically with the properties I desire?

It might have helped if you had posted the code that you're
using to do this, but I suspect that the issue is not in the
code,

I don't know what the problem is, but here's an off the wall
thought. Are you testing your code with the table's design
view open on the screen? If so, it won't reflect the
changes until you close the table and reopen it. In other
words, your code is probably working just fine and it's the
way you're testing it that's confusing the issue.

 

[Stephen J Levine]

I believe the database is open in design view the whole time because the code is run from within a VBA script within that database. However, it is another script within the database that is reading the values after they are set, both before and after opening the table and looking at the field. So I don't think it is that the database is not immediately reflective of the changes

While I have found an alternate solution, which I posted, and that was to use the ValidationRules property, nevertheless, I would be interested in other peoples' experience

sj

----- Marshall Barton wrote: ----

Stephen J. Levine MD wrote

Using visual basic, I am trying to programmatically
create, in a table, a text field using a combobox for
lookup with the following properties
Row Source = "F;fixed;M;Mobile
column count =
bound column =
column widths = 0.5;1.
list width = tru
limit to list = ye
appear to be set correctly by looking sequentially at all
properties for the field
DisplayControl 11
Rowsourcetype ValueLis
Rowsource F;Fixed;M;Mobil
ColumnCount
BoundColumn
ColumnWidths .5;1.
LimitToList tru
ListWidth 2.
GUID ???????
is still a text box, and when I change it to a combobox,
my other properties get changed as follows
Rowsourcetype Table/Quer
ColumnCount 1
BoundColumn 1
LimitToList Fals
ListWidth 0twi
GUID ???????
ColumnWidth -1
ColumnOrder 0
ColumnHidden Fals
ColumnHeads Fals
ListRows 8
IMEMode 0
IMESentenceMode 3
UnicodeCompression Fals
programmatically with the properties I desire

It might have helped if you had posted the code that you'r
using to do this, but I suspect that the issue is not in th
code

I don't know what the problem is, but here's an off the wal
thought. Are you testing your code with the table's desig
view open on the screen? If so, it won't reflect th
changes until you close the table and reopen it. In othe
words, your code is probably working just fine and it's th
way you're testing it that's confusing the issue

 

[Marshall Barton]

I believe the database is open in design view the whole time because the code is run from within a VBA script within that database. However, it is another script within the database that is reading the values after they are set, both before and after opening the table and looking at the field. So I don't think it is that the database is not immediately reflective of the changes.

While I have found an alternate solution, which I posted, and that was to use the ValidationRules property, nevertheless, I would be interested in other peoples' experience.

Maybe the problem is in your code? I can't reproduce the
behavior you're seeing, even when I set RowSourceType to the
invalid string "ValueList" (without the space between the
two words as you have in your post).

I will reiterate though, the table's design screen should be
closed when you make the change and then reopened after the
change or you will not see the new settings.

Here's the code I used (A97):
Public Sub ChangeFieldDisplay(TableName As String, _
FieldName As String)
SetFieldProp TableName, FieldName, _
"DisplayControl", dbInteger, acComboBox
SetFieldProp TableName, FieldName, _
"RowSourceType", dbText, "Value List"
SetFieldProp TableName, FieldName, _
"RowSource", dbMemo, "F;Fixed;M;Mobile"
SetFieldProp TableName, FieldName, _
"BoundColumn", dbInteger, 1
SetFieldProp TableName, FieldName, _
"ColumnCount", dbInteger, 2
SetFieldProp TableName, FieldName, _
"ColumnHeads", dbBoolean, False
SetFieldProp TableName, FieldName, _
"ColumnWidths", dbText, "0;1440"
SetFieldProp TableName, FieldName, _
"ListRows", dbInteger, 2
SetFieldPropTableName, FieldName, _
"ListWidth", dbText, "1800twip"
End Sub

Sub SetFieldProp(TableName As String, FieldName As String, _
PropName As String, PropType As Integer,
PropValue As Variant)
Dim db As Database
Dim tdf As TableDef
Dim fld As Field

On Error GoTo ErrHandler
Set db = CurrentDb()
Set tdf = db.TableDefs(TableName)
Set fld = tdf.Fields(FieldName)
fld.Properties(PropName) = PropValue

ExitHere:
fld.Properties.Refresh
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
Exit Sub

ErrHandler:
Select Case Err.number
Case 3270
fld.Properties.Append fld.CreateProperty(PropName,
PropType, PropValue)
Case Else
MsgBox Err.number & " - " & Err.Description
End Select
End Sub

Stephen J. Levine MD wrote:
[]

I have tried to do this by setting the properties. They

appear to be set correctly by looking sequentially at all
properties for the field.
Rowsourcetype ValueList []

How do I set the field in a table to be a combobox

programmatically with the properties I desire?

----- Marshall Barton wrote: -----
It might have helped if you had posted the code that you're
using to do this, but I suspect that the issue is not in the
code,

I don't know what the problem is, but here's an off the wall
thought. Are you testing your code with the table's design
view open on the screen? If so, it won't reflect the
changes until you close the table and reopen it. In other
words, your code is probably working just fine and it's the
way you're testing it that's confusing the issue.