Form to look up records, display, and report

[Bill Case]

Hello everyone . . . I hope this is the correct forum for
even the most basic of questions. I've got a scenario I'm
trying to achieve, and I think I'm missing a few of the
pieces I need to link it all together. Here's what I'm
trying to do:

1. I have a table with over 20000 records, names and
addresses. The table breaks out the name of the street
(i.e. if the address is 123 Main Street, there is a field
for the record containing "Main Street".

2. The end goal is to create a report with a list of
addresses for a particular street. In my DB, I've got
about 490 different streets. I want a report for all the
addresses and names on, say, Main Street. I've created a
report to list all the addresses, sorted by street, based
on a query.

3. The issue is that I want to be able to specify the
street name to be reported. I know about parameters in the
query, but who can keep track of the exact spelling of 490
streets? So, I want a form with a combo or list box that
would display the UNIQUE street names, then report
matching records for only that street name in a subform
(or whatever). After that, I need a command button to
dump the results of that lookup into a report and display
it on the screen.

4. I've tried all sorts of things, including making a
separate table with all of the unique street names
(called "Streets") (that's how I know there are about
490), then created a relationship to the detailed table
(called "Details") to connect them. I can run a query
with a parameter and specify the street, and the lookup
works fine.

5. The other thing I did is to create a subdatasheet
under the Streets table which is the Details table. I've
got a form where I have a combo box in the form header
that I can get a list of the streets, then select the
street and see the results in a subform. This works, but
I have to manually expand the subform, and it doesn't
navigate terribly well.

Sorry for the long details, but I thought it would be best
to show the whole picture. So, my basic question is, am I
heading in the right direction, or am I making this too
difficult? Can I make the subform automatically expand the
results? If so, can I make a command button to grab the
results and throw them into a report, or do I need some
sort of temporary table?

Whatever you can offer would be great! Thanks!

 

[Albert D. Kallal]

So, if I read your question, it could be shortened to:

I have a list of street names in a table. I want to have a user select that
street name in a combo box, and then launch a report with ONLY those records
that match the street field.

The solution is to take that small street list and build a un-bound form
(that is a form that is NOT attached to a table, so turn off all the junk
like record navigation buttons, record selectors etc). Now, use the combo
box wizard to build a combo box on this form based on that street list
table.

Now, place a nice button to "launch the report" on the form with the
following code behind this buttion:

dim strSQLWhere as string

if isnull(me.YourComboBoxName) = false then
strSQLWhere = "CityStreet = '" & me.comboBoxName & "'"
end if

docmd.OpenReport "your report",acViewPreivew,,strSQLWhere

The above means that you DO NOT need to use a query with parameters for the
report (you can use the query you have...but just remove those prompt
conditions, as the above combo box will now supply the "where" clause).

In fact, I often make forms for prompting users. Here is some screen shots
of what I mean:

http://www.attcanada.net/~kallal.msn/Search/index.html