Static and Current Copies

[sonoamore]

Hello everyone,



I have a entry system using a single entry form, and a single table. Let's call them ENTRY and INITIAL. The fields can be simply referred to as A, B, C, etc. What I'm trying to do is this:

1. Data entered on ENTRY form, populating table INITIAL.
2. When the submit button is pressed, it closes the form and copies the entry into a 3rd table I would like to add called CURRENT.
3. Users can only edit the information in the table CURRENT through a seperate form.

The idea is that the initial form would always be preserved in the INITIAL table, and through the use of a seperate form only be allowed to edit the copied data in CURRENT. That was there is always a comparison.

What I'm having trouble with is the coding to cause the submit button to write a copy of the data in the CURRENT table. It's probably simple and I'm overthinking it, but it's giving me trouble.

Any help would be greatly appreciated!

[June7]

Possible code:

DoCmd.RunCommand acCmdSaveRecord
CurrentDb.Execute "INSERT INTO CURRENT(A, B, C) SELECT A, B, C FROM INITIAL WHERE ID=" & Me.ID

If you want to keep an audit log of all edits to records, review: http://allenbrowne.com/AppAudit.html

[sonoamore]

Possible code:

DoCmd.RunCommand acCmdSaveRecord
CurrentDb.Execute "INSERT INTO CURRENT(A, B, C) SELECT A, B, C FROM INITIAL WHERE ID=" & Me.ID

If you want to keep an audit log of all edits to records, review: http://allenbrowne.com/AppAudit.html

That actually worked really, really well with one small problem:

There's so many fields I need to copy that it's throwing a fit. :P

[June7]

What is the nature of the 'fit' - error message, wrong results, nothing happens?

[sonoamore]

What is the nature of the 'fit' - error message, wrong results, nothing happens?

Sorry, should have been more specific.

By FIT I mean the string is so long when all the fields are put into that code that it forces a wrap around to another line and stops compiling.

There's about 90 fields, and the names are as short as they can be without going to , "1, 2, 3, etc"

[June7]

Really, I never knew that could be an issue.

Can use line continuation character and concatenation.

CurrentDb.Execute "INSERT INTO CURRENT(A, B, C) " & _
"SELECT A, B, C FROM INITIAL WHERE ID=" & Me.ID

Another option is if the tables have the same fields and the fields are in the same order in the design:

CurrentDb.Execute "INSERT INTO Current SELECT * FROM Initial WHERE ID=" & Me.ID

[sonoamore]

Really, I never knew that could be an issue.

Can use line continuation character and concatenation.

CurrentDb.Execute "INSERT INTO CURRENT(A, B, C) " & _
"SELECT A, B, C FROM INITIAL WHERE ID=" & Me.ID

Another option is if the tables have the same fields and the fields are in the same order in the design:

CurrentDb.Execute "INSERT INTO Current SELECT * FROM Initial WHERE ID=" & Me.ID

Worked perfectly. THANKS!