By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,640 Members | 2,077 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,640 IT Pros & Developers. It's quick & easy.

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

kcdoell
100+
P: 230
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…
Apr 22 '08 #1
Share this Question
Share on Google+
4 Replies


ADezii
Expert 5K+
P: 8,634
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.
    Expand|Select|Wrap|Line Numbers
    1. Public varCombo1Value As Variant
    2. Public varCombo2Value As Variant
    3. 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.
    Expand|Select|Wrap|Line Numbers
    1. varCombo1Value = Me![cboCombo1]
    2. varCombo2Value = Me![cboCombo2]
    3. 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:
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Form_Open(Cancel As Integer)
    2. Dim strSQL As String
    3.  
    4. strSQL = "Select * From Employees Where [FirstName] = '" & varCombo1Value & "' And [LastName] = '" & varCombo2Value & "'  And [City] = '" & varCombo3Value & "';"
    5.  
    6. Me.RecordSource = strSQL
    7. End Sub
Apr 23 '08 #2

NeoPa
Expert Mod 15k+
P: 31,487
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.
Apr 23 '08 #3

kcdoell
100+
P: 230
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):
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdTarget_Click()
  2. 'first hide the forecast form (we need the values for TargetAccts' form query)
  3.     Forms!Forecast.Visible = False
  4. 'then Open the target accounts form
  5.     DoCmd.OpenForm ("TargetAccts")
  6. End Sub
  7.  
Thanks to both of you!

Keith.
Apr 23 '08 #4

NeoPa
Expert Mod 15k+
P: 31,487
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 :)
Apr 23 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.