Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Expression doesn't work (Access XP)

    I have an expression with subtracts Quantity Taken from Quantity Received to = Units on Hand as follows Expr1: Sum([QuantityIn]+[QuantityOut]) This expression does what I want it to do if I enter the values directly into my Inventory Transaction Table, however, if I enter the transactions using the form I've created the expression doesn't work (eg. values from the form will give a result as follows QuantityIn = 10, QuantityOut = -5 Units on Hand = 10 where as if I enter the same values (10, -5) directly into the Inventory transaction table, the Units in Hand will be right (5). What do I need to change on the form?

    Thanks in advance for any help.

    Christa

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Expression doesn't work (Access XP)

    There is not enough detail to determine what is wrong.
    Where do you have the calculation? Do you refresh the form after the calc?

  3. #3
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Expression doesn't work (Access XP)

    The calculation is in a query and since I don't know what you mean by "refresh the form"...I probably don't. How would I do that? (Sorry, I'm a real newby at Access).

    Thanks,

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Expression doesn't work (Access XP)

    I don't understand what you are doing.
    You will have to describe a lot more about the form, what is the source of the form? Do you use the query you mentioned as the source of the form?
    Do you store the calculated value in the table that is part of the source behind the form?

    Failing all this, why don't you post a zipped version of your database, taking out the sensitive bits, so we can all assess what you are doing.

  5. #5
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Expression doesn't work (Access XP)

    Here is a zipped version of my database.

    Thanks,

    Christa

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Expression doesn't work (Access XP)

    Christa,

    I modify your query Units on Hand with Nz function to get the right quantity on hand.
    In the form Categories and products, I add a textbox Quantity on hand with a DLoopUp function to get the number.
    Hope this is what you want.
    Francois

  7. #7
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Expression doesn't work (Access XP)

    Thank you very much!!!

    A couple more questions...is there any way to force the entry of a negative number into the "Quantity Taken" box? And how can I make the Categories and Products form go to the last record (or a new blank record) when the form is opened?

    Thanks again,

    Christa

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Expression doesn't work (Access XP)

    In the design of the table select the quantity taken field.
    In the Validation rule enter :
    <0
    In the Validation text enter:
    Please enter a negative number
    or what ever text you want to appear in the message box.

    For the New blank record, set the Data Entry property of the form to Yes.
    Or if you want to be able to browse the existing records, then you could enter the following code in the on load event of the form:
    Private Sub Form_Load()
    DoCmd.GoToRecord acDataForm, "Categories and Products", acNewRec
    End Sub
    Francois

  9. #9
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Expression doesn't work (Access XP)

    Thank you!

  10. #10
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Expression doesn't work (Access XP)

    I added the code from the last post to my form and it worked great.
    Private Sub Form_Load()
    DoCmd.GoToRecord acDataForm, "Categories and Products", acNewRec
    End Sub
    Now I've changed the form and added two more tables "Inventory Transactions" and "Location". For some reason the code advancing the records to a new one no longer works. Is there a limit to the amount of tables one can use together with this code. The error I get is Run-time error '2105'

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Expression doesn't work (Access XP)

    Error 2105 means that Access can't go to the specified record. Perhaps the addition of the two tables has made the form non-editable. Can you go to a new record manually?

  12. #12
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Expression doesn't work (Access XP)

    You are right...it's uneditable...I can't go to a new record manually. Is there anything I can do about that (and still keep the 2 additional tables)?

    Thanks,

    PS. I've tried to delete some records from the database in order to make it small enought to attach but I can only get it down to 117KB.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Expression doesn't work (Access XP)

    If you have a query based on multiple tables, and you want the query to be updateable, there must be a unique index on the field on the "one" side of the join between two tables.

    See <post#=411702>post 411702</post#> for instructions on how to attach a database.

  14. #14
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Expression doesn't work (Access XP)

    I'm trying to join 4 tables...I've tried to do 2 separate queries and then join the queries...but I still can't get it to work. I've attached a copy of the database. The form in question is called "Inventory".

    Thanks in advance for any help.

    Christa

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Expression doesn't work (Access XP)

    The relationships between your tables are not quite correct.

    The Inventory Transactions table does not need a CategoryID field, since CategoryID follows from ProductID. Having CategoryID in Inventory Transactions led to a circular relationship in the record source of the form.

    Some other points:
    - The primary key in the Location table should be on Location Number, and the corresponding field in Inventory Transactions should therefore be numeric. The relationship between these tables should be on Location Number. You don't need the Location table in the record source of the form. It'll serve as row source of the Location combo box.
    - You should use ProductID from Inventory Transactions in the record source, not from Inventory.
    - The controls bound to fields from Inventory and Categories should be locked. You only should modify fields from Inventory Transactions in this form.

    See attached.

Page 1 of 2 12 LastLast

Posting Permissions

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