Use value of selected row on subform for the query of another subform.

[Axeia]

Hello,

Could anyone please tell me how I can link two subforms together on a form?
On the attached screenshot there is a form with two subforms. The left one is displaying de "Zaal" table and the right one is displaying de stoel table.

What I want to archieve and it's not doing at the moment is:


When "Grote Zaal" is selected on the left it should show all the results on the right.

I was hoping to do this by setting the record source of the right subform by changing the criteria of the form (of de Zaal column) on the right to something like "[Forms].[dbo_Zaal_subform].[Zaalnaam]" but this gets me an input dialog.

Is this even possible this way or I should be doing this using VBA and if so where can I find an example?

[orange]

What exactly do you mean by all records on the right. Can you describe the data a little more and tell us how the subforms are/should be related? I see 51 records on the right -- how many should there be?

[Axeia]

On the left there is the column 'Zaalnaam' which should match with the 'Zaal' column in the subform on the right.

Basically on the right it should just be a
WHERE Zaal = 'leftsubform.selectedrow.Zaalnaam'

Attached a screenshot with the relative part of the original DB diagram.

[orange]

When "Grote Zaal" is selected on the left it should show all the results on the right.

As mentioned, I see 51 records in the right subform. How many did you expect?

Can you show the Recordsource for the current right subform?

I think the recordsource for the right subform would be (untested, but best guess)

Me!dbo_stoel_subform.Form.RecordSource = "Select * from dbo_stoel where stoel.zaal = " & Forms!dbo_zaal_subform.Form.Zaalnaam

[Axeia]

Thank you Orange, getting close!
The query is now

SELECT dbo_Stoel.Stoelnummer, dbo_Stoel.Zaal, dbo_Stoel.Rang
FROM dbo_Stoel
WHERE (((dbo_Stoel.Zaal)=[Forms]![dbo_Zaal subform].[Form].[CurrentRecord]));

But it seems CurrentRecord doesn't give back the text of the selected column as the query is still prompting me for input rather than using the value.
I also tried the column name (Zaalnaam) which does the same (still prompting for input). When I manually enter "Grote Zaal" in the popup that prompts for input it works as expected.
.[CurrentRecord] doesn't seem to have any properties as the smartmenu/intellisense (whatever it's called in access) doesn't come up with anything.

[orange]

I'm not following the CurrentRecord thing.
Don't you want the recordsource for the
stoel subform to only have records for
Grote Zaal?

You may have to say Forms!dbo_zaal_subform.Form.Zaalnaam.Value ????
You may have to issue a requery, once you have modified the recordsource.

I'm working with Acc2003.

After posting this, I went searching for a sample Form with 2 subforms. If you have the Northwind database from M$oft,
the Customer Orders Form has 2 subforms - Customer Orders Subform1 and Customer Orders Subform2. These are "syncing records". I am going to attach 2 screenshots showing which Order is selected (top subform1) and which Product(s) belong to that Order (lower subform2).

Here is the OnCurrent code behind Subform1

Sub Form_Current()
' This code created by Form Wizard.
'This code is in Subform1
    Dim strParentDocName As String

    On Error Resume Next
    strParentDocName = Me.Parent.name

    If Err <> 0 Then
        GoTo Form_Current_Exit
    Else
        On Error GoTo Form_Current_Err
        Me.Parent![Customer Orders Subform2].Requery
    End If

Form_Current_Exit:
    Exit Sub

Form_Current_Err:
    MsgBox Err.Description
    Resume Form_Current_Exit

End Sub

Good luck