Creating a copy of a access record

[Hinfer]

I have an access database with an enforced primary key on all records. Users of this database wish to copy fields from one form/record to a new form. How can can I create a duplicate form without causing issues with the primary key??

Each form has a unique primary key and by replicating the form you are also copying the same primary key over which is invalid so a copy of the form cannot be created. How do I go about creating a copy of a form? I would prefer having a "Create Copy of Form" button to place at the end of a form.

By the way I must do this as users are too lazy to re-enter data into a new form.

 

[stickers]

Do you mean basically they want to create a new record based on a previous one (but then I guess might change the values in one or two fields?) If so I've done something similar but I'll have to root around to find the code for you. Will get back later.

 

[Hinfer]

Yes that's exactly what I want. Thanks. will wait for your post

Cheers

 

[stickers]

What version are you using? I'm on 2002 with ADO and this is the code I used. If you're using DAO I'm afraid you'll have to adapt this - I'm not sure how this lot would translate...

The idea of my code was to create a copy of records in 3 forms, so I'm going to try and simplify it, to just copying stuff in one. You may need to play with it a bit, but it should work more or less as it is! Good luck!

Private Sub cmdDefineSites_Click()
'subroutine variables
Dim intSiteID As Integer
Dim strNewSiteName As String 'the new site name that will be given
Dim cnCurrent As ADODB.Connection
Dim rsSiteDetails As ADODB.Recordset


'variables relating to values to be copied into new records in table
Dim strCurSiteName As String
Dim lngSiteArea As Long
Dim lngPopulation As Long
Dim datFFEStartDate As Date 'can be added back in once a default date is set
Dim strFunction As String
Dim intPercentNewBuild As Integer
Dim bolIsDefault As Boolean
Dim intNewSiteID As Integer

'handle any errors
On Error GoTo Err_cmdDefineSites_Click
'hide what is going on while working
Application.Echo False
DoCmd.Hourglass True

'instanciate connection variables
Set cnCurrent = CurrentProject.Connection
Set rsSiteDetails = New ADODB.Recordset

intSiteID = Me!cboSiteID.Value


'populate recordset with single record which is a copy of the current record
rsSiteDetails.Open "SELECT * FROM tblSiteDetails WHERE SiteID = " & intSiteID, cnCurrent, adOpenDynamic, adLockPessimistic

'fill other variables with the values from the record in the recordset (which is the current record in the form)
strCurSiteName = rsSiteDetails!SiteName
lngSiteArea = rsSiteDetails!SiteArea
lngPopulation = rsSiteDetails!Population
datFFEStartDate = rsSiteDetails!FFEStartDate
strFunction = rsSiteDetails!Function
intPercentNewBuild = rsSiteDetails!PercentNewBuild

'create a new Site name

strNewSiteName = strCurSiteName & "Temp"

With rsSiteDetails
.AddNew
!SiteName = strNewSiteName
!SiteArea = lngSiteArea
!Population = lngPopulation
!FFEStartDate = datFFEStartDate
!Function = strFunction
!PercentNewBuild = intPercentNewBuild
!IsDefault = False
.Update
End With
'***** you may not need this next line, if the form you are running the code from (in which you are wanting to copy records) is already open****** It opens the form, and only shows records where there is Temp in the SiteName - i.e. those that have just been added

DoCmd.OpenForm "frmSiteDetails", , , "SiteName LIKE '*Temp*'"

'this will just take you to the most recently added record, which should be the duplicate
DoCmd.GoToRecord , "frmSiteDetails", acLast

'***a reminder to the user to change the Site Name
MsgBox "Now enter the names of the real sites, using the navigation buttons at the bottom if necessary"

Forms!frmSiteDetails!txtSiteName.SetFocus

rsSiteDetails.Close
cnCurrent.Close
Set rsSiteDetails = Nothing
Set cnCurrent = Nothing

Exit_cmdDefineSites_Click:

Application.Echo True
DoCmd.Hourglass False

Exit Sub
Err_cmdDefineSites_Click:
'if any of the values to be copied is null, just ignore it and carry on
If Err.Number = 3021 Then
Resume Next
Else
MsgBox Err.Description & Err.Number Exit Sub
End If

End Sub