Duplicating record in mainform, subform and sub-subform

  • Thread starter Brian G via AccessMonster.com
  • Start date
B

Brian G via AccessMonster.com

Heya,
I have a database with a form that has a subform, I am able to duplicate a
record using an SQL statement quite ably for both. My problem arises trying
to duplicate the records for a sub-subform .

tables
1 Printing Price
Calcs

They relate one to many through Autonumber an autonumber field assigned by
the 1 Printing Price table and there could be as many as nine Autonumber
records for each Job / Estimate Number

lngID is the newly duplicated Job / Estimate Number

strSql = "INSERT INTO [1 Printing Price] ( [Job / Estimate
Number] ) " & _
"SELECT " & lngID & " As [Job / Estimate Number] " & _
"FROM [1 Printing Price] WHERE [Job / Estimate Number] =
" & Me.[Job / Estimate Number] & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError

strSq2 = "INSERT INTO [Calcs] ( [Estimate Number], Autonumber,
Ref ) " & _
"SELECT " & lngID & " As [Estimate Number], Autonumber,
Ref " & _
"FROM [Calcs] WHERE [Estimate Number] = " & Me.[Job /
Estimate Number] & ";"
DBEngine(0)(0).Execute strSq2, dbFailOnError

As you can see it works almost perfectly, I end up duplicating the exact
record from Calcs, assigning the new Job / Estimate Number and correct Ref
data, but what I want to do is Insert the new related Autonumber from 1
Printing Price into Calcs, not the Autonumber from the original Calcs table
record.

Any help would be appreciated. (Apologies for the messy names... I have
learned sooo much since starting this project!!)
 
B

Brian G via AccessMonster.com

bah, needed some normalization, the Estimate Number field was totally
unecessary the Autonumber and Ref fields are sufficient
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top