Preventing duplicate record entries where two fields are involved

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form named ACCOUNTS that is bound to a table that is also named
ACCOUNTS. The form is used only for entering data into the ACCOUNTS table. In
the table there are two text fields, one named FUNCTION, and the other named
OBJECT. The field named FUNCTION can contain repetitions of a four character
code, and the field named OBJECT can contain repetitions of a five character
code. The two fields together should not contain the same pair of codes such
as in rows 1 and 3 in the example below:

FUNCTION OBJECT
1) abcd wxyz
2) adcd hijk
3) abcd wxyz

I've tried DLookup and DCount in several attempts with If ... Then
statements as a Before Update event, but keep getting either mismatch, or
runtime, or "End If without block If" type errors. Is there code that will
prevent such duplicates?
 
The best way to prevent duplicates is to do it in the table. At least, that
is the first level you should look at...

Open your table in design view, then open your indexes. Create a new index
based on both fields and set the 'unique' property to 'Yes'.
To do this, in the first column, type a name for your new index then in the
column to the right select one of your fields, then in the field immediately
below that, select your other field. Go back to the name of the new index
(select it) and look at the properties at the base of the index form. Set the
unique property.

Once you have done that, you will not be able to enter duplicates of the
combination of those two fields.

Steve
 
Thanks Steve, that works fine!

I was hoping that maybe there was a way to stop, or capture, the process of
entering any "duplicated data" at the form level before any updating takes
place at the table level and uses up one of the AutoNumbers of the Primary
Key --- sort of keeping the AutoNumbers matching the Record Numbers, which is
probably being somewhat unrealistic here --- guess a fellow can always hide
that ID field that holds the AutoNumber and relieve undue concern,huh :)

Thanks again Steve,
RC
 
Here is something that I kept tinkering with until stumbling across the code
below appears to be working --- it appears to be stopping the duplicated data
in it tracks at the from level before it updates at the table level:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim strMsg As String

If Not IsNull(DLookup("[FUNCTION]", "ACCOUNTS", _
"[FUNCTION] = '" & Me.FUNCTION & "'AND [OBJECT] = '" & Me.OBJECT & "'"))
Then
Cancel = True
strMsg = "This Account Already Exist!" & Chr(13) _
& Chr(13) & "TRY AGAIN!"
MsgBox strMsg
Me.FUNCTION.SetFocus
End If

End Sub

I'm short on experience with code, and if this needs to be "cleaned up",
please holler back.
 
It's good practice to restrict such things at the lowest level first. Then,
you can add code to trap it in the form if you wish. Your code is fine but a
couple of things to mention...

You should use Chr(13) in combination with Chr(10) and in that order.
A replacement for this is vbCrLf.

You may want to consider adding error handling to your procedure.

Steve

RC said:
Here is something that I kept tinkering with until stumbling across the code
below appears to be working --- it appears to be stopping the duplicated data
in it tracks at the from level before it updates at the table level:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim strMsg As String

If Not IsNull(DLookup("[FUNCTION]", "ACCOUNTS", _
"[FUNCTION] = '" & Me.FUNCTION & "'AND [OBJECT] = '" & Me.OBJECT & "'"))
Then
Cancel = True
strMsg = "This Account Already Exist!" & Chr(13) _
& Chr(13) & "TRY AGAIN!"
MsgBox strMsg
Me.FUNCTION.SetFocus
End If

End Sub

I'm short on experience with code, and if this needs to be "cleaned up",
please holler back.

RC said:
Thanks Steve, that works fine!

I was hoping that maybe there was a way to stop, or capture, the process of
entering any "duplicated data" at the form level before any updating takes
place at the table level and uses up one of the AutoNumbers of the Primary
Key --- sort of keeping the AutoNumbers matching the Record Numbers, which is
probably being somewhat unrealistic here --- guess a fellow can always hide
that ID field that holds the AutoNumber and relieve undue concern,huh :)

Thanks again Steve,
RC
Click to expand...
Click to expand...
 
Thanks Steve for that advice with Chr(13) and Chr(10), and I did go ahead and
use the following On Error event:

Private Sub Form_Error(DataErr As Integer, Response As Integer)

Const conDuplicateKey = 3022
Dim strMsg As String

If DataErr = conDuplicateKey Then
Response = acDataErrContinue
strMsg = "This Account Already Exist " & Chr(13) _
& Chr(13) & "Record Entry Will Not Be Saved."
MsgBox strMsg
Me.Undo
End If


End Sub

Plus there is a Unique Index set on the ACCOUNTS table for the FUNCTION and
OBJECT fields --- hope this isn't overkill.
After "testing" that DLookup code in the "If ...Then" statement that's being
used as a Before Update event, I'm now running into some "quirky behavoir".
When I go back to edit any other field than the FUNCTION and OBJECT fields,
on a record in that ACCOUNTS form, such as the ACCOUNT_NAME field, the code
returns the message as though a duplication has been made in the FUNCTION and
OBJECT fields on that same record . I'm having to close to form to break
free. It's as though when any editing to an older record starts to update the
code sees the existing values in the FUNCTION and OBJECT fields as being
"duplicated". Any ideas?
Thanks,
RC

SteveM said:
It's good practice to restrict such things at the lowest level first. Then,
you can add code to trap it in the form if you wish. Your code is fine but a
couple of things to mention...

You should use Chr(13) in combination with Chr(10) and in that order.
A replacement for this is vbCrLf.

You may want to consider adding error handling to your procedure.

Steve

RC said:
Here is something that I kept tinkering with until stumbling across the code
below appears to be working --- it appears to be stopping the duplicated data
in it tracks at the from level before it updates at the table level:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim strMsg As String

If Not IsNull(DLookup("[FUNCTION]", "ACCOUNTS", _
"[FUNCTION] = '" & Me.FUNCTION & "'AND [OBJECT] = '" & Me.OBJECT & "'"))
Then
Cancel = True
strMsg = "This Account Already Exist!" & Chr(13) _
& Chr(13) & "TRY AGAIN!"
MsgBox strMsg
Me.FUNCTION.SetFocus
End If

End Sub

I'm short on experience with code, and if this needs to be "cleaned up",
please holler back.

RC said:
Thanks Steve, that works fine!

I was hoping that maybe there was a way to stop, or capture, the process of
entering any "duplicated data" at the form level before any updating takes
place at the table level and uses up one of the AutoNumbers of the Primary
Key --- sort of keeping the AutoNumbers matching the Record Numbers, which is
probably being somewhat unrealistic here --- guess a fellow can always hide
that ID field that holds the AutoNumber and relieve undue concern,huh :)

Thanks again Steve,
RC

:

The best way to prevent duplicates is to do it in the table. At least, that
is the first level you should look at...

Open your table in design view, then open your indexes. Create a new index
based on both fields and set the 'unique' property to 'Yes'.
To do this, in the first column, type a name for your new index then in the
column to the right select one of your fields, then in the field immediately
below that, select your other field. Go back to the name of the new index
(select it) and look at the properties at the base of the index form. Set the
unique property.

Once you have done that, you will not be able to enter duplicates of the
combination of those two fields.

Steve

:

I have a form named ACCOUNTS that is bound to a table that is also named
ACCOUNTS. The form is used only for entering data into the ACCOUNTS table. In
the table there are two text fields, one named FUNCTION, and the other named
OBJECT. The field named FUNCTION can contain repetitions of a four character
code, and the field named OBJECT can contain repetitions of a five character
code. The two fields together should not contain the same pair of codes such
as in rows 1 and 3 in the example below:

FUNCTION OBJECT
1) abcd wxyz
2) adcd hijk
3) abcd wxyz

I've tried DLookup and DCount in several attempts with If ... Then
statements as a Before Update event, but keep getting either mismatch, or
runtime, or "End If without block If" type errors. Is there code that will
prevent such duplicates?
Click to expand...
Click to expand...
Click to expand...
 
Is it a bound form?

Steve

RC said:
Thanks Steve for that advice with Chr(13) and Chr(10), and I did go ahead and
use the following On Error event:

Private Sub Form_Error(DataErr As Integer, Response As Integer)

Const conDuplicateKey = 3022
Dim strMsg As String

If DataErr = conDuplicateKey Then
Response = acDataErrContinue
strMsg = "This Account Already Exist " & Chr(13) _
& Chr(13) & "Record Entry Will Not Be Saved."
MsgBox strMsg
Me.Undo
End If


End Sub

Plus there is a Unique Index set on the ACCOUNTS table for the FUNCTION and
OBJECT fields --- hope this isn't overkill.
After "testing" that DLookup code in the "If ...Then" statement that's being
used as a Before Update event, I'm now running into some "quirky behavoir".
When I go back to edit any other field than the FUNCTION and OBJECT fields,
on a record in that ACCOUNTS form, such as the ACCOUNT_NAME field, the code
returns the message as though a duplication has been made in the FUNCTION and
OBJECT fields on that same record . I'm having to close to form to break
free. It's as though when any editing to an older record starts to update the
code sees the existing values in the FUNCTION and OBJECT fields as being
"duplicated". Any ideas?
Thanks,
RC

SteveM said:
It's good practice to restrict such things at the lowest level first. Then,
you can add code to trap it in the form if you wish. Your code is fine but a
couple of things to mention...

You should use Chr(13) in combination with Chr(10) and in that order.
A replacement for this is vbCrLf.

You may want to consider adding error handling to your procedure.

Steve

RC said:
Here is something that I kept tinkering with until stumbling across the code
below appears to be working --- it appears to be stopping the duplicated data
in it tracks at the from level before it updates at the table level:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim strMsg As String

If Not IsNull(DLookup("[FUNCTION]", "ACCOUNTS", _
"[FUNCTION] = '" & Me.FUNCTION & "'AND [OBJECT] = '" & Me.OBJECT & "'"))
Then
Cancel = True
strMsg = "This Account Already Exist!" & Chr(13) _
& Chr(13) & "TRY AGAIN!"
MsgBox strMsg
Me.FUNCTION.SetFocus
End If

End Sub

I'm short on experience with code, and if this needs to be "cleaned up",
please holler back.

:

Thanks Steve, that works fine!

I was hoping that maybe there was a way to stop, or capture, the process of
entering any "duplicated data" at the form level before any updating takes
place at the table level and uses up one of the AutoNumbers of the Primary
Key --- sort of keeping the AutoNumbers matching the Record Numbers, which is
probably being somewhat unrealistic here --- guess a fellow can always hide
that ID field that holds the AutoNumber and relieve undue concern,huh :)

Thanks again Steve,
RC

:

The best way to prevent duplicates is to do it in the table. At least, that
is the first level you should look at...

Open your table in design view, then open your indexes. Create a new index
based on both fields and set the 'unique' property to 'Yes'.
To do this, in the first column, type a name for your new index then in the
column to the right select one of your fields, then in the field immediately
below that, select your other field. Go back to the name of the new index
(select it) and look at the properties at the base of the index form. Set the
unique property.

Once you have done that, you will not be able to enter duplicates of the
combination of those two fields.

Steve

:

I have a form named ACCOUNTS that is bound to a table that is also named
ACCOUNTS. The form is used only for entering data into the ACCOUNTS table. In
the table there are two text fields, one named FUNCTION, and the other named
OBJECT. The field named FUNCTION can contain repetitions of a four character
code, and the field named OBJECT can contain repetitions of a five character
code. The two fields together should not contain the same pair of codes such
as in rows 1 and 3 in the example below:

FUNCTION OBJECT
1) abcd wxyz
2) adcd hijk
3) abcd wxyz

I've tried DLookup and DCount in several attempts with If ... Then
statements as a Before Update event, but keep getting either mismatch, or
runtime, or "End If without block If" type errors. Is there code that will
prevent such duplicates?
Click to expand...
Click to expand...
Click to expand...
 
Sorry about being late with this reply, and yes, the form, ACCOUNTS, is bound
to a table with the same name. Both having the following fields:

ACCOUNTS_ID ----- AutoNumber (primary key)
FUNCTION ---------- Text
OBJECT ------------- Text
ACCOUNT_NAME --- Text

Sort of in summary here --- in the table, ACCOUNTS, the fields FUNCTION and
OBJECT have been "assigned" as a "unique index". In the form, ACCOUNTS, the
text boxes FUNCTION and OBJECT are the ones that appear in that BeforeUpdate
event here: (Note: I've replaced that 2nd Chr(13) with Chr(10) as you had
recommended earlier)
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim strMsg As String

If Not IsNull(DLookup("[FUNCTION]", "ACCOUNTS", _
"[FUNCTION] = '" & Me.FUNCTION & "'AND [OBJECT] = '" & Me.OBJECT & "'"))
Then
Cancel = True
strMsg = "This Account Already Exist!" & Chr(13) _
& Chr(10) & "TRY AGAIN!"
MsgBox strMsg
Me.FUNCTION.SetFocus
End If

End Sub

RC

SteveM said:
Is it a bound form?

Steve

RC said:
Thanks Steve for that advice with Chr(13) and Chr(10), and I did go ahead and
use the following On Error event:

Private Sub Form_Error(DataErr As Integer, Response As Integer)

Const conDuplicateKey = 3022
Dim strMsg As String

If DataErr = conDuplicateKey Then
Response = acDataErrContinue
strMsg = "This Account Already Exist " & Chr(13) _
& Chr(13) & "Record Entry Will Not Be Saved."
MsgBox strMsg
Me.Undo
End If


End Sub

Plus there is a Unique Index set on the ACCOUNTS table for the FUNCTION and
OBJECT fields --- hope this isn't overkill.
After "testing" that DLookup code in the "If ...Then" statement that's being
used as a Before Update event, I'm now running into some "quirky behavoir".
When I go back to edit any other field than the FUNCTION and OBJECT fields,
on a record in that ACCOUNTS form, such as the ACCOUNT_NAME field, the code
returns the message as though a duplication has been made in the FUNCTION and
OBJECT fields on that same record . I'm having to close to form to break
free. It's as though when any editing to an older record starts to update the
code sees the existing values in the FUNCTION and OBJECT fields as being
"duplicated". Any ideas?
Thanks,
RC

SteveM said:
It's good practice to restrict such things at the lowest level first. Then,
you can add code to trap it in the form if you wish. Your code is fine but a
couple of things to mention...

You should use Chr(13) in combination with Chr(10) and in that order.
A replacement for this is vbCrLf.

You may want to consider adding error handling to your procedure.

Steve

:

Here is something that I kept tinkering with until stumbling across the code
below appears to be working --- it appears to be stopping the duplicated data
in it tracks at the from level before it updates at the table level:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim strMsg As String

If Not IsNull(DLookup("[FUNCTION]", "ACCOUNTS", _
"[FUNCTION] = '" & Me.FUNCTION & "'AND [OBJECT] = '" & Me.OBJECT & "'"))
Then
Cancel = True
strMsg = "This Account Already Exist!" & Chr(13) _
& Chr(13) & "TRY AGAIN!"
MsgBox strMsg
Me.FUNCTION.SetFocus
End If

End Sub

I'm short on experience with code, and if this needs to be "cleaned up",
please holler back.

:

Thanks Steve, that works fine!

I was hoping that maybe there was a way to stop, or capture, the process of
entering any "duplicated data" at the form level before any updating takes
place at the table level and uses up one of the AutoNumbers of the Primary
Key --- sort of keeping the AutoNumbers matching the Record Numbers, which is
probably being somewhat unrealistic here --- guess a fellow can always hide
that ID field that holds the AutoNumber and relieve undue concern,huh :)

Thanks again Steve,
RC

:

The best way to prevent duplicates is to do it in the table. At least, that
is the first level you should look at...

Open your table in design view, then open your indexes. Create a new index
based on both fields and set the 'unique' property to 'Yes'.
To do this, in the first column, type a name for your new index then in the
column to the right select one of your fields, then in the field immediately
below that, select your other field. Go back to the name of the new index
(select it) and look at the properties at the base of the index form. Set the
unique property.

Once you have done that, you will not be able to enter duplicates of the
combination of those two fields.

Steve

:

I have a form named ACCOUNTS that is bound to a table that is also named
ACCOUNTS. The form is used only for entering data into the ACCOUNTS table. In
the table there are two text fields, one named FUNCTION, and the other named
OBJECT. The field named FUNCTION can contain repetitions of a four character
code, and the field named OBJECT can contain repetitions of a five character
code. The two fields together should not contain the same pair of codes such
as in rows 1 and 3 in the example below:

FUNCTION OBJECT
1) abcd wxyz
2) adcd hijk
3) abcd wxyz

I've tried DLookup and DCount in several attempts with If ... Then
statements as a Before Update event, but keep getting either mismatch, or
runtime, or "End If without block If" type errors. Is there code that will
prevent such duplicates?
Click to expand...
Click to expand...
Click to expand...
 
Hi,
I would make one suggestion off subject just a bit. "Function" is a
reserved word in Access, you might consider changing the field name as
sometime in the future after you built everything it might eat your lunch.

Duff

RC said:
Sorry about being late with this reply, and yes, the form, ACCOUNTS, is
bound
to a table with the same name. Both having the following fields:

ACCOUNTS_ID ----- AutoNumber (primary key)
FUNCTION ---------- Text
OBJECT ------------- Text
ACCOUNT_NAME --- Text

Sort of in summary here --- in the table, ACCOUNTS, the fields FUNCTION
and
OBJECT have been "assigned" as a "unique index". In the form, ACCOUNTS,
the
text boxes FUNCTION and OBJECT are the ones that appear in that
BeforeUpdate
event here: (Note: I've replaced that 2nd Chr(13) with Chr(10) as you had
recommended earlier)
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim strMsg As String

If Not IsNull(DLookup("[FUNCTION]", "ACCOUNTS", _
"[FUNCTION] = '" & Me.FUNCTION & "'AND [OBJECT] = '" & Me.OBJECT & "'"))
Then
Cancel = True
strMsg = "This Account Already Exist!" & Chr(13) _
& Chr(10) & "TRY AGAIN!"
MsgBox strMsg
Me.FUNCTION.SetFocus
End If

End Sub

RC

SteveM said:
Is it a bound form?

Steve

RC said:
Thanks Steve for that advice with Chr(13) and Chr(10), and I did go
ahead and
use the following On Error event:

Private Sub Form_Error(DataErr As Integer, Response As Integer)

Const conDuplicateKey = 3022
Dim strMsg As String

If DataErr = conDuplicateKey Then
Response = acDataErrContinue
strMsg = "This Account Already Exist " & Chr(13) _
& Chr(13) & "Record Entry Will Not Be Saved."
MsgBox strMsg
Me.Undo
End If


End Sub

Plus there is a Unique Index set on the ACCOUNTS table for the FUNCTION
and
OBJECT fields --- hope this isn't overkill.
After "testing" that DLookup code in the "If ...Then" statement that's
being
used as a Before Update event, I'm now running into some "quirky
behavoir".
When I go back to edit any other field than the FUNCTION and OBJECT
fields,
on a record in that ACCOUNTS form, such as the ACCOUNT_NAME field, the
code
returns the message as though a duplication has been made in the
FUNCTION and
OBJECT fields on that same record . I'm having to close to form to
break
free. It's as though when any editing to an older record starts to
update the
code sees the existing values in the FUNCTION and OBJECT fields as
being
"duplicated". Any ideas?
Thanks,
RC

:

It's good practice to restrict such things at the lowest level first.
Then,
you can add code to trap it in the form if you wish. Your code is
fine but a
couple of things to mention...

You should use Chr(13) in combination with Chr(10) and in that order.
A replacement for this is vbCrLf.

You may want to consider adding error handling to your procedure.

Steve

:

Here is something that I kept tinkering with until stumbling across
the code
below appears to be working --- it appears to be stopping the
duplicated data
in it tracks at the from level before it updates at the table
level:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim strMsg As String

If Not IsNull(DLookup("[FUNCTION]", "ACCOUNTS", _
"[FUNCTION] = '" & Me.FUNCTION & "'AND [OBJECT] = '" & Me.OBJECT
& "'"))
Then
Cancel = True
strMsg = "This Account Already Exist!" & Chr(13) _
& Chr(13) & "TRY AGAIN!"
MsgBox strMsg
Me.FUNCTION.SetFocus
End If

End Sub

I'm short on experience with code, and if this needs to be "cleaned
up",
please holler back.

:

Thanks Steve, that works fine!

I was hoping that maybe there was a way to stop, or capture, the
process of
entering any "duplicated data" at the form level before any
updating takes
place at the table level and uses up one of the AutoNumbers of
the Primary
Key --- sort of keeping the AutoNumbers matching the Record
Numbers, which is
probably being somewhat unrealistic here --- guess a fellow can
always hide
that ID field that holds the AutoNumber and relieve undue
concern,huh :)

Thanks again Steve,
RC

:

The best way to prevent duplicates is to do it in the table. At
least, that
is the first level you should look at...

Open your table in design view, then open your indexes. Create
a new index
based on both fields and set the 'unique' property to 'Yes'.
To do this, in the first column, type a name for your new index
then in the
column to the right select one of your fields, then in the
field immediately
below that, select your other field. Go back to the name of the
new index
(select it) and look at the properties at the base of the index
form. Set the
unique property.

Once you have done that, you will not be able to enter
duplicates of the
combination of those two fields.

Steve

:

I have a form named ACCOUNTS that is bound to a table that is
also named
ACCOUNTS. The form is used only for entering data into the
ACCOUNTS table. In
the table there are two text fields, one named FUNCTION, and
the other named
OBJECT. The field named FUNCTION can contain repetitions of a
four character
code, and the field named OBJECT can contain repetitions of a
five character
code. The two fields together should not contain the same
pair of codes such
as in rows 1 and 3 in the example below:

FUNCTION OBJECT
1) abcd wxyz
2) adcd hijk
3) abcd wxyz

I've tried DLookup and DCount in several attempts with If ...
Then
statements as a Before Update event, but keep getting either
mismatch, or
runtime, or "End If without block If" type errors. Is there
code that will
prevent such duplicates?
Click to expand...
Click to expand...
Click to expand...
 
Thanks Pete, and now that you mention it, is object, also a reserved word?
Strongly considering going back through the entire app and making the change
you suggested, yet would like to "get 'em all" while I'm in there.

Pete said:
Hi,
I would make one suggestion off subject just a bit. "Function" is a
reserved word in Access, you might consider changing the field name as
sometime in the future after you built everything it might eat your lunch.

Duff

RC said:
Sorry about being late with this reply, and yes, the form, ACCOUNTS, is
bound
to a table with the same name. Both having the following fields:

ACCOUNTS_ID ----- AutoNumber (primary key)
FUNCTION ---------- Text
OBJECT ------------- Text
ACCOUNT_NAME --- Text

Sort of in summary here --- in the table, ACCOUNTS, the fields FUNCTION
and
OBJECT have been "assigned" as a "unique index". In the form, ACCOUNTS,
the
text boxes FUNCTION and OBJECT are the ones that appear in that
BeforeUpdate
event here: (Note: I've replaced that 2nd Chr(13) with Chr(10) as you had
recommended earlier)
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim strMsg As String

If Not IsNull(DLookup("[FUNCTION]", "ACCOUNTS", _
"[FUNCTION] = '" & Me.FUNCTION & "'AND [OBJECT] = '" & Me.OBJECT & "'"))
Then
Cancel = True
strMsg = "This Account Already Exist!" & Chr(13) _
& Chr(10) & "TRY AGAIN!"
MsgBox strMsg
Me.FUNCTION.SetFocus
End If

End Sub

RC

SteveM said:
Is it a bound form?

Steve

:

Thanks Steve for that advice with Chr(13) and Chr(10), and I did go
ahead and
use the following On Error event:

Private Sub Form_Error(DataErr As Integer, Response As Integer)

Const conDuplicateKey = 3022
Dim strMsg As String

If DataErr = conDuplicateKey Then
Response = acDataErrContinue
strMsg = "This Account Already Exist " & Chr(13) _
& Chr(13) & "Record Entry Will Not Be Saved."
MsgBox strMsg
Me.Undo
End If


End Sub

Plus there is a Unique Index set on the ACCOUNTS table for the FUNCTION
and
OBJECT fields --- hope this isn't overkill.
After "testing" that DLookup code in the "If ...Then" statement that's
being
used as a Before Update event, I'm now running into some "quirky
behavoir".
When I go back to edit any other field than the FUNCTION and OBJECT
fields,
on a record in that ACCOUNTS form, such as the ACCOUNT_NAME field, the
code
returns the message as though a duplication has been made in the
FUNCTION and
OBJECT fields on that same record . I'm having to close to form to
break
free. It's as though when any editing to an older record starts to
update the
code sees the existing values in the FUNCTION and OBJECT fields as
being
"duplicated". Any ideas?
Thanks,
RC

:

It's good practice to restrict such things at the lowest level first.
Then,
you can add code to trap it in the form if you wish. Your code is
fine but a
couple of things to mention...

You should use Chr(13) in combination with Chr(10) and in that order.
A replacement for this is vbCrLf.

You may want to consider adding error handling to your procedure.

Steve

:

Here is something that I kept tinkering with until stumbling across
the code
below appears to be working --- it appears to be stopping the
duplicated data
in it tracks at the from level before it updates at the table
level:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim strMsg As String

If Not IsNull(DLookup("[FUNCTION]", "ACCOUNTS", _
"[FUNCTION] = '" & Me.FUNCTION & "'AND [OBJECT] = '" & Me.OBJECT
& "'"))
Then
Cancel = True
strMsg = "This Account Already Exist!" & Chr(13) _
& Chr(13) & "TRY AGAIN!"
MsgBox strMsg
Me.FUNCTION.SetFocus
End If

End Sub

I'm short on experience with code, and if this needs to be "cleaned
up",
please holler back.

:

Thanks Steve, that works fine!

I was hoping that maybe there was a way to stop, or capture, the
process of
entering any "duplicated data" at the form level before any
updating takes
place at the table level and uses up one of the AutoNumbers of
the Primary
Key --- sort of keeping the AutoNumbers matching the Record
Numbers, which is
probably being somewhat unrealistic here --- guess a fellow can
always hide
that ID field that holds the AutoNumber and relieve undue
concern,huh :)

Thanks again Steve,
RC

:

The best way to prevent duplicates is to do it in the table. At
least, that
is the first level you should look at...

Open your table in design view, then open your indexes. Create
a new index
based on both fields and set the 'unique' property to 'Yes'.
To do this, in the first column, type a name for your new index
then in the
column to the right select one of your fields, then in the
field immediately
below that, select your other field. Go back to the name of the
new index
(select it) and look at the properties at the base of the index
form. Set the
unique property.

Once you have done that, you will not be able to enter
duplicates of the
combination of those two fields.

Steve

:

I have a form named ACCOUNTS that is bound to a table that is
also named
ACCOUNTS. The form is used only for entering data into the
ACCOUNTS table. In
the table there are two text fields, one named FUNCTION, and
the other named
OBJECT. The field named FUNCTION can contain repetitions of a
four character
code, and the field named OBJECT can contain repetitions of a
five character
code. The two fields together should not contain the same
pair of codes such
as in rows 1 and 3 in the example below:

FUNCTION OBJECT
1) abcd wxyz
2) adcd hijk
3) abcd wxyz

I've tried DLookup and DCount in several attempts with If ...
Then
statements as a Before Update event, but keep getting either
mismatch, or
runtime, or "End If without block If" type errors. Is there
code that will
prevent such duplicates?
Click to expand...
Click to expand...
Click to expand...
 
OK, here's something else I've stumbled across that seems (that's really
becoming a key word in my "adventures" with code) to make this whole matter a
lot simpler. In the last line of my On Error Event, I changed the Me.Undo to
Me.ACCT_FUNCT.SetFocus, and the On Error Event now looks like this:

Private Sub Form_Error(DataErr As Integer, Response As Integer)

Const conDuplicateKey = 3022
Dim strMsg As String

If DataErr = conDuplicateKey Then
Response = acDataErrContinue
strMsg = "ATTENTION! ACCOUNT DUPLICATION" _
& Chr(13) & Chr(10) & "This Account Already Exist!"
MsgBox strMsg
Me.ACCT_FUNCT.SetFocus
End If


End Sub

With the "unique index" in the ACCOUNTS table setup for the ACCT_FUNCT and
ACCT_OBJ fields, and the On Error Event code now written as it appears above,
the "account duplication error" is not only just trapped, it is "stopped" at
the form level before it can attempt to "update" the table (or so it seems to
be working that way so far.) Oh yeah, I took Pete's advice on those field
names, in regard to Access reserved words, and changed them.
Steve and Pete, thank you both for your help.

RC


RC said:
Thanks Pete, and now that you mention it, is object, also a reserved word?
Strongly considering going back through the entire app and making the change
you suggested, yet would like to "get 'em all" while I'm in there.

Pete said:
Hi,
I would make one suggestion off subject just a bit. "Function" is a
reserved word in Access, you might consider changing the field name as
sometime in the future after you built everything it might eat your lunch.

Duff

RC said:
Sorry about being late with this reply, and yes, the form, ACCOUNTS, is
bound
to a table with the same name. Both having the following fields:

ACCOUNTS_ID ----- AutoNumber (primary key)
FUNCTION ---------- Text
OBJECT ------------- Text
ACCOUNT_NAME --- Text

Sort of in summary here --- in the table, ACCOUNTS, the fields FUNCTION
and
OBJECT have been "assigned" as a "unique index". In the form, ACCOUNTS,
the
text boxes FUNCTION and OBJECT are the ones that appear in that
BeforeUpdate
event here: (Note: I've replaced that 2nd Chr(13) with Chr(10) as you had
recommended earlier)
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim strMsg As String

If Not IsNull(DLookup("[FUNCTION]", "ACCOUNTS", _
"[FUNCTION] = '" & Me.FUNCTION & "'AND [OBJECT] = '" & Me.OBJECT & "'"))
Then
Cancel = True
strMsg = "This Account Already Exist!" & Chr(13) _
& Chr(10) & "TRY AGAIN!"
MsgBox strMsg
Me.FUNCTION.SetFocus
End If

End Sub

RC

:

Is it a bound form?

Steve

:

Thanks Steve for that advice with Chr(13) and Chr(10), and I did go
ahead and
use the following On Error event:

Private Sub Form_Error(DataErr As Integer, Response As Integer)

Const conDuplicateKey = 3022
Dim strMsg As String

If DataErr = conDuplicateKey Then
Response = acDataErrContinue
strMsg = "This Account Already Exist " & Chr(13) _
& Chr(13) & "Record Entry Will Not Be Saved."
MsgBox strMsg
Me.Undo
End If


End Sub

Plus there is a Unique Index set on the ACCOUNTS table for the FUNCTION
and
OBJECT fields --- hope this isn't overkill.
After "testing" that DLookup code in the "If ...Then" statement that's
being
used as a Before Update event, I'm now running into some "quirky
behavoir".
When I go back to edit any other field than the FUNCTION and OBJECT
fields,
on a record in that ACCOUNTS form, such as the ACCOUNT_NAME field, the
code
returns the message as though a duplication has been made in the
FUNCTION and
OBJECT fields on that same record . I'm having to close to form to
break
free. It's as though when any editing to an older record starts to
update the
code sees the existing values in the FUNCTION and OBJECT fields as
being
"duplicated". Any ideas?
Thanks,
RC

:

It's good practice to restrict such things at the lowest level first.
Then,
you can add code to trap it in the form if you wish. Your code is
fine but a
couple of things to mention...

You should use Chr(13) in combination with Chr(10) and in that order.
A replacement for this is vbCrLf.

You may want to consider adding error handling to your procedure.

Steve

:

Here is something that I kept tinkering with until stumbling across
the code
below appears to be working --- it appears to be stopping the
duplicated data
in it tracks at the from level before it updates at the table
level:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim strMsg As String

If Not IsNull(DLookup("[FUNCTION]", "ACCOUNTS", _
"[FUNCTION] = '" & Me.FUNCTION & "'AND [OBJECT] = '" & Me.OBJECT
& "'"))
Then
Cancel = True
strMsg = "This Account Already Exist!" & Chr(13) _
& Chr(13) & "TRY AGAIN!"
MsgBox strMsg
Me.FUNCTION.SetFocus
End If

End Sub

I'm short on experience with code, and if this needs to be "cleaned
up",
please holler back.

:

Thanks Steve, that works fine!

I was hoping that maybe there was a way to stop, or capture, the
process of
entering any "duplicated data" at the form level before any
updating takes
place at the table level and uses up one of the AutoNumbers of
the Primary
Key --- sort of keeping the AutoNumbers matching the Record
Numbers, which is
probably being somewhat unrealistic here --- guess a fellow can
always hide
that ID field that holds the AutoNumber and relieve undue
concern,huh :)

Thanks again Steve,
RC

:

The best way to prevent duplicates is to do it in the table. At
least, that
is the first level you should look at...

Open your table in design view, then open your indexes. Create
a new index
based on both fields and set the 'unique' property to 'Yes'.
To do this, in the first column, type a name for your new index
then in the
column to the right select one of your fields, then in the
field immediately
below that, select your other field. Go back to the name of the
new index
(select it) and look at the properties at the base of the index
form. Set the
unique property.

Once you have done that, you will not be able to enter
duplicates of the
combination of those two fields.

Steve

:

I have a form named ACCOUNTS that is bound to a table that is
also named
ACCOUNTS. The form is used only for entering data into the
ACCOUNTS table. In
the table there are two text fields, one named FUNCTION, and
the other named
OBJECT. The field named FUNCTION can contain repetitions of a
four character
code, and the field named OBJECT can contain repetitions of a
five character
code. The two fields together should not contain the same
pair of codes such
as in rows 1 and 3 in the example below:

FUNCTION OBJECT
1) abcd wxyz
2) adcd hijk
3) abcd wxyz

I've tried DLookup and DCount in several attempts with If ...
Then
statements as a Before Update event, but keep getting either
mismatch, or
runtime, or "End If without block If" type errors. Is there
code that will
prevent such duplicates?
Click to expand...
Click to expand...
Click to expand...
 
I thought it was also so I looked it up on MSDN and it isn't for Access.
Good question though!
RC said:
Thanks Pete, and now that you mention it, is object, also a reserved word?
Strongly considering going back through the entire app and making the
change
you suggested, yet would like to "get 'em all" while I'm in there.

Pete said:
Hi,
I would make one suggestion off subject just a bit. "Function" is a
reserved word in Access, you might consider changing the field name as
sometime in the future after you built everything it might eat your
lunch.

Duff

RC said:
Sorry about being late with this reply, and yes, the form, ACCOUNTS, is
bound
to a table with the same name. Both having the following fields:

ACCOUNTS_ID ----- AutoNumber (primary key)
FUNCTION ---------- Text
OBJECT ------------- Text
ACCOUNT_NAME --- Text

Sort of in summary here --- in the table, ACCOUNTS, the fields FUNCTION
and
OBJECT have been "assigned" as a "unique index". In the form, ACCOUNTS,
the
text boxes FUNCTION and OBJECT are the ones that appear in that
BeforeUpdate
event here: (Note: I've replaced that 2nd Chr(13) with Chr(10) as you
had
recommended earlier)
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim strMsg As String

If Not IsNull(DLookup("[FUNCTION]", "ACCOUNTS", _
"[FUNCTION] = '" & Me.FUNCTION & "'AND [OBJECT] = '" & Me.OBJECT &
"'"))
Then
Cancel = True
strMsg = "This Account Already Exist!" & Chr(13) _
& Chr(10) & "TRY AGAIN!"
MsgBox strMsg
Me.FUNCTION.SetFocus
End If

End Sub

RC

:

Is it a bound form?

Steve

:

Thanks Steve for that advice with Chr(13) and Chr(10), and I did go
ahead and
use the following On Error event:

Private Sub Form_Error(DataErr As Integer, Response As Integer)

Const conDuplicateKey = 3022
Dim strMsg As String

If DataErr = conDuplicateKey Then
Response = acDataErrContinue
strMsg = "This Account Already Exist " & Chr(13) _
& Chr(13) & "Record Entry Will Not Be Saved."
MsgBox strMsg
Me.Undo
End If


End Sub

Plus there is a Unique Index set on the ACCOUNTS table for the
FUNCTION
and
OBJECT fields --- hope this isn't overkill.
After "testing" that DLookup code in the "If ...Then" statement
that's
being
used as a Before Update event, I'm now running into some "quirky
behavoir".
When I go back to edit any other field than the FUNCTION and OBJECT
fields,
on a record in that ACCOUNTS form, such as the ACCOUNT_NAME field,
the
code
returns the message as though a duplication has been made in the
FUNCTION and
OBJECT fields on that same record . I'm having to close to form to
break
free. It's as though when any editing to an older record starts to
update the
code sees the existing values in the FUNCTION and OBJECT fields as
being
"duplicated". Any ideas?
Thanks,
RC

:

It's good practice to restrict such things at the lowest level
first.
Then,
you can add code to trap it in the form if you wish. Your code is
fine but a
couple of things to mention...

You should use Chr(13) in combination with Chr(10) and in that
order.
A replacement for this is vbCrLf.

You may want to consider adding error handling to your procedure.

Steve

:

Here is something that I kept tinkering with until stumbling
across
the code
below appears to be working --- it appears to be stopping the
duplicated data
in it tracks at the from level before it updates at the table
level:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim strMsg As String

If Not IsNull(DLookup("[FUNCTION]", "ACCOUNTS", _
"[FUNCTION] = '" & Me.FUNCTION & "'AND [OBJECT] = '" &
Me.OBJECT
& "'"))
Then
Cancel = True
strMsg = "This Account Already Exist!" & Chr(13) _
& Chr(13) & "TRY AGAIN!"
MsgBox strMsg
Me.FUNCTION.SetFocus
End If

End Sub

I'm short on experience with code, and if this needs to be
"cleaned
up",
please holler back.

:

Thanks Steve, that works fine!

I was hoping that maybe there was a way to stop, or capture,
the
process of
entering any "duplicated data" at the form level before any
updating takes
place at the table level and uses up one of the AutoNumbers of
the Primary
Key --- sort of keeping the AutoNumbers matching the Record
Numbers, which is
probably being somewhat unrealistic here --- guess a fellow
can
always hide
that ID field that holds the AutoNumber and relieve undue
concern,huh :)

Thanks again Steve,
RC

:

The best way to prevent duplicates is to do it in the table.
At
least, that
is the first level you should look at...

Open your table in design view, then open your indexes.
Create
a new index
based on both fields and set the 'unique' property to 'Yes'.
To do this, in the first column, type a name for your new
index
then in the
column to the right select one of your fields, then in the
field immediately
below that, select your other field. Go back to the name of
the
new index
(select it) and look at the properties at the base of the
index
form. Set the
unique property.

Once you have done that, you will not be able to enter
duplicates of the
combination of those two fields.

Steve

:

I have a form named ACCOUNTS that is bound to a table that
is
also named
ACCOUNTS. The form is used only for entering data into the
ACCOUNTS table. In
the table there are two text fields, one named FUNCTION,
and
the other named
OBJECT. The field named FUNCTION can contain repetitions
of a
four character
code, and the field named OBJECT can contain repetitions
of a
five character
code. The two fields together should not contain the same
pair of codes such
as in rows 1 and 3 in the example below:

FUNCTION OBJECT
1) abcd wxyz
2) adcd hijk
3) abcd wxyz

I've tried DLookup and DCount in several attempts with If
...
Then
statements as a Before Update event, but keep getting
either
mismatch, or
runtime, or "End If without block If" type errors. Is
there
code that will
prevent such duplicates?
Click to expand...
Click to expand...
Click to expand...
 
This doesn't change anything in the code - but a form update and a
table update are one and the same (at least, I'm 99.99999% sure of
that!). Data isn't stored in the form - so when you say that you're
catching it at the form level before it goes to the table level - it's
all one and the same. Might help the understanding of what's going
on!

Cindy


I thought it was also so I looked it up on MSDN and it isn't for Access.

Thanks Pete, and now that you mention it, is object, also a reserved word?
Strongly considering going back through the entire app and making the
change
you suggested, yet would like to "get 'em all" while I'm in there.
Click to expand...
Hi,
I would make one suggestion off subject just a bit. "Function" is a
reserved word in Access, you might consider changing the field name as
sometime in the future after you built everything it might eat your
lunch.
Duff
Sorry about being late with this reply, and yes, the form, ACCOUNTS, is
bound
to a table with the same name. Both having the following fields:
ACCOUNTS_ID ----- AutoNumber (primary key)
FUNCTION ---------- Text
OBJECT ------------- Text
ACCOUNT_NAME --- Text
Sort of in summary here --- in the table, ACCOUNTS, the fields FUNCTION
and
OBJECT have been "assigned" as a "unique index". In the form, ACCOUNTS,
the
text boxes FUNCTION and OBJECT are the ones that appear in that
BeforeUpdate
event here: (Note: I've replaced that 2nd Chr(13) with Chr(10) as you
had
recommended earlier)
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
If Not IsNull(DLookup("[FUNCTION]", "ACCOUNTS", _
"[FUNCTION] = '" & Me.FUNCTION & "'AND [OBJECT] = '" & Me.OBJECT &
"'"))
Then
Cancel = True
strMsg = "This Account Already Exist!" & Chr(13) _
& Chr(10) & "TRY AGAIN!"
MsgBox strMsg
Me.FUNCTION.SetFocus
End If
End Sub
RC
:
Is it a bound form?
Steve
:
Thanks Steve for that advice with Chr(13) and Chr(10), and I did go
ahead and
use the following On Error event:
Private Sub Form_Error(DataErr As Integer, Response As Integer)
Const conDuplicateKey = 3022
Dim strMsg As String
If DataErr = conDuplicateKey Then
Response = acDataErrContinue
strMsg = "This Account Already Exist " & Chr(13) _
& Chr(13) & "Record Entry Will Not Be Saved."
MsgBox strMsg
Me.Undo
End If
End Sub
Plus there is a Unique Index set on the ACCOUNTS table for the
FUNCTION
and
OBJECT fields --- hope this isn't overkill.
After "testing" that DLookup code in the "If ...Then" statement
that's
being
used as a Before Update event, I'm now running into some "quirky
behavoir".
When I go back to edit any other field than the FUNCTION and OBJECT
fields,
on a record in that ACCOUNTS form, such as the ACCOUNT_NAME field,
the
code
returns the message as though a duplication has been made in the
FUNCTION and
OBJECT fields on that same record . I'm having to close to form to
break
free. It's as though when any editing to an older record starts to
update the
code sees the existing values in the FUNCTION and OBJECT fields as
being
"duplicated". Any ideas?
Thanks,
RC
:
It's good practice to restrict such things at the lowest level
first.
Then,
you can add code to trap it in the form if you wish. Your code is
fine but a
couple of things to mention...
You should use Chr(13) in combination with Chr(10) and in that
order.
A replacement for this is vbCrLf.
You may want to consider adding error handling to your procedure.
Steve
:
Here is something that I kept tinkering with until stumbling
across
the code
below appears to be working --- it appears to be stopping the
duplicated data
in it tracks at the from level before it updates at the table
level:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
If Not IsNull(DLookup("[FUNCTION]", "ACCOUNTS", _
"[FUNCTION] = '" & Me.FUNCTION & "'AND [OBJECT] = '" &
Me.OBJECT
& "'"))
Then
Cancel = True
strMsg = "This Account Already Exist!" & Chr(13) _
& Chr(13) & "TRY AGAIN!"
MsgBox strMsg
Me.FUNCTION.SetFocus
End If
End Sub
I'm short on experience with code, and if this needs to be
"cleaned
up",
please holler back.
:
Thanks Steve, that works fine!
I was hoping that maybe there was a way to stop, or capture,
the
process of
entering any "duplicated data" at the form level before any
updating takes
place at the table level and uses up one of the AutoNumbers of
the Primary
Key --- sort of keeping the AutoNumbers matching the Record
Numbers, which is
probably being somewhat unrealistic here --- guess a fellow
can
always hide
that ID field that holds the AutoNumber and relieve undue
concern,huh :)
Thanks again Steve,
RC
:
The best way to prevent duplicates is to do it in the table.
At
least, that
is the first level you should look at...
Open your table in design view, then open your indexes.
Create
a new index
based on both fields and set the 'unique' property to 'Yes'.
To do this, in the first column, type a name for your new
index
then in the
column to the right select one of your fields, then in the
field immediately
below that, select your other field. Go back to the name of
the
new index
(select it) and look at the properties at the base of the
index
form. Set the
unique property.
Once you have done that, you will not be able to enter
duplicates of the
combination of those two fields.
Steve
:
I have a form named ACCOUNTS that is bound to a table that
is
also named
ACCOUNTS. The form is used only for entering data into the
ACCOUNTS table. In
the table there are two text fields, one named FUNCTION,
and
the other named
OBJECT. The field named FUNCTION can contain repetitions
of a
four character
code, and the field named OBJECT can contain repetitions
of a
five character
code. The two fields together should not contain the same
pair of codes such
as in rows 1 and 3 in the example below:
FUNCTION OBJECT
1) abcd wxyz
2) adcd hijk
3) abcd wxyz
I've tried DLookup and DCount in several attempts with If
...
Then
statements as a Before Update event, but keep getting
either
mismatch, or
runtime, or "End If without block If" type errors. Is
there
code that will
prevent such duplicates?
Click to expand...
Click to expand...
Click to expand...
 
Was sort of wondering if my explanation was not accurate, being, as you said,
that since the form is bound to the table, any entry into any field on the
form would take place in that same field in the table at the same time. I'm
guessing that the Me.Function.SetFocus line at the end of the "error trap" is
returning the focus back to the FUNCTION field before all the fields that
make up the entire record are "saved" on the table. This is what appears to
be happening as each "test attempt" of entering a "duplicate record" is
stopped before it is "saved" as a record and the focus is "sent" back to the
filed named FUNCTION. It is also evident that the AutoNumber for the record
"remains available for use in a record" after a "test attempt" with
"duplicate data" is trapped and fails to be saved. The Me.Undo line I was
using earlier did not accomplish this, which was evident by all the
AutoNumbers that were being "used up" on each "test attempt" that was a
"duplicate" that returned the error message. It was as though the "duplicate
record" returned the error message, yet was "saved" as a record, and then was
removed later by the Me.Undo line.
Reckon, I'm guilty of looking at this in the same way that one old school
mechanical engineer used to say, "If the contraption now runs every time like
you want it to run, you can then say it's fixed".

RC

Cindy said:
This doesn't change anything in the code - but a form update and a
table update are one and the same (at least, I'm 99.99999% sure of
that!). Data isn't stored in the form - so when you say that you're
catching it at the form level before it goes to the table level - it's
all one and the same. Might help the understanding of what's going
on!

Cindy


I thought it was also so I looked it up on MSDN and it isn't for Access.

Thanks Pete, and now that you mention it, is object, also a reserved word?
Strongly considering going back through the entire app and making the
change
you suggested, yet would like to "get 'em all" while I'm in there.
Click to expand...
"Pete" wrote:
Click to expand...
Hi,
I would make one suggestion off subject just a bit. "Function" is a
reserved word in Access, you might consider changing the field name as
sometime in the future after you built everything it might eat your
lunch.

Sorry about being late with this reply, and yes, the form, ACCOUNTS, is
bound
to a table with the same name. Both having the following fields:
Click to expand...
ACCOUNTS_ID ----- AutoNumber (primary key)
FUNCTION ---------- Text
OBJECT ------------- Text
ACCOUNT_NAME --- Text
Click to expand...
Sort of in summary here --- in the table, ACCOUNTS, the fields FUNCTION
and
OBJECT have been "assigned" as a "unique index". In the form, ACCOUNTS,
the
text boxes FUNCTION and OBJECT are the ones that appear in that
BeforeUpdate
event here: (Note: I've replaced that 2nd Chr(13) with Chr(10) as you
had
recommended earlier)
Private Sub Form_BeforeUpdate(Cancel As Integer)
Click to expand...
Dim strMsg As String
Click to expand...
If Not IsNull(DLookup("[FUNCTION]", "ACCOUNTS", _
"[FUNCTION] = '" & Me.FUNCTION & "'AND [OBJECT] = '" & Me.OBJECT &
"'"))
Then
Cancel = True
strMsg = "This Account Already Exist!" & Chr(13) _
& Chr(10) & "TRY AGAIN!"
MsgBox strMsg
Me.FUNCTION.SetFocus
End If
Click to expand...
End Sub

"SteveM" wrote:
Click to expand...
Is it a bound form?

"RC" wrote:
Click to expand...
Thanks Steve for that advice with Chr(13) and Chr(10), and I did go
ahead and
use the following On Error event:
Click to expand...
Private Sub Form_Error(DataErr As Integer, Response As Integer)
Click to expand...
Const conDuplicateKey = 3022
Dim strMsg As String
Click to expand...
If DataErr = conDuplicateKey Then
Response = acDataErrContinue
strMsg = "This Account Already Exist " & Chr(13) _
& Chr(13) & "Record Entry Will Not Be Saved."
MsgBox strMsg
Me.Undo
End If
Click to expand...
End Sub
Click to expand...
Plus there is a Unique Index set on the ACCOUNTS table for the
FUNCTION
and
OBJECT fields --- hope this isn't overkill.
After "testing" that DLookup code in the "If ...Then" statement
that's
being
used as a Before Update event, I'm now running into some "quirky
behavoir".
When I go back to edit any other field than the FUNCTION and OBJECT
fields,
on a record in that ACCOUNTS form, such as the ACCOUNT_NAME field,
the
code
returns the message as though a duplication has been made in the
FUNCTION and
OBJECT fields on that same record . I'm having to close to form to
break
free. It's as though when any editing to an older record starts to
update the
code sees the existing values in the FUNCTION and OBJECT fields as
being
"duplicated". Any ideas?
Thanks,
RC
Click to expand...
"SteveM" wrote:
Click to expand...
It's good practice to restrict such things at the lowest level
first.
Then,
you can add code to trap it in the form if you wish. Your code is
fine but a
couple of things to mention...
Click to expand...
You should use Chr(13) in combination with Chr(10) and in that
order.
A replacement for this is vbCrLf.
Click to expand...
You may want to consider adding error handling to your procedure.

"RC" wrote:
Click to expand...
Here is something that I kept tinkering with until stumbling
across
the code
below appears to be working --- it appears to be stopping the
duplicated data
in it tracks at the from level before it updates at the table
level:
Click to expand...
Private Sub Form_BeforeUpdate(Cancel As Integer)
Click to expand...
Dim strMsg As String
Click to expand...
If Not IsNull(DLookup("[FUNCTION]", "ACCOUNTS", _
"[FUNCTION] = '" & Me.FUNCTION & "'AND [OBJECT] = '" &
Me.OBJECT
& "'"))
Then
Cancel = True
strMsg = "This Account Already Exist!" & Chr(13) _
& Chr(13) & "TRY AGAIN!"
MsgBox strMsg
Me.FUNCTION.SetFocus
End If
Click to expand...
End Sub
Click to expand...
I'm short on experience with code, and if this needs to be
"cleaned
up",
please holler back.
Click to expand...
"RC" wrote:
Click to expand...
Thanks Steve, that works fine!
Click to expand...
I was hoping that maybe there was a way to stop, or capture,
the
process of
entering any "duplicated data" at the form level before any
updating takes
place at the table level and uses up one of the AutoNumbers of
the Primary
Key --- sort of keeping the AutoNumbers matching the Record
Numbers, which is
probably being somewhat unrealistic here --- guess a fellow
can
always hide
that ID field that holds the AutoNumber and relieve undue
concern,huh :)
Click to expand...
Thanks again Steve,
RC
Click to expand...
"SteveM" wrote:
Click to expand...
The best way to prevent duplicates is to do it in the table.
At
least, that
is the first level you should look at...
Click to expand...
Open your table in design view, then open your indexes.
Create
a new index
based on both fields and set the 'unique' property to 'Yes'.
To do this, in the first column, type a name for your new
index
then in the
column to the right select one of your fields, then in the
field immediately
below that, select your other field. Go back to the name of
the
new index
(select it) and look at the properties at the base of the
index
form. Set the
unique property.
Click to expand...
Once you have done that, you will not be able to enter
duplicates of the
combination of those two fields.

"RC" wrote:
Click to expand...
I have a form named ACCOUNTS that is bound to a table that
is
also named
ACCOUNTS. The form is used only for entering data into the
ACCOUNTS table. In
the table there are two text fields, one named FUNCTION,
and
the other named
OBJECT. The field named FUNCTION can contain repetitions
of a
four character
code, and the field named OBJECT can contain repetitions
of a
five character
code. The two fields together should not contain the same
pair of codes such
as in rows 1 and 3 in the example below:
Click to expand...
FUNCTION OBJECT
1) abcd wxyz
2) adcd hijk
3) abcd wxyz
Click to expand...
I've tried DLookup and DCount in several attempts with If
...
Then
statements as a Before Update event, but keep getting
either
mismatch, or
runtime, or "End If without block If" type errors. Is
there
code that will
prevent such duplicates?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top