Use listboxes to organize records

[opopanax666]

(solved) Use listboxes to organize records

Hello everyone,

I have a form with 2 listboxes: 1 to show the name of the products having a code "v" (recordsource query), 1 to show the name of the products having a code "a".

2 arrowed buttons (left-right) would make a product "jump" from one to the other.

The way I wanted to do this is, when the appropriate button is clicked, the actual product code is changed, then the listboxes refreshed, so that it looks that the product jumped listboxes (when the code changes and the listboxes refreshed/requeried, it will disappear in one and appear in the other, because of the recordsource queries).

I want to do it this way, because it is imperative that the code gets changed!

But I have no idea how to get the record of the selected product "out" of the listbox. When I select a product and check e.g. the ".value" of the listbox, it is "null"!?

So how to "get" the record corresponding with the selected item in a listbox in VBA?

Thanks in advance!

 

[BlueIshDan]

Well, first thing, does your table have an id field?

 

[pr2-eugin]

Run an UPDATE Query on the Selected Item to update it to the appropriate Code. Then a simple requery followed by a refresh will give you the "jumped" feeling.

 

[BlueIshDan]

They need to know how to get the data from their selected item to do the update query with.

I was going to suggest storing the id and product selected in the RowSource with a ColumnSize value of 0. This will hide the ID value and display the product value with a free size.

When retrieving the value of the selected item you would say:

    If lstProducts.ItemsSelected.Count > 0 Then
        CurrentDb.Execute "UPDATE products SET code = """ & new_code & _
                          """ WHERE ID = " & lstProducts.Column(0, lstProducts.ItemsSelected(0))
    End If

 

[opopanax666]

Well, first thing, does your table have an id field?

Yes, it has an "id", but how does the listbox return it? I.e. what vba-code do I use to return the id of the selected item?

Run an UPDATE Query on the Selected Item to update it to the appropriate Code. Then a simple requery followed by a refresh will give you the "jumped" feeling.

I planned on using an update-query, but have no clue how to "get" the record on which to run the update-query (see quote above)...

 

[opopanax666]

They need to know how to get the data from their selected item to do the update query with.

I was going to suggest storing the id and product selected in the RowSource with a ColumnSize value of 0. This will hide the ID value and display the product value with a free size.

When retrieving the value of the selected item you would say:

    If lstProducts.ItemsSelected.Count > 0 Then
        CurrentDb.Execute "UPDATE products SET code = """ & new_code & _
                          """ WHERE ID = " & lstProducts.Column(0, lstProducts.ItemsSelected(0))
    End If

God, no wonder I had no clue! I'm gonna try this, and I'll keep you posted...

Edit: Yes, works like a charm. Thanks both of you!

 

[BlueIshDan]

When using your arrows to index through different codes you could have a global array storing your different codes and an index that is populated on load.

Your left and right buttons would simply say:

    [COLOR="SeaGreen"]'Right Button[/COLOR]
    If index < MAX_INDEX Then
        index = index + 1
        lstProduct.RowSource = "SELECT ID, product FROM products WHERE code = """ & code_list(index) & """"
    End If
    
   [COLOR="SeaGreen"]'Left Button[/COLOR]
    If index > MIN_INDEX Then
        index = index - 1
        lstProduct.RowSource = "SELECT ID, product FROM products WHERE code = """ & code_list(index) & """"
    End If

 

[BlueIshDan]

Run an UPDATE Query on the Selected Item to update it to the appropriate Code. Then a simple requery followed by a refresh will give you the "jumped" feeling.

Do remeber to take Pauls advice on Requery and Refresh To update your displays