Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2009

    Unanswered: Link a value in a suform to a record in the main form??

    Hi. I am very much a novice so please bear with me.

    I am a service engineer and have a database running on access 2000.

    I have a main form for printing invoices with a subform for parts used on a job. The subform is linked to another form (stock master form) so that I type a stock number into the subform and it automatically fills in the description and price etc from the stock form.

    The problem I have is that when I change the price of a part on the stock master form it changes the price on every record in the invoice main form where that part has been used before. How can I lock the price field of the subform to a specific record on the invoice main form?

    Sorry if this is a bit of a basic question but as I said I am novice!!

  2. #2
    Join Date
    Dec 2004
    Madison, WI
    I'm a little bit confused. Could you please re-explain?
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Nov 2009
    Sorry, it's quite hard to explain without knowing all the technical terms

    I use this form for generating customer invoices for work done & spare parts used.

    Main form "invoice master" contains subform "parts fitted". "parts fitted" was made using a query to link it to "Stock Master" which contains info on the parts. When I am creating an invoice i can just enter a stock number onto subform "parts fitted" and it fills in description and price etc automaticaly from "stock master".

    As the cost price of a spare part changes I have to update the selling price accordingly, however, If i change the price of a part in "stock master" it changes the price of that part on subform "parts fitted" on all preivious invoices where that part has been sold and therefore upsets the total amount of the invoice (which therefore upsets my accountant!)

    Is it possible to make changes to records in "stock Master" without it effecting historical records in subform "parts fitted" / somehow link the price at the time it was fitted to the specific record on the main form???

    Thanks for getting back to me, Hope that makes a bit more sense!?

  4. #4
    Join Date
    May 2005
    Nevada, USA
    Provided Answers: 6
    It sounds like you aren't saving the price as part of the invoice. The most common solution is probably to do that. You could also include date fields in your stock master table (or more likely a related price table), but I would only do that if the prices didn't change much. As I said, commonly the price is saved along with the other data in the invoice. That way it doesn't change when you change the price in the stock table.

Posting Permissions

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