Trying to filter form with subform and Junc table

[Montez659]

Is it possible to filter a main record based on a subform entry? Hard to find the wording that I need, but let me explain.

I have three tables, tblBudgMain, tblBudgSub, JUNCtblBudgetSub_BudgetMain.

So basically, I have a way for a person to narrow down the record to edit by first selecting the main budget item from a list box, which filters another listbox of all the subs under that main category. After choosing the sub item on the listbox that they want, it opens a form with a subform. The main form is mostly empty, but contains the budgMainID field. The subform will have the sub budget fields so that they can be edited. The subform only display's one record and cannot be navigated through. Shows in form view only, no datasheet.

I am basically thinking that I need to filter the main form first to the main budget id, and then filter the subform to the sub budget id, but I am at a loss as to how to accomplish this.

I thought that I could filter for the subform only, and I had this code to do it:

DoCmd.OpenForm "TEST_DEPfrmSettings_BudgetSub", acNormal, , "[budgSubID]= " & a

where a is the budgSubID selected from the listbox. But then I get the Access parameter box asking for "budgSubID" and if I put in the correct record ID, I can see it. I guess I am missing how the code is talking to the form to know to filter.

All help and wisdom is appreciated. And please, there might be a better way to do it, and I will gladly take suggestions on restructuring, but let that not be the focus of this post. Thanks!

 

[Montez659]

For reference sake, this is how I accomplished it. Mind you, it took a while to get it to work correctly and I got the idea after searching and finding this site: http://www.access-programmers.co.uk/forums/showthread.php?t=183379

In the On Double Click event of the list on my form:

Private Sub lstBudgetSub_DblClick(Cancel As Integer)

Dim i As Integer
Dim subID
Dim mainID

' add check to see if something is selected
For i = lstBudgetSub.ListCount - 1 To 0 Step -1
    If lstBudgetSub.Selected(i) Then
        subID = lstBudgetSub.Column(0, i)
        mainID = lstBudgetSub.Column(4, i)
        Exit For
    End If
Next i

DoCmd.OpenForm "TEST_DEPfrmSettings_BudgetSub", , , "[budgMainID]= " & mainID, , , [subID]
End Sub

and then in the On Load event of the final form/subform:

Private Sub Form_Load()

If Not IsNull(Me.OpenArgs) Then
Me.sub_JUNCtblBudgetSub_BudgetMain.SetFocus
DoCmd.FindRecord Me.OpenArgs
End If

End Sub

May this be a blessing to someone in the future!