Keep filtered recordset between 2 forms

[YoungLemon]

I have two forms with controls that retrieve a subset of all of the records
in a table. I would like put a command button on one form that will take me
to the other while retaining the recordset (or the filtered set of
records)...?

 

[Mike Painter]

I have two forms with controls that retrieve a subset of all of the
records in a table. I would like put a command button on one form
that will take me to the other while retaining the recordset (or the
filtered set of records)...?

You can pass the recordset the first form is based on to the second one with
the proper arguement in doCmd.OpenForms

 

[YoungLemon]

Yes, I am looking for the correct syntax to do this within the docmd. I will
first use the docmd to close the current window with accmdclosewindow. But I
cant get the correct syntax for opening the next form with the same
recordset. So, yes, the syntax for this command is what I need. When I try
docmd.openform, me.recordset = forms!nextform!recordset this acts like a
parameter query and prompts the user for info.

 

[YoungLemon]

So, this is what ended up working for me...

Private Sub Command_Click()
Dim InApFrm As String
Dim stLinkCriteria As String

InApFrm = "TheFormYouWantToOpen"
stLinkCriteria = "[FIELD_A]= '" & Forms![FormYoureCurrentlyOn]![FIELD_A]
& "' and [FIELD_B] ='" & Forms![FormYoureCurrentlyOn]![FIELD_B] & "'"

DoCmd.OpenForm InApFrm, , , stLinkCriteria
Exit Sub

I put the above in the 'OnClick' of the created command button placed on the
form I was currently on, to get to the form I wanted, while viewing the same
records

Yes, I am looking for the correct syntax to do this within the docmd. I will
first use the docmd to close the current window with accmdclosewindow. But I
cant get the correct syntax for opening the next form with the same
recordset. So, yes, the syntax for this command is what I need. When I try
docmd.openform, me.recordset = forms!nextform!recordset this acts like a
parameter query and prompts the user for info.

Subject: Re: Keep filtered recordset between 2 forms 5/2/2005 11:12 PM PST

By: Mike Painter In: microsoft.public.access

I have two forms with controls that retrieve a subset of all of the
records in a table. I would like put a command button on one form
that will take me to the other while retaining the recordset (or the
filtered set of records)...?

You can pass the recordset the first form is based on to the second one with
the proper arguement in doCmd.OpenForms

 

[YoungLemon]

Here is a previous post that would have helped if I knew what I was asking....

Answer
Subject: Re: How do I set a "drill down" on a specific cell in one form? So
ca. 11/25/2004 7:41 PM PST

By: Dirk Goldgar In: microsoft.public.access

I am simply trying to set up a drill down of data or a cell in a form.
The newly opened form will display data that adds up to or supports
the initial cell that was double clicked.

So the idea is that when you double-click this control (not a "cell",
that's Excel-speak"), another form will open that shows the supporting
detail?

First you need to be able to identify, from information in the current
record (the one you double-clicked), which detail records correspond to
it. Next you need to build a query that shows the necessary detail,
though not necessarily restricted to just the corresponding records, and
use this query as the RecordSource for a popup form. Then you need to
create an event procedure for this control's DblClick event that opens
that form while applying a WhereCondition argument to limit its
recordsource to just the relevant records. For example,

Private Sub txtBranchTotal_DblClick(Cancel As Integer)

Dim stLinkCriteria As String

If IsNull(Me.BranchOfficeID) Then Exit Sub

stLinkCriteria = "BranchOfficeID = " & Me.BranchOfficeID

DoCmd.OpenForm "frmBranchDetails", _
WhereCondition:=stLinkCriteria

End Sub

That's just an example, of course.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

So, this is what ended up working for me...

Private Sub Command_Click()
Dim InApFrm As String
Dim stLinkCriteria As String

InApFrm = "TheFormYouWantToOpen"
stLinkCriteria = "[FIELD_A]= '" & Forms![FormYoureCurrentlyOn]![FIELD_A]
& "' and [FIELD_B] ='" & Forms![FormYoureCurrentlyOn]![FIELD_B] & "'"

DoCmd.OpenForm InApFrm, , , stLinkCriteria
Exit Sub

I put the above in the 'OnClick' of the created command button placed on the
form I was currently on, to get to the form I wanted, while viewing the same
records

Yes, I am looking for the correct syntax to do this within the docmd. I will
first use the docmd to close the current window with accmdclosewindow. But I
cant get the correct syntax for opening the next form with the same
recordset. So, yes, the syntax for this command is what I need. When I try
docmd.openform, me.recordset = forms!nextform!recordset this acts like a
parameter query and prompts the user for info.

Subject: Re: Keep filtered recordset between 2 forms 5/2/2005 11:12 PM PST

By: Mike Painter In: microsoft.public.access

I have two forms with controls that retrieve a subset of all of the
records in a table. I would like put a command button on one form
that will take me to the other while retaining the recordset (or the
filtered set of records)...?

You can pass the recordset the first form is based on to the second one with
the proper arguement in doCmd.OpenForms