Link to home
Start Free TrialLog in
Avatar of thandel
thandel

asked on

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
Avatar of PatHartman
PatHartman
Flag of United States of America image

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.
ASKER CERTIFIED SOLUTION
Avatar of Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of thandel
thandel

ASKER

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?
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.
Avatar of thandel

ASKER

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?
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
Avatar of thandel

ASKER

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
Avatar of thandel

ASKER

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
Avatar of thandel

ASKER

Can't seem to get this code right.
Avatar of thandel

ASKER

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
Avatar of thandel

ASKER

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.
I was envisioning separate subforms for each type of multi-select item.  You may also need cascading combos for the first two items.
Your solution is using exactly what I recommend in my very first post.
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.
Avatar of thandel

ASKER

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?
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.
Avatar of thandel

ASKER

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?
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.
Avatar of thandel

ASKER

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.
Avatar of thandel

ASKER

I have never been at a site before that made the customer feel bad about involvement or a solution.