duplicate error message

[Jennyrd]

I actually have two questions:
1) Is there a way to duplicate a record without duplicating all of the
fields? I have a button that I set up using the wizard that duplicates the
record, but it also duplicates fields that I do not want to accept
duplicates. So if I hit duplicate the record, it pops up with an error about
the table not accepting duplicates for this field. Any good ideas?
2) I need a message box to pop up if someone inputs a duplicate unique id.
I was trying to use this text on the Form_Unload property, but it keeps
coming up with "syntax error missing operator on unique id" Can anyone see
the error in this code. I'm at the end of my rope! Thanks!!!!!

If Not IsNull(DLookup("Unique ID", "Census", "Unique Id='" & uniqueid &
"'")) Then
Cancel = (MsgBox("The Unique ID you entered already exists.&vbnewline&Is
this your intention?", vbYesNo = vbNo))
End If

 

[Ofer Cohen]

1. You can use an uppend query to insert the values you want
2. Use the before update event of the form instead of Unload event, so the
break will happen before you insert the record to the table.

Also, why do you use it as responed message if you can't insert the record
if it's duplicate?

Use instead
If Not IsNull(DLookup("Unique ID", "Census", "Unique Id='" & uniqueid &
"'")) Then
MsgBox "The Unique ID you entered already exists"
Cancel = True
End If

 

[missinglinq via AccessMonster.com]

Acess tells you you should have a Primary Key for all tables, then provides a
record copy function via the Comman Button Wizard that won't copy records if
they have a Primary Key! What a surprise!

The strategy is use is to assign thefields I want to copy to the new record
to variables, go to a new record then reverse the process:

########### Code ##################

Private Sub CopyRecord_Click()

'Copy fields to variables
MyFirstField = Me.FirstField
MySecondField = Me.SecondField
MyThirdField = Me.ThirdField

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

'Plugs old values into new record
Me.FirstField = MyFirstField
Me.SecondField = MySecondField
Me.ThirdField = MyThirdField

End Sub

 

[Jennyrd]

Thanks for replying! This worked except for certain fields. Not sure why.
These are all combo boxes or text boxes. Other combo boxes are populating.
Here's the code I'm using ( I put * next to the fields that are not
duplicating so you can see):

Private Sub Duplicate_Record_Click()

'Copy fields to variables
Site = Me.Site*
Report = Me.report_to
Cost = Me.cost_center
Function_box = Me.Function_box*
Department = Me.dept_name*
Country = Me.Country_Box
EPMLocal = Me.EPM_local
RWT = Me.RWT_Combo
Typebox = Me.Type_box
st_date = Me.st_date*
end_date = Me.end_date*
Agency_box = Me.Agency_box*
FTE = Me.FTE_box*
Secondment = Me.Secondment
Secondment_RWT = Me.Secondment_RWT*
STD = Me.STD
Terminated_Box = Me.Terminated_Box
Comments = Me.Comments
'Go to a new record
DoCmd.GoToRecord , , acNewRec

'Plugs old values into new record
Me.Site = Site*
Me.report_to = Report
Me.cost_center = Cost
Me.Function_box = Function_box*
Me.dept_name = Department*
Me.Country_Box = Country
Me.EPM_local = EPMLocal
Me.RWT_Combo = RWT
Me.Type_box = Typebox
Me.st_date = st_date*
Me.end_date = end_date*
Me.Agency_box = Agency_box*
Me.FTE_box = FTE*
Me.Secondment = Secondment
Me.Secondment_RWT = Secondment_RWT*
Me.STD = STD
Me.Terminated_Box = Terminated_Box
Me.Comments = Comments

Exit_Duplicate_Record_Click:
Exit Sub

Thanks alot for any help you can provide.
-Jenny

 

[Jennyrd]

Never mind my previous post, I got it. I had to name the variables something
very different to get them to copy. It works!! Thanks!!!
-Jenny