Copying the current record on a form to another table (1 Viewer)

jcarmichael

New member
Local time
Today, 19:37
Joined
Dec 17, 2007
Messages
6
is there an easy way to copy the current record on a form when clicking a command button such that a snapshot of that record is copied to a table for archiving purposes?

the table has about 120 columns so it is cumbersome to write an Insert Into sql statement within VBA code.

what i am attempting should be quite straightforward...i just want to take the current record in its entirety that is from a single table and append it to another table of the same structure.

any help would be appreciated!
 

Rabbie

Super Moderator
Local time
Today, 23:37
Joined
Jul 10, 2007
Messages
5,906
the table has about 120 columns so it is cumbersome to write an Insert Into sql statement within VBA code.

So many columns in a table make me wonder about your design. Is your data normalised? In general Access tables should be tall and thin not short and wide.

You should be able to use an Update query to copy the record from one table to another but make sure your design is correct first
 

jcarmichael

New member
Local time
Today, 19:37
Joined
Dec 17, 2007
Messages
6
So many columns in a table make me wonder about your design. Is your data normalised? In general Access tables should be tall and thin not short and wide.

You should be able to use an Update query to copy the record from one table to another but make sure your design is correct first

you are absolutely correct. the design is horrible. i did not design it, but at some point i will be moving the data to SQL Server so i will have some opportunity to normalize it.

at this point i have to deal with all of those columns. fortunately the records on the form i am dealing with all bound to the same table. so basically i just want to replicate the same table design in another table and just dump the current record to that table when a button is clicked.
 

Rabbie

Super Moderator
Local time
Today, 23:37
Joined
Jul 10, 2007
Messages
5,906
at this point i have to deal with all of those columns. fortunately the records on the form i am dealing with all bound to the same table. so basically i just want to replicate the same table design in another table and just dump the current record to that table when a button is clicked.

Use an Append Query. See Access help on how to do this. This will let you append the records you want to the archive table. It won't be hard if both tables have the same structure.
 

jcarmichael

New member
Local time
Today, 19:37
Joined
Dec 17, 2007
Messages
6
Use an Append Query. See Access help on how to do this. This will let you append the records you want to the archive table. It won't be hard if both tables have the same structure.

thank you.

i was attempting to use VBA behind a command button as follows:

SQL = "Insert Into newtable (column1, column2,...) Select * From originaltable"

DoCmd.RunSQL SQL

the problem is specifying all of the columns in destination table. it seems that the following syntax is not permissable:

Insert into newtable * Select * from originaltable...
 

boblarson

Smeghead
Local time
Today, 16:37
Joined
Jan 12, 2001
Messages
32,059
thank you.

i was attempting to use VBA behind a command button as follows:
If you don't want to manually specify all columns, then go to the QBE grid and drag and drop all columns into an append query. It will code it for you and then save the query with a name and just run the query from code and you won't have much work to do with this.
 

jcarmichael

New member
Local time
Today, 19:37
Joined
Dec 17, 2007
Messages
6
If you don't want to manually specify all columns, then go to the QBE grid and drag and drop all columns into an append query. It will code it for you and then save the query with a name and just run the query from code and you won't have much work to do with this.

i will work on this...thanks!

if i go this route, how do i specify in the criterial that i just want the current record on the form...that is, i just want to insert into the new table from the old table the current record?
 

boblarson

Smeghead
Local time
Today, 16:37
Joined
Jan 12, 2001
Messages
32,059
In the QBE grid it will let you enter the form and control in the criteria that you want it based on. So, if your unique ID field is on the form, you can just use the builder to navigate to the applicable control.

The syntax is like this:

[Forms]![YourFormNameHere]![YourTextBoxNameHere]
 

jcarmichael

New member
Local time
Today, 19:37
Joined
Dec 17, 2007
Messages
6
In the QBE grid it will let you enter the form and control in the criteria that you want it based on. So, if your unique ID field is on the form, you can just use the builder to navigate to the applicable control.

The syntax is like this:

[Forms]![YourFormNameHere]![YourTextBoxNameHere]

okay...that makes sense.

one more question.

when i use Docmd.openquery "qryName"

how do prevent the query from showing the result? i just want the query to execute and not show a preview of the results.
 

boblarson

Smeghead
Local time
Today, 16:37
Joined
Jan 12, 2001
Messages
32,059
For an append query (Action queries) you need to use either:

DoCmd.RunSQL "YourQueryNameHere"

or

CurrentDb.Execute "YourQueryNameHere'
 

jcarmichael

New member
Local time
Today, 19:37
Joined
Dec 17, 2007
Messages
6
For an append query (Action queries) you need to use either:

DoCmd.RunSQL "YourQueryNameHere"

or

CurrentDb.Execute "YourQueryNameHere'


does docmd.runsql allow for the name of a saved query to be used? my understanding was that it allows only a string that contains sql.
 

cassi84

New member
Local time
Today, 16:37
Joined
Dec 21, 2007
Messages
4
Is your query set as an append query?

If so, use

Private Sub
DoCmd.SetWarnings False

Dim stDocName As String

DoCmd.OpenQuery ("qryNameHere")
End Sub

This should append the records without showing you the results or using SQL.

Let me know how this works for you.
 

boblarson

Smeghead
Local time
Today, 16:37
Joined
Jan 12, 2001
Messages
32,059
Important ***** Important ***** Important

Private Sub
DoCmd.SetWarnings False
...


IMPORTANT NOTE:-------------------------------------------------------------------
If you use the DoCmd.SetWarnings False then you MUST remember to use DoCmd.SetWarnings True after the query is run. AND you should use an error handler and put that in the first line of it too. Otherwise you will find yourself without warnings. This will not reset automatically so you must include the DoCmd.SetWarnings True.

If you do not reset them you will find yourself without any warnings at all, including saving design changes, etc. And, if you just close an item, such as a form, without warnings it will close without a warning and it will DISCARD your changes. So, that is important.

---------------------------------------------------------------------------------------
 

Users who are viewing this thread

Top Bottom