Is there a way to do "save as" a new record in a form?

Y

YuXuan-ji

Other people may already have asked similar questions, but I found it's
painful to comb through other people's questions and answers. Plus when I did
the search, it came back with a list of results relate to my question, but
when I click on it, it doesn't give me the detail of the posted question and
answer. Instead, it went back to the "search" page.
Anyway, here is my question, is there a way to do "save as" a new record in
a form? This is how I'm doing now: finding an old record, duplicating it,
making the changes, and then saving the new one. But if I forget to do the
duplication, it 's quite easily I'll save the changes into an old record
instead of creating a new one, even though I have the BeforeUpdate procedure
to confirm the "save".
 
J

John Vinson

Other people may already have asked similar questions, but I found it's
painful to comb through other people's questions and answers. Plus when I did
the search, it came back with a list of results relate to my question, but
when I click on it, it doesn't give me the detail of the posted question and
answer. Instead, it went back to the "search" page.
Anyway, here is my question, is there a way to do "save as" a new record in
a form? This is how I'm doing now: finding an old record, duplicating it,
making the changes, and then saving the new one. But if I forget to do the
duplication, it 's quite easily I'll save the changes into an old record
instead of creating a new one, even though I have the BeforeUpdate procedure
to confirm the "save".
Click to expand...

I have to wonder if your tables are properly normalized! If (as it
appears) you very often have records which are almost identical in all
fields, it sounds like you're storing a lot of repeated data.

That said... I'd suggest putting a Command Button on the form, labeled
"Save As New" perhaps. This button would run an Append query to append
the currently selected record to the form's Recordsource table:

Private Sub cmdSaveAsNew_Click()
Dim db As DAO.Database
Dim qd As DAO.Querydef
Dim strSQL As String

On Error GoTo Proc_Err ' set up error trapping

' use your own table and fieldnames of course
strSQL = "INSERT INTO MyTable SELECT fieldA, fieldB, fieldC" _
& " FROM MyTable WHERE MyPrimaryKey = " & Me.txtPrimaryKey
Set db = CurrentDb
' create an unnamed, unsaved Query
Set qd = db.CreateQuerydef("", strSQL)
' execute the query
qd.Execute dbFailOnError
' Clean up after yourself
Set qd = Nothing
' navigate to the new record.
DoCmd.GoToRecord acForm, Me.Name, acLastRecord

Proc_Exit:
Exit Sub
Proc_Err:
<handle the error condition appropriately>
Resume Proc_Exit
End Sub

John W. Vinson[MVP]
 
Y

YuXuan-ji

It's definitely a good concern. The thing is I'm making this form only for
the purpose of easier data entry.
On the other hand, I'm just taking the baby steps in learning to use
Access and VB. And I found this website extremely helpful. Now my question is
 
J

John Vinson

Now my question is
on the code you gave me below: what is "Me.txtPrimaryKey" supposed to do?
Click to expand...

Me. (or Me!) refers to "the form that this code is part of".
Me.txtPrimaryKey is a reference to the textbox named txtPrimaryKey, a
control on the (current) form, which I was presuming contains the
value of this record's Primary Key. You would change the name of this
control to whatever control on *your* form contains the Primary Key
value.

If your table has no Primary Key - STOP. You're building a house of
cards - every table must have a Primary Key to uniquely identify the
records.

John W. Vinson[MVP]
 
Y

YuXuan-ji

Yes, I do have a primary key and I put that right after "Me." And i selected
all the fields except for the primary key to put after "SELECT".
Now I encountered another problem: when I click the button, it comes back
with error message as "You can't go to the specified record" and it points
out this line "DoCmd.GoToRecord acForm, Me.Name, acLastRecord ". So what's
the problem here?
 
J

John Vinson

Now I encountered another problem: when I click the button, it comes back
with error message as "You can't go to the specified record" and it points
out this line "DoCmd.GoToRecord acForm, Me.Name, acLastRecord ". So what's
the problem here?
Click to expand...

My apologies! My mistake: it should be acLast rather than
acLastRecord.

John W. Vinson[MVP]
 
D

D Soles

John,

I have been looking for something like this myself in order to save some
time entering data.

Now I am wondering, is the sub routine you wrote for the LAST record in the
database only? I would like to reference the CURRENT record in the form view.

Would I have to edit this routine?

Hope to hear from you,

Darcy Soles
 
M

MikeLostinWoods

I found the easiest way to do it was just to have a button tag the info, undo
changes, open a new record, paste the info, and save it. You might also
change the focus to keep from double hitting the save and getting an error
message.

Me.[whatever1]= Me.[whatever1]
Me.[whatever2].Tag = Me.[whatever2]
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
DoCmd.GoToRecord , , acNewRec
Me.[whatever1] = Me.[whateverq].Tag
Me.[whatever2] = Me.[whatever2].Tag
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
 
Top