MS ACCESS :: Multiple Query Fields & Combo Boxes

Multiple Criteria Query Using Combo Boxes
Hi All,

I am currently having problems with a multiple criteria query with combo boxes (see attached file).

The problem:

- When user defines all 3 criteria (e.g. selects Project: A, Category: Services and Equipment Type: Packages), the query returns a result - no problem here.

- When user defines no fields (i.e. all combo boxes empty), query returns all records - again, not a probelm.

- However, say a user wants to display all records belonging to just Project: A, for example, the query returns no records. Obviously, there a a number of combinations of this (another example would be a user wanting to show records from project: B, with Category: Equipment Type).

I have included the Is Null statement in the query but to no avail. Could anyone advise on a solution based on the attached database. Your help would be greatly appreciated!

Kind regards,

Stephen

View Replies ! View Related

Multiple Combo Boxes Instead Of Multi-select List For Query ... ?
I have a database to keep track of overtime. Via combo boxes, the form prompts the user for department, date range, and employee and returns the amount of overtime worked by that employee for that department in the given range. I'd like to put 5 combo boxes on the form and allow the user to select up to 5 employees, resulting in a report with each of the employees listed with their respective data. I have considered a multi-select list box, but since we have 300+ employees the ability to start typing a last name to select each employee seems a little more user-friendly. I do have a report that lists all employees in the selected department and date range, but want the ability to select up to 5 employees instead of look through a report/list with 300 names. I am new to Access, would like to know I'm proposing is the most feasible way to accomplish what I need and a general example of how the query would be set up. Thank you in advance for your time.
Ceidre

View Replies ! View Related

Multiple Combo Boxes
Hi guys

I'm creating a database where there will be multiple combo boxes on a form. When a user chooses an option from the first combo box, I want the available choices in the 2nd combo box to be reduced and so on.

I thought I'd create a navigation table so the first column has multiple occurances of each option, but then the second column has the available choices for the first option etc. Then, I thought I'd use the chosen value in combo box 1 and filter my navigation table depending on the result.

However, the field doesn't appear to be storing the value. When I set up the query on the combo box to select distinct navigation.[cause] from navigation where navigation[type]=forms!form name[type] it doesn't work.

Any ideas

Cheers

View Replies ! View Related

Multiple Combo Boxes On Same Form
I need a form that has multiple categories, 7, listed one after another vertically. I also need the categories divided in to 2 columns.
I need to have 2 combo boxes, 1 each controlling the info for each column.
Can I do this on 1 form. Suggestions would be appreciated.

Freddie

View Replies ! View Related

Using Multiple Criteria From Combo Boxes
Hi All,

I would like a form (in Access 2000) that allows users to search the database using combo boxes that specify criteria. So, upon load the form would be populated by every record, then as the user begins to make selections using the combo boxes they can narrow the results down to only a few (combo box filters are cascading; each one narrowing the results the other returned). There would also obviously need to be a reset button and 'all' fields in the combo boxes. I have found the code to filter by 1 combo box but if more than 1 is added (obviously) the filter resets to filter only by the current selection. (See the method I used here (http://www.freevbcode.com/ShowCode.asp?ID=4668))

Does anyone know how to bolt these together so they work in sync?

Please be gentle as I've only been learning Access for 2 months and VBA is still very new to me

Thanks,

Bobadopolis

View Replies ! View Related

Multiple Combo Boxes On The Form
Hi,

I need help in the following 2 problems:

I have two combo boxes on the form and print button.
In one combo box showing numbers 1,2,3,4 and 5.
Other combo box showing logical operator =,<,>
User is going to select from one combo box logical operator and from other number "means class field".
Print button prints the report. Report is based on the query.
In the query in criteria line I put the code
[FORMS]![MAINFORM]![CMB_CLASS]
It works for one combo box. Problem is to get criteria from second combo box or concatenate the both criteria’s. In query criteria line it should show like <2 or =3 or what ever user is selecting.

Second problem is in the same line. Three text boxes and 2 combo boxes showing "OR" and "AND" For example in one text box user enters last_name and then he select "OR", second text box enter city and then he select "AND" from second combo box and third text box enter department. After selection he is going to click the print button to print the report.

I really appreciate if anyone knows the solution or they used this type of thing in there application.

Thanks

View Replies ! View Related

Multiple Search Criteria With Combo Boxes
school has thrown me in to a bodemless ms access pit. can do a bit of VB but queries...I´m new to this stuff. i am glad to have found this fine forum.

i have:
-1 table: tblSpeler (65 entries)
-1 form: frmSpeler (already conected to a search query)
-1 search form: frmZoeken
*2 combo boxes: zoektekst (mp3 player make), zoektekst2 (mp3 player type), search button (cmdZoek).

my question:when i select a make, and then the type » i want that the form shows me the mp3 player with the selected make and type.

if more info needed, just ask. thanx

View Replies ! View Related

Use Combo Boxes Or Include Actual Fields
I am using a datasheet to show company info -

I have a field countyID which is a code field and a lookup table County

I want to show the text for county on the datasheet, should I base the datasheet on just the company table and use a combo to get the county text - or should I have query that brings in the county text.

Could the experts tell me the "correct" way of doing this?

What are the performance issues of the two methods. Is which method I choose dependent on how many records are in the lookup table?

Thanks Paul

View Replies ! View Related

Multiple Combo Boxes In Form To Choose Groups Of Records
Hello,
I am wondering if it is possible to have several combo "boxes" in one form. The form is based on one table. In the table there are several fields which use a look-up (combo) drop down box to choose from: Type, Description, Manufacturer and Location.

In the form I want a combo box for Type, to bring up specific records in a subform, then I want a combo box for Description to bring up another set of records (within that Type of equipment) and a third combo box for Location to bring up all equipment within that location.

When I have tried to set this up - it changes some of the data in the in the subform - which changes it in the underlying table. If it set the form to open in New Record, then nothing shows up in the drop down box. Is there some If, THen code I could use to make it look first in the Type, then in the Description field, then close those out and look for the set of records that match in the location field?

Does that make sense?

I know how to manipulate in MSAccess templates, etc., but I don't know much about the underlying codes.

Thanks for any help!!!

View Replies ! View Related

Using Multiple Combo Boxes To Retrieve (not Edit) Records From A Table
I'm creating a form that has combo boxes pertaining to each of the following fields from table "Documents": DocumentID (primary key), DocumentTitle, DocumentAuthor, and DocumentYear. No combo box is used with any priority over the others. The working form will allow a user to retrieve the full document record (data for all fields) by using any combo box they want, as well as any combination of combo boxes. This means that if a selection is made in one field's combo box, the drop-down lists in the other boxes need to update based on that preliminary selection. The filtered results for each field, based on any and all combo box selections, are always shown in a single datasheet on the form.

The kicker is that when a user starts filtering records by making selections from the drop down list in a combo box, but then decides to TYPE in another field's combo box, I want the combo box they typed in to do two things: 1) filter the records for that field based on what they typed, if any records meet that criteria (e.g. they typed "B" so only records beginning with B are shown), and 2) if no records match the typed criteria, the search starts all over (at the top of the cascade), filtering all library records based only on what they typed in the most recent combo box.

Does that make sense? Any tips welcomed.

I don't know how to cascade combo boxes in this way.

View Replies ! View Related

***Multiple Combo Boxes On Form To Filter Subform Records!!!!
HI All,

This is been a trouble to me more than a week....already passed deadline.
Please help me.

I have 4 forms.

First form consists of Projects.
So if you choose any Project and click on command button for other forms then you can goto that form. This is ok with my work.

All forms(1,2,3)....are based on the qryProjects........and Each form has subform......and linked child field of subform with master field of master form.......so I can see all the records containing the particular selected project from the projects form......................

NOW my problem???????? is I have 3 cascading combo boxes to filter the records in subforms.....and printing the results of filtered records...................This one i tried to check many posts...but i got bad luck with all the sources..........PLEASE HELP ME....HERE I AM attaching my FIle....

View Replies ! View Related

Filtering With 2 Combo Boxes And Then Populating Text/memo Fields
I have two questions.
I have a form with 2 combo boxes. The first pulls from a simple list. Once you make a choice, the second combo box is then filtered by the first. This works correct, except that once a choice is made in the first, the filter is locked. If you change the first combo box's value a second time it does not effect the second box. The query that I am using for the second combo box has two columns that pull the first 50 characters of a pair of memo fields. Quote: SELECT MSSS.SS_ID, Left([Application_Name],50) AS Expr1, Left([Description],50) AS Expr2, MSSS.Site_Code
FROM MSSS
WHERE (((MSSS.Site_Code)=[Forms]![Edit or Delete Requests]![Combo6])); These fields are then used to populate 2 text fields using an event procedure. Quote: Private Sub Combo10_AfterUpdate()
' Display Partial Application Name and Description based on choice
Me!txtApplication_Name = Me!Combo10.Column(1)
Me!txtDescription = Me!Combo10.Column(2)
End Sub This is working fine, but it starts the next problem.

I need to change the form to include the entire memo field into a text box. I was using the columns of the second combo box to fill them in. Since the combo box is limited to only 50 characters, i could only grab part of it. I am guessing there is a much better way to accomplish what I need, but I am too inexperienced with forms and vb to come up with it. Below are the fields I need from table MSSS
Business_Name
Application_Name
Description
Acronym
Level_1_Support
Level_2_Support
Escalation_process
Troubleshooting
Priority
Links
Modified
Disabled

Thanks in advance to any help!

View Replies ! View Related

Combo Box With Multiple Fields
Have been searching all morning & can't find solution (technique must be haywire)

Have created a combo box query with 3 fields, id-code-description
Objective is to select Code from combo box and populate Description field from the value in the combo box into another table

Have tried all sorts of techniques, none of which have worked

I am sure this has been answered a million times, but I just can't find it

Any help appreciated

View Replies ! View Related

Combo Box Completing Multiple Fields Help
Ok... big time newbie/hack here. I have a tiny bit of programming experience from waaay back with UNIX and C. Have basic database experience and understanding but no VB experience. So I appologize ahead of time if this seems really simple. I'm working on "fixing" a small access database/program for my wife's work. Very simple design.

I have a company table and a customer table. I have customer form and a company form. They may have several customers at the same company. So my design has them starting on the Customer form. They begin by filling in the Customer's Name and then they get to the Company Name combo box. They should select the Company Name from this box. If it is a new company and is not in the list they should click on the "New Company" button beside the Company Name field. Then the Company Form opens and they can add a new Company along with Company address and phone numbers etc. Then they close the Add Company Form and then can select the newly added Company form the Company Name combo box (by the way, the only way I could get this box to update with the new company name was to use a macro that only runs the Requery statement for the On Enter event... it works... I hope this is ok).

Anyway, this is where my problem arises. I need the selection of the Company Name to also pull over the address, phone number, fax number, etc. info from the Company table and complete it on the Customer form/table now. How do I do that? I think I need to do this with the After Update event with some VB code... but I have no idea how to actually pass this data. The property for my combo box is as follows:
Control Source........CompanyName
Row/Source Type.....Table/Query
Row Source......SELECT zMASCompanies.CompanyName FROM zMASCompanies;

The field doesn't have to be a combo box (if this is not the right way to do it)... I just need them to select a company name and have it fill in about 20 or 25 fields from the company table/form into the customer table/form.

I did try searching the forum and found a few things but nothing I found was very clear (especially since I am not sure I am even heading down the right path). Any assistance would be greatly appreciated!!!

Any help please????
Thanks,
Jeff

View Replies ! View Related

Query On Combo-boxes
I have a query based on two combo boxes in a form that links to a report, it works fine. What I want to be able to do is select criteria from one box and if the other is blank have it include everything (right now if one is blank the report will be be blank) and vice versa. Any suggestion on what to enter into the criteria section of the query.

Thanks in advance

View Replies ! View Related

Single Combo Box Entry Multiple Fields
In the main form, I have combo box which has the names of personnel that could be involved in a project (Every name is tied to a number as to eliminate spelling errors and things of the like). There are at most 2 people working on a project. I want to set up the subform so that if a name is selected in the main form, everything that person is involved with comes up in the subform, whether that person is the primary or the secondary staff member. :confused:

View Replies ! View Related

(Help!) Query On Values In Combo Boxes
I just started using Access a couple weeks ago. I bought a couple books, have read some help stuff in forums webwide, but I am still stuck on the basics of how to do the following~ Please Help ~

My DB:
I have a single main table, in addition there are several mini-tables used to support combo boxes for form inputing.

My Mission:
It's been requested of me that My Main Switchboard have an option to lead to a secondary form where the end-user can "quick-filter" records to generate a table or report based upon dropdowns and check boxes for 9 different search variables- the data will then be pulled from the original data table based on the 9 choices (or less, as there will be often certain variables left blank).

My Questions:
1) how do I tie the values from the combo boxes in my new form to supportive queries? (perhaps a parameter query with a macro that searches that particular combo box for values??) - or is there a better way to do this?
2) how do I tie all those queries together so that some of the 9 choices can be blank, and of the others the assurance that they'll stack- or further refine the results.
3) one of the search criteria is Industry Sector. there is a mini-table to provide the values for the drop-down, but in the main table there are four columns in case a particular company falls into more than one sector. will this present any problem or can a query be easily tailored to search any of those four columns for that value?

I want to thank anyone who cares to help very much, in advance. I know my issues probably seem very simple, but I've been tasked this duty of learning Access and working the company's DB on my own. The "comprehensive" books that I've purchased haven't seemed to clarify this particular issue, so here I am. :o

View Replies ! View Related

Select Query Using A Value In One Or 2 Combo Boxes
Hi,

I currently have a select query that has a where statement to select a value depending on the value of a combo box in a form.

WHERE (((tblSampleSubmission.SubmissionNumber)=[Forms]![frmReportPreview]![SubNumber]))

What I am wondering is there any way that the where statement can be for another form depending on what form is currently active by using an "or" statement?

Basically what I am trying to do is make the db as easy to use as possible and not have to type in the value again.

View Replies ! View Related

Query Based On 1-4 Combo Boxes
I have a rather simple query that will produce filtered results based on the user's selection in 1 to 4 separate combo boxes on a form. But I can't get it to do what I want it to do. Here are my vitals:

Form = frmQryAdHoc
City combo box = cboCity (from tblWARNData)
County combo box = cboCounty (from tblCounties)
MWA combo box = cboWMA (from tblLookUpData)

Query = qryAdHoc (source = tblWARNData)
CoCity column criteria = [Forms]![frmQryAdHoc]![cboCity]
CoCounty column criteria = [Forms]![frmQryAdHoc]![cboCounty]
CoMWA column criteria = [Forms]![frmQryAdHoc]![cboMWA]

The query works fine if I make selections in all 3 combo boxes. However, if I make a selection in only cboCity, the query returns no results because it doesn't find a matching record where the city = cboCity and the County and MWA fields are blank.

Here's how I want it to work:

If cboCity = Detroit and cboCounty = Wayne, then
Results = 40 records (where city = Detroit AND county = Wayne)

But if cboCounty = Wayne and cboCity & cboMWA are blank, then
Results = 120 records (where county = Wayne; cities & MWAs = various)

IOW, I want the query to filter only on the cbo's where a selection has been made by the user and ignore any empty cbo's. What statement do I need to enter in each cbo-related criteria field so the query filters only on the combo boxes where a selection has been made by the user?

Any help you can provide will be greatly appreciated. Thank you.

View Replies ! View Related

Clearing Text Boxes/combo Boxes?
Another quick request:

What would the code be for a button which clears the contents of a text box or a combo box on a form?

Many thanks,

Paul.

View Replies ! View Related

Combo Boxes / Text Boxes
Hi guys, I have a related databes with about 20 tables.

My main table stores the data linking with most of the other tables. This main table stores football match records with player line ups. Initially I had a problem linking the 11 player fields in the main table to the player table, Icould only do it with 1, so someone at work suggested to link it using the lookup function. This worked brilliantly. However, now I am designing a GUI with forms but the forms have combo boxes where the lookup function was used and when i change these to text boxes, the players names are replaced with their ID numbers.

Is there any way of creating the form without the unsightly combo boxes, as they won't be needed, the GUI will be read-only. I look forward to your help!!

View Replies ! View Related

Combo Boxes And Text Boxes
I have a table that has client names and addresses. I have designed a form to be able to invoice these clients and everything is fine however what I'd like to do is have the address of the client appear automatically.

I have set the client names in a combo box and would like their address to appear in either a text box or sunken label automatically from the table. Is this possible and how do I do it.

Please keep in mind that I've done basic programming so please be kind to this newbie :D

View Replies ! View Related

List Boxes And Combo Boxes
Hi everyone,
I have been making progress with customizing a
MS Access program, but one major problem is that
I have been trying to make a List Box or Combo Box
that I can use to enter data in the TABLE, but I
find that I get a pull-down list that has the list of
values from only the parameter that is primary key.
More specifically, the program is set up as follows:
I have two tables in this program: One that is called
"invoices" and one that is called "items". In both of
these tables, there is one common parameter, which
is "Item Number". Item number is the primary key,
and I used the "relationships" function to tie this
parameter to itself between the two tables.
I was successful in setting up a list-box for the
"Item Number", but when I try to set up a list-box
for another parameter that is supposed to display
a person's initials, the pull-down list displays the list
of item numbers instead of the list of people's initials.
In fact, I don't know if there is an extra step I need to
take so that the database stores a list of people's initials.
Instead, I just fill in the initials in the field for each
record for which the "Item Number" is the primary key.
How can I get the list-box to pull down a selection of
different people's initials, or in other words how can I get
all the people's initials to be stored so that the list of
initials can be looked up. Just so you know, I have tried
different choices of entries in the "Lookup" tab in the
Design mode of Tables, including Display Control,
Row Source Type, Row Source, and Bound Column,
but the outcome is that the only parameter that I
get get in the pull-down list is the primary key, which
is Item Number.
I appreciate any help you can offer in explaining how
to correct this.
On a separate note, one of the parameters is "Date", and
on the reports, I'm trying to figure out how to filter a
specific date range so that I can limit each report to a
specific month. Please advise me on this procedure as well.
Thanks.

View Replies ! View Related

Query With Multiple Fields To Filter
I am terrible with databases, but have designed a database that uses two text field types and 48 Yes/No check boxes. The two text fields describe documents (name and location), and all the check boxes indicate what types of documents they are.

What I need to do is to be able to run queries or reports where I can select CERTAIN of the Yes/No field types. For example, some of those check box fields are named 1A, 1B, 1C, 2A, 2B, 3A, 3B, 3C, 3D, 3E, 3F, 3G, (up through 8E) etc.

How can I get the query or report to bring up all of the records that have a check mark in either 1A, 1B, "or" 1C plus list the Field 1 and Field 2 that are text fields? I don't want it to list every record in the database, which is what is happening, because I want to then be able to sort the results.

I know it USED to be in my head how to do this, but I just can't seem to get it to work!

Thanks,

Toni

View Replies ! View Related

Unmatched Query - Using Multiple Fields
Hello,

My task is to compare our master membership table with a second table, removing matching names in the second table from the master table. (The second table are those individuals who chose not to receive a paper newsletter, so we want to save money and trees :) and not send them one.)

So to remove those names I tried the find unmatched query wizard but that only allows me to chose on one field it seems. There is no single unique identified field that would do this for both tables.

I thought if I built an unmatched query using the fields 'Last_Name' and 'Address1' and 'PostalCode' that should be sufficient for the task.

Here are portions the individual SQL statements that I need to get into one query.

FROM tblAllMembers LEFT JOIN qrySeekbyGroup ON tblAllMembers.Last_Name = qrySeekbyGroup.[Last Name]
WHERE (((qrySeekbyGroup.[Last Name]) Is Null));

FROM tblAllMembers LEFT JOIN qrySeekbyGroup ON [tblAllMembers].[Address1] = [qrySeekbyGroup].[Mailing Address Line 1]
WHERE ([qrySeekbyGroup].[Mailing Address Line 1] Is Null);

FROM tblAllMembers LEFT JOIN qrySeekbyGroup ON [tblAllMembers].[Postal_Code] = [qrySeekbyGroup].[Postal Code]
WHERE ([qrySeekbyGroup].[Postal Code] Is Null);

How is the SQL written so the find unmatched query will use all three of these sections in on query.

Thanks much for your assistance.

SAK

View Replies ! View Related

Query Help, Calculating Multiple Fields.
I've imported an Excel file into a table and now I've created a Query from it.
I now need to Add Fields (names are not in the table) and calculate totals for these renamed fields some of the answers are going to be the result of two or three fields.
Any help would be greatly appreciated.
thanks

View Replies ! View Related

Filter Query By Multiple Fields, One Value
Quick questions and I'm sure this is easy enough to warrant a quick response:

I have a query.
I have 12 different fields that may or may not have the value "Sabbatical".
I want to view only the records that have the value "Sabbatical" in AT LEAST one of those 12 fields.

Thanks in advance.

View Replies ! View Related

Multiple Query Fields Of A Form
Hello everyone!

I am new to MS Access. I am creating a small database for motor vehicle clearance. I have a SEARCH form where i want to put the fields for searching query table:

VEHICLE MAKE:

REF No.:

BEGIN DATE:
END DATE:

I need it in such a way that even when only one of the above fields is supplied with data it should still return correct data from the query table.

For dates (BEGIN DATE, END DATE) i tried: Between [forms]![search]![begin date] and [forms]![search]![end date]

This worked well when placed alone of the SEARCH form. But when i added more fields (VEHICLE MAKE, REF No.) the query returned nothing but a blank query table.

How can i possibly resolve this.

I will appreciated.

View Replies ! View Related

Between Date Query/multiple Fields
Hi! My question is similar to Hondasteed earlier today but a little different. I have a database that has 6 date fields. I would like to develop a query where the user enters a start and end date and the query checks all six of those fields for the appropriate dates and shows only those records who have dates in that range. The dates would change each month as we ran the query again. I would appreciate any help as this has really had me stuck and I'm guessing it's a simple solution. Thanks!

View Replies ! View Related

Filter - Subform _ Query , Multiple Combo Box
Hello All,

Thank you for taking time to view my access problem, I hope somebody can help / give advice on my problem.

I have a Form, within that form I have a subform. On the main Form I have several Combo Boxes.

ComboBoxes include; Year. Movie. Country.

What I want to do is when the user selects from the various values within each Combo Box to display the data in the subform accordingly.

I was able to use the Wizard to display the Movie Information correctly. The code is below for that. How can I go by doing this.

' Code to change the movie information, when they select the combo box
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[MovieID] = " & Str(Nz(Me![cmbMovieList], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

I am very sorry if my question seems broad, ill try to be more specific upon request. THANK YOU for your time and your help.

View Replies ! View Related