Link to home
Start Free TrialLog in
Avatar of stephenlecomptejr
stephenlecomptejrFlag for United States of America

asked on

Microsoft Access 2003 VBA code to add lookup display control as combo box and define its rows source, columns, etc.

In Access 2003, I have a field called DeptCode already created and want the display control be converted from Text Box to Combo box.  Row Source to be:  SELECT PROJ_DPT.Dept_Code, PROJ_DPT.Dept_Name FROM PROJ_DPT

Column Count to be 2 - Column Widths to be 0.5";1"
and list width to be 4"

And I want to be able to do this using VBA code instead of manually doing it.
Thanks in advance!

Avatar of oldmanbim
Flag of United States of America image

Hi Stephen,

Normally speaking, modifying form design on the fly is not really ideal.  

That being said, you can't change a control type unless the form is opened in design mode.  This means that the form that contains your textbox (I'll call it ctrlDeptCode) that you want to change to a combobox can't actually do the changing.  I'll call the form that contains ctrlDeptCode "frmTarget".

You'll want to make another form (I'll call it "frmEditTarget") that will make the modifications you want and then close.  Form frmEditTarget would have only one method:

Private Sub Form_Open(Cancel As Integer)
    Dim ctrl As Control
    ' Open the form you want to edit
    DoCmd.OpenForm "frmTarget", acDesign, , , , acHidden

    ' Access the control to be changed
    Set ctrl = Forms!frmTarget.Form.ctrlDeptCode

    ctrl.ControlType = acComboBox
    ' After the control type is changed, it seems one has
    ' to save/close the form before modifying an combobox-specific
    ' fields, such as RowSource
    ' Save/Close the form
    DoCmd.Close acForm, "frmTarget", acSaveYes
    ' Open the form again
    DoCmd.OpenForm "frmTarget", acDesign, , , , acHidden
    Set ctrl = Forms!frmTarget.Form.ctrlDeptCode
    ctrl.RowSource = "SELECT PROJ_DPT.Dept_Code, PROJ_DPT.Dept_Name FROM PROJ_DPT"
    ctrl.ColumnCount = 2
    '--> Put any other code to modify ctrlDeptCode here

    ' Save Changes
    DoCmd.Close acForm, "frmTarget", acSaveYes

    ' Open form with edited control
    DoCmd.OpenForm "frmTarget"

    ' Set Cancel = true, so that this form doesn't actually open
    Cancel = True

End Sub

This may not be the most efficient code, but I think it will do what you want.  

Avatar of stephenlecomptejr


I apologize that I didn't specify that all I'm doing is modifying a table only - not a form.
All you need is to manipulate various DAO properties of your field. For convenience, I added two functions inspired by the examples you can find on the help page for the Properties collection.

Avatar of harfang
Flag of Switzerland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
No, my apologies, one (meaning me) shouldn't answer questions on too little sleep and too much caffeine.  

Harfang has a very nice solution and deserves full marks.

In the "for what it's worth department", when I tried his code, the line
    SetProperty fld, "ColumnWidths", "0.5"";1""", dbText
did not raise an error, but Access 2003 did not interpret the property correctly when I opened the table.  It should have, Harfang's code is correct.  This might be a peculiarity of my installation, but if you run into the same problem, the line below also works
    SetProperty fld, "ColumnWidths", 0.5 * 1440 & ";" & 1440, dbText

Yes, this is strange. It might have something to do with regional settings. Anyway, your solution is probably more portable. When creating the same settings through the interface (in table design view), and looking at them with ObjectProperty(), I find:

ColumnWidths: 720;1440   -- which is 0.5";1" but in twips
ListWidth: 5760twip   -- which is 4"

From the interface, I can even use "cm" as unit for both properties; it works only for ListWidth from VB (e.g. "3cm"). Whatever the unit used, it's converted like above.

So, twips seem to be the best choice. Thanks for the testing!

harfang to the rescue
Yea - using Harfang's code I got this exact error:

The ColumnWidths property setting must be a value from 0 through 22 inches (55.87 cm) for each column in a list box or a combo box.

If there is more than one column, separate the numbers with either a semicolon or the list separator character.  List separator characters are defined in the Regional Settings section of Windows Control Panel.
Harfang - if you don't mind I would like to split these points between the both of you?

Harfang deserves all the points.
Thanks (to both of you), and success with your project!
harfang, is there anyway I could provide some contact information to you personally.
I really need your advice and was wondering if you could at least send me an email.

How best could I do so without posting my email address here?
There is an e-mail address on my profile page. Feel free to contact me for any EE related matter, but I will not answer questions you could have asked here.