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

Posted on 2007-10-16
Medium Priority
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
  • 2
LVL 19

Accepted Solution

frankytee earned 1500 total points
ID: 20090639
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

ID: 20091861
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

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

Maybe helpfull...

Rgrds, Joop

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

864 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