[Access] Duplicate error message
Solved
ETSSieb
Posted messages
31
Status
Member
-
Tessel75 -
Tessel75 -
Hello,
I am currently experiencing an anomaly with my Access form.
I created my table by setting the Ref field as the primary key.
What I am trying to do is be able to enter new items through this form and prevent duplicates of [Ref].
By default, duplicates are not saved. However, when I start entering an already existing [Ref] in my form, nothing happens.
It is only when I try to go to the next record that it indicates the record is not accessible, and only when I want to close this form does the Access error message regarding duplicates appear.
How can I display a custom error message as soon as the [Ref] field is updated with an existing element?
Thank you in advance for your help.
I am currently experiencing an anomaly with my Access form.
I created my table by setting the Ref field as the primary key.
What I am trying to do is be able to enter new items through this form and prevent duplicates of [Ref].
By default, duplicates are not saved. However, when I start entering an already existing [Ref] in my form, nothing happens.
It is only when I try to go to the next record that it indicates the record is not accessible, and only when I want to close this form does the Access error message regarding duplicates appear.
How can I display a custom error message as soon as the [Ref] field is updated with an existing element?
Thank you in advance for your help.
8 answers
My apologies.. I systematically mix up RechDom and DLookUp (They're the same thing, but the former is used in formulas, the latter in code!!!)
Dim MaRef
MaRef = DLookUp("Ref","MaTable","[Ref]=" & Ref.Value)
If Not IsNull(MaRef) then
MsgBox "Reference already exists!"
Ref.Value = ""
end if
There, it should work!
Otherwise, it's indeed in the After Update event of [Ref] that this procedure should be placed.
Manu
Dim MaRef
MaRef = DLookUp("Ref","MaTable","[Ref]=" & Ref.Value)
If Not IsNull(MaRef) then
MsgBox "Reference already exists!"
Ref.Value = ""
end if
There, it should work!
Otherwise, it's indeed in the After Update event of [Ref] that this procedure should be placed.
Manu
Do you have a control in your form called Ref?
When you receive this message, is there anything entered as a reference in your form?
Otherwise, add the line at the beginning of the procedure:
If IsNull(Ref.value) or len(Ref.value) = 0 then exit sub
This will prevent the procedure from running if nothing is entered in Ref.
Manu
When you receive this message, is there anything entered as a reference in your form?
Otherwise, add the line at the beginning of the procedure:
If IsNull(Ref.value) or len(Ref.value) = 0 then exit sub
This will prevent the procedure from running if nothing is entered in Ref.
Manu
Hello there,
The topic is a bit old, but it's exactly what I was looking for.
However, when copying the code from Manugeo, everything works well at first, the error dialog box is modified. I click OK, and then the drama begins: "Run-time error 2115 The macro or function assigned to the 'Before Update' or 'Validation Rule' property for this field prevents Microsoft Access from saving data in the field."
Knowing that [Ref] is called [Title] in my [T_Biblio] table (on Access 2010):
Private Sub Title_BeforeUpdate(Cancel As Integer)
Dim MaRef
MaRef = DLookup("Title", "T_Biblio", "[Title]=Title.Value")
If Not IsNull(MaRef) Then
MsgBox "Reference already in the base !"
Title.Value = ""
End If
End Sub
Thanks in advance for your help :)
The topic is a bit old, but it's exactly what I was looking for.
However, when copying the code from Manugeo, everything works well at first, the error dialog box is modified. I click OK, and then the drama begins: "Run-time error 2115 The macro or function assigned to the 'Before Update' or 'Validation Rule' property for this field prevents Microsoft Access from saving data in the field."
Knowing that [Ref] is called [Title] in my [T_Biblio] table (on Access 2010):
Private Sub Title_BeforeUpdate(Cancel As Integer)
Dim MaRef
MaRef = DLookup("Title", "T_Biblio", "[Title]=Title.Value")
If Not IsNull(MaRef) Then
MsgBox "Reference already in the base !"
Title.Value = ""
End If
End Sub
Thanks in advance for your help :)
Hello,
Given that the question is marked as resolved, it would have been better for you to open a new question, even if it references the current question by copying and pasting the address. It's not too late; you can copy and paste your current question into a new one.
That said, there is an error in the second response from Anonymous: ""My mistake... I systematically mix up RechDom and DLookUp (They're the same thing, but the former is used in formulas, the latter in code!!!)"". Both can very well be used in code as well as in formulas.
For the rest, you need to know what error 2115 is; it's up to you to look it up in the tutorial.
Now there is a much simpler method to prevent duplicate entries, and maybe that is exactly error 2115; it's to use indexes.
By setting your field (here MaRef) to ""Indexed without duplicates", the input will be blocked before it's recorded, and in this case, the piece of code is unnecessary, unless you want an alert before Access does it. Otherwise, it is also possible to use a validation condition for the record like ""DCount(blablabla)=0" and add a message in the property ""Message if error".
Given that the question is marked as resolved, it would have been better for you to open a new question, even if it references the current question by copying and pasting the address. It's not too late; you can copy and paste your current question into a new one.
That said, there is an error in the second response from Anonymous: ""My mistake... I systematically mix up RechDom and DLookUp (They're the same thing, but the former is used in formulas, the latter in code!!!)"". Both can very well be used in code as well as in formulas.
For the rest, you need to know what error 2115 is; it's up to you to look it up in the tutorial.
Now there is a much simpler method to prevent duplicate entries, and maybe that is exactly error 2115; it's to use indexes.
By setting your field (here MaRef) to ""Indexed without duplicates", the input will be blocked before it's recorded, and in this case, the piece of code is unnecessary, unless you want an alert before Access does it. Otherwise, it is also possible to use a validation condition for the record like ""DCount(blablabla)=0" and add a message in the property ""Message if error".
Thank you for your proposal, but this code does not work for me.
Should it have been placed in After Màj of [Ref]?
I inserted the code and immediately the following message appeared: "Compilation error: Expected list separator or )" highlighting the semicolon.
I assumed it needed to be replaced with a comma, but when I try to run the form, another message appears indicating that RechDom is unknown.
I am very new to VBA and Access. Can you help me?