CODEWITHSUNDEEP
sqlms-accessvba
jtchase08
jtchase08

Reputation: 660

Filter data in datasheet based on second datasheet

I have a form with two subforms in it in the following format:

Subform Example 1

on Subform1, Element1 has Data1. Value of Data1 points to Data1 on Subform2 which has a value of Value1

Right now both subforms are showing all data in each table. What I want to do is filter Subform2 based on the row selected in Subform1.

Subform Example 2

In this example, Element3 is selected, so the pair Data3 Value3 shows in Subform2.

I've tried accomplishing this by altering the SQL on Subform2, but nothing I do seems to do the trick. I don't know if I'm looking in the right place, or if I should look somewhere else.

If there's anything else I should provide, please don't hesitate to point it out. I want to provide enough information to come to a solution.

Upvotes: 0

Views: 41

Answers (3)

jtchase08
jtchase08

Reputation: 660

Results and explanation using methods in other answers

Okay, so I ended up coming to a solution for my specific problem, and hopefully by sharing here, it will help others in the future.

The other solutions to this question assume that your form hierarchy is as follows:

MainForm->Subform1->Subform2

As in, the second subform is in and owned by the first form. This will work for most applications, but not when both Subform1 and Subform2 are Datasheets.

The hierarchy in my case, and the hierarchy for the people I hope to help in the future is as follows:

MainForm->Subform1
MainForm->Subform2

As in, the second Subform is NOT owned by the first Subform.

With this hierarchy, the code in the other solutions doesn't work, unfortunately. However, there is a simple workaround:

Use hidden textbox control as a "link" between Subform1 and Subform2

(The following method uses the example names found in my original question)

From design view, create a Text Box in the MainForm, not in the Subform1 or Subform2.

On the Property Sheet for the newly created Text Box control, under the Data tab under Property "Control Source", put the following code:

=[Subform1].[Form]![Element1]

Obviously, replace Subform1 with your first subform, and typically Element1 will be Primary Key for that table.

Next, on the Property Sheet for the Text Box control, under the Format tab change Property "Visible" to No.

Next, we're going to change the "Link Master Fields" and "Link Child Fields" properties on Subform2, which can be found on the Data tab of the Property Sheet:

For the "Link Master Fields" property, put in the Name of the Text Box control you made. An example of a default name will be Text5. I renamed my Text Box to CurrentElementKey, but name it whatever you want. So in my "Link Master Fields" property, I put CurrentElementKey.

For the "Link Child Fields" property, since we put the Primary Key for Subform1 in the Text Box, we need to put the Foreign Key it relates to in Subform2. I can't tell you exactly what this will look like, because it will vary for your scenario. For example, you might have had Element1 be Element1PK on the first Subform, and it's Foreign Key on Subform2 as Element1FK. So you'd put Element1FK in "Link Child Fields".

If you have any questions, or require further explanation, please comment on this answer, and I'll do my best to help.

Upvotes: 0

LiamH
LiamH

Reputation: 1502

Open your query for subform2 in design view. Then set the criteria to =forms![mainform]![subform1].form![element].

then in VBA you need to requery subform2 when the selected record in subform 1 is changed. Go in to the on current event of subform1 and use the following:

private sub Form_Current()
forms![mainform]![subform2].requery
end sub

N.B. you may need to change the name of mainform, subform and the column name which I called element.

Upvotes: 1

Sheils
Sheils

Reputation: 343

You can do that by changing the recordsource of Subform2 on the OnCurrent event of subform1. The steps to do that are as follows:-

  • Open the form in design view
  • Go to the properties of subform1
  • Go to the event tab
  • Select eventprocedure from the oncurrent combo
  • double click on the button next to the event to go to the vba window

  • insert the following code

    Private Sub Form_Current()

Me.Parent.Subform2.Form.RecordSource = "Select data,value From TableName Where data=" & Me.Data

End Sub

Upvotes: 3

Related Questions