Multiple Duplicate Records in Combo Box

  • Thread starter Thread starter Bill Case
  • Start date Start date
B

Bill Case

I have a table with some 20,000 records of residential
addresses in a particular community. I have a report to
print lists of addresses on a particular street,
designated by the user.

I created a form with a combo box that looks up the
available street names from a table, displays a list of
unique street names, uses a SQL statement to apply the
chosen street to a query, and launches a report with the
results.

The purpose of the form is to provide the user with a
combo box from which he can select the street name, then
click the button to launch the report. Here's what the
button does:

-------
Private Sub Command13_Click()

Dim strSQLWhere As String

If IsNull(Me.Combo8) = False Then
strSQLWhere = "StreetName = '" & Me.Combo8 & "'"
End If

DoCmd.OpenReport "Entire List - All Addresses",
acViewPreview, , strSQLWhere
DoCmd.Maximize
End Sub
-------

The only way I have been able to make this work is to
make the source of the combo box (Combo8, as seen above)
be a table ("Streets") that includes only street names.
The problem with this is, if new records are added, I
have to make sure any new street names are also added to
the "Streets" table.

If I designate the source of the combo box to be the main
table (called "Details"), when I pull down the combo box,
I get hundreds of the same street name repeated (which
makes sense, I understand, since each is related to an
individual record in the table I'm referencing).

Question: Is there a way to restrict the combo box to
show only a single instance of the street name (that is,
unique names) from the Details table? Alternatively, is
there a way to somehow autocreate a subtable of just the
street names that gets updated whenever data is entered
into the Details table? (I realize this part of my
question is outside the scope of the "forms" newsgroup.)

Thank you in advance for any help you can offer.
 
Bill,

Assuming that your combobox only has one column for street,
change the row source to be...

Select DistinctRow Street From Addresses Group By Street

.... Changing any field or table names to yours. This should
pull one instance of every unique street.

Gary Miller
 
You don't have to give us War and Peace! If you want to show only unique
values in your combobox's RowSource then select Totals from the View menu.

Tom.
 
Bill Case said:
I have a table with some 20,000 records of residential
addresses in a particular community. I have a report to
print lists of addresses on a particular street,
designated by the user.
Click to expand...
You should not be using Access for this or if you do only after the
addresses have been passed through a CASS certified program that will give
you corrected addresses in a (post Office) normilazed manner and allow the
customer to choose the cheapest way to mail.
If everything goes first class it does not matter but a lot can be saved if
the mailing is large enough and the addresses sorted in walking order. (This
almost never is in numerical order.)
 
Pardon???
Click to expand...

If everything is going to be sent first class then Access is as good as
anything for mailing lists.
If the client wants to save money and has a large list then there are
database programs that
1. corrects addresses,
2. change them to what the post office wants to see and
3. will sort them in a variety of ways.

1 requires a lot of work and access to large amounts of information.
2. can be done in Access but will require a good bit of coding.
3. will work with little problem for ZIP codes but for maximum savings the
mail must be sorted in the way the postman walks his or her route. This also
requires a lot of input and coding.
It is an ongoing expensive business to do this and dedicated (flat file)
programs do it much better.
A 20,000 piece list is borderline but using a service to do the mailing will
probably be cheaper than doing it in house. (Unless a person does not value
their own time.)
 
Back
Top