Copying Records

  • Thread starter Thread starter Amanda Laughlin
  • Start date Start date
A

Amanda Laughlin

Good Morning all,

I have a bit of a difficult problem. I have a table that I
update regularly as information on it changes from season
to season. I use a form to regularly change dates, names,
job #'s, ect...

I want to set up a macro to exectute every time I enter a
final date that will cause the record currently open in my
form to copy into a separate table of permanate records.

Any Idea's?
 
It's unusual to need to copy a record from one table into another and keep
both records. Can you provide a bit more info about your setup and what you
want to have happen? Why can't the record stay where it is?
 
OK, I see what you're doing. Essentially, you're capturing a snapshot of the
record before you "change" it to the next season.

You can use an Append Query that will copy the record from one table to
another. What you could do is put a command button on your form (the one you
use to update the records) that a user would click to "copy" the record to
the permanent table. You then would run code on that command button's
OnClick event that would run an append query to do this.

Let's assume that your form (let's assume it's named frmUpdate) shows only
one record at a time. Let's assume that the primary key value is displayed
on this form in a control that is named txtPKey. Let's also assume that the
first table is named tblFirst and the permanent table is named tblPerm; and
let's assume that the field names are the same in both tables. Create a
query (let's call it qryAppending) that is based on the tblFirst. Select the
* "field" and put it on the query grid. Then add the primary key field
(whatever it is) to the query grid. In the "Criteria:" cell under the
primary key field, type this expression:
[Forms]![frmUpdate]![txtPKey]

Click on the Query Type icon on the query's toolbar. Select Append Query.
Choose tblPerm from the list of tables shown in the dropdown list. Under the
primary key field in the query grid, delete any name of a field from the
"Append To:" cell. Save and close the query.

In the design view of the form frmUpdate, click on the command button (let's
assume it's called cmdSave), click on the Properties icon on the toolbar,
click on Event tab, click in box next to On Click, and then click the "..."
box at far right of the box. Select "Code Builder" from the list box choices
that are displayed. You'll then see the Visual Basic Editor open up, with
three lines displayed:

Private Sub cmdSave_Click()

End Sub

with the cursor on the second, blank line. Type the following lines between
the first and third lines:

If vbNo = MsgBox("Are you sure you wish to save this record to " & _
"the permanent table?", vbQuestion+vbYesNo+vbDefaultButton2, _
"Save The Record?") Then Exit Sub
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryAppending"
DoCmd.SetWarnings True


Close the VBE window. Save and close the form.

Now, when you click on the command button when the form is open, it'll ask
if you want to append the current record; if you answer yes, it will append
the current record shown on the form to the permanent table.

Obviously, you need to change all my generic names to your actual names.
 
Ken,
This worked beautifully. Thank you for your help.
Amanda
-----Original Message-----
OK, I see what you're doing. Essentially, you're capturing a snapshot of the
record before you "change" it to the next season.

You can use an Append Query that will copy the record from one table to
another. What you could do is put a command button on your form (the one you
use to update the records) that a user would click to "copy" the record to
the permanent table. You then would run code on that command button's
OnClick event that would run an append query to do this.

Let's assume that your form (let's assume it's named frmUpdate) shows only
one record at a time. Let's assume that the primary key value is displayed
on this form in a control that is named txtPKey. Let's also assume that the
first table is named tblFirst and the permanent table is named tblPerm; and
let's assume that the field names are the same in both tables. Create a
query (let's call it qryAppending) that is based on the tblFirst. Select the
* "field" and put it on the query grid. Then add the primary key field
(whatever it is) to the query grid. In the "Criteria:" cell under the
primary key field, type this expression:
[Forms]![frmUpdate]![txtPKey]

Click on the Query Type icon on the query's toolbar. Select Append Query.
Choose tblPerm from the list of tables shown in the dropdown list. Under the
primary key field in the query grid, delete any name of a field from the
"Append To:" cell. Save and close the query.

In the design view of the form frmUpdate, click on the command button (let's
assume it's called cmdSave), click on the Properties icon on the toolbar,
click on Event tab, click in box next to On Click, and then click the "..."
box at far right of the box. Select "Code Builder" from the list box choices
that are displayed. You'll then see the Visual Basic Editor open up, with
three lines displayed:

Private Sub cmdSave_Click()

End Sub

with the cursor on the second, blank line. Type the following lines between
the first and third lines:

If vbNo = MsgBox("Are you sure you wish to save this record to " & _
"the permanent table?",
Click to expand...
vbQuestion+vbYesNo+vbDefaultButton2, _
 
You're welcome.

--
Ken Snell
<MS ACCESS MVP>

Amanda Laughlin said:
Ken,
This worked beautifully. Thank you for your help.
Amanda
-----Original Message-----
OK, I see what you're doing. Essentially, you're capturing a snapshot of the
record before you "change" it to the next season.

You can use an Append Query that will copy the record from one table to
another. What you could do is put a command button on your form (the one you
use to update the records) that a user would click to "copy" the record to
the permanent table. You then would run code on that command button's
OnClick event that would run an append query to do this.

Let's assume that your form (let's assume it's named frmUpdate) shows only
one record at a time. Let's assume that the primary key value is displayed
on this form in a control that is named txtPKey. Let's also assume that the
first table is named tblFirst and the permanent table is named tblPerm; and
let's assume that the field names are the same in both tables. Create a
query (let's call it qryAppending) that is based on the tblFirst. Select the
* "field" and put it on the query grid. Then add the primary key field
(whatever it is) to the query grid. In the "Criteria:" cell under the
primary key field, type this expression:
[Forms]![frmUpdate]![txtPKey]

Click on the Query Type icon on the query's toolbar. Select Append Query.
Choose tblPerm from the list of tables shown in the dropdown list. Under the
primary key field in the query grid, delete any name of a field from the
"Append To:" cell. Save and close the query.

In the design view of the form frmUpdate, click on the command button (let's
assume it's called cmdSave), click on the Properties icon on the toolbar,
click on Event tab, click in box next to On Click, and then click the "..."
box at far right of the box. Select "Code Builder" from the list box choices
that are displayed. You'll then see the Visual Basic Editor open up, with
three lines displayed:

Private Sub cmdSave_Click()

End Sub

with the cursor on the second, blank line. Type the following lines between
the first and third lines:

If vbNo = MsgBox("Are you sure you wish to save this record to " & _
"the permanent table?",
Click to expand...
vbQuestion+vbYesNo+vbDefaultButton2, _
"Save The Record?") Then Exit Sub
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryAppending"
DoCmd.SetWarnings True


Close the VBE window. Save and close the form.

Now, when you click on the command button when the form is open, it'll ask
if you want to append the current record; if you answer yes, it will append
the current record shown on the form to the permanent table.

Obviously, you need to change all my generic names to your actual names.
--
Ken Snell
<MS ACCESS MVP>




.
Click to expand...
Click to expand...
 
Back
Top