Multi-Field Primary Key w/Listbox

[Robin]

In Access 2002/2003. I would like to launch a pop-up continous form showing
SEGMENT (below) by double clicking on an item in a list box based on
ASSIGNMENT (below). My tables have multiple fields which make up the primary
key but the listbox "value" is limited to one field. Is there any way to do
this? The structure is something like this:
CLIENT ENGAGEMENT ASSIGNMENT SEGMENT
ClientID---ClientID-----------ClientID-----------ClientID
EngagementID---EngagementID---EngagementID
EngagementYr---EngagementYr---EngagementYr
AssignmentID----AssignmentID
SegmentID

 

[Scott Lichtenberg]

Robin

You can try creating a concatenated field to be the "key" to the query which
you use as the rowsource for your combo box.

SELECT ClientID & "|" & EngagementID &"|" & EngagementYear & "|" &
AssignmentID AS MyKey,
ClientID,
EngagementID,
EngagementYear,
AssignmentID

FROM MyTable

Set the width of the first column to zero to hide this field. When the user
clicks on the list box, you will be able to read the values of the
individual fields by looking at the columns of the list box. (You don't
have to bother parsing the concatenated field.)

"SELECT SegmentID FROM MyOtherTable
WHERE ClientID = " & Me!lstListBox.Column(1) & " AND EngagementID = " &
Me!lstListBox.Column(2) etc.

 

[Robin]

Scott

Thank you! I've got the first part working - it's creating the concatenated
field but I'm a little confused as to the second part of your answer. If
that (SELECT SegmentID from MyOtheTable...) is the Record Source of the
pop-up form, should I replace the Me! with the Form name where the listbox
resides? OR should I use that in the Click Event VBA that opens the form?
I've tried both ways and get an error when used as the Record Source and a
syntax error when used in the VBA. Sorry I'm a little rusty!

Thanks again.

 

[Robin]

For closure only, it turns out I did not need the concatenated field. Your
WHERE statement led me to the following:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "popMStaffCACS"
stLinkCriteria = "[ClientID]=" & "'" & Me![lstMStaffCA].Column(0) & "'" & _
" AND [EngagementID]=" & "'" & Me![lstMStaffCA].Column(1) & "'" & _
" AND [EngagementYr]=" & "'" & Me![lstMStaffCA].Column(2) & "'" & _
" AND [AssignmentID]=" & "'" & Me![lstMStaffCA].Column(3) & "'"

DoCmd.OpenForm stDocName, , , stLinkCriteria

Thank you!