Add Item to List Box

[meadows43]

Hi,

I have two list boxes on my form. I want the user to select an item from the first list box, click a button, and have the item be added to the second list box. What's the most straightforward way to add an item to a list box with VBA? I know in Visual Basic.NET you use Me.ListBoxName.Items.Add(item), but this does not work in Access 2000. I get an error "Item not found in collection."

Thanks in advance,

Chris

 

[dcx693]

Access does not support the .Add method for listboxes (or comboboxes).

The easiest way to do this, assuming you're not in a multi-user environment, is to have a column in the table that feeds into your listbox that stores a boolean value.

On loading the form with the listboxes, fill them from the source table. Depending on the value of that boolean, the item will be either in the first or second listbox.

As a user selects an item from a listbox and clicks on the command button, run an update query to flip the value of the boolean variable and requery both listboxes.

If you're working multi-user, write back.

 

[meadows43]

Thanks, that's a good idea. And no, I'm not in a multi-user environment. I am having trouble though retrieving the selected item from the first list box. I wrote a function that loops through the list of the ItemsSelected property, but I am using a macro for the command button's onclick event, and I need to use the value returned by the function in the SQL update query. I thought about using a form level variable to hold the selected value, but I feel like I'm making this harder than it needs to be. How does one normally get the selected value from a list box when using a macro?

Thanks,

Chris

 

[dcx693]

Sorry, lost track of this thread, but now I'm back.

If you're using a multi-select listbox, then you need to loop through the Me.lstBox.ItemsSelected collection. If not, then you can just get the value of the listbox from an expression like Me.lstBox.

Here's a sample function for looping through the ItemsSelected collection using VBA:

Private Sub cmdTest_Click()
Dim varItem As Variant

For Each varItem In Me.lstNames.ItemsSelected
  Debug.Print Me.lstNames.ItemData(varItem)
Next varItem

End Sub

If you're using a single-select listbox though, it's much simpler as I said. The value of the listbox is:
Me.lstBox

Just call your update query either from within the ItemsSelected loop function, or using the single listbox value depending on your situation.

 

[meadows43]

Ok, I think I've almost got it.

I'm getting a data type mismatch error for my query string when I click the command button, though.

Here's the function I'm using


Private Sub Command9_Click()

Dim varItem As Variant
Dim strSQL As String

strSQL = "UPDATE Ingredients SET Ingredients.InQuery = Yes WHERE Ingredients.[Ingredient Num] = '" & Me.List32.ItemData(varItem) & "'"

For Each varItem In Me.List32.ItemsSelected
DoCmd.RunSQL (strSQL)
Next varItem

DoCmd.Requery (Me.List32)
DoCmd.Requery (Me.List28)

End Sub


Thanks again.

 

[WayneRyan]

Meadows,

You've got to put the assignment to strSQL INSIDE the loop.
The way it is varItem is null and the strSQL never gets
changed.

Wayne

 

[dcx693]

WayneRyan is right. Be careful about your data types.

For:
InQuery = Yes, do you mean
InQuery=True ?

For Ingredients.[Ingredient Num], is Ingredient Num a numeric value or a text string? Your statement is treating it like a text string.

 

[meadows43]

I removed the single quotes before and after Me.List32.ItemData(varItem) in the strSQL assignment statement. Ingredient Num is a number not a text string.

Wayne, thanks for pointing out my error with the strSQL assignment.

But now I get an error "Run time error '2109' There is no field named '345' in the current record." I get the error on the line that requeries the first list box.

Thanks,
Chris

 

[WayneRyan]

Chris,

It sounds like you have a multi-column listbox and you
are not using the .Column reference properly. The first
column is 0, then 1, etc.

Wayne

 

[WayneRyan]

Chris,

DoCmd.Requery (Me.List32)
DoCmd.Requery (Me.List28)

Should be:

Me.List32.Requery
Me.List28.Requery

Wayne

 

[meadows43]

Wayne,

Thanks, now it works fine. I'm still feeling my way around VB, so I thought I could use the DoCmd.Requery syntax. Thanks for setting me straight (again).

Chris