Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    how to pass a global value (Access 2000)

    I have spent a whole daying trying to resolve a very tricky and devious problem.I cannot pass the golbal value city in case i give a condition.
    The condition i have given is the following
    OnOpen event of a report:
    If Forms![FBenchmark]![Office].DefaultValue = False Then
    Me.RecordSource = strAll
    Else
    Me.RecordSource = strBas

    What is funny i receive no errors,but i get the results for the first office, as if Forms![FBenchmark]![Office] = 1,
    regardless whether i choose office 2 or 3 etc. If i remove the condition, and write only Me.RecordSource = strBas, then everythiing is OK.
    Just to explain myself.I need to have the figures of the sales for all the offices but i cannot achieve it throught the global value city.

    Below is my whole code in the OnOpen event of the report:


    Dim city As Long
    city = Forms![FBenchmark]![Office]

    Dim strBas As String
    Dim strAll As String
    Dim sqlafid As String

    sqlafid = "AND ((Customers.afid)= " & city & ")"

    ' if i choose an option then show the results for that office
    strBas = " SELECT DISTINCTROW Orders.OrderID, Sum([Order Details].[UnitPrice]*[Quantity]) AS Subtotal, Orders.customerid, Orders.orderdate, Sum([Order

    Details].cartons) AS SumOfcartons, Sum([Order Details].Quantity) AS SumOfQuantity, Sum([Order Details].liters) AS SumOfliters, customers.CompanyName,

    customers.afid " & _
    " FROM customers INNER JOIN (Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON customers.Customerid = Orders.customerid

    " & _
    "GROUP BY Orders.OrderID, Orders.customerid, Orders.orderdate, customers.CompanyName, customers.afid, Orders.paymentid " & _
    " HAVING (((Orders.customerid) " & strNotIn & sqlafid & " AND ((Orders.paymentid)=False))"
    =============

    ' if i do no choose an option then show all the results for all office, and it is achieved by omiting sqlafid
    strAll = " SELECT DISTINCTROW Orders.OrderID, Sum([Order Details].[UnitPrice]*[Quantity]) AS Subtotal, Orders.customerid, Orders.orderdate, Sum([Order

    Details].cartons) AS SumOfcartons, Sum([Order Details].Quantity) AS SumOfQuantity, Sum([Order Details].liters) AS SumOfliters, customers.CompanyName,

    customers.afid " & _
    " FROM customers INNER JOIN (Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON customers.Customerid = Orders.customerid

    " & _
    "GROUP BY Orders.OrderID, Orders.customerid, Orders.orderdate, customers.CompanyName, customers.afid, Orders.paymentid " & _
    " HAVING (((Orders.customerid) " & strNotIn & " AND ((Orders.paymentid)=False))"

    If Forms![FBenchmark]![Office].DefaultValue = False Then
    Me.RecordSource = strAll
    Else
    Me.RecordSource = strBas
    End If


    May be somebody might help me out of my endless and futile efforts.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: how to pass a global value (Access 2000)

    Sorry, but I don't understand why you're testing the default value of the form control. You would ordinarily test the value. The default value is a property you set for new records, but it doesn't ordinarily change between records and it is only meaningful on a bound control.
    Charlotte

  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how to pass a global value (Access 2000)

    thank you indeed for the wish help.s.Default value was one of the many unsuccessful efforts.But equally,if i put only
    If Forms![FBenchmark]![office] = False, without the Default.
    it doesnt work either

    I will be grateful for any help

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: how to pass a global value (Access 2000)

    What type of control is Forms![FBenchmark]![Office] .
    It seems to return a number, yet you are testing for it being false.

    I would put a pair of radio buttons ( an option group) saying something like "all offices" and "selected office", and if "selected office" were chosen, then a combobox of offices would be visible for an office to be selected.

    So the test would be
    if Forms![FBenchmark]![Alloffices] = false then etc

    ps what has this got to do with global values?
    Regards
    John



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •