[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Copying a number to a table when adding new record

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

Have next 2 - related - tables:

t1_AUTOID (autonumber, related to t2)


t1_autoid_rel  (related one-many with t1)

Now, when adding a new related record to t2, I want the latest t1_base_number to be copied to t2_new_number.
t1_base_number changes all the time, but when I've added a new t2-record, after that the t2_new_number shouldn't change anymore.
So just copy the latest version t1 number to the t2 field.

Prefable no SP... but in a view?  VB-code in Access front-end is ok as well..
Question by:joop123456
  • 2
LVL 61

Expert Comment

ID: 20085437
Form an Access Front end, you can use VBA in your form's After Insert event:

Dim strSQL as string
dim lngMaxID as Long
lngMaxID = DMax("t1_AutoID", "t1")

strSQL = "UPDATE T2 SET t2_new_number = " & Dlookup("t1_base_number", "t1", "T1_AUTOID = "  & lngMaxID)
Currentdb.execute strSQL, dbFailOnError
LVL 61

Accepted Solution

mbizup earned 2000 total points
ID: 20085456
Correction... you need to specify which t2 record to update:

Dim strSQL as string
dim lngMaxID as Long
lngMaxID = DMax("t1_AutoID", "t1")

strSQL = "UPDATE T2 SET t2_new_number = " 
strsql = strsql & Dlookup("t1_base_number", "t1", "T1_AUTOID = "  & lngMaxID)
strSQL = strSQL &  " t2_AUTOID = " & me.t2_AUTOID          '<--- assumes t2_autoID is a field in the form's recordsource
Currentdb.execute strSQL, dbFailOnError


Author Comment

ID: 20087501
Hi mbizup!

Have a new situation here as well, just slightly different:


Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
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, …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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…

834 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