Losing my query parameters when my form closes that are needed for another form.....

[kcdoell]

I have a form (Form1) that displays records based on 3 unbound combo boxes that are also on my form. My record source for this form is a query “ReQryForecast” (which looks at those combo boxes via the criteria setting of that query). If changes are made to one of the combo boxes, the records displayed will change via my simple code in the after update event of my unbound combo box.

This works fine….

Now I have a command button on Form1 that will launch a new form (Form2) and at the same time close Form1. Form2 also has a record source set to a query “QryTarget” with 3 unbound combo boxes that feed off of it. What I am trying to do use the same parameters that were chosen/displayed in Form1 before it closed. My problem is that I am losing the parameters (in the 3 unbound combo boxes), because I already closed Form1.

Is there a way to retain the choices displayed in the combo boxes of Form1 before I close it, so that it can be used for my Form2 query?

Any ideas would be great.

Thanks,

Keith.

P.S. In the past, I would leave Form 1 open and hide it by having Form2 created as a bigger form. This time, I can not do that. So there in lies my problem that I was using a trick to get by………

Stuck…

[ADezii]

I have a form (Form1) that displays records based on 3 unbound combo boxes that are also on my form. My record source for this form is a query “ReQryForecast” (which looks at those combo boxes via the criteria setting of that query). If changes are made to one of the combo boxes, the records displayed will change via my simple code in the after update event of my unbound combo box.

This works fine….

Now I have a command button on Form1 that will launch a new form (Form2) and at the same time close Form1. Form2 also has a record source set to a query “QryTarget” with 3 unbound combo boxes that feed off of it. What I am trying to do use the same parameters that were chosen/displayed in Form1 before it closed. My problem is that I am losing the parameters (in the 3 unbound combo boxes), because I already closed Form1.

Is there a way to retain the choices displayed in the combo boxes of Form1 before I close it, so that it can be used for my Form2 query?

Any ideas would be great.

Thanks,

Keith.

P.S. In the past, I would leave Form 1 open and hide it by having Form2 created as a bigger form. This time, I can not do that. So there in lies my problem that I was using a trick to get by………

Stuck…

There are several Methods that you can use, this is just one of them.

1. In a Standard Code Module, declare 3 Public Variables to represent the 3 values of the Combo Boxes on Form1. Since I have no idea of what Data Type(s) are involved, I declared them as Variants.
   

   Public varCombo1Value As Variant
   Public varCombo2Value As Variant
   Public varCombo2Value As Variant

2. Prior to Form1 closing, place the Values of these Combo Boxes into the previously declared Variables, substituting your own Control names.
   

   varCombo1Value = Me![cboCombo1]
   varCombo2Value = Me![cboCombo2]
   varCombo3Value = Me![cboCombo3]

3. In the Open() Event of Form2, dynamically build the SQL String and assign it to the Record Source of Form2. Assuming all values are Strings, an example would be something similar to:
   

   Private Sub Form_Open(Cancel As Integer)
   Dim strSQL As String
    
   strSQL = "Select * From Employees Where [FirstName] = '" & varCombo1Value & "' And [LastName] = '" & varCombo2Value & "'  And [City] = '" & varCombo3Value & "';"
    
   Me.RecordSource = strSQL
   End Sub

[NeoPa]

In your calling form, instead of closing after opening the new one, simply hide your(it)self (Me.Visible = False).

That way, you can either open it again later, or pass the name of the calling form to the new form via OpenArgs and let all called forms try to make the calling form visible again just prior to closing itself.

Opening it again later will simply select it. It won't (I believe) rerun the Form_Open() procedure.

[kcdoell]

ADezil:

I liked your method and is what I was thinking about. I knew there must be a method of storing the variables and using them for future reference... Thanks for your insight and knowledge on the subject.

Because I am pressed for time, I did use NeoPa's down and dirty suggestion (visible = true or false.). It seems to be working so far for what I need it to do. In the end, the following code is what I used, inserted in the "On Click" event of my command button (Form1 = Forecast & Form2 = TargetAccts):

Private Sub cmdTarget_Click()
'first hide the forecast form (we need the values for TargetAccts' form query)
    Forms!Forecast.Visible = False
'then Open the target accounts form
    DoCmd.OpenForm ("TargetAccts")
End Sub
 

Thanks to both of you!

Keith.

[NeoPa]

Multiple ideas always seem to leave food for thought anyway Keith.

I use the hiding of active forms as a standard in all my databases anyway, but I also save data in publicly accessible variables when the need arises.

Glad we could help anyway :)