Create recordset after load

[JString]

To any and all who are willing to help...

My project contains two forms that I'm having some trouble with. Form1
pulls a snapshot recordset from the back-end and assigns it to a local
listbox that is used for browsing through the records. Form2 is set to
'Single Form' view and contains two subforms which display related data and
the underlying tables for both subforms have a many-to-one relationship with
the table for Form2.

I need to code the listbox in Form1 that contains the recordset so that when
a record is double-clicked, Form2 is opened with a one-record recordset
containing this record.

I've had some success by using the docmd.openform method with its 'where'
parameter set to filter out a single record, but when Form2 is opened, the
user has the option to click the 'Remove Filter' button to load all records.
This is something that I don't want the user to be able to do.

If I try opening Form2 first (without a recordsource) and then set its
'RecordSource' property to a new SQL statement, I run into the 'Enter
Parameter Value' prompt because of its linked subforms.

Does anybody know of a better way to do this?

Thanks in advance.

 

[tina]

if Form1 stays open while Form2 is open AND if Form2 is always opened from
Form1, then you can use the listbox control in Form1 as criteria on the
RecordSource of Form2. if Form2's RecordSource is not already a query, then
create a query, and add criteria to the relevant field, as

Forms!Form1!ListboxControlName

alternately, or in addition to the above, you can add a custom menu and
toolbar to the form, that do not include the Remove Filter option.

hth

 

[JString]

I hadn't thought of that, and it would probably be a good solution. Thanks!

However I did figure out a way to get it to work, even though it's a bit
unorthodox. I ended up removing the 'recordsource' properties of the two
subforms linked to Form2. In the open event of Form2, first Form2's
recordsource is set to a new SQL statement based on Form1's data, and then
the subforms' recordsources are set to their permanent queries. This ends
up bypassing the 'Enter Parameter' prompt that I was running into, but it
just feels improper for some reason

 

[JString]

I just tried your method and it works great, and it's alot less complicated!

 

[tina]

glad it worked for you.

I just tried your method and it works great, and it's alot less complicated!