Get a lookup Form using combo boxes to pull data from a querie and send it to a rpt.

[wgreen]

I have not done any access work in a lot of years and I cannot figure out my current problem.

1. I have a single table database that has 30 columns.
2. I am creating reports to extract certain data from
the records.
3. None of the records have all of the fields filled
in.
4. I have created queries for each type of report that
I need. All of the queries work but they all return
there data from all of the records in the database.
5. I have created Reports for all of the queries. All
of the reports work but the reports also contain
data from all of the records in the database.
6. I have created a form that contains Combo boxes
(1 for each report). My intent is to select the
data I need in the report based on the text
contained in the combo box. The combo boxes are set
up to pull only unique values from the table because
many of the values are duplicates. They are also
set up to "Limit to List" so that only information
entered into the table is available for selection.
This function also works correctly.
7. Each Combo Box has a command button that is supposed
to execute the querie, based on the text contained
in the combo box. The querie should retrieve the
information from only the records that contain the
combo box criteria and send that information to the
report.
8. I need help figuring out how to get just the
information that I want from the querie and then
getting the querie to send it to the report.
9. Note: I can make it work correctly by having the
querie ask for the search criteria but this database
has over three thousand records. That is why I
want to use a combo box and be able to start typing
and let the combo box narrow down my search for the
correct search criteria.
10. Note: I have found numerous pieces of code online
that showed promise but none of them have worked
out. Any help would be greatly APPRECIATED ! ! ! !

Thanks
wgreen

[zmbd]

Lots of information and very little to actually work with...

From the first blush:

1. I have a single table database that has 30 columns.
3. None of the records have all of the fields filled in.

Read: Database Normalization and Table Structures
Between 1 and 3, it truly sounds as if there is an issue with the database design. Hard to be certain without more information about the actual table design.

Perhaps the data was originally based on a Spreadsheet? If so, then most likely there are design issues.

4. I have created queries for each type of report that
I need. All of the queries work but they all return
there data from all of the records in the database

Most respectfully, your query isn't working as it is not returning the dataset you desire. Unfortunately, you haven't provided enough information to begin to effectively suggest how to modify the query(ies) to limit your record set.

With that said; however, the issue is most likely within your criteria. Either not set, or not correctly set. Without your SQL and the table design it's hard to tell. Also one can use other predicates, TOP n to limit the recordset to a number of records or DISTINCT/DISTINCTROW to filter out duplicates.

Here are two links to basic tutorials on creating simple queries...
+ MS - Create a simple select query

+MS Queries II: Create basic select queries (presents the information in a slightly different format)

Compare the design between the tutorials and yours.

5. I have created Reports for all of the queries

Related to "4." Fix the queries and the reports will most likely show the corrected record set. One may be able to even reduce the number of reports depending on the design of the database...

6. I have created a form that contains Combo boxes (1 for each report). My intent is to select the data I need in the report based on the text contained in the combo box.(...)

Not even sure where to begin with this...
Because your queries are not functioning correctly, there's most likely a very low probability that these will work as intended. One possibility is to feed the selected value as a parameter to the query such as in this tutorial: MS: Use a parameter to make a query ask for input - Create a form that collects parameters one would use the combobox instead of the textbox. However, I have a suspicion that there will be some VBA coding involved here.

Of note here, is what is actually being pulled in the combobox... just because text is displayed, doesn't mean that's what the actual value of the combobox is... especially if someone has used Lookup/Multi-value-fields in the table.

One thing I like to do is use the form to show the records to be reported and one technique that I have found useful is cascading comboboxes and form filtering
+Cascaded Form Filtering
+Multiple Combobox Filter with Clear Option
Once the form shows the correct record set, it is usually straight forward to use the form's recordset as the feed to the report and there are several methods to do this.


Other than the 3000 record... the remainder of your post is a rehash of the previous points.
Now just to be clear...
When you look at a database, each row in the "table" is a record. Within that record is a "Field" that contains the information related to that record. SO, an entry in the data table such as:
[PK][Field1][Field2][Field3]...[Field300]
Is one record, not 301 records. With this in mind do you have a table with 30 fields, and 100 rows, which would be only a 100 records, or do you actually have 3000 rows (records) in the table.


++ Because we ask that each thread to normally be focused on a single issue, IMHO, we may need to focus with your raw data table. Once that is under control, we may need start a new thread (linked back to this one :) ) to deal with your queries and, of course, new threads as we go for the form and reports as needed.