By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,921 Members | 1,305 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,921 IT Pros & Developers. It's quick & easy.

Removing items from Combo boxes

P: n/a
I would like to be able to utilise a combo box where once an item has
been selected then that item no longer appears in that list. Is there
some code anyone has which will achieve this?

If I select an item in to the subform I need to be able to reinstate it
in the combo box to use again.

Hey, am I asking too much?

TIA - Ray

Jan 3 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
You will need some understanding of VBA and SQL to achieve this.
In essence, you want to create a query statement that chooses the unused
records, and assign that as the RowSource of the combo.

To help you figure out what the SQL statement should look like, create a new
query an in the first dialog choose the Unmatched Query wizard. Once it has
created the query that chooses the records from the lookup table that are
not found in the subform's table, switch that query to SQL View (View menu.)
That's an example of the string you need to create.

It is then a matter of using that string like this:
Dim strSql As String
strSql = "SELECT ...
Me.[Combo1].RowSource = strSql

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Chesne" <as******@paradise.net.nzwrote in message
news:11**********************@42g2000cwt.googlegro ups.com...
>I would like to be able to utilise a combo box where once an item has
been selected then that item no longer appears in that list. Is there
some code anyone has which will achieve this?

If I select an item in to the subform I need to be able to reinstate it
in the combo box to use again.

Hey, am I asking too much?

TIA - Ray
Jan 3 '07 #2

P: n/a
Chesne wrote:
I would like to be able to utilise a combo box where once an item has
been selected then that item no longer appears in that list. Is there
some code anyone has which will achieve this?

If I select an item in to the subform I need to be able to reinstate it
in the combo box to use again.

Hey, am I asking too much?

TIA - Ray
In Northwind there is a form called Customers.

It has a combo box named Country.

The rowsourcetype of the combo is "Table/Query"
The rowsource of the combo is "SELECT DISTINCT Customers.Country FROM
Customers"

When I put this code into the form's module, an item is removed when
clicked.

Private Sub Country_Click()
Country.RemoveItem Country.ListIndex
End Sub
Private Sub Form_Open(Cancel As Integer)
With Country
.RowSourceType = "Value List"
.RowSource = _
CurrentProject.connection.Execute("SELECT DISTINCT
Customers.Country FROM Customers").GetString(adClipString, -1, , ",")
End With
End Sub

If you keep track of these removed items and their index in the list as
they are removed, possible with a modularly scoped flexible array or
collection, you should be able to reinstate them with something like
Country.AddItem ARemovals(12), 23

I tried this in Access 2007. YRMV.

Jan 3 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.