trouble creating duplicate record due to key 1

[frantik]

Hi! - Help!!!

I have a relational database, I would like to include a "Copy Record" button to enable the user to create a duplicate record.

The duplicate record through the wizard will not work because there is a key field.

Also there are related tables including a 1-M rel, that data needs copying from. (all displayed via subforms on the main form)

Is there an easy way to go about this? And can someone explain it to me please!

Thanks

 

[RickSpr]

Actually, what you're asking for is impossible. The concepts of having a duplicate record, and having a unique key, are incompatible. To paraphrase, you're saying you want every record to have a unique identifier (key), but you also want to have two records with the same values including the identifier.

I imagine that what you really want isn't to store a duplicate record in the table, but to make a copy of the record for some other purpose, perhaps to store in a different table or to serve as a template for a new record. Please explain more about what you want to do with this duplicate record.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[frantik]

Hi Rick

Sorry I didn't explain myself very well, what I want to do is to use it as a template for another record.

The new record will need a new unique identifier but all other fields should be the same as the one it was copied from, then the user can make changes instead of creating a very similar record from scratch.

Does this help?

 

[RickSpr]

The subforms complicate the issue, so let me start by describing how I would do it for a main form with no subforms.

In the command button's Click event procedure, declare a Variant variable for each control you want to copy. In the code, save the value of each of these controls, move to a new record, and then reload each control from its variable. Example using controls named txtLastName, txtFirstName, and cboDepartment:

Private Sub cmdCopy_Click()
    Dim varLastName As Variant
    Dim varFirstName As Variant
    Dim varDepartment As Variant

    varLastName = txtLastName
    varFirstName = txtFirstName
    varDeparment = cboDepartment
    DoCmd.GoToRecord , , acNewRec
    txtLastName = varLastName
    txtFirstName = varFirstName
    cboDepartment = varDepartment
End Sub

Note that as soon as you fill a value into any of the controls on the new record--even if you're just assigning Null to it--Access considers the new record modified, and fills in the Autonumber field if there is one. Thus, if the user clicks the Copy button and then decides not to add another record, he will have to click the Undo button before closing the form or browsing to another record. If he doesn't, and the fields you fill in allow the record to pass validation, the record will be added with the copied values only. This will not be obvious to the user, and you may end up with bogus records in the database.

The problem with the subforms is that, in general, a subform may have zero, one, or many records being displayed. The user may have entered one, which then leaves the empty "new" record current, or may have clicked on (possibly edited) an existing record, leaving that one current. In any case, only the values of the current record in a subform are available to code through the subform controls and properties. Which record do you want to copy from? You could always copy from the current record, but that would mean the user has to click on the desired record before clicking the Copy button, which seems awkward and unintuitive. And what if the empty record is current?


Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[frantik]

Brilliant - Thanks Rick - that bit works perfectly. I do have to figure out how to regenerate the unique ref no, but I think that will be fine.

But.. the big problem now.... how would you go about copying the records of a subtable/subform? - the subtable corresponding to the current (visible) record?

thanks a lot

Fran

 

[RickSpr]

I see--you want to copy all the subform records.

A form (or subform) has only one current record. It can display multiple records, but only the current record is held in memory; the others are stored in the table. I'm afraid that, since you're now talking about multiple records in a child table, you're actually going to have to store the records in the child table, and let the user modify or delete them. Furthermore, relational integrity is going to require that the record be stored in the parent table before you can add the child table records.

What this means is that you can't get away with just filling in fields in the form--you actually have to create the duplicate records, and leave it to the user to modify or (if he changed his mind about copying) delete them. It also means that you have to copy enough data to make records that pass all the validation criteria.

I really do not like this approach. You're putting bogus data into your tables, even if only temporarily. A user can forget it has to be deleted and leave it there through a momentary mental lapse. Likewise, another user can see the bogus data in the database while the first user is changing it. It could be included in a report being run by a third user. I just don't like it at all.

A modified approach that avoids these problems would be to create duplicates of the "live" tables used by the main form and subform. When the Copy button is clicked, you copy the current row from the main form and all the rows from the subform to the duplicate tables, then set the form's and subform's Record Source properties to the duplicate tables. The user can then edit the data as necessary, and click a Save button that copies the records back to the live tables, or click a Cancel button to discard them. This is similar to the first approach, but the bogus records are never present in the live tables.

The code might be something like this: (Note that I modified the code from my previous post to handle unsaved changes in the current record when the Copy button is clicked. Note also that I assume the parent table key is an Autonumber.)

Private Sub cmdCopy_Click()
    Dim lngRecordID As Long

    ' Save the current record if necessary. Exit if canceled
    If Me.Dirty Then 
        On Error GoTo ErrorExit
        RunCommand acCmdSaveRecord
        On Error GoTo 0
    End If

    ' Copy the parent table record
    DoCmd.RunSQL "DELETE * FROM ParentTableDuplicate"
    DoCmd.RunSQL "DELETE * FROM ChildTableDuplicate"
    ' In the following SQL statement, you must omit the key field
    DoCmd.RunSQL "INSERT INTO ParentTableDuplicate (Field1, Field2, etc.) " _
        & "SELECT Field1, Field2, etc. " _
        & "FROM ParentTable"
    ' Retrieve the key of the record just inserted, to use 
    ' as the foreign key in the duplicate child table
    lngRecordID = DLookup("Key", "ParentTableDuplicate")

    ' Copy the child table records
    DoCmd.RunSQL "INSERT INTO ChildTableDuplicate (ForeignKey, Subfield1, Subfield2, etc.) " _
        & "SELECT " & lngRecordID & ", Subfield1, Subfield2, etc. " _
        & "FROM ChildTable"

    ' Adjust the form and subform to use the duplicate tables
    ' (or queries based on them)
    Me.RecordSource = "ParentTableDuplicate"
    Me.Subform1.Form.RecordSource = "ChildTableDuplicate"

    ' Hide the navigation bar and reveal the Save and Cancel
    ' buttons (as an indication of Copy mode to the user)
    Me.NavigationBar = False
    cmdSave.Visible = True
    cmdCancel.Visible = True
    ' Don't allow user to add any records in Copy mode
    Me.AllowAdditions = False

ErrorExit:
End Sub

Private Sub cmdSave_Click()
    Dim db As DAO.Database
    Dim rstLive As DAO.Recordset
    Dim rstDup As DAO.Recordset
    Dim lngRecordID As Long

    ' Copy the modified record back to the parent table.
    ' We have to use recordsets here to capture the key,
    ' which we need to use as the foreign key when we copy
    ' the child table records back.
    On Error GoTo ErrorHandler
    Set db = CurrentDb()
    Set rstLive = db.OpenRecordset("ParentTable")
    Set rstDup = Me.RecordsetClone
    With rstLive
        .AddNew
        !Field1 = rstDup!Field1
        !Field2 = rstDup!Field2
        etc.
        lngRecordID = !Key
        .Update
    End With
    rstLive.Close
    rstDup.Close

    ' Copy the modified child table records back to the live
    ' child table, replacing the foreign key
    DoCmd.RunSQL "INSERT INTO ChildTable (ForeignKey, Subfield1, Subfield2, etc.) " _
        & "SELECT " & lngRecordID & ", Subfield1, Subfield2, etc. " _
        & "FROM ChildTableDuplicate"

ErrorExit:
    ' Restore the forms' record sources, reveal the nav bar,
    ' hide the Save and Cancel buttons, and allow additions
    Me.RecordSource = "ParentTable"
    Me.Subform1.Form.RecordSource = "ChildTable"
    Me.NavigationBar = True
    Me.AllowAdditions = True
    cmdSave.Visible = False
    cmdCancel.Visible = False
    Set rstLive = Nothing
    Set rstDup = Nothing
    Set db = Nothing
    Exit Sub
ErrorHandler:
    MsgBox "Runtime error (" & Err.Number & "):" & vbcrlf _
        & Err.Description, vbExclamation
    Resume ErrorExit
End Sub

Private Sub cmdCancel_Click()
    ' Restore the forms' record sources, reveal the nav bar,
    ' hide the Save and Cancel buttons, and allow additions
    Me.RecordSource = "ParentTable"
    Me.Subform1.Form.RecordSource = "ChildTable"
    Me.NavigationBar = True
    Me.AllowAdditions = True
    cmdSave.Visible = False
    cmdCancel.Visible = False
End Sub

Final note: This approach can not support multiple users, because they might interfere with one another's records in the duplicate tables. If you need multiuser support, you probably need to create the duplicate tables on the fly, with unique temporary names. Don't forget to delete the duplicate tables when either Save or Cancel is clicked.


Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[frantik]

Hi Rick

I just cannot get this to work - still at the first stage I am afraid, I have tried and tried.

It is now failing with a syntax error at


DoCmd.RunSQL "INSERT INTO ChildTableDuplicate (ra_no, option, details) " _
& "SELECT " & lngRecordID & ", option, details " _
& "FROM Tb_options"

IngRecord is a string value because my key field is comprised of lettters and numbers - it is not autocount.

Dim IngrecordID as string was included

I have removed all indexes from the duplicate tables, and have tested that they will accept just those fields.

My sql is not very good - what have I done wrong please??

Thanks

Fran

 

[frantik]

Hi Rick

A progress report - well of sorts!!

I have copied the SQL for the parentduplicate and editted it - it seems to work a little better now, however, it is asking for the parameter value for CM34 - the value that lngRecordID corresponds to.

When I enter CM34 (the foreign key I want the record set of) - all values are copied to the childduplicate table?

What am I doing???? Does this make any sense?

thanks

Fran

 

[RickSpr]

Because lngRecordID is a string in your case, you need to modify the SQL to represent it as a string literal, in apostrophes:
& "SELECT '" & lngRecordID & "', option, details " _

(The "lng" prefix is meant to hint that it's a Long variable. For string variables, the prefix convention is "str". It will work if you don't change it, but somebody else reading your code will find it confusing if you don't.)

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[frantik]

Thanks again Rick, I apologise for my ignorance, I seem to be having problems especially with my SQL.

It is however working in a fashion, except that it does not selectively display the recordset requested, it displays all values in the child table.

All the values are copied to this table in the first place - is this right? and how can I ensure that just the recordset associated with the current record is displayed.

Thanks so much

Fran

 

[RickSpr]

Oops! My bad. It wasn't supposed to copy all the child records, but I left out the WHERE clause.

Replace this section of cmdCopy_Click:

    ' Copy the child table records
    DoCmd.RunSQL "INSERT INTO ChildTableDuplicate (ForeignKey, Subfield1, Subfield2, etc.) " _
        & "SELECT '" & lngRecordID & "', Subfield1, Subfield2, etc. " _
        & "FROM ChildTable "
        & "WHERE lngRecordID = '" & lngRecordID & "'"

Note that I inserted the apostrophes where the value of lngRecordID will be inserted into the SQL string. (Of course, you still need to substitute the appropriate names for everything.)

If that doesn't fix the problem, please copy and paste your entire cmdCopy_Click procedure here. It will be a lot easier for both of us if we can use the real names of things.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[frantik]

Thanks Rick

Am trying! but..

My key field is ra_no
parent table (query) Q_RA_Browsecriteria
child table Tb_options

bad names for sure - but I'm learning!!

If I comment out & "WHERE RecordID = '" & RecordID & "'" does not come up with an error, but copies all records!!

Cheers

Fran

Private Sub Command241_Click()
Dim RecordID As String

' Save the current record if necessary. Exit if canceled
If Me.Dirty Then
On Error GoTo ErrorExit
RunCommand acCmdSaveRecord
On Error GoTo 0
End If

' Copy the parent table record
DoCmd.RunSQL "DELETE * FROM ParentTableDuplicate"
' In the following SQL statement, you must omit the key field
#
#If I omit the key, ra_no, how do I retrieve it for the #foreign key?
#

DoCmd.RunSQL "INSERT INTO ParentTableDuplicate (ra_no, project_no, category,location, sublocation, subcategory, consequence) " _
& "SELECT ra_no, project_no, category, location, sublocation, subcategory, consequence " _
& "FROM Q_RA_Browsecriteria"

' Retrieve the key of the record just inserted, to use
' as the foreign key in the duplicate child table
RecordID = DLookup("ra_no", "ParentTableDuplicate&quot
'RecordID = DLookup("ra_no", "Q_RA_Browsecriteria&quot

DoCmd.RunSQL "INSERT INTO ChildTableDuplicate ( ra_no, Op_Consequence, OP_Likelihood, cost,uniqueid) " _
& "SELECT '" & RecordID & " ',Op_Consequence, OP_Likelihood, cost,uniqueid " _
& "FROM Tb_options" _
& "WHERE RecordID = '" & RecordID & "'"


' Adjust the form and subform to use the duplicate tables
' (or queries based on them)
Me.RecordSource = "ParentTableDuplicate"
Me.Fm_suboptions.Form.RecordSource = "ChildTableDuplicate"

ErrorExit:

End Sub

 

[RickSpr]

I hoped you would cut and paste, then copy. You left out a space after "Tb_options". It needs to be:
& "FROM Tb_options " _ (note space inserted)

#If I omit the key, ra_no, how do I retrieve it for the #foreign key?
I was assuming ra_no was an AutoNumber. You can't specify a value for an AutoNumber on an INSERT. Was my assumption wrong?

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[frantik]

Hi Rick!

my daft mistake!! Ra_No is a number that is automatically generated from the category chosen and a number field, which is updated via a routine which operates On Insert. It is not an autonumber.

Is that why it doesn't work? I have removed my space, no longer get errors, but it doesn't copy only the records selected either!

Would it work if I took the value from the ra_no text box on the form?

Thanks

 

[RickSpr]

Leaving out Ra_No when copying the parent record would definitely keep it from working, since that would leave the key Null.

We've come to a fork in the road. Now that I understand your application better, I think I see a better way to go about this whole copy-a-record process, and it's probably what you wanted in the first place. What you could do, instead of this weird temporary table stuff, is to just make a copy of the current Q_RA_Browsecriteria record, assigning it a new ra_no, and also make copies of the associated Tb_options records, inserting the same ra_no into them. The possible drawback is that these would be inserted directly into the table, which has the following consequences:
1. The 'template' records will actually be in the table before they've been changed by the user. Another user browsing or printing could see this 'bogus' record before this user finishes editing it. That could be confusing or misleading, or cause people to act on wrong information, unless they understand how this copy operation works. You would need to educate them about this.
2. If the user clicked the Copy button and then changed his mind, he must delete the record on the main form. (If you don't have Delete Cascade turned on for the relationship between Q_RA_Browsecriteria and Tb_options, he must also delete the subform records.)
3. If the user edits the main form and part of the subform, but doesn't want all of the subform records, he must delete the subform rows he doesn't want to keep.

If we take this approach, your code is smaller and simpler, your database will need compacting less often, and the user interface will be more intuitive, aside from the consequences listed above. (With the temporary table approach, the user has to go into "copy mode", edit the data, and then exit "copy mode" by either clicking a Save button or a Cancel button. While he's in copy mode, he can't browse or search for other records, because he's actually looking at the temporary tables and there are no other records in them.)

So what do you think? Do you want to make actual duplicate records in the tables (other than the key), and rely on the users to clean up any duplicate data they decide they don't want? Or should we continue developing this solution using temporary tables?

Important: I need to know the following as we continue:
1. Exactly how is ra_no generated? If it involves generating a unique number, is it critical that the numbers be consecutive, or is it OK if they have occasional gaps?
2. What is/are the key fields of Tb_options? Is uniqueid an AutoNumber?

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[frantik]

Do you know – I actually finally got it copying the right records into the right duplicate tables by adding another WHERE argument and using the text box, containing ra_no on the current form. But….

What you have just proposed sounds even better, I like the idea of more intuitive and simpler! The duplication of records at this stage is not a problem because until they are approved, any record is subject to change, that will be understood. Deleting the records is also OK.

Yes please! Making the actual duplicate records in the tables sounds perfect.

The details you requested are as below:

1.) Credit for generating the ra_no must be given to an unknown code poster, the thread I borrowed it from was unable to help with the originator either!
The ra_no is generated by taking the Category prefix from the category table, and adding the count of that category from the id table. The lastid in the id table is updated on the After Insert event on the main form. The way this is calculated does leave quite a margin for error in that two people could be entering the same category record at the same time, but I am afraid I can not think of a better way. It does not matter if they are non consecutive, or if there are gaps.

Category table (e.g.)
Category prefix
Commercial cm
Health and Safety hs
Etc.

Id table (e.g.)
Prefix last id
Cm 23
Hs 18

Code:
Function getnextidwithprefix(Prefix As String) As Variant

Dim stable As String 'table storing ids
Dim db As DAO.Database ' this database
Dim rst As DAO.Recordset 'recordset with
Dim intretry As Integer


stable = "ID"
'set the table with the ids in
Set db = CurrentDb()
'set ref to this db
Set rst = db.OpenRecordset("select * from " & stable & " where prefix='" & Prefix & "';", dbOpenDynaset) 'opentable with counter
'open query which looks for a record that matches the prefix
If rst.EOF And rst.BOF Then
'if there are no records found then this is a new prefix
rst.AddNew
rst!Prefix = Prefix
'store the prefix
rst!Lastid = 1
'set the counter going
rst.Update
'save the rec.
rst.MoveFirst
'move to the 1st rec.
Else
'prefix already exists
rst.MoveFirst
'move to the record
rst.Edit
'increase the counter
rst!Lastid = rst!Lastid + 1
rst.Update
'save the rec
End If

getnextidwithprefix = Prefix & Format(rst!Lastid, "0000&quot
Forms![Fm_Risk_Ass]!Text83 = Prefix & Format(rst!Lastid, "0000&quot
'return unique id

rst.Close ' tidy up
Set rst = Nothing
db.Close
Set db = Nothing

Exitgetnextid:
Exit Function

End Function

Private Sub Form_AfterInsert()

Dim UID As String
UID = DLookup("UID", "N_tblLocalUser&quot
On Error Resume Next

[Fm_tbstatus].Form![Text6] = UID
[Fm_tbstatus].Form![UserID] = UID

Call getnextidwithprefix([Forms]![Fm_Risk_Ass]![Cb_Cat])
End Sub

2.) Key fields of Tb_options are option and ra_no (combined key). Uniqueid is me playing around, it is unique but plays no part in the app!!!!

Thanks Rick.

 

[RickSpr]

There's a problem with the way you're using getnextidwithprefix. It's a function that returns a value, but you're using a Call statement to call it, and internally it's setting Forms![Fm_Risk_Ass]!Text83. You should delete this line from the function:
Forms![Fm_Risk_Ass]!Text83 = Prefix & Format(rst!Lastid, "0000&quot
and change the Call statement to:
Forms![Fm_Risk_Ass]!Text83 = getnextidwithprefix([Forms]![Fm_Risk_Ass]![Cb_Cat])
I need you to do this so that the code for the Copy button can get an ra_no without the function trying to store it in the form. (If you call getnextidwithprefix from anywhere else in your code, you need to make the change in those places as well.)

Private Sub Command241_Click()
Dim NewRA_no As String
Dim rst As DAO.Recordset

' Save the current record if necessary. Exit if canceled
If Me.Dirty Then
On Error GoTo ErrorExit
RunCommand acCmdSaveRecord
On Error GoTo 0
End If

' Get an ra_no to use with the duplicate record
' TODO: Change the category field if necessary
NewRA_no = getnextidwithprefix(category)

' Duplicate the Q_RA_Browsecriteria record, replacing the ra_no with the new one
DoCmd.RunSQL "INSERT INTO Q_RA_Browsecriteria " _
& "(ra_no, project_no, category, location, sublocation, subcategory, consequence) " _
& "SELECT '" & NewRA_no & "', project_no, category, location, sublocation, subcategory, consequence " _
& "FROM Q_RA_Browsecriteria " _
& "WHERE Ra_No = '" & ra_no & "'"

' Duplicate the Tb_options records, replacing the ra_no with the new one
DoCmd.RunSQL "INSERT INTO Tb_options " _
& "(ra_no, Op_Consequence, OP_Likelihood, cost, uniqueid) " _
& "SELECT '" & NewRA_no & "', Op_Consequence, OP_Likelihood, cost, uniqueid " _
& "FROM Tb_options " _
& "WHERE ra_no = '" & ra_no & "'"

' Position the form to the new record
Set rst = Me.RecordsetClone()
rst.FindFirst "ra_no = '" & NewRA_no & "'"
If rst.NoMatch Then
MsgBox "Programming error - couldn't position to new record", vbCritical
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing
ErrorExit:
End Sub

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[frantik]

Hi Rick

Have done as you have suggested. It copies records into the tables with a new ra_no, it only takes the first record from the options table through- and brings up the couldn't position to new record box.

I have asked it to add the record to the table behind the query as well, have tried it both ways! It works the same.

thanks so much for this I can see it working!

Thanks

Fran

 

[RickSpr]

If there are multiple Tb_options rows before clicking the Copy button, they should all be copied with the new ra_no. Have you opened the table itself to check?

If it's really only copying one record when there are several with the original ra_no, I'm stumped. It can't be getting errors on records after the first, or you'd see a runtime error message describing the error.

I think I might know what's causing the record positioning error, though. New records don't automatically show up in a form's recordset, unless the form itself added them. We need to requery the form before we search for the new record. Modify the code by inserting the boldface line below.
' Position the form to the new record
Me.Requery
Set rst = Me.RecordsetClone()


Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[frantik]

Hi Rick

Works perfectly except for one thing - my table design. The key fiels of the options table is actually comprised of two fields, ra_no and option. Option is an autonumber that counts the options for that assessment.

If I remove the key from the table and don't append the field option it works perfectly.

I guess it is the autonumber that is causing the problem. Is there anyway I can number the options, without using autonumber? - I can't leave the field blank as it is part of the key!!

Thanks

Fran.