Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

is there a way to automatically cancel the parameter message box?

Discussion in 'Information Technology' started by msmuzila@gmail.com, Jul 28, 2006.

  1. msmuzila@gmail.com

    msmuzila@gmail.com
    Expand Collapse
    Guest

    is there a way to automatically cancel the parameter message box on a
    form open?

    Thanks
    Matt
     
  2. Loading...


  3. BruceM

    BruceM
    Expand Collapse
    Guest

    Do you mean you want it not to show up at all? Details needed.

    <msmuzila@gmail.com> wrote in message
    news:1148991882.396577.310900@u72g2000cwu.googlegroups.com...
    > is there a way to automatically cancel the parameter message box on a
    > form open?
    >
    > Thanks
    > Matt
    >
     
  4. msmuzila@gmail.com

    msmuzila@gmail.com
    Expand Collapse
    Guest

    Yes, i want it to cancel to box automatically. So that it appears that
    the box never showed
     
  5. BruceM

    BruceM
    Expand Collapse
    Guest

    Remove the criteria from the query.

    <msmuzila@gmail.com> wrote in message
    news:1149022681.462457.45880@38g2000cwa.googlegroups.com...
    > Yes, i want it to cancel to box automatically. So that it appears that
    > the box never showed
    >
     
  6. msmuzila@gmail.com

    msmuzila@gmail.com
    Expand Collapse
    Guest

    I need to filter the selection to the specific combo box selection
     
  7. BruceM

    BruceM
    Expand Collapse
    Guest

    You need to provide details. I'm not going to guess what you mean. What
    selection? What combo box? If the parameter dialog box shows up when the
    form opens, the form's record source query has criteria. Eliminate the
    criteria and you will eliminate the dialog box.

    <msmuzila@gmail.com> wrote in message
    news:1149079006.329600.128770@f6g2000cwb.googlegroups.com...
    >I need to filter the selection to the specific combo box selection
    >
     
  8. msmuzila@gmail.com

    msmuzila@gmail.com
    Expand Collapse
    Guest

    this is what i put in the crosstab criteria for the parameter

    IIf(IsNull([forms]![Part_Monthly]![Combo36]),1010,[forms]![Part_Monthly]![Combo36])

    when i open my form it still asks me for
    [forms]![Part_Monthly]![Combo36]

    when i hit cancel on this message box, it goes to the correct spot, so
    is there a way to automatically cancel the parameter message box?
     
  9. BruceM

    BruceM
    Expand Collapse
    Guest

    What is "crosstab criteria for the parameter"? When you view the query in
    design view, is there something in the Criteria row? What is
    [Forms]![Part_Monthly]![Combo36]? Is it from a form you are using for the
    criteria? If so, the form needs to be open.
    [Forms]![Part_Monthly]![Combo36] is something the query can't find. What is
    it? Only you can answer that.
    It may be best if you post the SQL. To do that, open the query, click View
    > SQL, then copy and paste into a message.


    <msmuzila@gmail.com> wrote in message
    news:1149130304.662280.129390@c74g2000cwc.googlegroups.com...
    > this is what i put in the crosstab criteria for the parameter
    >
    > IIf(IsNull([forms]![Part_Monthly]![Combo36]),1010,[forms]![Part_Monthly]![Combo36])
    >
    > when i open my form it still asks me for
    > [forms]![Part_Monthly]![Combo36]
    >
    > when i hit cancel on this message box, it goes to the correct spot, so
    > is there a way to automatically cancel the parameter message box?
    >
     
  10. msmuzila@gmail.com

    msmuzila@gmail.com
    Expand Collapse
    Guest

    here is the sql

    PARAMETERS [forms]![Part_Monthly]![Combo36] Long;
    TRANSFORM Sum([QTY_SHIP]*[QUANTITY]) AS [Parts Shipped]
    SELECT Month([DATE]) AS Months
    FROM (ORDER_DETAILS INNER JOIN PRODUCT_PARTS ON ORDER_DETAILS.PRODUCT =
    PRODUCT_PARTS.PRODUCT) INNER JOIN Invoices ON ORDER_DETAILS.ORDER_ID =
    Invoices.INV_NO
    WHERE (((Year([DATE]))>Year(Now())-6) AND
    ((PRODUCT_PARTS.PART_ID)=IIf(IsNull([forms]![Part_Monthly]![Combo36]),1010,[forms]![Part_Monthly]![Combo36])))
    GROUP BY Month([DATE])
    ORDER BY Month([DATE]), Year([DATE])
    PIVOT Year([DATE]);

    The form is Part_monthly and the Combo box on that form is Combo36,
    which is the parameter for the query It works fine once it is open. But
    the intial open is the problem because the combo has no value yet.
     
  11. BruceM

    BruceM
    Expand Collapse
    Guest

    I had imagined that Part_Monthly was a form from which you opened another
    form. Now it appears that you are trying to open Part_Monthly. If so, what
    is the point of the criteria? If I am mistaken, and Part_Monthly is a form
    from which you are opening another form, it needs to stay open while the
    other form is opening.

    <msmuzila@gmail.com> wrote in message
    news:1149165362.389886.115020@j55g2000cwa.googlegroups.com...
    > here is the sql
    >
    > PARAMETERS [forms]![Part_Monthly]![Combo36] Long;
    > TRANSFORM Sum([QTY_SHIP]*[QUANTITY]) AS [Parts Shipped]
    > SELECT Month([DATE]) AS Months
    > FROM (ORDER_DETAILS INNER JOIN PRODUCT_PARTS ON ORDER_DETAILS.PRODUCT =
    > PRODUCT_PARTS.PRODUCT) INNER JOIN Invoices ON ORDER_DETAILS.ORDER_ID =
    > Invoices.INV_NO
    > WHERE (((Year([DATE]))>Year(Now())-6) AND
    > ((PRODUCT_PARTS.PART_ID)=IIf(IsNull([forms]![Part_Monthly]![Combo36]),1010,[forms]![Part_Monthly]![Combo36])))
    > GROUP BY Month([DATE])
    > ORDER BY Month([DATE]), Year([DATE])
    > PIVOT Year([DATE]);
    >
    > The form is Part_monthly and the Combo box on that form is Combo36,
    > which is the parameter for the query It works fine once it is open. But
    > the intial open is the problem because the combo has no value yet.
    >
     
  12. msmuzila@gmail.com

    msmuzila@gmail.com
    Expand Collapse
    Guest

    Part_Ship_Monthly is the crosstab query that generates the information
    for Part_Monthly, a form.
     
  13. BruceM

    BruceM
    Expand Collapse
    Guest

    Then you need to eliminate the criteria, or at least change it. Setting the
    criteria to something within the criteria is meaningless.

    I have suggested several times that you remove the criteria. Go to query
    design view, and clear the criteria that references the combo box. Have you
    done so?

    <msmuzila@gmail.com> wrote in message
    news:1149252494.817372.257160@g10g2000cwb.googlegroups.com...
    > Part_Ship_Monthly is the crosstab query that generates the information
    > for Part_Monthly, a form.
    >
     
  14. Danny J. Lesandrini

    Danny J. Lesandrini
    Expand Collapse
    Guest

    I haven't been following this thread closely enough, so maybe I'm off base,
    but from what Bruce suggested, I think my idea might help. The issue is this:

    * You have a query that is based on values on a form
    * Sometimes the query runs when the form is open
    * Sometimes not, but you want it to run all the same.

    DON'T base the criteria on the form controls! Base it on a function, and
    allow the function to look up the value.

    Why? Because the function can handle the error of the form not being open.

    Instead of criteria like this:
    IIf(IsNull([forms]![Part_Monthly]![Combo36]),1010,[forms]![Part_Monthly]![Combo36])

    It will look like this:
    GetCombo36(1010)

    Then, you create a Function to do the logic, like this:

    Public Function GetCombo36(ByVal lngID As Long) As Long
    On Error Resume Next
    Dim lngCombo36 As Long

    ' You'll need an IsLoaded or equivilent function
    If IsLoaded("Part_Monthly") Then
    lngCombo36 =[forms]![Part_Monthly]![Combo36]
    Else
    lngCombo36 =lngID
    End If

    GetCombo36 = lngID
    End Function

    If the query parameter is using a function, then it will always work.
    --

    Danny J. Lesandrini
    dlesandrini@hotmail.com
    http://amazecreations.com/datafast


    "BruceM" <bamoob@yawwhodawtcalm.not> wrote ...
    > Then you need to eliminate the criteria, or at least change it. Setting the criteria to something within the criteria
    > is meaningless.
    >
    > I have suggested several times that you remove the criteria. Go to query design view, and clear the criteria that
    > references the combo box. Have you done so?
    >
    > <msmuzila@gmail.com> wrote in message news:1149252494.817372.257160@g10g2000cwb.googlegroups.com...
    >> Part_Ship_Monthly is the crosstab query that generates the information
    >> for Part_Monthly, a form.
    >>

    >
    >
     
  15. msmuzila@gmail.com

    msmuzila@gmail.com
    Expand Collapse
    Guest

    where do i place the function, does it go in a module? or on the form.
     
  16. Danny J. Lesandrini

    Danny J. Lesandrini
    Expand Collapse
    Guest

  17. msmuzila@gmail.com

    msmuzila@gmail.com
    Expand Collapse
    Guest

    getting a debug error on the isLoaded function, should i use a
    different one?
     
  18. Danny J. Lesandrini

    Danny J. Lesandrini
    Expand Collapse
    Guest

    Yes, I mentioned that you'd need an IsLoaded function. If you're using Access 2002, then
    you can use this built-in function ...

    CurrentProject.AllForms("frmMyForm").IsLoaded

    Otherwise, you may need your own function ...

    Public Function IsLoaded(ByVal strFormName As String) As Integer
    Const conObjStateClosed = 0
    Const conDesignView = 0
    If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
    If Forms(strFormName).CurrentView <> conDesignView Then
    IsLoaded = True
    End If
    End If
    End Function


    --

    Danny J. Lesandrini
    dlesandrini@hotmail.com
    http://amazecreations.com/datafast


    <msmuzila@gmail.com> wrote in message news:1149855812.061469.141400@u72g2000cwu.googlegroups.com...
    > getting a debug error on the isLoaded function, should i use a
    > different one?
    >
     

Share This Page