List Box Question

[al718]

is there a way for me to compile a list using cascading list boxes?

for example:

everything category that i double click on list1 will result in every member of that category being displayed in list2

i'd idealy like to be able to mail out letters to different groups of people - so i would need a list box to retain everyvalue it recieves

i dont know how to go about this - it seems alittle different from all the cascading questions

any suggestions?

 

[Hayley Baxter]

Have a look at this link. If you're stuck let me know and I'll make you a sample.

Link

For some reason that link does not want to open where I want it to > Go to Forms > Limit contents Combo/Listbox

 

[al718]

wow - you're fast

thanks for the super quick response hayley!

i have been looking at the site for a bit but cannot find a walk through of how to make a list box retain things

any idea where i can find info on it? or does anyone have an example?

 

[dcx693]

I think you might be asking for something different than what Hayley Baxter proposed? Are you looking to have a list box present a list of all possibilities where, if you double-click an entry, the entry will "move" to another list box and be tagged somehow as being selected?

This description:

everything category that i double click on list1 will result in every member of that category being displayed in list2

does not describe the scenario above, but what Hayley Baxter's solution is for.

Perhaps you could elaborate more.

 

[al718]

sorry

sorry for being vague

but i am ultimately trying to create a form that allows me to do the following:

i have names and addresses in a table and those records are categorized

list1 displays the categories
double clicking an item on list1 will result in all records under that category to go into list2 (i want to be able to populate the list with a different set of addresses each time)

list2 will be the list that i will send the letter to - so i need to be able to have multiple categories in the list at once

also - certain addresses may have multiple categories - so i dont know a way aroudn that either


i hope this isnt too much to ask for - i have no idea what it involves

thanks to everyone who is taking the time to help out!!!

albert

 

[dcx693]

When you say that you want to "retain" the values? Do you mean that you need the database to store the fact that you've done a mailing to everyone in the selected category? Or do you just need to view it on screen?

If you double-click on one category in the first list box, then double-click another, you want members of both categories to be displayed, right?

 

[al718]

yes i would want members of both categories to be displayed

and what i meant by retaining was just that they both are on the list at the same time

what i am intending to do is have a table that has records of letters - so each letter would have multiple categories that it was sent to

i'm sorry for making this all so complex - is there a better way of doing this? i am open to sugestions -

thanks again


albert

 

[al718]

will the code that hayley proposed do the job that i just described?

or will each click replace data in the second list box?

 

[dcx693]

The code in Hayley's link will only store one category at a time (the current list will be overwritten when you double-click a 2nd category), but you can adapt it to retain the previous choice(s).

You need to get it working for just the first choice before we do that, though. Then it's basically a matter of using this line of the code:
Me!cbxCombo2.RowSource = strSQL
and making it something like:
Me!cbxCombo2.RowSource = Me!cbxCombo2.RowSource & strSQL
That's not the right syntax, but that's the basic idea. You take the existing rowsource of the listbox and just add to it.

 

[al718]

hey! thanks for all the help so far

ive been thrown around to different projects and now i have time to get back to this database


i have it working to cascade the boxes where the second list is overwritten after each click of the first box

i see the logic of your code now - its soposed to accumulate the row sources right?

but where do i put the code? what event does it go under?

 

[dcx693]

You want the rowsource to be updated each time your user double-clicks a choice in the first box, right? So put it in the double-click event of the first list box. You need to be careful about the syntax for the rowsources like this:
Me!cbxCombo2.RowSource = Me!cbxCombo2.RowSource & strSQL
because each rowsource string has an trailing semicolon character. You need to chop that off the first rowsource. Then you need to join them together in the form of a union query. So a more correct statement would be:
Me!cbxCombo2.RowSource = Left(Me!cbxCombo2.RowSource,Len(Me!cbxCombo2.RowSource)-1) & " UNION (" & Left(strSQL,Len(strSQL)-1) & ");"

if you follow....

 

[al718]

oh dear,

i'm learning so much from everyone!!!

i rememmber the start of this summer when anything that had anything to do with VBA made me cringe - and now i almost understand it

ive got the two list boxes: listcat and list2

ok, so i have this coding so far:

Private Sub listcat_DblClick(Cancel As Integer)
Dim strSQL As String
strSQL = "SELECT Addresses.Firstname, Addresses.[Last name], Addresses.category FROM Addresses WHERE (((Addresses.category) Like [forms]![main].[listcat])); "
Me!List2.RowSourceType = "Table/Query"
Me!List2.RowSource = strSQL
Me!List2.RowSource = Left(Me!List2.RowSource, Len(Me!List2.RowSource) - 1) & " UNION (" & Left(strSQL, Len(strSQL) - 1) & ");"
End Sub


it doesnt give me an error, but it still overwrites the second box
and i have no idea how to make the syntax any better
any ideas as to what i am doing wrong?