Duplicate Alert

[Guest]

Hi! I have a field that is indexed with duplicates OK and that's the way I want it....however, I would like to be alerted that the entry already exists, do you want to continue, if a duplicate is entered into that field on the attached form.

Example: Account #1234 already exists as a record...I am entering a new record and type 1234 in the Account # field on the form...a box pops up alerting me that the 1234 already exists and would I like to continue.....

Can this be done? (Using Access 2003)

Thanks,
Jenny

 

[GVaught]

If the table that allows duplicates is a child to the parent table, then
this capability is moot. Child/Related tables (many side) is designed to
have duplications. If this is a master table and the account is used to link
other records in another table, then duplication is a no-no. I would
suggest that you learn about Normalization and Relationships in a database
before you develop a table with a lot of records that will become useless
later down the road.

--
G Vaught

Hi! I have a field that is indexed with duplicates OK and that's the way

I want it....however, I would like to be alerted that the entry already
exists, do you want to continue, if a duplicate is entered into that field
on the attached form.

Example: Account #1234 already exists as a record...I am entering a new

record and type 1234 in the Account # field on the form...a box pops up
alerting me that the 1234 already exists and would I like to continue.....

 

[Guest]

I would
suggest that you learn about Normalization and Relationships in a database
before you develop a table with a lot of records that will become useless
later down the road.

GVaught,

Thanks for the criticism. I thought this board was for help. Maybe I wasn't explicit enough in my question and someone else will be kind enough to help me...

Because the account number is related to different vendors, it is possible that it can be the same for two different vendors...that's why I relate the tables with an unique, non-duplicatible acctID. My issue is that I wanted to prevent users from entering the same bill (vendor/account#) twice. That's why I want a pop-up to alert me that the # is a duplicate and then I can check to see if it's the same vendor. Obviously, if it is the same vendor, I won't enter it again...if it is a different vendor, I will.

Thanks,
Jenny

 

[stefan hoffmann]

hi,

Can this be done? (Using Access 2003)

try the BeforeUpdate event:

If Not IsNull(DLookup("ID","YourTable","NewEntry")) Then
Cancel = (MsgBox("Proceed?", vbQuestion+vbYesNo) = vbNo)
End If

--> stefan <--

 

[Guest]

Hi Stefan,

Thank you for your help....it didn't work but I think I'm missing something...what did you mean by "NewEntry"? Here is what I have:


Private Sub CustNumber_BeforeUpdate(Cancel As Integer)

If Not IsNull(DLookup(CustNumber, Accounts Table Query, "NewEntry")) Then
Cancel = (MsgBox("Proceed?", vbQuestion + vbYesNo) = vbNo)
End If

End Sub

Or...should it be the Table name even though the form is based on a query?

Thanks,
Jenny

 

[stefan hoffmann]

hi Jenny,

it's your syntax.

Private Sub CustNumber_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup(CustNumber, Accounts Table Query, "NewEntry")) Then
Cancel = (MsgBox("Proceed?", vbQuestion + vbYesNo) = vbNo)
End If
End Sub
Or...should it be the Table name even though the form is based on a query?

The DLookup should look like the following, don't ignore the quotation
marks:

DLookup("CustNumber", _
"[Accounts Table Query]", _
"CustNumber=" & CustNumber.Value)

The first parameter is the field name to lookup: "CustNumber" means in
your view must exist the field with name CustNumber.

The second is the table/view name: "[Accounts Table Query]" use the
query as source, the brackets are only to ensure that the query can be
found, because you use spaces in the name.

And the third is a where condition, which should return one value or
null if not found: "CustNumber=" & CustNumber.Value. CustNumber.Value is
the edit on your form.

So it reads: lookup the value CustNumber in [Accounts Table Query] which
has CustNumber = New Number.

--> stefan <--

 

[Guest]

Stefan,

You are the Bomb!!! Thank you sooooo much....it works!

Thanks,
Jenny

hi Jenny,

it's your syntax.

Private Sub CustNumber_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup(CustNumber, Accounts Table Query, "NewEntry")) Then
Cancel = (MsgBox("Proceed?", vbQuestion + vbYesNo) = vbNo)
End If
End Sub
Or...should it be the Table name even though the form is based on a query?

The DLookup should look like the following, don't ignore the quotation
marks:

DLookup("CustNumber", _
"[Accounts Table Query]", _
"CustNumber=" & CustNumber.Value)

The first parameter is the field name to lookup: "CustNumber" means in
your view must exist the field with name CustNumber.

The second is the table/view name: "[Accounts Table Query]" use the
query as source, the brackets are only to ensure that the query can be
found, because you use spaces in the name.

And the third is a where condition, which should return one value or
null if not found: "CustNumber=" & CustNumber.Value. CustNumber.Value is
the edit on your form.

So it reads: lookup the value CustNumber in [Accounts Table Query] which
has CustNumber = New Number.

--> stefan <--

 

[Guest]

Stefan, I was looking at your advice about duplicate alert, I am trying to do
the same thing only that I have a unbound combox[txtsrchrqstNo] that a user
will enter the rqstNumber and an alert should popup and tell the user that
there are several(indicate# if possible) duplicates of the requests in the
database


I hope you will be able to help me with this issue!

hi Jenny,

it's your syntax.

Private Sub CustNumber_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup(CustNumber, Accounts Table Query, "NewEntry")) Then
Cancel = (MsgBox("Proceed?", vbQuestion + vbYesNo) = vbNo)
End If
End Sub
Or...should it be the Table name even though the form is based on a query?

The DLookup should look like the following, don't ignore the quotation
marks:

DLookup("CustNumber", _
"[Accounts Table Query]", _
"CustNumber=" & CustNumber.Value)

The first parameter is the field name to lookup: "CustNumber" means in
your view must exist the field with name CustNumber.

The second is the table/view name: "[Accounts Table Query]" use the
query as source, the brackets are only to ensure that the query can be
found, because you use spaces in the name.

And the third is a where condition, which should return one value or
null if not found: "CustNumber=" & CustNumber.Value. CustNumber.Value is
the edit on your form.

So it reads: lookup the value CustNumber in [Accounts Table Query] which
has CustNumber = New Number.

--> stefan <--