Redisplaying selected in listbox

[avagodro]

I have a listbox that has the following SQL in a query:

SELECT tblResponsesList.Rspns, tblResponsesList.QstnID
FROM tblResponsesList
WHERE (((tblResponsesList.QstnID)=[Form]![QstnID]));


I want to have it if I return back to the record at some point, the listbox will show all the options and have selected the selected choices that are stored in tblResponses.

I have tried:

For x = 0 To ListRspns.ListCount - 1
If ListRspns.ItemData(x) = DLookup("Rspns", "tblResponses", "QstnID=" & Me!QstnID) Then
ListRspns.Selected(x) = True
End If
Next x

However, for some reason this is not working. Thoughts? Could it be because I have 2 columns in my listbox (Rspns and QstnID)?

 

[mmorancbt]

You need to explain your table structure a bit better. Is tblResponseList the complete list of options and tblResponse a table the contains a many to many join to another table like tblCustomer – for instance.
tblResponseList would have a unique key and a response text field
responsekey | responsetext

tblResponse would have a customer id field and a response id field
cust_id | responsekey

For each option on a given customer record, you would have an entry in tblResponse.

Once again, I’m kind of guessing at what you want.

You want a list box that shows you all the possible options but highlights those that are already part of the record. If someone selects a new option or de-selects an option and the record is save, you want those changes to be saved back to tblResponse. This means both deleting and adding records as needed.

Before I go any further, let me know if I am understanding your requirements.

Thanks.

Matthew Moran (career blog and podcast below)
Career Advice with Attitude for the IT Pro

http://blogs.ittoolbox.com/pm/career

http://techcareerconsult.blogspot.com

 

[avagodro]

You actually have it right on.
The table structure is:
tblAssociates
--AssociateID
--Associate_Last_Name
--Associate_First_Name
--etc...

tblResponse
--AssociateRef (1-many with tblAssociates.AssociateID)
--QstnID
--Rspns
--ResponseDate

tblQuestions
--QstnID (1-many with tblResponses.QstnID)
--text
--etc

tblResponsesList
--QstnID (1-many with tblQuestions.QstnID)
--Rspns

In the listbox it shows all options in tblResponsesList that have a corresponding QstnID to the one in tblQuestions. When selections are made in the listbox, the results are saved to tblResponses. I am looking at wanting the listbox to still pull from tblResponsesList, but "select" the result from tblResponses if someone goes to review. The listbox contains the fields Rspns and QstnID.