Using VBA to Build a Row Source for an Access field
I would like to use VBA to automate the process of creating a lookup field in Access. When I do this manually, I go to the "Lookup" tab within the field, change the Display Control to a Combo box, and write an SQL statement into the Row Source to have it get its values from the lookup table.
I'm attempting to reproduce these steps as a VBA sequence. I start by appending a Long Integer field to the table in which I'm interested. Then I set the "DisplayControl" property to 111 (a combo box) and the "RowSourceType" to "Table/Query". When doing this manually, setting the Display Control to "Combo Box" adds "RowSource", "ColumnCount", and '"ColumnWidths" to the available list of properties, which are relevant only when the display control is a Combo Box. Similarly, when I manually reset the Display Control to a combobox, these additional properties are available for me to set in VBA code. However, resetting the "DisplayControl" property in VBA does not automatically create the added properties I need to perform the transformation as a purely programmatic process. I've added a screen shot of the added properties available to the field when I manually change the Display Control.
I would appreciate some help in figuring this out.
Sincerely, ~Peter Ferber
I'm attempting to reproduce these steps as a VBA sequence. I start by appending a Long Integer field to the table in which I'm interested. Then I set the "DisplayControl" property to 111 (a combo box) and the "RowSourceType" to "Table/Query". When doing this manually, setting the Display Control to "Combo Box" adds "RowSource", "ColumnCount", and '"ColumnWidths" to the available list of properties, which are relevant only when the display control is a Combo Box. Similarly, when I manually reset the Display Control to a combobox, these additional properties are available for me to set in VBA code. However, resetting the "DisplayControl" property in VBA does not automatically create the added properties I need to perform the transformation as a purely programmatic process. I've added a screen shot of the added properties available to the field when I manually change the Display Control.
I would appreciate some help in figuring this out.
Sincerely, ~Peter Ferber
Sub CreateField(strTable As String, strField As String, strsql As String)
Dim MyDB As Database
Dim MyTbl As TableDef
Dim MyField As DAO.Field
Set MyDB = CurrentDb
Set MyTbl = MyDB.TableDefs(strTable)
Set MyField = MyTbl.CreateField(strField, dbLong)
MyTbl.Fields.Append MyField
MyField.Properties("DisplayControl") = 111 'The "111" value converts to a combo box
MyField.Properties("RowSourceType") = "Table/Query"
'The properties below don't exist until the Display Control is changed to a combo box. But the added properties "RowSource", "ComolumCount" and
'"ColumnWidths" do not magically appear after I've changed DisplayControl to "111".
MyField.Properties("RowSource") = strsql
MyField.Properties("ColumnCount") = 2
MyField.Properties("ColumnWidths") = "0;1440"
End Sub
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
This is yet another reason why most Access developers do not set Conmbobox controltypes in tables, let alone try to change the controltype "On-the-fly", via VBA at runtime.
See here:
http://access.mvps.org/access/lookupfields.htm
Creating a combobox on the "form" is fine though.
Creating/Modifying Controls at runtime decompiles your application, so you have to have to be very disciplined in compiling your code, saving your objects and running the compact/repair utility...
This is probably the reason for your issue here.
At runtime, the change is not "Logged" into the DB until the internal structure is refreshed,
...Either by saving the object, or running the Compact/Repair utility, or compiling the code.
So it is best to do all three to be on the safe side.
Can you state the "Why" behind this approach, ...in case there is a more standardized way of achieving this?
JeffCoachman
See here:
http://access.mvps.org/access/lookupfields.htm
Creating a combobox on the "form" is fine though.
Creating/Modifying Controls at runtime decompiles your application, so you have to have to be very disciplined in compiling your code, saving your objects and running the compact/repair utility...
This is probably the reason for your issue here.
At runtime, the change is not "Logged" into the DB until the internal structure is refreshed,
...Either by saving the object, or running the Compact/Repair utility, or compiling the code.
So it is best to do all three to be on the safe side.
Can you state the "Why" behind this approach, ...in case there is a more standardized way of achieving this?
JeffCoachman
Here's how to do what you're after:
Public Sub CreateField(pstrTable As String, _
pstrField As String, _
pstrRowSource As String)
Dim dbsDatabase As DAO.Database
Dim fldField As DAO.Field
Dim prpProperty As DAO.Property
Dim tdfTableDef As DAO.TableDef
Set dbsDatabase = CurrentDb
Set tdfTableDef = dbsDatabase.TableDefs(pstrTable)
Set fldField = tdfTableDef.CreateField(pstrField, dbLong)
tdfTableDef.Fields.Append fldField
Set prpProperty = fldField.CreateProperty("DisplayControl", dbInteger, acComboBox)
fldField.Properties.Append prpProperty
Set prpProperty = fldField.CreateProperty("RowSourceType", dbText, "Table/Query")
fldField.Properties.Append prpProperty
Set prpProperty = fldField.CreateProperty("RowSource", dbText, pstrRowSource)
fldField.Properties.Append prpProperty
Set prpProperty = fldField.CreateProperty("ColumnCount", dbInteger, 2)
fldField.Properties.Append prpProperty
Set prpProperty = fldField.CreateProperty("ColumnWidths", dbText, "0;1440")
fldField.Properties.Append prpProperty
End Sub
Premium Content
You need an Expert Office subscription to comment.Start Free Trial