Adding items to list control (VBA)


I’m trying to create a method by which a user can make a selection from a combo box and (containing two columns).

Upon hitting a command button I would like the selected values of columns 1 & 2 to be added to list control (columns 1 & 4).  I would then like to remove the entry from the combo box?

Is this possible?

Who is Participating?
In your case, something like:

lstBox01.AddItem me.cboData.column(0) & "; ; ;" & me.cboData.column(1)

Dropping rows from the combo box is very tricky when the listbox does not have a table rowsource.  (see various recommendations above).  But, you might do someting like this:  Iterate over the listbox rows and build an SQL String. lke this:

  sql = "select top 1 12345 from yourtable union select top 1 54321 from your table"

In this example, the listbox has two rows and 12345/54321 are the values from the id column of those rows

 Then change the combobox rowsource like this;

"Select id, col2 from yourtable
where id not in ("  & sql & ")"

What are the name and rowsource of the combo box?
What are the name and rowsource of the list control?

Do you really want a command button, isn't selecting from the combobox sufficient?
How are you filling the combo box?  Is it based on a table or are you adding items in code or what?

What goes in columns 2 & 3 of the list control? Are there columns after column 4?

Are you talking about a  (non_Access)  LIST control or a (Access)  LISTBOX control?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

andyw27Author Commented:
Its a listbox control (Access Form).

The combo box is populated by setting the rowsource property (Via SQL in the code)

The other columns will be populated by another combo box.

Using the command button is the prefered choice.
Jeffrey CoachmanMIS LiasonCommented:
<The combo box is populated by setting the rowsource property (Via SQL in the code)>
Setting the Rowsource property to What?
Make the rowsource of the listbox a table.  Add/Remove rows from the listbox using insert/delete on the table.  In the rowsource of the combo box add a condition "where not exists (select whatever from listboxtable)" .  Requery both combobox and listbox after making changes to the listbox table.

Jeffrey CoachmanMIS LiasonCommented:

Yes, perhaps you should first take a step back and explain what this system is seeking to accomplish and also explain the "Why" behind this system, case there is a more efficient way of doing this...
Please provide a *Specific* scenario as an example.

A listbox typically displays values from a table or a query.
You don't typically "Add" Items to a listbox control (via a combobox), you typically add items to the Listbox's RowSource.

It is not clear from your post what the Real goal is here.

Are you looking to add values to the listbox, or add an underlying record to the listbox rowsource.

andyw27Author Commented:
The combo control displays a distinct list from a table.  I need the list box to be populated so that I can reuse another portion of code.

This second portion essentially loops through each row contained in the listbox control.

It looks as if the best way is as follows:

Each time a selection is made the SQL statement for the combo box WHERE clause could be amended so that it essentially removes the selected choice.

I had some success in adding items to listbox control with:

lstBox01.AddItem "text1 ; ; text2 ; "

However I cannot figure out the syntax to use variables within this.  For example this does not work:

lstBox01.AddItem me.cboData.value ; ; text2 ; "
Jeffrey CoachmanMIS LiasonCommented:
Not quite sure what "Scenario" your above post explains...?,
...but If it were me, I would add/remove Items from the underlying table(s)...

(via update queries keying off of an "IsSelected", yes/no field)

Here is what I have used without issue for years...

The easy part of the puzzle, try this:

lstBox01.AddItem me.cboData.value & "; ; ;" & me.text2

For removing the row from the combo box, please give my the exact syntax of the rowsource.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.