Saving a Record AS A NEW RECORD from a form (1 Viewer)

[valley]

Iam very new to ACCESS. So please do forgive me for
believing that the "SAVE AS" function in ACCESS is as easy as
something similar we do in Microsoft Word.

After searching this forum and others I realise that there is no easy way
and it is considered as some thing for serious users.
(with additional coding not available in ACCESS)
Perhaps there are easier ways that I am not aware of.

I would like to open a record in a FORM which has one Combo Box
for text data, several text and Numeric fields.
I then need to change a few items and save the record as a New
Record
Only a few (mostly numeric fields) and some date entries will be changed
as well as a couple of calculated fields in the NEW RECORD.

I shall appreciate any suggestion as to how to do this.

 

[gemma-the-husky]

you are working on a integrated database application,

so save as saves either the entire database, or an individual form or report etc, which isnt what you want.

------------------
try this - create a table with the fields you need, then use the form wizard to create a form for this table.

at the bootom of the form are a number of vcr-like keys (navigation buttons) offering (effectively) start, previous, reordnumber, next, last, and newrecord.

this form will start by showing you record 1, and you can then use the navi buttons to move through the records, changing details as you go. To enter a new record, you need to click the * at the bottom, which will take you to the new record.

note that changing details on an existing record does that - IT CHANGES THE EXISTING RECORD, which is definitely not the same as ADDING A NEW RECORD

 

[valley]

Hello,
Many thanks for the reply.
I am familiar with the form navigation.
What I am looking for is to change items in a record and save as a NEW record.

The Navigation can only make the changes and save an existing record or create NEW record from scratch.

 

[neileg]

gemma, I don't think that's the issue.

valley, one way of doing this is to find the record that you want to use as the basis of a new record and copy it to a second table. Then you can change the record in the new table, and when it's done, append this record back to your main table. Then empty the second table ready for the next one.

 

[valley]

Hello Neileg,
Many thanks for the reply.
Actually I need to do this by code, attaching thecode to
an event or other property of the form.

 

[neileg]

Hello Neileg,
Many thanks for the reply.
Actually I need to do this by code, attaching thecode to
an event or other property of the form.

Ah, this is homework?

 

[valley]

Not exactly.

I have come across more elaborate ways using additional carry over routine etc.
I was wondering if anyone has come across with more ingeneous ways of doing this using ACCESS or VB code

For your info I am not a coder or have good experience.

 

[valley]

I have tried the code below which saves a new record but doesn't copy anything.
Any help to get it to copy the fields will be much appreciated

Private Sub Save_Record_Click()
'Private Sub cmdDupe_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the record in the form.
Dim NewKey As String

'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
'If Me.NEWRECORD Then
'MsgBox "Select the record to duplicate."
'Else
'Get the new key
'NewKey = InputBox("Enter the new Policy No")
'You need to test there was an entry, and of a suitable type.

With Me.RecordsetClone
.AddNew
'!KeyFieldName = NewKey
'!OrderDate = Date
'etc for other fields.
.Update

'Show the new record
Me.Bookmark = .LastModified
End With
'End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "Save_Record_Click"
Resume Exit_Handler
End Sub

 

[gemma-the-husky]

i think what makes it hard is that you will be editing an existing record, which you don't want to do. the best thing to do is to clone the record first, I think.

so in a buttonclick event,

using a recordset, you would want to do

dim rst as recdset
set rst=currentdb.openrecordset(me.recordsource) - ie the same data as the form is based on

rst.addnew
rst!field1 = formfield1
rst!field2 = formfield2 - etc

this is harder , you will have to set all the fields you want in the new record on at a time, based on your current form

rst.update - now this adds the new record - the only trouble is, is that this will fail probably as copying a record may well try to duplicate non-duplicating keys

rst.close

'now you want to repositition your form so

application.runcommand acCmdRecordsGoToLast

hope this works for you

 

[valley]

Hello,

Many thanks for yur reply.
I think you are right. The method you have shown is a hard way as I have more than 30 field but It works.
Here is the modified code for the button.(I haven't done all fields yet )
I have some auto fields like dat and some calculated fields
Please let me know if I can improve this bit of code

Private Sub Save_Record_Click()
'Private Sub cmdDupe_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the record in the form.


'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NEWRECORD Then
MsgBox "Select the record to duplicate."
Else
'Copy current field values to variables
MyCust_ID = Me.[Cust_ID]
MyAddress = Me.[Address]
MyCity = Me.[City]
MyPostcode = Me.[Postcode]
MyCounty = Me.[County]
MyTelephone = Me.[Telephone]

'Go to a new record
DoCmd.GoToRecord , , acNewRec

'Plugs old values into new record
Me.[Cust_ID].Value = MyCust_ID
Me.[Address].Value = MyAddress
Me.[City].Value = MyCity
Me.[Postcode].Value = MyPostcode
Me.[County].Value = MyCounty
Me.[Telephone].Value = MyTelephone
End If

'With Me.RecordsetClone
'.AddNew
'!KeyFieldName = NewKey
'!OrderDate = Date
'etc for other fields.
'.Update

'Show the new record
'Me.Bookmark = .LastModified
'End With
'End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "Save_Record_Click"
Resume Exit_Handler
End Sub

 

[neileg]

Not exactly.

I have come across more elaborate ways using additional carry over routine etc.
I was wondering if anyone has come across with more ingeneous ways of doing this using ACCESS or VB code

For your info I am not a coder or have good experience.

I don't understand. What is the problem with using a temporary table as I suggested? Do you think that using code is more respectable than using queries?

 

[valley]

Hello neileg,

Certainly not, especially when I don't know coding at all.
The reason is I am developping this to run on PDAs(Handhelds) to be synchronised with a database running on a desktop.
After trying out various PocketPC based databases I realised that keeping everything simple is the only way. So I am trying to fit everything in to one TABLE and one FORM. So far so good.
I need to sort out certain calculated fields
Hope that explains why I am not taking conventional ways.
There are a couple of other threads on this forum where I have other questions generously being helped by forum members. Many thanks to everyone.

 

[gemma-the-husky]

copying via variables is perfectly acceptable, i'm sure. Its what i would do i think. as long as you can get the new record populated without errors, there's no problems.

 

[neileg]

valley
Yeah, OK, whatever works for you. I was only suggesting the temporary table route because you were having trouble with the coding. I do understand some of the issues with PocketPC apps.