Find record on form and Me.subform based on two listboxes on separate form

[msmuzila]

I have 3 forms
frmPart_Procedures
frmParts
frmProcedure (subform of frmParts)

On the frmPart_Procedures I have two list boxes (List2 and List4),
List2 shows all the Part_ID and List4 shows the Procedure_ID for the
Part_ID selected in the List2.

Objective: When I double click a Procedure_ID in List4, I want it to
open the frmParts (which has frmProcedure as a subform) and find the
record associated with the selections in the listboxes from
frmPart_Procedures. frmParts would find the Part_ID selected in List2
and frmProcedure would find the Procedure_ID selected in List4.

I have searched high and low for an answer to the problem and have not
come across one.
Any help will be most appreciated

 

[John Welch]

Hi. Here's one idea to do what you need. I'm going to give you a brief
answer here and leave it to you to look into the details. In the doubleclick
event of List4, put code like this:
DoCmd.OpenForm "frmParts", , , "partID=" & Me.list2
this will open frmParts and filter it so that it goes to the record with the
ID selected in List2. (Look at the 'where condition' of the docmd.openform
method)
Then you can run some more code that uses a recordsetclone of the subform to
find the record associated with list4 and go to that record. Search help and
google for these keywords: navigating form recordsetclone bookmark
hope this gives you a start
-John

 

[msmuzila]

this is waht i have so far, it finds the Parts_ID from the selection in
List2, but i dont know how to add it to also find the Procedure_ID from
List4



Private Sub List4_DblClick(Cancel As Integer)

Dim stDocName As String
Dim stLinkCriteria As String
Dim rs As Object

stDocName = "frmParts"

stLinkCriteria = "[Part_ID]=" & Me![List2]
DoCmd.OpenForm stDocName
Set rs = Forms(stDocName).Recordset.Clone
rs.FindFirst stLinkCriteria
If Not rs.EOF Then Forms(stDocName).Bookmark = rs.Bookmark

Exit_List4_DblClick:
Exit Sub

Err_List4_DblClick:
MsgBox Err.Description
Resume Exit_List4_DblClick

End Sub

 

[msmuzila]

Figured it out, here is the answer:

Private Sub List4_DblClick(Cancel As Integer)

Dim stDocName As String
Dim stLinkCriteria As String
Dim rs As Object

stDocName = "frmParts"

stLinkCriteria = "[Part_ID]=" & Me![List2]
DoCmd.OpenForm stDocName
Set rs = Forms(stDocName).Recordset.Clone
rs.FindFirst stLinkCriteria
If Not rs.EOF Then Forms(stDocName).Bookmark = rs.Bookmark

Set frm = Forms("frmParts").[qryProcedure].Form

With frm.RecordsetClone
.FindFirst "Procedure_ID = " & Me![List4]
If .NoMatch Then
MsgBox "Not found in subform"
Else
frm.Bookmark = .Bookmark
End If
End With
Set frm = Nothing

End Sub