We help IT Professionals succeed at work.

Access Load form name and capion to two separate ComboBoxes

Murray Brown
Murray Brown asked
on
Hi

I am using the following Access VBA code to load a list of forms to a Combobox
How do I load the form Captionto an additional ComboBox?


Sub oLoadFormsToCombo()

On Error GoTo EH

    Dim frm As Object
    Dim I As Integer
    
    For I = cmbFormsAndReports.ListCount - 1 To 0 Step -1
        Me.cmbFormsAndReports.RemoveItem (I)
    Next I
    
    Me.cmbFormsAndReports.SetFocus
    Me.cmbFormsAndReports.Text = ""
    Me.cmbFormsAndReports.AddItem ("")
    For Each frm In CurrentProject.AllForms
        Me.cmbFormsAndReports.AddItem (frm.Name)
    Next
    
Exit Sub
EH:
MsgBox "Error loading forms: " & Err.Description
    
End Sub

Open in new window

Comment
Watch Question

IT would maybe look something like this:
Sub oLoadFormsToCombo()

On Error GoTo EH

    Dim frm As Object
    Dim I As Integer
    
    For I = cmbFormsAndReports.ListCount - 1 To 0 Step -1
        Me.cmbFormsAndReports.RemoveItem (I)
    Next I
    
    Me.cmbFormsAndReports.SetFocus
    Me.cmbFormsAndReports.Text = ""
    Me.cmbFormsAndReports.AddItem ("")
    For Each frm In CurrentProject.AllForms
        Me.cmbFormsAndReports.AddItem (frm.Name)
       docmd.OpenForm frm.name,acDesign,,,,acHidden
       me.cmbFormsCaption.AddItem (forms(frm.name).Caption)
       docmd.close acForm,frm.name
    Next
    
Exit Sub
EH:
MsgBox "Error loading forms: " & Err.Description
    
End Sub

Open in new window

Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
thanks very much
You might need to exclude the active form, otherwise Access will try to switch that into design view. It could be something like this:
 if me.name<>frm.name then
       docmd.OpenForm frm.name,acDesign,,,,acHidden
       me.cmbFormsCaption.AddItem (forms(frm.name).Caption)
       docmd.close acForm,frm.name
 End If

Open in new window

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:

 But why would you want these in two separate combo boxes, instead of displaying both the formname and caption in the same combo with appropriate column widths so that both can be seen when dropped down, which is what I would do.  Then, if you need to actually see both after a form is selected from the combo, use the AfterUpdate event to populate a textbox to display the caption of the selected form.