When i try to execute the code via a comand button, i get an error and the duplicate record operation does not occur. one thing i noticed is that i have a lot of lookup fields (i.e. FK's to lookup tables one-to-many relationships) in underlying table being populated by the form. i've created combo boxes on the form to populate the FK's in the underlying table. the error that occurs when trying to use the above code produces a "paste errors" table and in that paste errors table instead of the bound column values from the combo boxes (i.e. PK values from the lookup tables) it shows the display values from the combo boxes. i'm not sure if this has do with anything, but i couldn't figure out why it was doing this.
does anyone have any ideas how i could get this duplicate record procedure to work?
I am having an issue with one person having trouble getting an error when trying to duplicate a record.
The subform is set up with a button to duplicate a record. This allows most of the data that stays the same to be copied to a new records and then minor changes can be made to update the record for the new month. Since there are memo fields, in these records, this saves a lot of retyping. One person who is using the database gets the following error when clicking on the duplicate record button.
The command or action 'Copy' isn't available now.
It makes a new blank record but won't copy over the information. You can go to the record and copy each field individually and paste it into the new record, but this is not very practical.
I have not been able to duplicate the error and no one else using the database is having this happen.
Having a problem when saving a record that has been edited and contains a duplicate field. Here is what I'm doing.
I have an existing record that is being viewed by the user. I have an edit button on the form that is displaying the record. When the user clicks the edit button I do the following
'User clicked on edit customer record
Private Sub CustEditRec_Click()
'Store current customer key in string so we can cross
'Set customer record test string so we can determine
'what the user is doing
CustomerRecStat = "edit"
' Go unlock the customer data fields for editing
UnlockCustomerFields
TxtCompanyName.SetFocus
'Go Unlock the customer editing buttons
UnLockCustomerAddButtons
'Lock the add, delete, edit buttons
CustAddRec.Enabled = False
CustEditRec.Enabled = False
CustDelRec.Enabled = False
'Go Disable the customer navagation buttons
DisableCustomerNavigation
txtCustomerKey.Visible = True
txtCustomerKey.Locked = False
txtCustomerKey.Enabled = True
'Set focus on the customer key
txtCustomerKey.SetFocus
'disable & hide the customer key combobox
cmboCustomerKey.Locked = True
cmboCustomerKey.Enabled = False
cmboCustomerKey.Visible = False
Exit_CustEditRec_Click:
Exit Sub
Err_CustEditRec_Click:
MsgBox Err.Description
Resume Exit_CustEditRec_Click
End Sub
The field which duplicate entries are not allowed in the table is txtCustomerKey. Now remember we are just editing a record NOT ADDING A NEW ONE.
When the user finished making the changes to the record we use the same procedure to save the changes as we when the user is adding a new record...here it is.
'User clicked save customer record
Private Sub CustSaveRec_Click()
On Error GoTo Err_CustSaveRec_Click
SaveCustomerRecord:
'Update the table data fields with the data contained on the form
'*** IF WE GOT THIS FAR WITH OUT ERRORS WE SAVED THE RECORD
'*** GO AHEAD & DISABLE THE FORMS VARIOUS FIELDS
'*** & BUTTONS ONCE AGAIN AS WE ARE JUST BACK TO VIEWING
'*** THE CUSTOMERS DATABASE
'Enable and unlock the customer key field
txtCustomerKey.Visible = True
txtCustomerKey.Locked = False
txtCustomerKey.Enabled = True
'Hide & disable the customer keycombo box
cmboCustomerKey.Locked = True
cmboCustomerKey.Enabled = False
cmboCustomerKey.Visible = False
'Set focus on the customer key field
txtCustomerKey.SetFocus
'Lock the customer fields
LockCustomerFields
'Enable the navigation buttons
EnableCustomerNavigation
'Lock the customer adding buttons
LockCustomerAddButtons
'Clear the record testing status
CustomerRecStat = ""
txtCustomerKey_AfterUpdate
Exit_CustSaveRec_Click:
Exit Sub
Err_CustSaveRec_Click:
'If the error generated was by a duplicate value.
'This can only be caused by the customer key as this
'is the only field which does not allow duplicate values.
'so warn the user of this duplicate value error and set
'the focus on the customer key field
If Err.Number = 3022 Then
'if user is editing a record
If CustomerRecStat = "edit" Then
'And the entered customer key has not changed
If Me.txtCustomerKey = EditCustKey Then
'Return to saving the record as the key is
'not really a duplicate
GoTo SaveCustomerRecord
End If
End If
Select Case MsgBox("This Customer ID was already located in the database. Click OK to enter a new Customer ID or Cancel to stop adding this record?", vbExclamation + vbOKCancel + vbDefaultButton1, "Duplicate Customer ID")
Case vbOK
Me.txtCustomerKey.SetFocus
Resume Exit_CustSaveRec_Click
Case vbCancel
'Go simulate undo record click
CustUndoRec_Click
Resume Exit_CustSaveRec_Click
End Select
End If
MsgBox Err.Description
Resume Exit_CustSaveRec_Click
End Sub
The problem is when the user is editing a record. The database assumes the txtCustomerKey is a duplicate in the table. However we are not adding a new record so the duplicate error is false. Its just that the txtCustomerKey is the same as the record being edited. Its not DUPLICATED its the SAME.....
Any help anyone? Sorry for the long post but I'm a strong believer in the more information the better when trying to solve a problem....
I need to be able to produce an error on a form where the user attempts to create a new user that already exists. The error needs to be produced when the save button is clicked.
See below for the existing code on my save button which exists in form, 'frmAddEditUser':
Private Sub cmdSave_Click() If IsNull(cboManagerID) = True Then MsgBox "Please select a manager", vbExclamation, "Details Missing!" Me.cboManagerID.Requery
[Code] ....
Currently, if I attempt to create a user with the same Payroll ID as an existing user, my 'User details saved' message box is displayed and to the user it looks like a new record has saved successfully which isn't the case.
I need a message box to appear displaying something like "Payroll ID n already exists" where n is the 'PayrollID' of the duplicate record. (Is it possible to combine a value from a certain control and ones own custom text?)
Note: PayrollID is a combination of letters and numbers, i.e. two letters, followed by 5 numbers, e.g. JD12345.
For reference, the table/field and and control are called, '[tblUser].[PayrollID]' and 'txtPayrollID' (on 'frmAddEditUser') respectively; it is from this field and control that I want the error to be produced if the user is entering the same values which are existing in 'tblUser'.
error message I am getting when I click on my Duplicate Record button (created through the wizard).
I have two combo boxes on the main form that populates data when the user makes a selection from the combo box. First combo box populates project data and the second combo box populates equipment data. The form is working well with the two combo boxes populating the data into the main table.
Now I would like to add a duplicate record button to copy a record and paste the data as a new record. So, I added a duplicate record button using the wizard and I am receiving the following AfterUpdate error.
Run-time error 3020: Update or CancelUpdate without AddNew or Edit.
This is the code I am using to copy and paste a duplicate record:
Private Sub InputForm_DupRec_Button_Click() On Error GoTo Err_InputForm_DupRec_Click DoCmd.RunCommand acCmdSelectRecord DoCmd.RunCommand acCmdSaveRecord DoCmd.RunCommand acCmdCopy DoCmd.RunCommand acCmdPasteAppend Exit_InputForm_DupRec_Click: Exit Sub Err_InputForm_DupRec_Click: MsgBox Err.Description Resume Exit_InputForm_DupRec_Click
It seems to be duplicating the data from the first combo box, but not the second one where it errors out.
In the above field i had set Sno is PRIMARY KEY. The problem is after entering all field, then only it will show the duplicate Sno error. What I want is, if Sno is duplicate, when i type SNo , successively i have to get the SNo is duplicate.
the below scrip was taken from another forum member.
i have put this in the Forms / error VBA
but i still a little lost
i have set 3 fields to be indexed not to allow duplicate information (Forename, Surename, DOB)
i was then advised to error trap so it would highlight that i have enterd a duplicate record on typing in the DOB (As that as third feild to be entered)
so i then used the below script, however this doesnt highlight that it is a duplicate it just doesnt let me go on to the next record, the only time the error message is highlighted is when i go into design mode.
anyideas on where to go from here.
Private Sub Form_Error(DataErr As Integer, Response As Integer) If DataErr = 3022 Then Response = acDataErrContinue MsgBox "Tis MerID alredy exist!" Exit Sub End If If DataErr = 2169 Then Response = acDataErrContinue MsgBox "Tis record will not be saved!" Exit Sub End If
Hi, I have a rather large database that was built in Access '97 and recently converted to 2003. I have a couple problems now. I get an error message about not being able to update the form because it would create a duplicate value in the primary key, index, or relationship. (Error 3022) I didn't build this particular database so I'm not totally sure why somethings are done the way they are. I've spent a long time going through everything to try to understand the relationships and I've asked lots of people at work to look at it, but no one seems to understand why it's doing what it's doing. The Access Help information has helped me understand some things better, but not this problem. I've gone through every table and removed all primary keys and set all indexes to allow duplicates. Then, when I was looking at the relationships and object dependancies, I think everything looks to be in order. However, when i add info directly to the table instead of using the form, the form is not updated with the new info. the table keeps the information, but isn't communicating with the form and i suspect reverse is true which is where the error is coming from. But how do I detect that for sure and how can I fix it with out starting over? I've gone through and added data to one field in the form at a time to try and narrow down what field is causing the problem, but no matter what i get the same error. Is there a way to find out exactly what field is causing the error? Thanks for any help you can offer!
I created a subforum with an updateable query, and this works. I can view the results of the query, modify them and add new records.
However I shortly realized that once I deleted a record (either by some VBA code, or by SQL) that I could not re-add the same record through the subform.
I've made sure that the record is deleted, so there is no duplication of the key. I've made sure that Index is Duplicates OK, so there is no duplication issues there.
I have a table with one primary key (ClientID) which is an autonumber (cannot be duplicated), and another field which is a number field which is set to a random 6 digits (also cannot be duplicated) whenever a new client record is created.
Now, the main database is on a desktop, and my partner and i are connected to this main database through our laptops whenever we're in the office. For the last 6 months, I had only the ClientID in the form (from the Client Table), and everything would work fine whenever we created a new record in the Form, even if we created a new record at the same time on our personal laptops, the numbers would automatically be increased by one w/ no error.
I just added the other field now, the regular 6 digit random number field, and sometimes (only sometimes) when we both have a different client and start a new record, we get error messages that says we're creating duplicates. When that error message comes up, one of us has to close down the form completely, and reopen and start a new record again. Not sure why this is happening. Here is the error message.
I have a form - see attached image. The first text box is called Job_No. When a number is entered here how can I check that the number doesn't already exist when the user tabs to the next box. Then if it does exist display a custom message to the effect "This already exists" and not the Access default duplicate error message.
I have a form which is based on student records. What I'm trying to get Access to do is to bring up an error message as soon a duplicate student number has been entered. At present I've managed to replace the Access standard error message 3022 with my own by using the following code:
Private Sub Form_Error(DataErr As Integer, Response As Integer) Const conDuplicateKey = 3022 Dim strMsg As String
If DataErr = conDuplicateKey Then Response = acDataErrContinue strMsg = "Duplicated record. Please recheck your data." MsgBox strMsg End If End Sub
However the error message only appears when you try to leave the form (e.g. start a new record). I would like the error message to appear as soon as the user leaves the Student Number text box on the form. Is there anyway to do this?
I have a table with about 3600 keys, numbered from 1 to 3600. When I try to enter a new record, the autonumber starts in the 20's. I get an error message about duplicate keys, and I have to cancel. If I try again the autonumber goes up by one, but it will take hours to do this with approximately 3600 tries to get past the highest exisiting key number. Is there a way to make Access reset this? THis table is linked to a many-to-many table and I can't start over or I will lose all my relationships.
So now, I'm using the code below to prevent duplicate name entry and it is working great - EXCEPT when I enter a first or last name which contains a ' (ie, O'Tool, O'Malley, O'Hern)...anyone got any ideas for me on how to make this not happen?
The error I get is:
Run Time error '3075'
Syntax error (missing operator) in query expression '[Last Name]='O'Hern' And [First Name]='Lori'.
The code i'm using is:
Private Sub Last_Name_AfterUpdate()
'Check for duplicate first and last name using DCount
If DCount("*", "[Constituents]", "[Last Name]= '" & Me![Last Name] & "' And [First Name] = '" & Me![First Name] & "'") > 0 Then Beep MsgBox "This first and last name already exists in the database. Please check that you are not entering a duplicate constituent before continuing.", vbOKOnly, "Duplicate Value" Cancel = True End If
How to control the below message to something like this through VBA:
"The Job: " & JobNumber & " already exists in the table." or to open an alternate form where they can make the change. Allowing duplicates is not appropriate for what I am trying to accomplish. I just want a friendly way of rerouting a user to understandable answer.
instead of this:
"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."
I was using the following code on a field (ItemCode) that was indexed to prevent duplication of records. The intent is that the user will get an error message that a duplicate exists before they enter all the data for the record and get the built in error message that Access 2003 provides when an index violation has occurred.
Code: Private Sub ItemCode_BeforeUpdate(Cancel As Integer) Dim Answer As Variant Answer = DLookup("[ItemCode]", "tblQuestions", "[ItemCode] = '" & Me.ItemCode & "'") If Not IsNull(Answer) Then MsgBox "Item Code already exists" & vbCrLf & "Please enter unique Item Code.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate" Cancel = True Me.ItemCode.Undo Else: End If End Sub
Now, the index for this is based on two fields (ItemCode and Question Group). I would like to display the same message before update but don't know how to include the second field in the syntax.
I have a database with a table that uses a numeric field as a PK. If I use the built in "Duplicate" Command button on a form it creates an exact duplicate of the record, which then Access won't allow because the PK is no longer unique.
Does anyone know of a way that I could make the number be the max number + 1 when I click the "Duplicate" Button?
I have a recored set and i dont want a duplicate of the record set in the same table.
Say if my table name is businessskills and contains three fields(columns) and their names(Email , B_code, Rating) highlighted in red with data are as below
The access table has one primary key StudentID which is indexed with no duplicates. (StudentID consists of schoool ID + an auto increment #)
When a second student is entered with the same name and birthdate into the table but with a different studentid the record cannot be saved as there is a duplicate value. If the birthdate is removed the record can be saved. If another character is entered after the first name with the birthdate the record can be saved.
I have tried adding time to the record, that did not solve it. I tried changing name and birthdate to index yes duplicates okay that did not solve it.
The only relationship is on studentid, I cannot understand why the birthdate is the problem when there is no relationship or primary key on that value.
Has anyone experienced this issue and can explain why access views this as a duplicate record when in fact it is not!
I have a form (with subform in it) and i fill it with different values. After some time, i need to fill another record (new one), with similar values.Is it possible to make a duplicate of my previous record, and put it in the "new record" so that i won't have to fill all fields again, only change excisting ones to different values.
On my form I've got an afterupdate event that checks if the information entered already exists and this works absolutely fine. However what I would like is the option to go to the existing record if one is found, but I can't get it to work.
This is my code currently;
Code: Private Sub Job_No_AfterUpdate() If DCount("*", "PACKING", "[Job No>]='" & Me.[Job No] & "'") > 0 Then If MsgBox("Job Number already exists! Go to record?", vbYesNo, "DST PLANNER") = vbYes Then Dim rs As Object
[Code].....
The check for the Job Number works fine but when I click Yes on the message box, the form stays on the current record instead of moving to the existing record.
i managed to get checking for duplicate record by ONE field ie member_no
code :
If DCount("*", "runner", "member_no = " & Me.member_no) > 1 Then MsgBox " This member is already exist!" & vbCrLf Me.c_memberid.SetFocus Else DoCmd.CancelEvent End If
how i want to get checking duplicate record by TWO fields ie member_no and run_no ?
I have a search form passing text from unbound controls to a query. These two fields (AuthorName & Title) of the query are from two different tables (Authors & Papers) set up in a many to many join. The 3 dummy records I am using are as follows:
Record 1 AuthorName: Smith (Author 1) Title: SmithTitle
Record 2 AuthorName: Smith (Author 1) AuthorName: Jones (Author 2) Title:SmithJonesTitle (i.e. two authors for this record)
Record 3 Author: Jones (Author 2) Title: JonesTitle
If I stick in 'Jones' into the Author field and 'JonesTitle' in the Title field I correctly get 1 result from the query (record 3). However, oddly, if I put in Smith and SmithTitle I get two records (2 x record 1)!!!!! There aren't two records!!! Similarly if I leave both blank I get all the records and again there is a duplicate of the Smith record (and only this one is duplicated!)
The query criteria are:
[AuthorName] Like [Forms].[Search]![Author] & "*" [Title] Like [Forms].[Search]![Title] & "*"
