link data from a listbox to a form with a subform

[Saskia]

I desperately need some help.

I've created two forms:
1. Course Bookings
2. Course Bookings list

The first form contains the following fields:
- Course Code (Unbound) it’s a combo box
- Venue Name (Bound)
- Course date (Bound)
- Start time (Bound)
- Finish time (Bound)
- Trainer name (Bound)

This form has a subform Enrol Participant. The fields in
this subform are:
- Participant number (combo box)
- Price

The second form contains a list with all course bookings.
I would like to link this to the Course Booking form so
that if I click a particular course booking I can see all
the details in the Course Bookings form.

This is the code I have used so far (but it doesn't work):

Private Sub List0_DblClick(Cancel As Integer)
On Error GoTo Err_List0_DblClick

Dim stDocName As String
Dim stLinkCriteria1 As String
Dim stLinkCriteria2 As String

stDocName = "frmCourse Bookings"

stLinkCriteria1 = "[Course Code]= '" & Me![List0] & "'"
stLinkCriteria2 = "[Participant Number]=" & Me![List0]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_List0_DblClick:
Exit Sub

Err_List0_DblClick:
MsgBox Err.Description
Resume Exit_List0_DblClick

End Sub


Could anyone help me, please?

 

[SteveA]

In your code you are setting a stLinkCriteria1 and a stLinkCriteria2. However, when you open the new form you pass stLinkCriteria which doesn't have any value. If you want to filter by Course Code and Participant Number you need to set stLinkCriteria after setting stLinkCriteria1 and stLinkCriteria2. ie

stLinkCriteria1 = "[Course Code]= '" & Me![List0] & "'"
stLinkCriteria2 = "[Participant Number]=" & Me![List0]
stLinkCriteria = stLinkCriteria1 & " and " & stLinkCriteria2
DoCmd.OpenForm stDocName, , , stLinkCriteria

This should open stDocName and only display records matching the Course Code and Participant number selected.

HTH
SteveA

 

[Saskia]

I finally got it working for the course code but I can't get it related to the right Participant number in the subform.

This is the code that I've got sofar:

Private Sub List0_DblClick(Cancel As Integer)
On Error GoTo Err_List0_DblClick

Dim stDocName As String
Dim stLinkCriteria1 As String
Dim stLinkCriteria2 As String

stDocName = "frmCourse Bookings"

stLinkCriteria1 = "[Course Code]= '" & Me![List0] & "'"
stLinkCriteria2 = "[sbfrmEnrol participants].form![Participant Number]=" & Me![List0].Column(6)
DoCmd.OpenForm stDocName, , , stLinkCriteria1 & " AND " & stLinkCriteria2

Exit_List0_DblClick:
Exit Sub

Err_List0_DblClick:
MsgBox Err.Description
Resume Exit_List0_DblClick

End Sub