Copying a number to a table when adding new record

Posted on 2007-10-16
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
    LVL 61

    Expert Comment

    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

    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

    Hi mbizup!

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

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    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…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    745 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

    15 Experts available now in Live!

    Get 1:1 Help Now