none
TempVars to link Forms RRS feed

  • Question

  • I'm using SetTempVar on the "on click event" in a form to open a related form.  This works great of there is only 1 expression.  My problem is I have a form based on a query where the field needing the SetTempVar has the possibility of opening 1 of 10 different forms.  Is there a simple way to set this criteria?

    Or is there an easier way to open a related form other than usine SetTempVar?

    Monday, August 14, 2017 5:13 PM

All replies

  • Or is there an easier way to open a related form other than usine SetTempVar?

    Hi FK_!234,

    When opening a related form, you can add a value in the OpenArgs argument of the OpenForm method.

    In the Open or Load event  of the opened form you can analyze the Me.OpenArgs property to decide what to do further.

    Imb.

    Monday, August 14, 2017 5:35 PM
  • Hi,

    I am not sure I understand the question/problem. How can SetTempVar a limitation in this case? Can you please elaborate on your process? Thanks.

    Monday, August 14, 2017 6:56 PM
  • Hi,

    I am not sure I understand the question/problem. How can SetTempVar a limitation in this case? Can you please elaborate on your process? Thanks.

    I have a field called "Serial Numbers" in a Continuous Form which is pulling from a query.  These serial numbers are associated with up to 10 different products.  Each product has it's own pop-up form with details.  I'm needing to be able to click a serial number and open the corresponding pop-up Product form.  

    If I SetTempVars and OpenForm for Product A, all of the serial #'s want to open the form for Product A.  I'm needing to know how to set multiple SetTempVars in the same form field to be able to distinguish and open the related Product Form (Product A, Product B, Product C, Product D, etc.).

    Monday, August 14, 2017 7:15 PM
  • Hi,

    Thanks for the additional information. Can you post a sample code showing how you're using SetTempVars and also how you're opening the form with it?

    Monday, August 14, 2017 7:38 PM
  • I have a field called "Serial Numbers" in a Continuous Form which is pulling from a query.  These serial numbers are associated with up to 10 different products.  Each product has it's own pop-up form with details.  I'm needing to be able to click a serial number and open the corresponding pop-up Product form.  

    Hi FK_1234,

    Clicking on a serial number could open a form with the associated products. Clicking on a product could then open then pop-up Product form.

    Imb.

    Monday, August 14, 2017 7:39 PM
  • Hi,

    Thanks for the additional information. Can you post a sample code showing how you're using SetTempVars and also how you're opening the form with it?

    I'm going to try to post an image:  

    Monday, August 14, 2017 7:42 PM
  • I have a field called "Serial Numbers" in a Continuous Form which is pulling from a query.  These serial numbers are associated with up to 10 different products.  Each product has it's own pop-up form with details.  I'm needing to be able to click a serial number and open the corresponding pop-up Product form.  

    Hi FK_1234,

    Clicking on a serial number could open a form with the associated products. Clicking on a product could then open then pop-up Product form.

    Imb.

    Each serial # is only associated with 1 product; so the above suggestion will not work.  When a serial # is clicked, it needs to open to a single-view form of the serial # with additional info about that specific product.  I'm open to any other suggestions.  Using SetTempVars is the only way I have found to do this.
    Monday, August 14, 2017 7:44 PM
  • Hi,

    Thanks for posting an image. According to your code, the value in the Serial Number field is stored in a TempVar and then a form called "HoistingSys_form" is opened to this specific serial number, correct? If so, are you asking how to identify which form (HoistingSys, etc.) to open based on the serial number?

    Monday, August 14, 2017 7:50 PM
  • Hi,

    Thanks for posting an image. According to your code, the value in the Serial Number field is stored in a TempVar and then a form called "HoistingSys_form" is opened to this specific serial number, correct? If so, are you asking how to identify which form (HoistingSys, etc.) to open based on the serial number?

    Yes, that is correct.  I tried using an If, Then statement, but since the source is a Union Query, I couldn't get it to work how I wanted.
    Monday, August 14, 2017 7:53 PM
  • Okay, do you have a table identifying which form to open for each serial number? For example, is each serial number specific to a particular product and each product corresponds to a particular form you want to open?

    Monday, August 14, 2017 7:59 PM
  • Okay, do you have a table identifying which form to open for each serial number? For example, is each serial number specific to a particular product and each product corresponds to a particular form you want to open?

    Yes, each product has it's own table (this is where the pop-up forms come from).  However, the form containing the SetTempVars and Serial Number are pulling from a union query; not a table.
    Monday, August 14, 2017 8:03 PM
  • Hi,

    I think I have solution for you... How about if you modify the UNION query to include the product or form name for each product table? Then, you should have the information you need for your TempVar, correct?

    Monday, August 14, 2017 8:10 PM
  • Hi,

    I think I have solution for you... How about if you modify the UNION query to include the product or form name for each product table? Then, you should have the information you need for your TempVar, correct?

    Well, this is about to become a little trickier...the union query I'm using is a union query combining two other union queries (for some reason a union query has a limit on select statements?) so the two original union queries have SELECT statements of which table the info is from but the combined union query does not (SQL View).
    Monday, August 14, 2017 8:21 PM
  • Hi,

    You should be able to "manually" add a column/field to the result of your UNION query. For example, let's say you have the following query:

    SELECT SerialNo FROM Product1
    UNION
    SELECT SerialNo FROM Product2
    UNION
    SELECT SerialNo FROM Product3

    All I am saying is modify it like so:

    SELECT "Product1" AS Product, SerialNo FROM Product1
    UNION
    SELECT "Product2", SerialNo FROM Product2
    UNION
    SELECT "Product3", SerialNo FROM Product3

    Hope it makes sense. Give it a try and let us now if it doesn't help.

    Cheers!

    Monday, August 14, 2017 8:30 PM
  • Each serial # is only associated with 1 product; so the above suggestion will not work.  When a serial # is clicked, it needs to open to a single-view form of the serial # with additional info about that specific product.  I'm open to any other suggestions.  Using SetTempVars is the only way I have found to do this.

    Hi FK_1234,

    You can open a recordset using a sql-string something like:

        "SELECT * FROM Product_tbl WHERE Serial_nr = "  & <clicked serial #>.

    I assume that you have a field in this record that identifies the product specification form.

    Now you can open the form with: DoCmd.OpenForm <fieldname from Product_tbl>

    Or do I still not get the problem?

    Imb.

    Imb.

    Monday, August 14, 2017 8:30 PM
  • I believe I already have what you are talking about.  Below are my 3 union queries.  The first two are what the 3rd one is combining.  The 3rd one is the control source for my form.

    Monday, August 14, 2017 8:41 PM
  • Hi,

    Unfortunately, I don't think you have the same one I was proposing. For example, I didn't see any column with literal values in it (with quotes). This literal column is the one you can use to identify which form to open. The result of the UNION query with literal values might look something like this:

    Product   | SerialNo

    Product1 | 12345
    Product1 | 54321
    Product2 | 56789
    Product2 | 98765
    Product3 | 24680
    Product3 | 86421
    Product3 | 94024

    Hope it helps... 

    Monday, August 14, 2017 8:49 PM
  • Hi,

    Unfortunately, I don't think you have the same one I was proposing. For example, I didn't see any column with literal values in it (with quotes). This literal column is the one you can use to identify which form to open. The result of the UNION query with literal values might look something like this:

    Product   | SerialNo

    Product1 | 12345
    Product1 | 54321
    Product2 | 56789
    Product2 | 98765
    Product3 | 24680
    Product3 | 86421
    Product3 | 94024

    Hope it helps... 

    Here are my headers from the datasheet view of the previous SQL views:  I have no problem creating the SetTempVars with the serial # column.  The problem lies with detecting the correct form to open.  

    Monday, August 14, 2017 9:41 PM
  • I see, okay, are the forms named the same as the product? If so, you could try something like:

    SetTempVars
      Name FormName
      Expression =[Product]

    SetTempVars
      Name TempProduct
      Expression =[Serial]

    OpenForm
      Form Name =TempVars!FormName
      Where Condition =[Serial #]=TempVars!TempProduct

    Hope it helps...

    Monday, August 14, 2017 9:58 PM
  • I see, okay, are the forms named the same as the product? If so, you could try something like:

    SetTempVars
      Name FormName
      Expression =[Product]

    SetTempVars
      Name TempProduct
      Expression =[Serial]

    OpenForm
      Form Name =TempVars!FormName
      Where Condition =[Serial #]=TempVars!TempProduct

    Hope it helps...

    This definitely does not work.
    Tuesday, August 15, 2017 3:19 PM
  • Hi,

    Sorry to hear that. Since I don't have a copy of your database, I can't do any testing. Are you able to share a demo database with dummy data?

    Tuesday, August 15, 2017 3:42 PM
  • Hi,

    Sorry to hear that. Since I don't have a copy of your database, I can't do any testing. Are you able to share a demo database with dummy data?

    Sorry, I can't.  The database is too complex, and I'm on a time crunch.  

    Maybe if we look at it from a different perspective?  Let's ignore the SetTempVar and say I need to create a Macro to say open this form or that form.  

    Tuesday, August 15, 2017 5:28 PM
  • Before that, can you specify what "does not work" mean? Did you get any errors? Remember, one assumption with the above code was the forms to open have the same name as the products. Is this assumption valid? If not, you'll have to modify the code to translate each product to its corresponding form.

    Okay, if we only had two forms to open, we can use an If statement to determine which form to open. If we had multiple forms, we'll need a way to identify which one to open. One approach I mentioned is either having a table to assign the form's name to open with each product (unless each form was named the same as the product, see above). In a macro, we can "look up" the name of the form based on the product from our query. Or better yet, include the name of the form in the query, so we don't have to look it up.

    Hope it helps...

    Tuesday, August 15, 2017 5:41 PM