Reselect items in listbox based on records (1 Viewer)

[rainman89]

[Resolved] Reselect items in listbox based on records

Hi all,

So I have a set of records that I want to let people edit when they realize they have made a mistake.

Currently they select from multiple listboxes and that populates records based on their user ID

What I would like is if they already have a record for a certain term, they can go in and edit that record. Hopefully this could be done with the same form, but what I need right now is to figure out how to reselect the items in a listbox that are in the recordset.

I have the listbox bound to a generic list of possibilities and would like when they click edit for them to be able to see those items they have already selected in the listbox (highlighted)

I can currently get the records pulled into a recordset, but i cannot get the listbox to reselect.. any help would be appreciated. Thanks in advance

Set rs = Db.OpenRecordset("qry_benchskill", dbOpenDynaset)
reccount = rs.RecordCount
MsgBox reccount

rs.MoveFirst
    Do Until rs.EOF
            studentBench = DLookup("benchmarkID", "qry_benchskill", "studentYearID=3")
            'MsgBox studentBench
            For x = 0 To Me.List1.ListCount - 1
            'MsgBox Me.List1.Column(0)
                'MsgBox "see me?"
                Me.List1.Selected(studentBench) = True
            Next x
            rs.MoveNext
    Loop

This works for only one record. It doesnt go through the whole 44 records that it should be...

 

[pbaldy]

The DLookup will return the same value every time, since it has a fixed criteria. Perhaps it should be referring to the recordset for a value?

 

[rainman89]

The DLookup will return the same value every time, since it has a fixed criteria. Perhaps it should be referring to the recordset for a value?

Not very familiar with recordsets... can you pull things like a dlookup can?

in now you can do findfirst record where a condition is met, etc.. but how do you get it to return a value such as ID??

 

[pbaldy]

You're already opening a recordset, you're just not doing anything with it other than the loop. Within your loop, you can get the ID or any other value in that query with:

rs!FieldName

 

[rainman89]

You're already opening a recordset, you're just not doing anything with it other than the loop. Within your loop, you can get the ID or any other value in that query with:

rs!FieldName

Thanks Paul, That works... now i have to get the listbox to show them all at once.


EDIT::Multiselect??? haha. forgot to turn that on

 

[pbaldy]

It looks like you're on the right track, you just need to refer to the current record in the recordset somehow with this line:

Me.List1.Selected(studentBench) = True

either by setting studentBench or using the field directly in that line.

 

[rainman89]

Got it. Thank you!!

Once again cant't give you rep because I have before.... oh well!

 

[pbaldy]

I'm glad to have helped you out, Ray. I'm not in it for the points, but I appreciate the thought.