If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Check for Duplicate Records
I have a table in which there are records containing serial numbers and other
data describing items in the record. The serial number is supposed to be unique to each record and is the record key. When I try to enter a record with a duplicate serial number, Access displays two messages when I leave my data entry form. The messages are “The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship" (which is apparently Error 3022). and a message to the effect that I cannot save the record at this time. This is exactly what I want to happen , however a. could you please advise me on how I can create some code that would inform the user in a user-friendly message that he/she is attempting to allocate a serial number that has already been used ? b. to which event the code should be attached? c. do I need to use VB or can I create a macro to achieve this? I tried the following code in various events linked to the serial number field, but this did not work. However, I am not very good at creating VBcode!! If (Me.RecordsetClone.RecordCount) 0 Then MsgBox "This serial number has been used previously", , "TEST" Cancel = True End If End Sub I am using Access 2002 with Access 2000 file formats -- Thanks in anticipation. |
#2
|
|||
|
|||
Check for Duplicate Records
Hi Rillo,
good place to put code to check for duplicates is the Before Update event for the form (note this is not the same as the control's before update event). Here is some sample code for a number field/control called ClientID -- Prviate Sub Form_BeforeUpdate(Cancel As Integer) Dim strCriteria As String strCriteria = "[ClientID] = " & Me.ClientID 'Debug.Print strCriteria If Me.NewRecord Or Me.ClientID Me.ClientID.OldValue Then If DCount("*", "tblRef", strCriteria) 0 Then Cancel = True End If End If End Sub Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Rillo" wrote in message ... I have a table in which there are records containing serial numbers and other data describing items in the record. The serial number is supposed to be unique to each record and is the record key. When I try to enter a record with a duplicate serial number, Access displays two messages when I leave my data entry form. The messages are "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship" (which is apparently Error 3022). and a message to the effect that I cannot save the record at this time. This is exactly what I want to happen , however a. could you please advise me on how I can create some code that would inform the user in a user-friendly message that he/she is attempting to allocate a serial number that has already been used ? b. to which event the code should be attached? c. do I need to use VB or can I create a macro to achieve this? I tried the following code in various events linked to the serial number field, but this did not work. However, I am not very good at creating VBcode!! If (Me.RecordsetClone.RecordCount) 0 Then MsgBox "This serial number has been used previously", , "TEST" Cancel = True End If End Sub I am using Access 2002 with Access 2000 file formats -- Thanks in anticipation. |
#3
|
|||
|
|||
Check for Duplicate Records
On Sun, 13 Sep 2009 16:37:01 -0700, Rillo
wrote: This is exactly what I want to happen , however a. could you please advise me on how I can create some code that would inform the user in a user-friendly message that he/she is attempting to allocate a serial number that has already been used ? b. to which event the code should be attached? c. do I need to use VB or can I create a macro to achieve this? You should use the BeforeUpdate event of the serial number control to do so (or, in some circumstances, the BeforeUpdate event of the Form). I would use VBA code, though in 2007 it may be ok to use macros - I'm not well versed in them, though. The VBA code for a textbox named txtSerialNo, to check for duplicates in the field SerialNo in table MyTable, would be Private Sub txtSerialNo_BeforeUpdate(Cancel as Integer) If Not IsNull(DLookUp("SerialNo", "MyTable", _ "SerialNo = " & Me!txtSerialNo) Then MsgBox "This serial number has already been entered", vbOKOnly Cancel = True End If End Sub If the serial number is a Text datatype, you need some quotemarks: If Not IsNull(DLookUp("SerialNo", "MyTable", _ "SerialNo = '" & Me!txtSerialNo & "'") Then You can get fancier, offering to jump to the record for that serial number, etc. if you wish - post back for more details. -- John W. Vinson [MVP] |
#4
|
|||
|
|||
Check for Duplicate Records
Hi Jeanette
Thanks for this. Please excuse my ignorance - as I said Vb code is not something with which I am very familiar. I assume that in your code I can replace ClientID with SerialNumber (the name of my field). However, what is tblRef and where do I place the code re my MsgBox? Thanks again "Jeanette Cunningham" wrote: Hi Rillo, good place to put code to check for duplicates is the Before Update event for the form (note this is not the same as the control's before update event). Here is some sample code for a number field/control called ClientID -- Prviate Sub Form_BeforeUpdate(Cancel As Integer) Dim strCriteria As String strCriteria = "[ClientID] = " & Me.ClientID 'Debug.Print strCriteria If Me.NewRecord Or Me.ClientID Me.ClientID.OldValue Then If DCount("*", "tblRef", strCriteria) 0 Then Cancel = True End If End If End Sub Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Rillo" wrote in message ... I have a table in which there are records containing serial numbers and other data describing items in the record. The serial number is supposed to be unique to each record and is the record key. When I try to enter a record with a duplicate serial number, Access displays two messages when I leave my data entry form. The messages are "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship" (which is apparently Error 3022). and a message to the effect that I cannot save the record at this time. This is exactly what I want to happen , however a. could you please advise me on how I can create some code that would inform the user in a user-friendly message that he/she is attempting to allocate a serial number that has already been used ? b. to which event the code should be attached? c. do I need to use VB or can I create a macro to achieve this? I tried the following code in various events linked to the serial number field, but this did not work. However, I am not very good at creating VBcode!! If (Me.RecordsetClone.RecordCount) 0 Then MsgBox "This serial number has been used previously", , "TEST" Cancel = True End If End Sub I am using Access 2002 with Access 2000 file formats -- Thanks in anticipation. |
#5
|
|||
|
|||
Check for Duplicate Records
John
I tried the following code in the BeforeUpdate event on the form. Private Sub Form_BeforeUpdate(Cancel As Integer) If Not IsNull(DLookup("SerialNumber", "Property Items", _ "SerialNumber = '" & Me!txtSerialNumber & "'")) Then MsgBox "This serial number has already been entered", vbOKOnly Cancel = True End If End Sub I then tried the code in the BeforeUpdate event of the Serial Number box on the form i.e. Private Sub SerialNumber_BeforeUpdate(Cancel As Integer) However, I still cannot get the message to display. What have I done wrong? -- Thanks again "John W. Vinson" wrote: On Sun, 13 Sep 2009 16:37:01 -0700, Rillo wrote: This is exactly what I want to happen , however a. could you please advise me on how I can create some code that would inform the user in a user-friendly message that he/she is attempting to allocate a serial number that has already been used ? b. to which event the code should be attached? c. do I need to use VB or can I create a macro to achieve this? You should use the BeforeUpdate event of the serial number control to do so (or, in some circumstances, the BeforeUpdate event of the Form). I would use VBA code, though in 2007 it may be ok to use macros - I'm not well versed in them, though. The VBA code for a textbox named txtSerialNo, to check for duplicates in the field SerialNo in table MyTable, would be Private Sub txtSerialNo_BeforeUpdate(Cancel as Integer) If Not IsNull(DLookUp("SerialNo", "MyTable", _ "SerialNo = " & Me!txtSerialNo) Then MsgBox "This serial number has already been entered", vbOKOnly Cancel = True End If End Sub If the serial number is a Text datatype, you need some quotemarks: If Not IsNull(DLookUp("SerialNo", "MyTable", _ "SerialNo = '" & Me!txtSerialNo & "'") Then You can get fancier, offering to jump to the record for that serial number, etc. if you wish - post back for more details. -- John W. Vinson [MVP] |
#6
|
|||
|
|||
Check for Duplicate Records
Here are the explanations.
Assuming that SerialNumber is a text field in the table-- Prviate Sub Form_BeforeUpdate(Cancel As Integer) Dim strCriteria As String strCriteria = "[SerialNumber ] = """ & Me.SerialNumber & """" 'Debug.Print strCriteria If Me.NewRecord Or Me.SerialNumber Me.SerialNumber .OldValue Then If DCount("*", "[NameOfTable]", strCriteria) 0 Then Cancel = True Msgbox "This serial number has already been used. " _ & "Correct your entry or press Esc to cancel." End If End If End Sub Replace NameOfTable with the name of the table that has the field SerialNumber. If your table has spaces or other odd characters in its name, use square brackets like this [NameOfTable]. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Rillo" wrote in message ... Hi Jeanette Thanks for this. Please excuse my ignorance - as I said Vb code is not something with which I am very familiar. I assume that in your code I can replace ClientID with SerialNumber (the name of my field). However, what is tblRef and where do I place the code re my MsgBox? Thanks again "Jeanette Cunningham" wrote: Hi Rillo, good place to put code to check for duplicates is the Before Update event for the form (note this is not the same as the control's before update event). Here is some sample code for a number field/control called ClientID -- Prviate Sub Form_BeforeUpdate(Cancel As Integer) Dim strCriteria As String strCriteria = "[ClientID] = " & Me.ClientID 'Debug.Print strCriteria If Me.NewRecord Or Me.ClientID Me.ClientID.OldValue Then If DCount("*", "tblRef", strCriteria) 0 Then Cancel = True End If End If End Sub Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Rillo" wrote in message ... I have a table in which there are records containing serial numbers and other data describing items in the record. The serial number is supposed to be unique to each record and is the record key. When I try to enter a record with a duplicate serial number, Access displays two messages when I leave my data entry form. The messages are "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship" (which is apparently Error 3022). and a message to the effect that I cannot save the record at this time. This is exactly what I want to happen , however a. could you please advise me on how I can create some code that would inform the user in a user-friendly message that he/she is attempting to allocate a serial number that has already been used ? b. to which event the code should be attached? c. do I need to use VB or can I create a macro to achieve this? I tried the following code in various events linked to the serial number field, but this did not work. However, I am not very good at creating VBcode!! If (Me.RecordsetClone.RecordCount) 0 Then MsgBox "This serial number has been used previously", , "TEST" Cancel = True End If End Sub I am using Access 2002 with Access 2000 file formats -- Thanks in anticipation. |
#7
|
|||
|
|||
Check for Duplicate Records
Thank You
"Jeanette Cunningham" wrote: Here are the explanations. Assuming that SerialNumber is a text field in the table-- Prviate Sub Form_BeforeUpdate(Cancel As Integer) Dim strCriteria As String strCriteria = "[SerialNumber ] = """ & Me.SerialNumber & """" 'Debug.Print strCriteria If Me.NewRecord Or Me.SerialNumber Me.SerialNumber .OldValue Then If DCount("*", "[NameOfTable]", strCriteria) 0 Then Cancel = True Msgbox "This serial number has already been used. " _ & "Correct your entry or press Esc to cancel." End If End If End Sub Replace NameOfTable with the name of the table that has the field SerialNumber. If your table has spaces or other odd characters in its name, use square brackets like this [NameOfTable]. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Rillo" wrote in message ... Hi Jeanette Thanks for this. Please excuse my ignorance - as I said Vb code is not something with which I am very familiar. I assume that in your code I can replace ClientID with SerialNumber (the name of my field). However, what is tblRef and where do I place the code re my MsgBox? Thanks again "Jeanette Cunningham" wrote: Hi Rillo, good place to put code to check for duplicates is the Before Update event for the form (note this is not the same as the control's before update event). Here is some sample code for a number field/control called ClientID -- Prviate Sub Form_BeforeUpdate(Cancel As Integer) Dim strCriteria As String strCriteria = "[ClientID] = " & Me.ClientID 'Debug.Print strCriteria If Me.NewRecord Or Me.ClientID Me.ClientID.OldValue Then If DCount("*", "tblRef", strCriteria) 0 Then Cancel = True End If End If End Sub Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Rillo" wrote in message ... I have a table in which there are records containing serial numbers and other data describing items in the record. The serial number is supposed to be unique to each record and is the record key. When I try to enter a record with a duplicate serial number, Access displays two messages when I leave my data entry form. The messages are "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship" (which is apparently Error 3022). and a message to the effect that I cannot save the record at this time. This is exactly what I want to happen , however a. could you please advise me on how I can create some code that would inform the user in a user-friendly message that he/she is attempting to allocate a serial number that has already been used ? b. to which event the code should be attached? c. do I need to use VB or can I create a macro to achieve this? I tried the following code in various events linked to the serial number field, but this did not work. However, I am not very good at creating VBcode!! If (Me.RecordsetClone.RecordCount) 0 Then MsgBox "This serial number has been used previously", , "TEST" Cancel = True End If End Sub I am using Access 2002 with Access 2000 file formats -- Thanks in anticipation. |
#8
|
|||
|
|||
Check for Duplicate Records
On Sep 14, 2:45*pm, "Jeanette Cunningham"
wrote: Here are the explanations. Assuming that SerialNumber is a text field in the table-- Prviate Sub Form_BeforeUpdate(Cancel As Integer) Dim strCriteria As String * * strCriteria = "[SerialNumber ] = """ & Me.SerialNumber & """" * * 'Debug.Print strCriteria * * If Me.NewRecord Or Me.SerialNumber Me.SerialNumber .OldValue Then * * * * If DCount("*", "[NameOfTable]", strCriteria) 0 Then * * * * * * Cancel = True * * * * * * Msgbox "This serial number has already been used. " _ * * * * * * & "Correct yourentryor press Esc to cancel." * * * * End If * * End If End Sub Replace NameOfTable with the name of the table that has the field SerialNumber. If your table has spaces or other odd characters in its name, use square brackets like this [NameOfTable]. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Rillo" wrote in message ... Hi Jeanette Thanks for this. Please excuse my ignorance - as I said Vb code is not something with which I am very familiar. I assume that in your code I can replace ClientID with SerialNumber (the name of my field). However, what is *tblRef and where do I place the code re my MsgBox? Thanks again "Jeanette Cunningham" wrote: Hi Rillo, good place to put code to check for duplicates is the Before Update event for the form (note this is not the same as the control's before update event). Here is some *sample code for a number field/control called ClientID -- Prviate Sub Form_BeforeUpdate(Cancel As Integer) Dim strCriteria As String * * strCriteria = "[ClientID] = " & Me.ClientID * * 'Debug.Print strCriteria * * If Me.NewRecord Or Me.ClientID Me.ClientID.OldValue Then * * * * If DCount("*", "tblRef", strCriteria) 0 Then * * * * * * Cancel = True * * * * End If * * End If End Sub Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Rillo" wrote in message ... I have a table in which there are records containing serial numbers and other data describing items in the record. The serial number is supposed to be unique to each record and is the record key. When I try to enter a record with aduplicateserial number, Access displays two messages when I leave my dataentryform. The messages are "The changes you requested to the table were not successful because they would createduplicatevalues in the index, primary key, or relationship" (which is apparently Error 3022). and a message to the effect that I cannot save the record at this time. This is exactly what I want to happen , however a. could you please advise me on how I can create some code that would inform the user in a user-friendly message that he/she is attempting to allocate a serial number that has already been used ? b. to which event the code should be attached? c. do I need to use VB or can I create a macro to achieve this? I tried the following code in various events linked to the serial number field, but this did not work. However, I am not very good at creating VBcode!! If (Me.RecordsetClone.RecordCount) 0 Then * * * *MsgBox "This serial number has been used previously", , "TEST" * * * *Cancel = True * *End If End Sub I am using Access 2002 with Access 2000 file formats -- Thanks in anticipation.- Hide quoted text - - Show quoted text - Jeanne, I have the same question as Rillo, however, I have more than one primary keys. How I can modify your codes so I can get the warning message about the duplicates after the cursor leaves the field of the last primary key? Thanks, Shirley |
Thread Tools | |
Display Modes | |
|
|