Duplication of record

[Dophia]

Hi Everyone:

I have a form on which there was a "duplicate record" button, created with the wizard. Now it now longer works.The error is that the "append/paste is not available now". But, if I go into the query, I can copy and paste a new record, after I change the primary key's value.

Can anyone suggest what could be happening to stop the duplication?

Sophia

 

[RuralGuy]

You probably need to save the current record first.
If Me.Dirty Then Me.Dirty = False

HTH RuralGuy (RG for short) acXP winXP Pro
Please respond to this forum so all may benefit

 

[Dophia]

Thanks for your suggestion, but the current record is already saved. What happens is that someone can go back to a previous record with the same data that they want to copy and then they duplicate it.

Sophia

 

[RuralGuy]

In that case, is your RecordSource updateable?

HTH RuralGuy (RG for short) acXP winXP Pro
Please respond to this forum so all may benefit

 

[TheAceMan1]

How are ya Dophia . . .

To tell if the [blue]Recordset[/blue] of a [blue]form or query[/blue] is [blue]updateable[/blue] have a look at the AddNew button

, if it has that disabled look

then the recordset is uneditable!

Note: for the proper indication in forms the [blue]Allow Additions[/blue] property has to be set to [purple]Yes[/purple]. If set to [purple]No[/purple] the visual effect is the same as [blue]disabled![/blue] .

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[missinglinq]

The problem with the Wizard's Duplicate Record routine is that if part of the record that's being copied is a Primary Key, Access won't allow it, as it would violate the PK's uniqueness!

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[RuralGuy]

Good point linq! I looked at it and you are correct. Still no response from the OP.

HTH RuralGuy (RG for short) acXP winXP Pro
Please respond to this forum so all may benefit

 

[Dophia]

Hello Everyone: Thanks for all of your help.

The recordset is updateable and the Allow Additions property is set to "yes".

I am copying the primary key, but before saving it, I had a message appear for the user to change the user key to another unique number.

I have been trying to fix this for a long time. I have basically started the form over and I am down to the bare bones with no other code attached, except for the duplication command.

For some reason, the error I get now is ..."The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, index or relationship. Change the data in the field or fields that contain the duplicate data, remove the index, or redefine the index to allow duplicate entries and try again."

The form's duplicate command worked for months and now it doesn't. I have gone back to previous copies to see if there has been a change that I couldn't remember, but not that I have found.

This is my query, which is updateable.
SELECT tblAnimals.Pound_Sheet_No, tblAnimals.Animal_ID, tblAnimals.Invoice_No, tblAnimals.Current_Status, tblAnimals.Animal_Admission_Category, tblAnimals.Cruetly_File_No, tblAnimals.Release_Date, tblAnimals.Muliple_Admission, tblAnimals.Arrival_Date, tblAnimals.Arrival_Time, tblAnimals.ACO_Clerk, tblAnimals.Pick_up_Time, tblAnimals.Cage_No, tblAnimals.Cage_Rm_No, tblAnimals.FosterParent_Name, tblAnimals.Category_ID, tblAnimals.Sex, tblAnimals.Altered, tblAnimals.[Vaccinated_?], tblAnimals.[Declawed_?], tblAnimals.Collar, tblAnimals.Tag_No, tblAnimals.Chip_Tatoo_ID_Mark, tblAnimals.[Animal's_Name], tblAnimals.Character_Comment, tblAnimals.[Cat Breed], tblAnimals.[Dog Breed Main], tblAnimals.[Dog Breed Mix], tblAnimals.[Other animal breed], tblAnimals.Colour_animal, tblAnimals.Date_of_birth, tblAnimals.Age_Estimated, tblAnimals.Age_Estimated_Mth_Yr, tblAnimals.Weight, tblAnimals.Found_at, tblAnimals.Corner_of, tblAdmission_description.Admission_description, tblAdmission_description.PS_No, tblAdmission_description.Sick, tblAdmission_description.Injured, tblAdmission_description.Deceased, tblAdmission_description.Abandoned, tblAdmission_description.At_Large, tblAdmission_description.Home_Pick_Up, tblAdmission_description.Found_by_ACO, tblAdmission_description.Found_by_Citizen, tblAdmission_description.Dropped_Off, tblAdmission_description.Quarantine, tblAdmission_description.CT, tblAdmission_description.Balancing_field, tblAnimals.Animal_Out_Category, tblAnimals.Euthanized, tblAnimals.Other, tblAnimals.Euthanize_ACO, tblAnimals.Dosage, tblAnimals.Euthanized_Date, tblAnimals.Comment, tblAnimals.Other_Reason, tblAnimals.Donation, tblAnimals.Euthanization_Fee, tblAnimals.PickUp_Fee, tblAnimals.Out_of_Timmins_Fee
FROM tblAnimals INNER JOIN tblAdmission_description ON tblAnimals.Pound_Sheet_No = tblAdmission_description.PS_No
ORDER BY tblAnimals.Pound_Sheet_No;


Sophia

 

[ProgramError]

I use the following sql statement. The where criteria selects the current record by identifying its unique ID. Put in the appropriate field and table names.

strSQL = "INSERT INTO table " & _
"SELECT DISTINCT all the fields separated by commas except the primary" & _
"FROM originating table " & _
"WHERE a certain field = identifying criteria"
Debug.Print strSQL
DoCmd.RunSQL strSQL

Ian Mayor (UK)
Program Error
Always make your words sweet and nice. Because you never know when you may have to eat them.

 

[TheAceMan1]

Dophia . . .

See my code here! thread702-1382356

The code shows how to transfer any record to new via defaults. Since you PK is autonumber just don't set its tag property.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[Dophia]

Thank you for your help. Sorry for the delay, but I was away for a few days.

Aceman: I tried what you suggested, but I get a "write conflict" and it asks if I want to overwrite the record, which I do not. Do you know what I am doing wrong? I put the "?" in the "smart tags" properties of the fields I wanted to copy.

ProgramError: I need a little more guidance. Where do I put the SQL? In the form's module on the control button?

PS I was able to get an even older copy of my db and I copied the form and subforms to my new db and it works!! The only problem is that there has been more code since I used the old db....but even when I delete the new code, to see if it is the problem, it still doesn't work!

Is there anyway to compare two forms with their controls?

Sophia

 

[TheAceMan1]

Dophia . . .

I can't tell unless you post what you tried! . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[Dophia]

Aceman: I used the code from the link that you suggested:

Private Sub btnDuplicate_DblClick(Cancel As Integer)
Dim ctl As Control, DQ As String
DQ = """"
If Not Me.NewRecord Then
For Each ctl In Me.Controls
If ctl.Tag = "?" Then
ctl.DefaultValue = DQ & ctl & DQ
End If
Next
End If
End Sub

I put the "?" in the fields that I wanted duplicated in the properties smart tag.

Sophia

 

[TheAceMan1]

Dophia . . .

Is the name of your button (cherck the [blue]Name[/blue] property) [purple]btnDuplicate?[/purple]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[Dophia]

Aceman: I checked and it is correct.
Sophia

 

[Dophia]

I have the strangest update to my problem. I copied the old forms and queries to my new db, with a "2" at the end of the names, to distinguish between old and new. Now, my form which didn't work, works and I didn't do anything at all. I tried this again from scratch and the same result!

What's happening??????
Sophia

 

[TheAceMan1]

Roger That Dophia! . . .

In the link I provided The code was set to trigger on the forms [blue]Double-Click[/blue] event. You have it set to trigger on the buttons [blue]Double-Click[/blue] event.

You can either double-click the button or move the code to the [blue]On Click[/blue] event of the button and execute a single-click. For testing without making any changes just perform a [blue]Double-Click[/blue] of the button!

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[Dophia]

Aceman: I still get the same error message. It is not creating a new record. Is there something else that I am missing?

Sophia

 

[TheAceMan1]

Dophia . . .

Hmmmmmm . . . and when you entered the [blue]?[/blue] in the [blue]Tag[/blue] property of the controls, was it with [blue]"?"[/blue] or without quotes [blue]?[/blue] Should be without [blue]?[/blue] . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[Dophia]

Aceman: I had the ? in the "smart tag", in error. I have fixed this, but I still get the error. Is the code supposed to create a new record?

Sophia