473,700 Members | 2,732 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

1 New Member
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
Oct 29 '15 #1
1 931
zmbd
5,501 Recognized Expert Moderator Expert
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.
Oct 30 '15 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

3
1999
by: Paul | last post by:
I have some option boxes and combo boxes that looks up values on a separate table (i.e. campus table with campusID & campus name fields) When I choose a selection from the combo box, it puts the information in a main table when I do ADD RECORD. Only the campusID (a number) gets added in the main table. How can I get the corresponding value displayed on the table (i.e. campus name) instead of the equivalent campusID (which is unique).
1
1710
by: Cillies | last post by:
Hi! I have a form that displays information using DLookUp. Now at present I am going into the query and entering criteria that I want the form to display. i.e. the form is for a sports team and I will enter in the team I want to display and the season I want displayed. therfore recieving the results of the team that played on the selected season. Now I want to use a form to call the results form using drop down
1
1733
by: Cillies | last post by:
Does any one know if it is possible to search a database by using combo boxes from a form. I have a form which includes comboboxes. I want to know If I can use these combo boxes to search, i.e. I have a location combobox, and If I wanted to select all entries from that location, would it be possible to enter a find mode like you do in Filemaker pro 5.5 then select the relevant location coupled with any other search data, click find and...
0
2386
by: Andrew | last post by:
Hello, I am trying to create a chart whose underlying query is linked to 2 combo boxes on the same form. I want to pass the values from the combo boxes into the chart query to allow the chart data to be manipulated easily. The combo box uses "Row Source Type" = Table/Query. The "Row Source" references a query to extract a distinct list of data.
5
1886
by: Simon Jans | last post by:
Hello I'm having serious troubles creating a seach form showing data from multiple tbls, I'll try to explain my problem using a simple example by the way I'm using Acces 2003 tblEquipment equipment ID description brand ID price ...
8
3355
by: hollinshead | last post by:
Hi there i have bit of an issue. i haver this database that is purely used for searching records under certain criteria. This criteria is chosen by the user on a form using list boxes and combo boxes. So when the criteria is chosen the user selects a click command button and then the results are displayed on another form. I have successfully created the code for the combo boxes on the form.The list boxes are are the problem which are set to...
6
3570
by: mercout | last post by:
Hey, I've been trying to create a search form in access for a while now, searching through books and emails. I have the search form set up with 11 combo box's, 3 text box's, a view button, and a clear button. Once the user enters certain fields, all of the boxes do not have to be filled and clicks the view button, a report will pop up with detail of what was found.I found this code...
10
5734
by: BarbQB | last post by:
Hi all. I working with an existing access DB at my job and have been asked to add some forms and queries. I've been doing well so far, but this last part of the project has got me stuck. My question is I am trying to create a form to look up a record based off of the selections in 2 combo boxes. ComboBox1 is a list of customer ID's and ComboBox2 is a list of products based off the selection of the customer ID. ComboBox1 is from the Customers...
1
1996
by: Bhawesh9975 | last post by:
Hellow, I am trying to make Railway time table in excel through Visual basic. I have 2 combo Boxes (From and To)& Multiple Text Boxes to populate Train No, Train Name, Arrival & departure (TextBox 1-2-3-4) respectively. When I select Combo box 1 & 2, data is populated in all cited 4 text box correctly(1-2-3-4). But in text box no. 5-6-7-8/9-10-11-12 etc are also populated the same data as appear in text box no 1-2-3-4. Coding which I create is...
0
8721
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9209
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9069
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8918
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7804
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6558
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4653
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3085
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2024
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.