Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2008
    Posts
    4

    Unanswered: Table Default Values

    I have a database with two tables :
    CUSTOMER PART DETAIL which contains the fields Part No and Unit Price
    CUSTOMER ORDER DETAIL which contains Part No and Price Paid.
    I would like the Price Paid in CUSTOMER ORDER DETAIL to be the Unit Price from the CUSTOMER PART DETAIL table as default (but it needs to have the ability to be overwritten if required)
    The Price paid is always the same as the unit price but when prices change and I change the Unit Price in CUSTOMER PART DETAIL I lose the history of previous prices. I do not want to have to input the Paid every time either.
    Am I Over Complicating this. Sorry my database knowledge is fairly limited at the moment. I would appreciate any help. Thanks

  2. #2
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Perhaps:

    Im guessing you will have a form at some stage for order input.

    The detail of this form will be based on CUSTOMER ORDER DETAIL records.

    Each line of the detail will probably have:

    *A combo box (drop-down) for item reference?
    Price Paid
    ...Other fields

    You can base the rowsource of this combo on the customers own prices with two columns 'Part No' and 'Unit Price'. (Have a query called say qryCustomerPrices and when the customer is chosen, update this query to represent the chosen customers prices).

    During the After_Update event of the combo control on the form, you can then change the Price Paid to the value of the combo box (But the second column - the price rather than the reference)>

    me.PricePaid = me.comboControlName.Column(1)

    NB Column(0) is the first column so the above would give the price for the chosen item.

    This would then still allow the user to change the price for that particular order line.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  3. #3
    Join Date
    Nov 2008
    Posts
    4
    Many thanks for your response. I am still struggling though as my database knowledge is limited and I am trying to follow through on somebody elses design. I have a form Customer Order Main and Customer Order Subform (This is where the price paid value is to be displayed). This form is linked to a query Customer Order Detail Qry and details of the orders are inputted through this form. I have a text box (Text34) which I would like to have the Price Paid value in. I would like this to contain the value from the Unit Price field as default but I would like to amend this value if I need to. Could somebody please run through this step by step in laymens terms. Thankyou

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    when you user decides what part/product they want I'd suggest you then populate the price with the appropriate value from the product/price table
    theres no way that I now if dong this automatically in the database itself, but you can do it using the user interface in Access (through forms).

    theres several ways of doing it
    one is to use a dlookup on the change of prodcut ID
    another is to stuff a list or combo box with the product and price
    the other is to very

    the precise details depend on how you let your users select products.
    how your user interface works...

    how do you select the product....
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2008
    Posts
    4
    The Product is selected by a drop drop. This is linked into a cutomer and only parts produced for that customer are selectable.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    a drop down? presumably you mean a list or combo box
    What I'd suggest you do is...

    in the list/combo box/s onclick event place some code which sets the price

    Code:
    mycontrolname.value = dlookup("myproductprice", "MyTableName", "myProductID = " & cint(mylistbox.value))
    You will need to fill in the relevant values
    MyControlName is the name of the control you want to set to the product price
    MyProductPrice is the name of he column in your table that stores the product price
    MyTableName is the name of the table contaning details about your products
    myProductID is the name of the product ID in the product table
    mylistbox is the name of the dropdown control on your form

    there will be some errors in the above code, bound to be as its made on the fly. have a look at the help system or the net for help on the dlookup function

    you will need to consider how you want to handle the situation where you have a price already and the user clicks the product..... how do you want to cater for the user changing the product code

    there is an alternative method which stuffs the prce fo the product as a hidden column into the list box and uses the items selected property to retireve the relevant value
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Nov 2008
    Posts
    4
    Let me start again and try to be a little more explicit about what I am after as I still cannot get this to work. I have a form Customer Order Main and Customer Order Subform. The order details are entered into the subform and customer details are drawn into Customer Order Main. The Customer Order Subform is linked to a query Customer Order Detail Qry which inputs data into the CUSTOMER ORDER DETAIL table.
    I have another table CUSTOMER PART DETAIL which stores the Part No and Unit Price. The problem is that when the Unit Price is updated I lose the ability to be able to track prices paid by cutomers at any given point.
    What I need to be able to do is generate an order with a field that puts the Unit Price from the CUSTOMER PART DETAIL table into a list box on the Customer Order Subform as a default value. I would then like this value to be stored as Price Paid into the CUSTOMER ORDER DETAIL table. The value only needs to be put into the list box when the order is generated but is then frozen at this value even if the Unit Price in the CUSTOMER PART DETAIL table is updated.
    I could do this simply by hard entering a price paid value every time I generate an order but I would like to "automate the process" and remove errors through data entry. I hope that this makes more sense.

  8. #8
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Let me start again - read my initial reply - This should give you a reasonable solution.

    If you don't understand it or need help, let me know.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  9. #9
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Here goes then:

    Your order form will probably have:

    Main Order Form
    Subform with detail

    Have an 'item reference' combo box on the 'detail' subform.
    Control source = Your 'item reference field' in CUSTOMER ORDER DETAIL table.
    Rowsource type = Table / Query

    I would normally use an actual query here, but for the purposes of simplicity, we will just have the SQL in the control

    You said "which contains the fields Part No and Unit Price" I trust it also
    has a CustomerID field of some sort...so 'Item Reference' row source would be something like:

    SELECT Part_No, Unit_Price
    FROM tblCustomerPartDetail
    WHERE CustomerID = Forms!MainOrderFormName.CustomerIDField

    (You could aslo add an ORDER BY clause to sort in alphabetical or price order if required.)

    On the subform 'Item Reference' control, set the number of columns to 2 - Item reference / Price

    After the customer has been selected, update the source of the combo box on the subform
    so that the prices are specific to the chosen customer. *(As long as there are not a massive amount of items in which case there may
    be a more efficient way to do this).

    During the AfterUpdate event of the CustomerID field on the main form:

    Forms!MainOrderFormName.SubformName.form.cboItemRe ference.rowsource = "SELECT Part_No, Unit_Price
    "FROM tblCustomerPartDetail " & _
    "WHERE CustomerID = '" & me.CustomerID &"'"

    After the user selects a product from this drop down, set the DEFAULT price
    for the current order line to the customers price for that item with code
    triggered by the AfterUpdate event for that control:

    Private Sub cboItemReference_AfterUpdate()

    'Set the text and price accordingly for the current order-line item.
    Me.PricePaidField = Nz(Me.cboItemReference.Column(1), 0)

    End Select

    This should accommodate your 'customer-specific' prices but will also allow them to overwrite the
    default on a line-by-line basis.

    Hope this helps
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if all else fauls consider posting your db here
    make a copy of it
    remove any irrelevant tables, forms & reports
    sanitise (mangle or currupt) any sensitive data
    compact and repair the db to minimise its size
    zip it (at least I think you have to zip it to attach it to this forum)

    and someone will have a look at it
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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