Adding items to list control (VBA)

Posted on 2011-05-11
Medium Priority
Last Modified: 2012-06-27

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?

Question by:andyw27
  • 4
  • 3
  • 2
  • +1
LVL 42

Expert Comment

ID: 35739288
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?
LVL 77

Expert Comment

ID: 35739340
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?

Author Comment

ID: 35739881
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.
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35739926
<The combo box is populated by setting the rowsource property (Via SQL in the code)>
Setting the Rowsource property to What?
LVL 42

Expert Comment

ID: 35740496
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.

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35741744

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, ...in 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.


Author Comment

ID: 35744513
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 ; "
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35746865
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...

LVL 42

Expert Comment

ID: 35748186
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.
LVL 42

Accepted Solution

dqmq earned 2000 total points
ID: 35748888
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 & ")"


Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question