Copying a number to a table when adding new record

Hi,

Have next 2 - related - tables:

t1_AUTOID (autonumber, related to t2)
t1_base_number
t1_etc

and

t2_AUTOID
t1_autoid_rel  (related one-many with t1)
t2_new_number
t2_etc

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..
joop123456Asked:
Who is Participating?
 
mbizupCommented:
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


0
 
mbizupCommented:
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
0
 
joop123456Author Commented:
Hi mbizup!

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

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_22896877.html
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.