Pre-set list box

I have a form that pulls data into various list boxes from a single table.  Some of the list boxes are multi selected.  Eventually this form will be used to select various services and their code will be place into another form.

I have this working.  What I was planning on when opening this form if there are any codes existing from that other form to preselect the list boxes based on those codes.  

I have not added this functionality as I don't know how to preselect values based on a field that contains various values... this values will be spaced delimited.

Please see attached MDB file with what I've got thus far.

Thanks for any assistance.
BETA.mdb
thandelAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
I would not use this technique at all for data I need to save.  Use subforms instead.  They require no code.  The multi-select checkbox is most useful for selecting items to include in a report or an export file as an example.
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
It is possible to do what you want.

To mark a row as selected you use something like this:

lst.Selected(i) = True

Open in new window


where i is the index for the row you want to mark as selected.

See: http://allenbrowne.com/func-12.html
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
thandelAuthor Commented:
OK thank you but how would I know given a particular code value which row it falls on and given that I have more than once code (Ex V2020 V2100) how can I parse through that list and mark each item?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

PatHartmanCommented:
If you are actually trying to save the data, why don't you want to use a subform which requires NO code?  If you want to simulate the multi-select listbox, you'll need code in at least two places.

1. in the form's current event, you will need to highlight the selected options.
2. in the form's after update event, you will need to delete the existing rows in the many side table and insert the selected rows.  If you don't delete the old data, you will end up keeping items that were deselected and you won't be able to get rid of them.  Of course you write more complicated code to find them and delete them.

Again, I suggest that if you are not comfortable writing VBA, use the subform method.  It requires NO!!!!!! code.

You should also be aware that by using the code solution, you may end up with a partially complete transaction.  I.e. the main record is saved but the Multi_select list box data is not saved or worse, it is deleted but not repopulated.  All in all, this is a dangerous solution if you don't know how to create transactions and I'm not sure you can wrap the form save into the same transaction as the Multi-select listbox update.  If you really insist on this method, the only safe method is to use an unbound form which requires a fair amount of code.  When you use the subform solution, Access takes care of surrounding the updates of both forms in a transaction so if one fails, both fail.
0
thandelAuthor Commented:
After a user select the desired services the form will close.  When the form reopens for the next use all selections will be reset so I'm not worried about that. But the subform... can you provide an example?

Regarding my original question is it possible to run thrugh each list box and set values based on a value in a text box?
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
OK thank you but how would I know given a particular code value which row it falls on and given that I have more than once code (Ex V2020 V2100) how can I parse through that list and mark each item?

It would be the opposite of how you figure out what was selected.

In the link I previously posted ( http://allenbrowne.com/func-12.html ) it shows how to loop through all this items in a listbox and mark them as selected. In your case you will only mark the ones as select that match some other list from the other form you referenced.


 I concur with PatHartman that sub forms would be much easier.

I do what you are attempting with sub forms and temp tables. I use append and update queries to do all the work. It take only a few lines of code.


I only use a multi-select list box on rare occasions when the this is 10 or less. I find that users do not find then user friendly. I generally avoid them for a better UX.

When I do use List boxes, I use them in pairs. One shows only the items not selected.  The other shows the items selected. This works much better when there are lots of possible items to select.  

I don't remember where I found this example but it is a good one for multiple list boxes.
SelectRecords.ZIP
0
thandelAuthor Commented:
OK thanks you but if I wanted to select codes based on some sort of text values... is there a sample code?

I see what you are saying about the subforms but honestly I am lookking for clean form without the subform feel or the numberous drop down fields
0
thandelAuthor Commented:
Something like:

    For Each ItemIndex In Me.List30.Items
        If Me.List30.ItemData(ItemIndex) = Me.Text32.Value Then
            MsgBox "GOT IT"
            'Me.List30.Selected(ItemIndex) = True
        Else
            MsgBox "NEVER FOUND"
        End If
    Next
0
thandelAuthor Commented:
Can't seem to get this code right.
0
thandelAuthor Commented:
Seem to have this working.


    t = Me.List30.ListCount
       
    For l = 0 To t - 1
        If Me.List30.ItemData(l) = Me.Text32.Value Then Me.List30.Selected(l) = True
    Next
0
thandelAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for thandel's comment #a39523553

for the following reason:

Very simple code subforms not needed.
0
PatHartmanCommented:
I was envisioning separate subforms for each type of multi-select item.  You may also need cascading combos for the first two items.
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
Your solution is using exactly what I recommend in my very first post.
0
PatHartmanCommented:
I don't post much here so I am sometimes confused by the interface but it looks like mine was the first response to the post.  Not that it matters.  The OP seems to want to write code and doesn't understand how to use property settings to control the look of a subform.
0
thandelAuthor Commented:
Thanks TheHiTech, but I didn't find your initial response as helpful as I didn't find it as a complete solution... it didn't cover how to go and check each item in the list but only how to set it as marked.  Did I miss something in your response?
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
I gave the best answer I could based on the following:

What I was planning on when opening this form if there are any codes existing from that other form to preselect the list boxes based on those codes.  

It is very difficult to give a complete solution based on what you originally posted.

Your example did not include any instructions on on how to use your form or  anything else that would allow me to give you a better solution.  My second post example in more detail what you needed to do.

I think I now know why you can't use a sub form. I think you are storing all the values from the multi-select list box in a single field instead of using a properly normalized data structure.
0
thandelAuthor Commented:
I am not storing any values in the lists... marely using it as a means to have the user select items then I will then parse through to make a single text line entry into another form space delimited.  

I'm sorry  I didn't follow your solution. 50% to your solution and 50% to mine sound fair?
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
I didn't follow your solution.

Actually you used 100% of my solution otherwise it would have never worked.

FWIW:
Looks like you are notorious for selecting your own solution and not awarding points.  Not a great way to encourage people to want to help you.

You do whatever you feel is ethical and fair.
0
thandelAuthor Commented:
Thank you.  The question needed a solution to run though a list... the way I read you solution was only a solution to mark a list item as marked "True"... it didn't offer a solution how to run through the entire list and mark items as needed.  

A final solution of:

 t = Me.List30.ListCount
       
    For l = 0 To t - 1
        If Me.List30.ItemData(l) = Me.Text32.Value Then Me.List30.Selected(l) = True
  Next

Was not simple, short, didn't need sub forms and was not complicated.  

Clearly I misread your solution.  Thank you for your responses.  I do appreciate it just over looked the solution.
0
thandelAuthor Commented:
I have never been at a site before that made the customer feel bad about involvement or a solution.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.