I want the user to decide if duplicates are ok

[ThomasK via AccessMonster.com]

I need to enter addresses. Im using the fields StreetNumber, Direction,
StreetName, BuildingNumber, and Suite. They are contained in tblAddresses
with the primary key field AddressID which is an autonumber. I dont want to
have duplicate addresses entered. I know that I could use an index on the
fields but sometimes the fields can be left blank, such as when there is no
suite or building number.

Id like to present the user with a list of similar addresses and let them
select the one they want to use. Or, if there is no address that they want to
use, use the one they entered.

Any way to do this?

 

[Wayne-I-M]

If it was me I may do something like this

Create a form from a query (QBF from the address fields) then use this to
open the form if nessessary.

Have a GoToRecord on the new form based on the QBF.

Use an AfterUpdate event on the address form to trigger this (not sure which
control would trigger the event - up to you that bit)

If ((DLookup("[AddressID]", "[tblAddresses]", "[StreetNumber] ='" &
Form!CD1stName & "' AND [Direction] = '" & Form!Direction & "'"))) Then
Beep
msg = "This address may already be listed" & vbCrLf & vbCrLf & "Do you want
to look at other possible matches?"
style = vbYesNo
title = "Address Match Found"
responce = MsgBox(msg, style, title)
If responce = vbYes Then
DoCmd.OpenForm "Your form name here" etc etc
End If
End if

Of course you may want to add more item to the lookup StreetName,
BuildingNumber, Suite, etc but hope you get the general idea

 

[ThomasK via AccessMonster.com]

I think I understand what your saying. But, one very dumb question, "QBF" not
sure what that stands for. So if I understand right, the AfterUpdate event
(or a comand button) would trigger the code from the address form to lookup
similar addresses, and list them on a form?

Thanks for the help.

If it was me I may do something like this

Create a form from a query (QBF from the address fields) then use this to
open the form if nessessary.

Have a GoToRecord on the new form based on the QBF.

Use an AfterUpdate event on the address form to trigger this (not sure which
control would trigger the event - up to you that bit)

If ((DLookup("[AddressID]", "[tblAddresses]", "[StreetNumber] ='" &
Form!CD1stName & "' AND [Direction] = '" & Form!Direction & "'"))) Then
Beep
msg = "This address may already be listed" & vbCrLf & vbCrLf & "Do you want
to look at other possible matches?"
style = vbYesNo
title = "Address Match Found"
responce = MsgBox(msg, style, title)
If responce = vbYes Then
DoCmd.OpenForm "Your form name here" etc etc
End If
End if

Of course you may want to add more item to the lookup StreetName,
BuildingNumber, Suite, etc but hope you get the general idea

I need to enter addresses. Im using the fields StreetNumber, Direction,
StreetName, BuildingNumber, and Suite. They are contained in tblAddresses

[quoted text clipped - 8 lines]

Any way to do this?

 

[Wayne-I-M]

Sorry QBF = Query By Form

The basics are that you filter a query by the contents of controls on a
form. This would work for you are it allows nulls
You can get more information from here

http://support.microsoft.com/kb/286828/en-us

You would base you other form (that users are led to in the event of there
being multiple address the same) on this QBF query


The event to trigger the action is up to you. Say you have House Number,
Street, Post/Zip Code.

You could trigger the event after you input Nunber and street (in this case
it would be AfterUpdate of street) so you would look for all records that
have the same number and street - BUT you may have lots of 123 High Street.
So you may want to check for Post/Zip code as well (123 High Street ABC123)
as this would give a more exact search

Hope this helps


--
Wayne
Manchester, England.

I think I understand what your saying. But, one very dumb question, "QBF" not
sure what that stands for. So if I understand right, the AfterUpdate event
(or a comand button) would trigger the code from the address form to lookup
similar addresses, and list them on a form?

Thanks for the help.

If it was me I may do something like this

Create a form from a query (QBF from the address fields) then use this to
open the form if nessessary.

Have a GoToRecord on the new form based on the QBF.

Use an AfterUpdate event on the address form to trigger this (not sure which
control would trigger the event - up to you that bit)

If ((DLookup("[AddressID]", "[tblAddresses]", "[StreetNumber] ='" &
Form!CD1stName & "' AND [Direction] = '" & Form!Direction & "'"))) Then
Beep
msg = "This address may already be listed" & vbCrLf & vbCrLf & "Do you want
to look at other possible matches?"
style = vbYesNo
title = "Address Match Found"
responce = MsgBox(msg, style, title)
If responce = vbYes Then
DoCmd.OpenForm "Your form name here" etc etc
End If
End if

Of course you may want to add more item to the lookup StreetName,
BuildingNumber, Suite, etc but hope you get the general idea

I need to enter addresses. Im using the fields StreetNumber, Direction,
StreetName, BuildingNumber, and Suite. They are contained in tblAddresses

[quoted text clipped - 8 lines]

Any way to do this?

 

[big osborne]

I need to enter addresses. Im using the fields StreetNumber, Direction,
StreetName, BuildingNumber, and Suite. They are contained in tblAddresses
with the primary key field AddressID which is an autonumber. I dont want
to
have duplicate addresses entered. I know that I could use an index on the
fields but sometimes the fields can be left blank, such as when there is
no
suite or building number.

Id like to present the user with a list of similar addresses and let them
select the one they want to use. Or, if there is no address that they want
to
use, use the one they entered.

Any way to do this?

--


Only using computer unions all time originally cheat/headphones be with to
it.

 

[ThomasK via AccessMonster.com]

I think that this is just what I was looking for. I'm going to work with it
for a few days and I'll let you know how it goes.

Thanks,
ThomasK

 

[ThomasK via AccessMonster.com]

Well I created a form using my address table. In the form header I put my
unbound textboxes using the method from the link you provided. The way it
works now, the user puts in data in the unbound text boxes, and then hits a
command button (search) that runs a query, that populates the form with all
the records that match the unbound text boxes. This part works fine.

Now I need a way for the user to add a record from the unbound text boxes if
there are no similar records after they hit the search button. I tried doing
an append query, but I couldnt get that to work.

It sure seems like Im going down a very complicated path to do a very simple
thing.

Thanks,
Thomas