How find a record on a form based on a record of another form

[OOzy]

Dears,

Before I ask my question, I would like to tell you what I have and what I am trying to do. I have a main form “MAIN” that contains a subform named Sub1. Sub1 is connected to tbl1, I would like to do the following:

When I click on sub1 record1, I would like a Form “formB” to be opened (which I know how to do) then based on the content of sub1:record1 FormB should show that record.

You might have guessed that sub1 (tbl1) contains keys to other information in FormB.


I really hope that it is clear. Can anyone show how to do so?

 

[SarahG]

Create a new query based on Tbl1. Add criteria to this query so that it pulls out the currently selected record. I presume you have an ID field of some sort in Tbl1, so make the criteria for this field like so:
=Forms!Main.subformcontrolname.form!textcontrolname
where textcontrolname is the name of the control on the subform which holds the ID field you are interested in.

Now make this new query the recordsource of formB.
Hope this helps.

 

[ALEKSJAY]

create the " open form " button with the wizard. One of the options when you select the button to open a new form is that it will ask you if you want to show all the records, or if you want to show specific data to display, based on a field in the current form. Choose that option and set the values as needed.

 

[Zion7]

I agree with both Sarah & AleksJay, but for the sake of simplicity, I might lean towards AleksJay's.


Assuming both forms have same recordsource...
DoCmd.OpenForm "FormB",,,"pkPrimaryKey =" & Me.PrimaryKey

Assuming forms are linked by a Primary & foreign key...
DoCmd.OpenForm "FormB",,,"fkForeignKey =" & Me.PrimaryKey

either way, I assume you get the idea. Any criteria, you chose to filter with, will work.
The first part "fkForeignKey=", refers to FormB.
2nd part, refers to calling form.
Any criteria that relates, will work.

Hope this helps, good luck!