Double click list box with dual-field primary key

[pbbriggs]

Hi,

Thanks to this forum, I have a search form which, after the search is run, displays a list box of the search results. Then, when the user double clicks an item in the list box, a new form pops up, with the details of the item from the list box.

My problem is that the table from which the list box is populated has a dual-field primary key, and I can't figure out the proper syntax to have the pop-up form only pull the one record I am interested in. My current code (which was built under the assumption that the records would only have one primary key) is as follows:


Private Sub lstFind_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDetail", , , _
"[BID] = Forms![frmFind]![lstFind]", , acDialog
End Sub

This code works fine, except that what I really want is a combination of [BID] and [Date] to drive the opening of frmDetail. The way it is now, the pop-up form (frmDetail) will open, filtered for *all* records with BID equal to the BID of the item in the listbox. Unfortunately, it's the BID & Date combo that is a unique key. I tried a few obvious solutions (see below) but both resulted in the pop-up form (frmDetail) opening blank (i.e., with no results).



'*** This didn't work
Private Sub lstFind_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDetail", , , _
[BID]&[Date] = Forms![frmFind]![lstFind]", , acDialog
End Sub

'*** This also didn't work
'*** I was surprised that even if I didn't try to add
'*** the second field of the primary key, the syntax
'*** for the list box with a field name after it didn't
'*** work at all.
Private Sub lstFind_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDetail", , , _
"[BID] = Forms![frmFind]![lstFind].[BID] AND [Date] = Forms![frmFind]![lstFind].[Date] ", , acDialog
End Sub

Anyway, if anyone knows if this is possible to do, I'd greatly appreciate it.

Thanks
Patricia

 

[RoyVidar]

Hi!

Your where condition might look something like this:

"[BID] = " & Forms![frmFind]![lstFind] & " AND [Date] = #" & Forms![frmFind]![lstFind].column(1) & "#"

This assuming your datefield is in column 2 of the list (it's zero based) and the BID field is numeric.

If the controls are in the current form, you could shorten the references to me!lstfind and me!lstfind.column(1)

BTW strongly advice to avoid using reserved words for fieldnames. That will sooner or later cause headaches (date).

HTH Roy-Vidar

 

[pbbriggs]

Thanks RoyVidar,

I will give this a try. I had actually constructed a workaround where I extracted the two fields into two separate boxes upon the double-click action. Then I used those two text boxes to pull up the second form. Your method looks cleaner, though.

And yes, you're right, the name of my date field is not actually Date (nor is my form called "Find&quot -- I was just simplifying the names for the sake of the post. But thanks for the heads-up!!

Patricia