Trying to AddItem to a ListBox

[PeteJohnston]

I've got a pair of list boxes where the first one is populated from a table, the second will initially be empty. I need the usual command buttons to add and remove items from the first list and put them into the second. I'm pretty sure I've used the .AddItem method (and the .RemoveItem) in the past and when I check the Access 2K help it says that I can add them in this way but intellisense doesn't give me the option! The object browser doesn't show me that the method exists either. Can I do it this way or not? I'm a bit confused that the help says one thing but when I try to code it it says I can't. Is it only available in VB? The guy wants it done in Access so I don't have the option of re-writing it in VB.

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[BillPower]

Hi PeteJohnston,

I have an example of how to do this posted at:

http://www.wpower.fsbusiness.co.uk/

The file to look at is: nathan1967 . This is the code I have used:

Dim ctl As Control, varItm As Variant, strVal As String
Set ctl = Me!listSysImpacted
For Each varItm In ctl.ItemsSelected
strVal = strVal & ctl.ItemData(varItm) & "; "
Next
ListSystemImpactedSelected.RowSourceType = "Value List"
ListSystemImpactedSelected.RowSource = strVal

Bill

 

[TonyJollans]

Hi PeteJohnston,

I'm sure Bill's code will do for you, but a (possible) explanation.

AddItem is available for Listboxes in Excel and Word UserForms and it is possible that you have picked up on the help for that - help has caught me out more than once like that - it doesn't seem to restrict itself to the application you happen to be in.

AddItem (and RemoveItem) are not available for Listboxes in Access 2K but are, I understand, available in 2002 (but still not the Clear method).

Enjoy,
Tony

 

[PeteJohnston]

Guys
I can load the left-hand list initially that way but it makes it a pain to find the one which has been clicked, add it to the right-hand list then remove it from the left-hand list. I've just had a thought - if I define a couple of arrays to hold the items in each list I can load the list boxes using

SELECT * FROM qryList WHERE KeyID IN (IDs in arr1)

for the first and

SELECT * FROM qryList WHERE KeyID IN (IDs in arr2)

Then I just have to do a little bit of manipulation of the IDs between the two arrays. Any other thoughts on alternative/better ways will be gratefully received.

Bill - thanks for the link. I'll have a look

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[cykopat]

If you use the VB Control of ListView I believe you can use additem then. However, if I understand correctly. You have two list boxes, 1 populates with Data, the other is blank. You want to select an item from 1 and populate 2 with it.

Are you storing the populations of two in a Table? If so, he's what I would do.

Dim sSQL As String
sSQL = "INSERT INTO [poptable] ([Field]) VALUES (" & lstone & &quot"
CurrentDb.Execute (ssql)
lst2.Requery

 

[PeteJohnston]

cykopat I have one table which holds information received by e-mail which has to be approved by an operator (to avoid applications from Mickey Mouse and Homer Simpson) before being added to the main database. I don't want to use another table just to hold records until they make up their mind and click the Confirm button. I've changed it to use the SELECT .... WHERE KeyID IN (...) code from my last post and it works great. It looks slick as ....

All
Thanks for the suggestions

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)