Access VBA use list box in form to update combo box

Murray Brown
Murray Brown used Ask the Experts™
on
Hi

I am using the following code to open a form with a listbox on it. I want the ListBox to contain
the same data as a ComboBox that was next to the button. When the user selects an item from the listbox I want the form to close and the combobox to have that item selected

Private Sub btnManufacturer_Click()

    Dim frm As Form

    DoCmd.OpenForm "f_List", acNormal
    Set frm = Forms("f_List")

 
End Sub
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Neil FlemingConsultant and developer

Commented:
You may have to supply a sample database, or screenshots.. this is not very clear.
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Hi Neil. The image shows the ComboBox called Manufacturer_Combo. When the user clicks the button to the right it opens a form called f_List
shown to the right. I want to populate the ListBox called List1 on that form with what is in Manufacturer_Combo. When the user then selects an item from the LIst1 listbox it closes the formand puts the selected text into Manufacturer_Combo

1
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
Why no simply copy the RowSource?
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Senior Developer
Commented:
E.g.

The calling form:

Private Sub btnShowListBoxForm_Click()

  FormOpen "ListBoxForm", , , , , acDialog, Me.Form.Name & ";" & cboYourComboBox.Name

End Sub

Open in new window

The ListBox form:

Option Compare Database
Option Explicit

Private m_Params() As String

Private Sub btnSave_Click()

  Forms(m_Params(0)).Form.Controls(m_Params(1)).Value = lstDestinationListBox.Value
  FormClose Me.Name

End Sub

Private Sub CopyComboBoxSettings(ByVal CSourceComboBox As Access.ComboBox)

  lstDestinationListBox.RowSourceType = CSourceComboBox.RowSourceType
  lstDestinationListBox.RowSource = CSourceComboBox.RowSource
  lstDestinationListBox.ColumnCount = CSourceComboBox.ColumnCount
  lstDestinationListBox.ColumnWidths = CSourceComboBox.ColumnWidths

End Sub

Private Sub Form_Open(Cancel As Integer)
   
  m_Params() = Split(Me.OpenArgs, ";")

  Cancel = -1
  If FormIsLoaded(m_Params(0)) Then
    CopyComboBoxSettings Forms(m_Params(0)).Form.Controls(m_Params(1))
    Cancel = 0
  End If

End Sub

Open in new window

Needs some error handling and the case when no value is selected in ListBox.

p.s. FormOpen/Close are my DoCmd wrappers.
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
I don't understand what   m_Params() = Split(Me.OpenArgs, ";") does and this results in a sub or function not defined error
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Daniel thanks. What code would I use to copy the row source. It looks like Ste5an is doing something like that
ste5anSenior Developer
Commented:
I don't understand what   m_Params() = Split(Me.OpenArgs, ";") does and this results in a sub or function not defined error
Me.OpenArgs is a string of "FormName;ComboBoxName" and Split() does what it says.

Did you replace FormOpen and FormClose with the corresponding DoCmd calls? An did you implement a FormIsLoaded() method?
EE29165525.accdb
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Thanks very much

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial