CODEWITHSUNDEEP
ms-accesscomboboxvba
leeand00
leeand00

Reputation: 26392

Setting the rowsource property of a combobox in another database?

I've looked all over the place for this and I can't find it; I looked in ADO, and DAO.

Is there a way using VBA to edit the row source property of a combo box on a form in another .mdb file? I have to make this change on a bunch of .mdb files and I'd rather use a script to do it, than to do it one at a time.

Upvotes: 2

Views: 922

Answers (1)

HansUp
HansUp

Reputation: 97101

Use COM automation to create an Access application session where you open a database, open the target form in design mode, and then alter the target combo box's Row Source.

Public Sub AlterComboRowSource(ByVal pDb As String, _
        ByVal pForm As String, _
        ByVal pCombo As String, _
        ByVal pRowSource As String, _
        Optional ByVal pEdit As Boolean = False)

    Dim objAccess As Access.Application
    Dim frm As Form
    Dim cbo As ComboBox

    Set objAccess = New Access.Application
    objAccess.Visible = True '<- useful while debugging
    objAccess.OpenCurrentDatabase pDb, True
    objAccess.DoCmd.OpenForm pForm, acDesign
    Set frm = objAccess.Forms(pForm)
    Set cbo = frm.Controls(pCombo)
    Debug.Print cbo.RowSource

    If pEdit = True Then
        cbo.RowSource = pRowSource
        objAccess.DoCmd.Close acForm, pForm, acSaveYes
    End If
    objAccess.Quit acQuitSaveNone
End Sub

Here is my test procedure, which demonstrates how to use the first procedure:

Public Sub test_AlterComboRowSource()
    Const cstrCombo As String = "cmbEmployeeName"
    Const cstrForm As String = "frmLogin"
    Dim strDbPath As String
    Dim strSelect As String

    strDbPath = "C:\Users\hans\Documents\Access\Scratch.accdb"
    strSelect = "SELECT e.EmployeeID, e.FirstName & ' ' & e.LastName AS [Employee Name] " & _
        "FROM tblEmployees AS e " & _
        "WHERE e.Inactive=False ORDER BY 2;"

    AlterComboRowSource strDbPath, cstrForm, cstrCombo, strSelect, True
End Sub

Those procedures worked as intended when I tested them in Access 2010.

It should be more efficient to do Set objAccess = New Access.Application just once, and then re-use objAccess, opening each of your databases, altering the combo Row Source, and then closing the database.

However since this may be a one-off situation, perhaps you don't care much about execution speed.

Upvotes: 1

Related Questions