duplicate record in from and subform (1 Viewer)

Glad it worked. Don't forget to mark the thread as Solved (in Thread Tools top of page)

theinviter said:

Thanks

Done successfully .

Click to expand...

one more thing please ,

if i wanna add more fields in table 2 ( S3 , S4)

how to modify the code.

Inviter,

Do you have a specification or statement of requirements?

In database and application development, you seem to have a classic "scope creep" approach.

Here is wikipedia description:
Scope creep (also called requirement creep, function creep, or kitchen sink syndrome) in project management refers to changes, continuous or uncontrolled growth in a project's scope, at any point after the project begins. This can occur when the scope of a project is not properly defined, documented, or controlled.

jdraw said:

Inviter,

Do you have a specification or statement of requirements?

In database and application development, you seem to have a classic "scope creep" approach.

Here is wikipedia description:
Scope creep (also called requirement creep, function creep, or kitchen sink syndrome) in project management refers to changes, continuous or uncontrolled growth in a project's scope, at any point after the project begins. This can occur when the scope of a project is not properly defined, documented, or controlled.

Click to expand...

'***HERE IS THE NEW CODE
strSql = "INSERT INTO Table2 ( s1, s2 ) " & _
"SELECT Table2.s1, " & lngID & " AS Expr1 " & _
"FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.s2 " & _
"WHERE (((Table1.ID)=" & lngOldID & "));"

i mean any advise on how to modify this code if i want to additional field in table 2 (s3, s4)

theinviter said:

'***HERE IS THE NEW CODE
strSql = "INSERT INTO Table2 ( s1, s2 ) " & _
"SELECT Table2.s1, " & lngID & " AS Expr1 " & _
"FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.s2 " & _
"WHERE (((Table1.ID)=" & lngOldID & "));"

i mean any advise on how to modify this code if i want to additional field in table 2 (s3, s4)

Click to expand...

i done it

thanks alot

Best advice I can offer: re : Add new field to a table

Go to table design add the field(s) you need, then change the Select accordingly.

https://www.w3schools.com/sql/sql_insert_into_select.asp

or use SQL to alter the Table, then use the modified Select.
https://www.w3schools.com/sql/sql_alter.asp

Congrats! on solving your issue.

You have the Insert SQL statement:

"INSERT INTO Table2 ( s1, s2 ) SELECT Table2.s1, " & lngID & " AS Expr1 " & _
"FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.s2 WHERE (((Table1.ID)=" &
lngOldID & "));"

For each field in the INSERT INTO statement, there are values in the SELECT statement. So if you add more fields in the INSERT INTO statement, you need to add more values in the SELECT statement. The SELECT statement shows you what values to put into the fields in the INSERT INTO statement, and in the same order. In the above example, the value of Table2.s1 is inserted into field s1, and the value lngOldID is inserted into field s2.

You can test any SQL statement by putting the SQL (without the quotes) into the Access Query builder SQL area, then clicking Design View to see exactly what it is doing in graphic form. That is the way I always build my SQL statements - it's so much easier to see what you are doing.