Link to home
Create AccountLog in
Avatar of Murray Brown
Murray BrownFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Access Load form name and capion to two separate ComboBoxes

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

ASKER CERTIFIED SOLUTION
Avatar of Anders Ebro (Microsoft MVP)
Anders Ebro (Microsoft MVP)
Flag of Denmark image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Murray Brown

ASKER

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

 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.