Results 1 to 5 of 5
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Need help with formula in control (Access 2000>)

    Hi,

    In the attached DB, I need some help on a form. If you open the OrderInfo form, I need the Cost control to be populated with a cost from the Costs table based on a match of SupplierID and DestinationID. I was thinking in the lines of a Lookup Function. Based on the ID of Supplier and Destination selected from the combos, the Cost control must collect the value from the Cost Table!

    Tx
    Regards,
    Rudi

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

    Re: Need help with formula in control (Access 2000>)

    If the control source of the Cost text box is a formula, the text box will not be bound to the Cost field any more. If Cost will always be calculated from Supplier and Destination, you don't need a Cost field in the OrderInfo table.

    If you want to store the Cost field in the table, the control source of the Cost text box must be the Cost field, so you cannot use a formula. Instead, you must use the After Update event of the Supplier and Destination combo boxes. To populate the Cost field in already existing records, you'd need to run an update query once.

    Which do you need?

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Need help with formula in control (Access 2000>)

    Tx for the reply.

    The cost field in the table is not needed...(Still a remnant of previous attempts). You will have noticed that I have a table called costs that will contain all supplier and destination combinations with their appropriate cost value. I would like to know if a lookup function can be used to populate the cost control on the form based on the users selection of the supplier and destination combos. If this is possible, can you assist me in getting the function to work.

    If the function is not the answer, then I will appreciate a macro that will populate the control with the after update event. This is a new DB, so there will not be any existing data to hassle about.

    Tx
    Regards,
    Rudi

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

    Re: Need help with formula in control (Access 2000>)

    You can use the following expression as control source

    =DLookUp("Cost","Costs","SupplierID=" & [SupplierID] & " AND DestinationID=" & [DestinationID])

    This will result in an error if either SupplierID or DestinationID is blank. To avoid this:

    =DLookUp("Cost","Costs","SupplierID=" & Nz([SupplierID],0) & " AND DestinationID=" & Nz([DestinationID],0))

    If there is no match, the result will be blank (null)

    Don't forget to set the Format property of the Cost text box - now that it is no longer bound to a currency field, formatting is not automatic.

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Need help with formula in control (Access 2000>)

    Tx. It is working really well!
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,
    Rudi

Posting Permissions

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