Copying a number to a table when adding new record - 2

Posted on 2007-10-16
Last Modified: 2013-11-28

How will the VBA be with one table extra in between?  But now from discount >> discount2
(the question, when adding a new related record to tblL2, I want the latest "discount" to be copied to "discount2" in an access form.)

-ZagerijID (unique record)

-ContractAUTOID (unique record)
-ZagerijIDContract (one-to-many relation with unique field "ZagerijID" from "qryZagerijen_REL_be")

-tbl2AUTO (unique record)
-ContractAUTOID_TL2  (one-to-many relation with unique field "ContractAUTOID" from "tblL")

All records mentioned will be in the form's record source.

When making a query of the 3 tables it looks like this:
SELECT qryZagerijen_REL_be.Discount, tblL2.discount2, qryZagerijen_REL_be.ZagerijID, tblL1.ContractAUTOID, tblL2.tbl2AUTO, tblL2.ContractAUTOID_TL2, tblL1.ZagerijIDContract
FROM (qryZagerijen_REL_be INNER JOIN tblL1 ON qryZagerijen_REL_be.ZagerijID = tblL1.ZagerijIDContract) INNER JOIN tblL2 ON tblL1.ContractAUTOID = tblL2.ContractAUTOID_TL2;

Question by:joop123456
    LVL 19

    Accepted Solution

    what do you mean by "want the latest "discount" to be copied to "discount2" in an access form"
    because yourquery
    -ZagerijID (unique record)

    means there can only be one discount per ZagerijID.
    need more explanation

    Author Comment

    Hi frankytee!

    Yes, every ZagerijID (=Supplier) has only one - latest - discount percentage.  Since it is changing
    sometimes and when I enter an order, I want to work further in this order (tblL1 and tblL2) always
    with the latest discount percentage available at that time.
    In case this unique discount-% is changing, the discount2 fields shouldn't be updated. Only updated
    (>copied) when adding a new record in tblL2.

    (Percentages in a certain period stick should together with orders from that period, new orders might have new %-rates)

    Author Comment

    Forgot to mention, I noticed in the main-posting that I posted a similar question a while ago:

    Maybe helpfull...

    Rgrds, Joop

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now