Form to look for similar addresses

[ThomasK via AccessMonster.com]

Im trying to create a form that will look for similar addresses to one
entered and allow the user to choose from those addresses or use the original
address that they entered. My database stores information on businesses, the
businesses are organized by address. I have a addresses table and a
businesses table, one to many. I need to figure out a way for my users to
enter an address, (street number, direction, and street name) and then look
to see if there are any similar addresses already in the database. This would
be to prevent creating two adderesses that are similar and to hopefully keep
someone from entering 544 East Main, instead of using 544 East Main, suite
100.

My thought was to have a form where the user would enter the address into
unbound textboxes, then hit a comand button that will lookup and displayed a
list of addresses based on the street number, direction, and street name. The
user could then pick from the list of similars and go to that record or if
there were no similars use the one they entered.

But, Im not sure how to set this all up and keep it simple and easy to use.
Any suggestions?

 

[Damon Heron]

This should get you started.
Add a textbox, command button, and listbox to your form.
This example uses a table with employeeID, name, address, cty.
Set the row source of the listbox to "SELECT tblEmp.EmpID, tblEmp.EmpNm,
tblEmp.Empadd, tblEmp.EmpCty FROM tblEmp;" '(substituting your table info)
type an address in the textbox.

The click event of the command button:

Private Sub Command2_Click()
Dim strsql As String
Dim mystr As String
mystr = "*" & Me.Text0 & "*"
strsql = "SELECT tblEmp.EmpID, tblEmp.EmpNm, tblEmp.Empadd, tblEmp.EmpCty "
& _
"FROM tblEmp WHERE (((tblEmp.Empadd) Like """ & [mystr] & """));"
Debug.Print strsql
Me.List3.RowSource = strsql
End Sub

Now your addresses (if any) that match what you type will show up in the
listbox. The trick is in the Like and "*" wildcard characters.
From there, you can select an item from the listbox (see Help)

Damon

 

[ThomasK via AccessMonster.com]

Thanks for your help,
I put in my references, but Im having a problem getting it to work. It's
giving me an error. Any ideas on what I did wrong?

Tom

 

[ThomasK via AccessMonster.com]

Sorry, forgot to show you what I did.....lol....here it is......


Private Sub Command2_Click()
Dim strsql As String
Dim mystr As String
mystr = "*" & Me.Text0 & "*"
strsql = "SELECT tblAddresses.AddressID, tblAddresses.StreetNumber,
tblAddresses.Direction, tblAddresses.StreetName "
& _
"FROM tblAddresses WHERE (((tblAddresses.StreetNumber) Like """ & [mystr] &
"""));"
Debug.Print strsql
Me.List3.RowSource = strsql
End Sub

 

[Damon Heron]

Is the name of your textbox Text0?
Is the name of your command button Command2?
Is StreetNumber a text field?
What is the error msg?

In the immediate window, the strsql should be printed.
What does it say?
Can you copy that string from the immediate window and paste it in a query
and get it to run correctly?

Damon

 

[ThomasK via AccessMonster.com]

Is the name of your textbox Text0?

yes

Is the name of your command button Command2?
yes

Is StreetNumber a text field?

Whoops, got me on that one... it's a number field....that didnt occure to me..
..

What is the error msg?

Compile error: Syntax error

Could this be caused by it not being a text field?

In the immediate window, the strsql should be printed.
What does it say?

The immediate window in the debug window is empty. This is a little past my
level so forgive my ignorance. The problem seems to be on this line, at least
this line is red when the error occures.

&_
"FROM tblAddresses WHERE (((tblAddresses.StreetName) Like """ & [mystr] & """)
);"

Thanks again for the help, as you can see Im in over my head here..

Tom

 

[Damon Heron]

Due to the way the forum wraps the text, it appears that you have used my
example, which has caused the error.
When you have more than one line of text, at the end of the line, put the
ampersand and underline character: & _

"SELECT tblEmp.EID, tblEmp.ENm, tblEmp.Eadd, tblEmp.Cty " & _
"FROM tblEmp WHERE (((tblEmp.Eadd) Like """ & [mystr] & """));"

Now the fact that the immediate window is blank, code is stopping before the
debug.print strsql line,
so you won't have anything in the immediate window.
Secondly, your example shows streetname, so the streetnumber should make any
diff,
if you are testing with "like" for the name.

Damon

Is the name of your textbox Text0?
yes

Is the name of your command button Command2?
yes

Is StreetNumber a text field?

Whoops, got me on that one... it's a number field....that didnt occure to
me..
.

What is the error msg?

Compile error: Syntax error

Could this be caused by it not being a text field?

In the immediate window, the strsql should be printed.
What does it say?

The immediate window in the debug window is empty. This is a little past
my
level so forgive my ignorance. The problem seems to be on this line, at
least
this line is red when the error occures.

&_
"FROM tblAddresses WHERE (((tblAddresses.StreetName) Like """ & [mystr] &
""")
);"

Thanks again for the help, as you can see Im in over my head here..

Tom

 

[ThomasK via AccessMonster.com]

Your right, and it worked. Now when I click the command button it looks at
Text0 and displays similar addresses in the listbox. This is what I used.

Private Sub Command2_Click()
Dim strsql As String
Dim mystr As String
mystr = "*" & Me.Text0 & "*"
strsql = "SELECT tblAddresses.AddressID, tblAddresses.StreetNumber,
tblAddresses.Direction, tblAddresses.StreetName " & _
"FROM tblAddresses WHERE (((tblAddresses.StreetNumber) Like """ & [mystr] &
"""));"
Debug.Print strsql
Me.List3.RowSource = strsql
End Sub

Now if I wanted to compair more than one field, say StreetNumber, Direction,
and StreetName, could I just compare more than one textbox? Maybe if I had
Text0, Text1, and Text2.

Also, how could I still use the address that the user had entered into the
text boxes if there was no similar address in the table and the listbox came
back empty?

My goal is for the user to input an address, check to see if its already in
the database (maybe something similar is and they just mistyped), select the
address if its found (if not use the one they entered), and use that address
to reference the business information thats in the business table.

Thanks again for all your help,
Tom