Retrieve Forms Based on Data in Subform?

[WillW]

I have a form, "frmMain", from which I navigate to other forms based on the
line item (record) selected on frmMain. Selecting a row and clicking the
appropriate command button allows users to open new forms linked by (and
filtered on) the primary key, WorkId. This works fine in many cases.

However, I cannot figure out how to open forms where WorkId is available
only in a subform (again, filtered by the selected WorkId).

Any suggestions are welcome. Thanks in advance.

Bill

 

[Albert D. Kallal]

it should not change much.

If you look at the following screen shot, the results are in a sub-form.

http://www.members.shaw.ca/AlbertKallal/Search/index.html

the code behind the button is:

docmd.OpenForm "frmCustomers",,,"id = " & me!id

Take a look at how I added a button to the sub-form, and on a continues
form...they repeat...

So, if you actually place the button in the sub-form...much the same code
works

 

[WillW]

Thanks very much for your very quick response. I wonder if I made my
question clear enough, however.

In my case, the command button is opening a different form that contains a
subform. The PK (WorkId) I need to access is available only in the subform -
not in the form itself.

So, from frmMain, I want to open frmEvent, on which is a subform,
frmProgram, where frmMain.WorkId = frmProgram.WorkId.

Does your solution take this situation into account?

Thanks again.

 

[Albert D. Kallal]

So, from frmMain, I want to open frmEvent, on which is a subform,
frmProgram, where frmMain.WorkId = frmProgram.WorkId.

Does your solution take this situation into account?

Well, I was actually suggesting to move the button and code into the
sub-form.

If your design is such that you don't need/want to move the code + button to
the sub-form, then you can still pluck/grab the sub-form value.


so, assuming our main form is

frmMain, and sub-form is frmProgram

We want to open a frmEvent based on the current reocord selected in the
sub-form frmProgram.

the code is quite easy:

dim lngWrkID as long
me.frmProgram.form.Refresh ' only need this if editing can occur in
the sub-form...

lngWrkID = Me.frmProgram.form!WrkID
docmd.OpenForm "frmEvent",,,"WorkId = " & lngWrkID

 

[WillW]

Not quite --

frmMain has no subform.

Clicking a command button on frmMain opens frmEvent.

frmEvent has a subform, frmProgram, which contains WorkId.

I want to see only frmEvent where frmProgram.WorkId = frmMain.WorkId

Maybe I figure this out based on your previous help, but I'm not sure.

Thanks.

 

[Albert D. Kallal]

frmEvent has a subform, frmProgram, which contains WorkId.

I want to see only frmEvent where frmProgram.WorkId = frmMain.WorkId

Maybe I figure this out based on your previous help, but I'm not sure.

ah, ok....

You can attack this several ways.

docmd.OpenForm "frmEvent",,,,,,me!WorkID

In the frmEvent on-load event, go:

me.frmProgram.RecordSet.FindFirst "WorkID = " & me.OpenArgs



--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)



docmd.Open "frmEvent"

Then, in the fomrs on-load event