Multiple Instance of Form, problem with Recordsource query

[markjenkinsrf]

I am trying to open multiple instances of the same form, using the Allen Brown method. I have a master form that contains the Brown prorgamming, which will sucesfully open many copies of the desired form. This form called Layout, uses a query, LayoutQuery, for its Recordsource, this query has two criteria from listboxes on the master form. The query is opened by the master form

When Layout(1) opens, it uses the records from the LayoutQuery. Here the user can look at data from the query with Dlookup calls to the query.

When Layout(2) is opened from the master, it now has the same Layoutquery but with new criteria. This is all working fine.

The problem is, if I return to Layout(1), the Layoutquery has changed to the the Layoutquery(2) and the records are now different than from the original Layoutquery(1).

How can I keep Layout(1) syncronized with Layoutquery(1) after the 2nd form is opened?

I thought about using a Recordset, but I understand that won't work with Dlookups.

Thanks for any help.

 

[Roku]

I am trying to open multiple instances of the same form, using the Allen Brown method.

What is the 'Allen Brown' method? Is it relevant here?

The quick answer to your question is to use different record sources for each instance of the form. How you achieve that depends upon the relationships between forms, their controls and tables.

Perhaps you can save the initial query string from LayoutQuery in the Form_Open or Form_Deactivate event, then restore that string as the form's record source in the Form_Activate event? That can cater for form switching, but it can also be unwieldy if you have complex relationships.

If this doesn't help, please provide more specific details of the problem.

 

[markjenkinsrf]

Roku:

Thanks for the response. The Allen Browne (my misspelling the first time) seems to be the defacto method of of creating multiple instances of the same form. It uses Collection method to keep track of all the open forms. And it's working fine.

I think your thinking is correct, need to have the Master form create a separate Query for each Layout form that is opened, but I'm not sure how to do that and then pass the info about that Query to the Layout form, and have it use that new Query for it's recordsource. The Layout form uses the query to extract data from the query for display purposes only, no new data or updates. Tons of Dlookups.

Maybe you may have an example how to create the LayoutQuery with a new name and link it to the Layout form ?

thanks again for your assistance

Mark

 

[Roku]

Please post the SQL for your LayoutQuery. In the Query designer, go to SQL view and copy what's there.

Are the listbox contents static for the duration of a session (i.e. when the DB is open)? If they are, then perhaps saving the index to the item relevant to each form might be a way to control the query.

 

[markjenkinsrf]

Roku

Here is the Layout Query SQL from my DB

SELECT ChannelBank.ID, ChannelBank.CBSite, ChannelBank.CBLocation, ChannelBank.CardLocation, ChannelBank.InternalCard, ChannelBank.PortNum, ChannelBank.CktNum, ChannelBank.T1Num, ChannelBank.ChanNum, ChannelBank.TermLoc, ChannelBank.TermBlock, ChannelBank.Term_T, ChannelBank.Term_T1, ChannelBank.Term_R, ChannelBank.Term_R1, ChannelBank.Term_E, ChannelBank.Term_M, ChannelBank.XCLoc, ChannelBank.XCBlock, ChannelBank.XC_T, ChannelBank.XC_T1, ChannelBank.XC_R, ChannelBank.XC_R1, ChannelBank.XC_E, ChannelBank.XC_M, ChannelBank.PwrAFuse, ChannelBank.PwrBFuse, ChannelBank.DwgReference
FROM ChannelBank
WHERE (((ChannelBank.CBSite)=[Forms]![frmMasterCBSelector]![lstMasterCBSite]) AND ((ChannelBank.CBLocation)=[Forms]![frmMasterCBSelector]![lstMasterCBLocation]));

This Query gets its records from a Table "ChannelBank" but the two lstboxes
lstMasterCBSite and lstMasterCBLocation get there inputs from a diferent table.

I'm not sure how to know what the lifetime of the lstboxes contents would be ?

thanks

Mark

 

[Roku]

The volatility of ListBox content is fundamental to the solution to the problem. For now, I shall assume that neither ListBox on the Master form changes its content within the time-frame during which you switch between instances of the Layout form.

The solution I propose is that you take a local copy of each ListBox item when the new form is opened and use that to manage the record selection within the form. This solution doesn't cater for changing ListBox contents in Master whilst the Layout form is open, but it would be possible to adapt for this if necessary.

Here are the steps – all in the Layout form.

1. add two TextBoxes to the Layout form (let's call them 'txbMasterCBSite' and 'txbMasterCBLocation'). You can put them anywhere convenient in Header, Detail or Footer sections. Set Visible=No. You could set Height, Left, Top & Width=0 as well if you want them both to be in a known location. (you can leave them visible with standard height & width for testing, just to see that they change as expected).

2. change your SQL in the Form Data tab:

SELECT * FROM ChannelBank;

As you see, I have substituted the field names list with *, which may or may not be correct to your needs. The SQL here can be as you want/need it; the important point is that the WHERE clause is omitted.

3. add a Form_Open event-handler:

[COLOR=black][FONT=Arial]Private Sub Form_Open(Cancel As Integer)[/FONT][/COLOR]
[COLOR=black][FONT=Arial][COLOR=black]Rem Retrieve the ListBox items from the Master form[/COLOR][/FONT]
[FONT=Arial]Me.txbMasterCBSite = [Forms]![frmMasterCBSelector]![lstMasterCBSite][/FONT][/COLOR]
[COLOR=black][FONT=Arial]Me.txbMasterCBLocation = [Forms]![frmMasterCBSelector]![lstMasterCBLocation][/FONT][/COLOR]
[COLOR=black][FONT=Arial][COLOR=black]Rem Check content is valid and set filter string if OK[/COLOR][/FONT]
[FONT=Arial]If Not (Me.txbMasterCBSite = "" Or Me.txbMasterCBLocation = "") Then[/FONT][/COLOR]
[COLOR=black][FONT=Arial]Me.Filter = "CBSite='" & txbMasterCBSite & "' AND CBLocation='" & txbMasterCBLocation & "'"[/FONT][/COLOR]
[COLOR=black][FONT=Arial]Me.FilterOn = True[/FONT][/COLOR]
[COLOR=black][FONT=Arial]End If[/FONT][/COLOR]
[FONT=Arial][COLOR=black]End Sub[/COLOR][/FONT]

The Form_Open event retrieves the current ListBox values. It checks that neither TextBox is empty, then sets the filter to use the values.

You might need to add some more robust error-checking, but the principle works OK in my tests.

See if this does what you need. If not, let us know – with relevant details.

 

[markjenkinsrf]

Roku: Thanks again for your input. I used some of your suggestions to solve this problem.

Main issue was after opening the 2nd form based on new criteria, CBSite and CBLocation, the query was no longer matching the 1st form opened. The only reason to open the 2nd form was too change to a different Site/Location, so I had to accomodate that.

My solution was as follows.

1. Transfer MasterCBSite and MasterCBLocation to the new Layout form when form opened.
2. Created a temporary query(1) from the layout form that used the Site/Location data, and incorpoated the form's Hwnd number into the name of the query.
3. Use this query for the form that created it.
4. When openeing 2nd Form(2), created another query(2) with a different name, using the 2nd forms Hwnd.
5. The important bit: When jumping back to Form(1) anytime I need to reference query(1), I had to redefine the query name at the start of any procedure that needed to access the query. If I didn't redefine the name, it would use query(2) instead of query(1)

After 4 days of thinking, writing, testing, it finally works.

Thanks again for your assistance.

Mark

 

[Roku]

Glad it worked out

There's no substitute for thinking - especially over 4 days!