Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update form field (Access2002)

    I wonder If somebody could help me please. I a relative novice to access and am struggling to make sense of what I am doing. Basically I have two questions:

    1) How can I amend my query so that it will show me what the price for each order detail line should be?
    2) On my sales order form how can I populate the sales price automatically once I have entered the location, product, Std, Clr or Stn Flags?

    I have attached a sample database for reference.

    Many thanks
    Hayden
    Attached Files Attached Files

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

    Re: Update form field (Access2002)

    Hi Hayden,

    Welcome to the Lounge.

    I have removed the SalesPrice field from the tblSalesOrderDetails table - it is derived information that should not be stored in a table.

    I have amended your query as follows:

    - Made it into a normal selection query instead of a Totals query - you want the individual sales details.
    - Added Qty.
    - Removed the quotes from around "0.00" in the expression for Price - it is numeric, not a string.
    - Added a calculated field SalesPrice:[Qty]*[Price].

    I have changed the Record Source of the subform to the query, so that it picks up SalesPrice from the query.

    Post back if it does not do what you want.
    Attached Files Attached Files

  3. #3
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update form field (Access2002)

    Hello Hans

    Thanks for your speedy reply, I think I understand what you did, but my explanation is probably not the best, so let me try again.

    Each product has a different price depending on its type and where it is being sold. By type I mean whether it is Standard, Coloured or Stained (with a Standard being cheeper than a Stained for example). I have tried to set up tblSalesPrices to reflect this.

    When a customer orders a product, I want the sales price on the order form to automatically pick up the correct sales price and display it. (obviously multiplied by the qty) (actually having looked at it again I should probably include a UnitPrice field as well don't you think?) - anyway - I have tried to design the form in such a way that when the order is entered the user will enter the location the product and tick one of the boxes (Std, Clr or Stn) and then this combination of entries will hopefully return the correct unit price value.

    When I look at the form and the query now it seems to be duplicating the line numbers. What would be the reason for this?

    Regards
    Hayden

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

    Re: Update form field (Access2002)

    I'm sorry, my modification is not correct.

    Your tblSalesOrderDetails table has separate Yes/No fields for Std, Clr and Stn. This means that it's possible to check any number of them, from none to all three. From your descripton, I would guess that you want one and only one of these to apply to each Sales Order Detail line. Is that correct? (If so, we're going to have to change the table structure slightly)

  5. #5
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update form field (Access2002)

    Correct Hans,

    The answer is yes, for each order line there can be only one type (either Std, Clr or Stn) at any one time.

    I would be greatful if you could point out the error of my ways.

    Regards
    Hayden

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

    Re: Update form field (Access2002)

    Basically, you need one field for the type, with possible values Std, Clr or Stn (or codes for those). The prices table will have to be restructured to have separate records for each type.

    I'll try to incorporate these ideas into your database, but it'll be several hours before I'll have time for that.

    BTW, do you already have a lot of "real" data in your tables? If , we can create some tools to convert the data from the old format to the new format. If you only have demo data, that's not important.

  7. #7
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update form field (Access2002)

    Hans,

    Please don't rush, I have got lots of time.

    I would like to learn from this as well so if it is possible could you explain your thinking when you do get round to helping me out.

    I am fortunate that there is no "real" data yet, the database is still in its beginning stages of development, and like I said from my first post, I still regard myself as very much a novice at developing access databases. So I think it will be some time before I complete it.

    Best regards
    Hayden

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

    Re: Update form field (Access2002)

    I've been looking at it, but the basic design needs more re-thinking, there are so many relationships now that queries are not updatable.

  9. #9
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update form field (Access2002)

    You're right Hans

    I've also been thinking it over and realised that it is not as simple as I first thought, I'm going to redesign it and when I run into troubles I'll ask for help, if that's ok?

    Thanks for your help thus far.

    Kind regards
    Hayden

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

    Re: Update form field (Access2002)

    Good luck, and don't hesitate to ask questions.

Posting Permissions

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