[RESOLVED] Field Picker

[Webskater]

I'm looking for a bit of advice before I try to do something.

I need to replicate the sort of form you get in MSAccess where you are selecting the fields you want in a query.

There are two boxes - one on the left and one on the right and, between them, 4 buttons that allow you to:
select one item from the box on the left and move it to the box on the right
select all items from the box on the left and move to the right
select one item from the right and move to the left
select all items from the right and move to the left

Which controls should I use?
Two rich text boxes?
Is there a built-in control in VB for this?
I want users to be able to shift-click and control-click in either box to select items that are not necessarily next to each other etc.

Appreciate any input on the best way to implement this.

Thanks in advance.

[Hack]

Attached is an example.

[amrita]

1.Use two list boxes and 4 buttons
2.Set the multiselect property of both
3.Try this code for moving selected items from list1 to list2

Private Sub Command1_Click()
    For i = 0 To List1.ListCount - 1
        If List1.List(i) = "" Then Exit For
        If List1.Selected(i) = True Then
            List2.AddItem List1.List(i)
            List1.RemoveItem i
        End If
    Next
End Sub

4.Proceed with other buttons as such

[Hack]

That is precisely what my example does with code for all 4 buttons.

[Webskater]

Attached is an example.

Wow! I was hoping to get pointed in the right direction not to get the code - thanks very much!

[amrita]

both of us have replied at the same time.!!

[Hack]

I've use this type of thing in projects beyond count, including the one I'm currently working on.

Do you have any other questions on this topic?

[Webskater]

I've use this type of thing in projects beyond count, including the one I'm currently working on.

Do you have any other questions on this topic?

Well ... since you ask ... what do you do if you want to retrieve an ID associated with each item. If Oranges are 7 and Apples are 4 etc and what you want to be able to access is those values?

Thanks again. (Blast - already marked thread as resolved!)

[Hack]

By "ID" do you mean its ListIndex?

[Webskater]

No, if I pull the data from a database - I mean the primary key associated with each item.

cheers

[Hack]

You are loading lstFrom from a database table, right?

How many primary keys are there on this table?

[Hack]

Also, when you have selected what you want from lstFrom and move those items over to lstTo, what happens next?

[Webskater]

The list on the left contains a list of products that users can click on to compare them.

So, when the list on the right is populated - they will click a 'Compare' button and I will pass the list of ProductIDs (hidden) in the list to retrieve the details from the database.

Thanks again

[Hack]

In what will these details be displayed?

[Webskater]

Let's say the user decides to compare oranges, apples, pears and peaches. I'll be passing say 4,7,9,13 (the ProductIDs) as the parameters to a SQL statement to return various fields which I will then put into an excel spreadsheet for the user to import into their own reporting tool.

Cheers

[Hack]

Ok, so if I'm reading you right, you need to build an IN clause which would read

IN ('oranges', 'apples', 'pears', 'peaches')

With the SELECT whatever FROM whatever WHERE product etc.

Is this right?

[Webskater]

Hi, sorry, I'm not explaining myself very well.

I need to be able to associate a productID with the products listed in each box.

So the one on the left might contain:

4 Oranges
7 Apples
6 Peaches
23 Pineapples
15 Plums
29 Grapefruit

Where 4,7,6,23,15,29 are the respective Product IDs but which are hidden from the user.

And, by the time the user has clicked on three products the right box might contain:

7 Apples
6 Peaches
15 Plums

I need to be able to retrieve the ProductIDs - 7,6 and 15) so I can pass them to a Select statement whose WHERE clause will look like ...

WHERE ProductID = 7 OR ProductID = 6 OR ProductID = 15

Cheers

[Hack]

You are explaing yourself, but this

WHERE ProductID = 7 OR ProductID = 6 OR ProductID = 15

is not the way to do it.

The way to do it is:

WHERE productID IN (6, 7, 15)

[Webskater]

Okay - understand you should use 'IN' instead of multiple 'ORs' - but how do I associate the ProductIDs with the Products in each box?

Cheers

[Hack]

Tell me your database table design.

How are they laid out with respect to productIDs, products and product details?

Are you using just one table for all of this or are there multiple tables involved?

(Incidentially, until we get this worked out, edit the first post and backspace RESOLVED out of the thread title, then scroll down to the icon section and click No Icon - you can click the Mark Thread Resolved again later on.)

[Webskater]

Been doing a bit of reading - I know what I need to do now but don't have the syntax to do it.

You wrote ...

With lstFrom
    .AddItem "Oranges"
    .AddItem "Apples"
    .AddItem "Pears"
    .AddItem "Peaches"
    .AddItem "Plums"
    .AddItem "Prunes"
    .AddItem "Bananas"
End With

I need to do this

.AddItem "Oranges"
.ItemData(?) = 7 (i.e. the ProductID for Oranges)
.AddItem "Apples"
.ItemData(?) = 4 (i.e. the ProductID for Apples)

I don't know what to put in as the index of ItemData - i.e. where I have put the '?'

If I write ...

1stFrom.ItemData(1stFrom.ListIndex) it throws a wobbly and says the ListIndex = -1

But as you have just added a row I would have thought the ListIndex would be set to the current row?

Cheers

[Webskater]

Have just figured out how to get the ProductIDs into the ItemDatas and out again.

Thanks again for all your help.

[Hack]

So, is this resolved again?

[Webskater]

It is indeed.

Cheers