Prevent duplicate Customers and there addresses

[Guest]

Is this possible?
On My form I have several fields which are.
CompanyName
BillingAddress
City
State
Zipcode
Let me use BassPro Shop for example: They have a chain of stores in
different cities with different billing addresses. I can't have it Indexed
for no duplicates in my Table for addresses because if one of there stores
Street address is 3201 e hwy 6 Located in Hammond In and John Doe's Street
address is the same for Chicogo.
I need some vb code that will check Company Name, BillingAddress, City and
State
The reason is that it is possible to have the same mailing address in two
different city or States and This way I know without a doubt that there is or
is not any duplicates before update. I have the following code so far but it
isn't working. The debug keeps Highlighting "Me.Bookmark = rsc.Bookmark" in
my code

-------- My Code----------
Private Sub BillingAddress_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone
SID = Me.BillingAddress.Value
stLinkCriteria = "[BillingAddress]=" & "'" & SID & "'"
If Not IsNull(DLookup("CustomerID", "Customers", "BillingAddress")) Then
Cancel = (MsgBox("Do You Want To Proceed?", vbQuestion + vbYesNo) = vbNo)
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing
End Sub
------------------end Code---------------------
Thanks for any help in advance
Alvin

 

[TC]

Forget the forms, reports & code for the moment. You need to get the
table structures correct. In general, you should do that /before/ you
even /think/ of writing any forms or reports.

It sounds to me, as if you have two concepts here: a "chain" (company,
organization, franchise, whatever you want to call it); and a "store"
(being a single member of that chain, company, organization, franchise,
or whatever).

Here is how you would do that:

tblChain
ChainID (PK)
Name
AdrsLine1
(etc.)

tblStore
StoreID (PK)
ChainID
AdrsLine1
(etc.)

tblChain stores data pertaining to the chain as a whole, regardless of
how many stores it has. So in tblChain, the field AdrsLine1 is the
first line of the address of the corporate headquarters, for example.

tblStore stores data pertaining to a single member of a chain. Part of
that data is, the ChainID of the chain to which that store belongs. In
tblStore, AdrsLine1 is the first line of the address of that specific
store.

By that means, you can get chain data out of tblChain, and individual
store data out of tblStore.

Does that help?

TC (MVP Access)
http://tc2.atspace.com

 

[Jeff]

Dear Alvin,

Surely, each zipcode is unique, so you only have to check to see if
there is a company name and a zipcode already in your tables.

If DLookup("CompanyName", "tblYourTable", Name = Me.txtName ZIP=
Me.txtZIP) <> 0 Then
MsgBox "Company name and Zipcode already registered. Please check your
details."

Hope this helps, good luck..

Jeff C

 

[Guest]

Thank you Jeff,
Over the weekend I came up with the following code to check for the mailing
address and zip. I know that it is possible to have two customers in the same
city with like names, James Smith at P.O.234 and another James Smith at2234
5st but you can only have one address per zip. Incase others need it here we
go
----------------------------Start Code--------------------
'Require Billing Address
If IsNull(BillingAddress) Then
MsgBox "You Have not listed a Billing Address!", vbInformation, "No
Billing Address"
Else
'Limit spaces in Billing Address
Me.BillingAddress = Replace([BillingAddress], " ", " ")
Me.BillingAddress = Replace([BillingAddress], " ", " ")
Me.BillingAddress = Replace([BillingAddress], " ", " ")
'Compare Billing Address and Postalcode
If Not IsNull(DLookup("[BillingAddress]", "Customers", "BillingAddress = '"
& Me.BillingAddress & "' and [PostalCode] = '" & Me.PostalCode & "'")) Then
MsgBox "This Address is Already Listed in the database." & Chr(13) &
Chr(10) & Chr(10) & "Please Make sure you aren't Duplicating Records!"
Me.Undo
End If
End If
-----------------------------------------End
Code--------------------------------

 

[TC]

How can you possibly accept a restriction of "one address per zip"?
What will you do when there are several stores in the same zip?

Your problem is almost certainly not in your code. It is almost
certainly in the design of your tables.

Believe me, it is best to get the tables right, at the start of the
process. The longer you leave it, the harder it is :-(

HTH,
TC (MVP Access)
http://tc.atspace.com

 

[Guest]

you could have the primary key for more than one field, assuming all of the
fields are in one table.

 

[Guest]

Thank you Jody,
I'm not sure how to use the primary key? Could you elabriate on that a littl
more orgive an example with my code. All fields are in the same table.
Thanks again
Alvin

you could have the primary key for more than one field, assuming all of the
fields are in one table.

Is this possible?
On My form I have several fields which are.
CompanyName
BillingAddress
City
State
Zipcode
Let me use BassPro Shop for example: They have a chain of stores in
different cities with different billing addresses. I can't have it Indexed
for no duplicates in my Table for addresses because if one of there stores
Street address is 3201 e hwy 6 Located in Hammond In and John Doe's Street
address is the same for Chicogo.
I need some vb code that will check Company Name, BillingAddress, City and
State
The reason is that it is possible to have the same mailing address in two
different city or States and This way I know without a doubt that there is or
is not any duplicates before update. I have the following code so far but it
isn't working. The debug keeps Highlighting "Me.Bookmark = rsc.Bookmark" in
my code

-------- My Code----------
Private Sub BillingAddress_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone
SID = Me.BillingAddress.Value
stLinkCriteria = "[BillingAddress]=" & "'" & SID & "'"
If Not IsNull(DLookup("CustomerID", "Customers", "BillingAddress")) Then
Cancel = (MsgBox("Do You Want To Proceed?", vbQuestion + vbYesNo) = vbNo)
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing
End Sub
------------------end Code---------------------
Thanks for any help in advance
Alvin