Duplicate entries

[jon.howitt]

I have a slight problem

I want to keep all my dtat entries in the same table for ease of
mainternance but I want to avoid duplicate entries except in one type
of instance.

I have several areas I want to record database entries which normally
have a unique case reference number, I can avoid duplicates of this by
using the caseref and Area as a combined Primary key but in another
instance for admin entries the caseref field will be left blank which
would appear to the database as a duplicate entry. I am using a single
form to entry all the data to the table.

can I do this with the primary key or is their any code I can use in
the form to alert for duplicates and give a yes/no popup message to
continue prior to entry in the table.

 

[HKComputer]

I would change the primary key setup and use DCount to check for duplicate
values.

If DCount("[CaseRef] & [Area]", "tblCase", "[CaseRef]= " & Me.txtCaseRef & "
And [Area]= " & Me.txtArea &"") > 0 Then
MsgBox ("Your Message Here")
Me.txtCaseRef.SetFocus
End If


To understand a little bit about a DCount statement, the first part contains
the fields in the table that need to be checked or evaluated for existing
data:

("[CaseRef] & [Area]",

Surround the field or fields with quotes as I did. Use & to seperate fields
if looking up more than one. End with a comma.

The second part states which table the fields exist in:

"tblCase",

Surround with quotes and end with a comma.

The last part is the where statement. It describes the criteria we are
looking for. The way I have it formatted, it checks to see if an entry in
the table already has the same CaseRef and Area:

"[CaseRef]= " & Me.txtCaseRef & " And [Area]= '" & Me.txtArea &"'")

Surround the whole where statement in one set of quotes. You can use the Or
operator instead of And if you need to. You can also check for empty fields
in the table:
"[CaseRef]= ""
You may have to use the Nz function to make it return correctly. Also, be
aware that you will need different punctuation for different data types. I
surrounded & Me.txtArea & with a set of apostrophies and a set quotes because
it is text/string data. Date data gets worse yet. You must use the # sign
but sometimes you must use other functions to change the date into something
the computer can read. That is outside the scope of this explanation.

You didn't give quite enough information for me to give you a whole chunk of
code that you can copy. You may need to tweak this to make it work.

 

[jon.howitt]

Many thanks I'll give it a try, luckily it doesn't currently involve
and Date functions but may do in the future.

cheers
Jon_H

 

[jon.howitt]

I tried the following code with a table called Issues which has the
fields Caseref an Function I get the following error

Syntax error (missing operator) in query expression
'Caseref=1234567 And Function='.

the case ref is filled in before the function in this instance would it
be better to select the function from the combobox first before trying
to run the code on the caseref. the function comboboxis tied to another
table to make up the list of options.

Private Sub Caseref_BeforeUpdate(Cancel As Integer)

If DCount("[Caseref] & [Function]", "Issues", "[Caseref]=" & Me!Caseref
& " And [Function]=" & Me!Function & "") > 0 Then
MsgBox ("This case " & Me!Caseref & " Already has an entry ")
Me!Caseref.SetFocus
End If

End Sub

cheers

 

[HKComputer via AccessMonster.com]

Just taking a quick glance at your code here. You cannot use Function as a
field name (Is that what you are doing?). It is a "reservered name". You
will find others, lots of them in fact. Just use some common sense when
naming a field. What you could do in your case is use something VFunction.
I have date fields sometimes labeled as GDate or HDate, depending what they
are for. Some more works you may want to stay away from:

Case
Select
Where
Name
Connect
Date
Time
Seek

The second problem is an error on my part. DLookup can contain two fields in
the beginning of the expression but DCount cannot. Basically, the first part
of the statement just states what you want to count. The where part of the
statement can reference multiple fields, even though they were not named in
the beginning. I've changed the offending part of this statement but I
didn't change your field named Function:

Private Sub Caseref_BeforeUpdate(Cancel As Integer)

If DCount("[Caseref]", "Issues", "[Caseref]=" & Me!Caseref &" And [Function]
=" & Me!Function & "") > 0 Then
MsgBox ("This case " & Me!Caseref & " Already has an entry ")
Me!Caseref.SetFocus
End If

End Sub


I might also suggest that you start using the accepted naming conventions for
objects:

Examples:
Table tblIssues (always start with tbl (l as in
larry)
Textbox on Form txtCaseref (always start with txt unless you have good
reason not to)
Combo on Form cboFunction (" cbo
")
Form frmCase (" frm
")
CheckBox chkProcessed (" chk
")

Field names don't follow a naming convention other than your own. They
should be meaningful, should never use reserved names, and should not be more
than about 15 characters (to keep things easy in your code).

I tried the following code with a table called Issues which has the
fields Caseref an Function I get the following error

Syntax error (missing operator) in query expression
'Caseref=1234567 And Function='.

the case ref is filled in before the function in this instance would it
be better to select the function from the combobox first before trying
to run the code on the caseref. the function comboboxis tied to another
table to make up the list of options.

Private Sub Caseref_BeforeUpdate(Cancel As Integer)

If DCount("[Caseref] & [Function]", "Issues", "[Caseref]=" & Me!Caseref
& " And [Function]=" & Me!Function & "") > 0 Then
MsgBox ("This case " & Me!Caseref & " Already has an entry ")
Me!Caseref.SetFocus
End If

End Sub

cheers

 

[Jon_H]

Just taking a quick glance at your code here. You cannot use Function as a
field name (Is that what you are doing?). It is a "reservered name". You
will find others, lots of them in fact. Just use some common sense when
naming a field. What you could do in your case is use something VFunction.
I have date fields sometimes labeled as GDate or HDate, depending what they
are for. Some more works you may want to stay away from:

Case
Select
Where
Name
Connect
Date
Time
Seek

The second problem is an error on my part. DLookup can contain two fields in
the beginning of the expression but DCount cannot. Basically, the first part
of the statement just states what you want to count. The where part of the
statement can reference multiple fields, even though they were not named in
the beginning. I've changed the offending part of this statement but I
didn't change your field named Function:

Private Sub Caseref_BeforeUpdate(Cancel As Integer)

If DCount("[Caseref]", "Issues", "[Caseref]=" & Me!Caseref &" And [Function]
=" & Me!Function & "") > 0 Then
MsgBox ("This case " & Me!Caseref & " Already has an entry ")
Me!Caseref.SetFocus
End If

End Sub


I might also suggest that you start using the accepted naming conventions for
objects:

Examples:
Table tblIssues (always start with tbl (l as in
larry)
Textbox on Form txtCaseref (always start with txt unless you have good
reason not to)
Combo on Form cboFunction (" cbo
")
Form frmCase (" frm
")
CheckBox chkProcessed (" chk
")

Field names don't follow a naming convention other than your own. They
should be meaningful, should never use reserved names, and should not be more
than about 15 characters (to keep things easy in your code).

I tried the following code with a table called Issues which has the
fields Caseref an Function I get the following error

Syntax error (missing operator) in query expression
'Caseref=1234567 And Function='.

the case ref is filled in before the function in this instance would it
be better to select the function from the combobox first before trying
to run the code on the caseref. the function comboboxis tied to another
table to make up the list of options.

Private Sub Caseref_BeforeUpdate(Cancel As Integer)

If DCount("[Caseref] & [Function]", "Issues", "[Caseref]=" & Me!Caseref
& " And [Function]=" & Me!Function & "") > 0 Then
MsgBox ("This case " & Me!Caseref & " Already has an entry ")
Me!Caseref.SetFocus
End If

End Sub

cheers

I have decided to remove the function part and tried the following but
stillappears with an error
If DCount("[Caseref]", "Issues", "[Caseref]=" & Me!Caseref & "") > 0 Then
MsgBox ("This case " & Me!Caseref & " Already has an entry ")
Me!Caseref.SetFocus
End If

I now get data type mismatch in criteria expression

 

[HKComputer via AccessMonster.com]

If DCount("[Caseref]", "Issues", "[Caseref]=" & Me!Caseref & "") > 0 Then

MsgBox ("This case " & Me!Caseref & " Already has an entry ")
Me!Caseref.SetFocus
End If

If DCount("[Caseref]", "Issues", "[Caseref]='" & Me!Caseref & "'") > 0 Then
MsgBox ("This case " & Me!Caseref & " Already has an entry ")
Me!Caseref.SetFocus
End If

What data type is Caseref? Is it a text field, number field, or autonumber?
The code I rewrote above is the same as yours only I surrounded " & Me!
Caseref & " with apostrophies, assuming that your field is text. I also use
Me.Caseref instead of !, though that may not make any difference. Also, it
would be better if your textbox didn't have the same name as your field.
Sometimes Access gets confused about whether you're referring to a field or a
textbox.