Filtering a form

[lovelydan]

I have designed a form(main form) which has a record source that runs subforms. this works perfectly. I also have a macro that is attached to this subform which filters records on a different form. One of my subforms contains personnel IDs and few details. When I click on the ID field the macro runs and opens another form, filtering it by the ID number. This works when the subform is opened independently but when the subform is opened on a record source frame and you try to click on the id field you get an error message that Ms access can't find the subform referred to in a macro.

How can I filter using a code? I tried using DoCmd.penform command and it works, but I fail to come up with code that can pick the id number from the subform and opens the other form filtering it by the same ID. I need help.

[nico5038]

In general when wanting to have two subforms synchronized I use a (hidden) textfield on the main form.
In the first subform I add in the OnCurrent event a line like:

Parent.txtPersID = Me.PersID
 

This will fill the proper ID, next I link the other subform (just single click to get the LinkageFields property) to this txtPersID and when changing the first subform's row, the other subform will show the related Persons record.

Getting the idea ?

Nic;o)

[lovelydan]

Nic;

i do get the idea and i know this would work with subforms..but what iam trying to do is to open a new form from a subform not opening another subform. so i dont know if the Parent. expression will work.

[nico5038]

OK, for referring to fields in a subform you need to use qualificaton like described in http://access.mvps.org/access/forms/frm0031.htm
This, combined with the docmd.openform with a filter, would do the trick.

Nic;o)

[NeoPa]

Dan,

What Nico has posted covers forms, subforms and their interrelationships well, but if as you say, you are working with a form which is separate from the current form, but invoked by that same current form, then you simply need a reference to that separate form. If the separate form was invoked from code within the current form then it must know the name of that form at least, if it doesn't maintain an object reference to it. Using an object reference is even easier of course, but if only the name is available then Forms({Name}) gives the same reference to the form. From there what you want to reference is down to you (and should be pretty straightforward).

[lovelydan]

If Text_EmployeeID is the name of the textbox that contains the employee Id in the subform and Frm_Employee_Details is a form that displays the details about an employee, i have used:

DoCmd.OpenForm "Frm_Employee_Details", , , "empID=" & Me.Text_EmployeeID.Value

but i get a data type mismatch error 3464.

[lovelydan]

thanx i managed.... hope you guys enjoy your day