troubleshooting Question

Using VBA to Build a Row Source for an Access field

Avatar of PeterFrb
PeterFrbFlag for United States of America asked on
Visual Basic ClassicMicrosoft Access
3 Comments1 Solution3744 ViewsLast Modified:
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

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

Open in new window

ChangeDisplayControl.bmp
ASKER CERTIFIED SOLUTION
Jez Walters
Business Data Analyst

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros