Queries :: Add ALL Selection To Combo Box

Aug 19, 2013

I have a combo box that filters records on a subform by user name. I'd like to add an "all" option to the combo box list so that all records regardless of user name may be displayed at once. I have attempted a few approaches to this using a "union query" but am failing to achieve the correct syntax. The query I'm using is this:

Code:

select top 1 0,"**ALL**" from tblauditstaff
union All
SELECT tblAuditStaff.AuditStaffID, [tblauditstaff].[auditstafflastname] & ", " & [tblauditstaff].[auditstafffirstname] AS [Auditor Name], tblAuditStaff.AuditStafffirstName, tblAuditStaff.AuditStaffLastName, tblAuditStaff.AuditStaffResponsible, tblDiscrepancy.DiscrepancyAssignedTo, tblDiscrepancy.DiscrepancyActualCompDate,

[Code] ....

This is the error message I'm getting:

The number of columns in the two selected tables or queries of a union query do not match.

View Replies


ADVERTISEMENT

Queries :: Populating One Combo Box Based On Selection In Another

Jul 20, 2014

I can't quite get my code right for populating one combo box based on the selection in another. I didn't create this Access database but I've been asked to add in a second combo box that is based on the selection of the first combo box.

My first combo box is for Rohe (the location of the Maori tribe) and the second is Iwi (the tribe name). So my first combo box (Rohe) has Dvic_ROHE written in the control source and its row source code is:

SELECT tblLookUpValues.DatabaseFieldValue
FROM tblLookUpValues INNER JOIN tblFormQuestionLookUp ON tblLookUpValues.LookUpID = tblFormQuestionLookUp.LookUpID
WHERE (((tblFormQuestionLookUp.FormID)=2) AND ((tblFormQuestionLookUp.QuestionNumber)=75))
ORDER BY tblLookUpValues.DisplayOrder;

The combo box for Rohe (tribe location) is populated by a table named tblLookUpValues. The second combo box for Iwi (tribe) has Dvic_Iwi in the control source. I created a table named tbl_Rohe_Iwi_lookup that contains ROHE (a list of tribe locations) and IWI (the tribes). There are 12 tribal locations and another 10-14 tribes in each location.
The form that the Rohe and Iwi combo boxes are located on is called frmDeceased. This is the code that I attempted to write so that the second combo box (Iwi) is populated with only the tribes that are in the Rohe (location) that was selected from the Rohe combo box. But when I run the query it comes back with no results.

SELECT distinct tbl_Rohe_Iwi_lookup.Rohe, tbl_Rohe_Iwi_lookup.Iwi
FROM tbl_Rohe_Iwi_lookup
WHERE (((tbl_Rohe_Iwi_lookup.Rohe)=[forms]![frmDeceased]![Dvic_Rohe])) UNION select distinct null, null
FROM tbl_Rohe_Iwi_lookup
ORDER BY tbl_Rohe_Iwi_lookup.Iwi;

View 5 Replies View Related

Queries :: Sum Table Values Based On Combo Box Selection?

May 20, 2014

I am trying to write a query that gets all the telephone hours from a worker done under supervision in a town (qry_svhoursbytown). I have a table that has the workers details, including town (Crisis_support_workers), then another table that has what hours each worker has done (Supervision_Hours). The town is selected from a combo box on the form (frmSearch), under the heading �total hours by location�.

I try and select Bathurst then press recalculate results and in the text box next to supervision hours, it says 66, but if you look in the Supervision_Hours table, there is only 11 hours for the workers that are in Bathurst.

In the query, I have the sum total of the hours field in the Supervision_Hours table, the Town field from the Crisis_support_workers table with the total selected as Where and in that criteria I have [forms]![frmSearch]![ComboTown], then I just have the town field displayed.

What do I need to change to get it to sum correctly?

I have attached the database below so people can take a look.

View 1 Replies View Related

Queries :: Run Query Based On Combo Box Selection On Button Click

Jun 25, 2013

I am currently working on an existing database (not created by me) which contains several queries. Each query relates to a particular product.

I am wanting to create a form which has a combo box so that a user can choose a particular product from the combox options and then click on the command button to run the correct query.

How to do this. I have created the form and the combox (together with the list of products). I just need to know what Event Procedure code I need to enter to programme it to look for the correct query and then run it.

For example.

I have the following queries
product1query
product2query
product3query

I have the following options in the combo box
product1
product2
product3

How do I get the command button to look at the product1 option in the combo box and then find and run the product1query.

View 9 Replies View Related

Queries :: How To Update Combo Box Values Based On User Selection

Sep 25, 2014

I have a combo box which gets its values from sql server using a query which is called "get_query_reason", which works fine. Now I want to update combo box values based on a user selection, st string. Have written the code, but does not work:

Dim qDef As QueryDef
Dim Query As String
Dim st As String
Dim rs As Recordset
st = "SOV"
Set qDef = CurrentDb.QueryDefs("get_query_reason")

[Code]....

View 3 Replies View Related

Change Combo Contents Based On Previous Combo Selection

Oct 20, 2005

Hi everyone,

I have two combo boxes on the same form bound to a table. I want the contents of the next combo box to change based on the previous combo selection e.g

cboContinent cboCountry
Africa Zambia
Africa Congo
Africa South africa
Europe England
Europe Holland

If I choose Africa in cboContinent, I want to see only Zambia, South Africa and Congo under cboCountry and if I choose Europe I want to see only England and Holland

Thanks
Humphrey

View 1 Replies View Related

Populate Combo Box Based On Selection In Another Combo Box (Access 2010)

Jul 11, 2012

I'm have quite a difficult time getting a form in Access 2010 to perform the way I would like it to. I have multiple tables that I've created, and a query that contains the data from the various tables. I then ran the form wizard to select the data from the query that I wanted to populate in my form and I've added 2 combo boxes.

What I want to do:
1. I want users to be able to select a category in combo box #1 (example: "Bag")
2. I want users to be able to select a detail in combo box #2 based on the category they selected in combo box #1 (example: Combo box #1, "bag" would populate the following selections for combo box #2: "sandwich" and "tool")
3. I want users to then receive a list of suppliers that provide the product they have selected, either "Bag: Sandwich" or "Bag: Tool"

I have combo box #1 populating a list of categories already. However, I am not able to get combo box #2 to provide choices based on the selection chosen for combo box #1.

View 1 Replies View Related

Combo Box Selection Triggering 2nd Combo Box Tables

Oct 26, 2004

I'm trying to get one combo box control what tables are available from another combo box. Example, combo box (a) includes numbers 1-5. Selecting (1) makes the data in combo box (b) specific to that selection. Selecting (2) makes the data in combo box (b) something different (pulls data from a different table).

Can anyone help me with this? Thanks!

View 1 Replies View Related

Forms :: Combo Box Value Based On Selection In Other Combo Box?

Nov 29, 2014

I'm trying to build a database for daily work. My work in daily basis I have to fill all condition for several items.

We have two areas, with two locations under each area, and three systems under each location and each system contain more than 500 items.

I created all tables and fill by all information:

1-Table 1: Area 1, Area 2, Area 3, Area 4, Area 5, Area 6.
2-Table 2: Location 1, Location 2.
3-Table 3: System 1, System 2, System 3.
4-Table 4: all items under System 1-1-1
5-Table 4: all items under System 1-1-2

[Code] ....

Last table will be LogBook which will be as follow:

date l time l area l location l system l item l Conition1 l Conition2 l Conition3

My question regarding to the form of the above table:

How can I make a combo box for area field and when I select for example area1 will appear only the locations which under area1 in location field, and when I select location1 for example will appear only the systems under location1 in system field, and when I select system1 one for example will appear only items under system 1.Combo box list will be based on the selection in previous combo box and so on.

View 3 Replies View Related

Combo Box Values Based On Another Combo Box Selection

Feb 6, 2013

I have a form, with a subform and another subform

Once a supplier name is selected from the combo box i only want it displayed that suppliers products in the subform, subform. (products combo box) I can get a basic query to show these results but can get the combo box to do the same.

The design screen is also attached below ....

View 14 Replies View Related

Combo Box Populated By Selection In Other Combo Box

Jul 13, 2013

I have a form with a number of combo boxes to select criteria in order to narrow down a list. A selection in any combo box should narrow your options in the other combo boxes.

For example:

Combo 1 is a list of all the states
Combo 2 is a list of all the cities

Selecting NY in combo 1 will change the list in combo 2 so that it only shows cities in NY

Or, selecting Rochester in combo 2 first will edit the list for combo 1 to show only states that have a city named Rochester

The point in me doing this is to be able to narrow down a list of over 230000 different but similar items by varying criteria in order to track inventory and value.

Scrolling through the whole list, typing criteria in manually each time and building new queries and reports every time I need to find something specific are not options.

I just need the combo boxes in a form to set the criteria to filter out the list in real time. Each selection made needs to narrow your options for the next selection.

View 3 Replies View Related

Combo Box Selection

Jan 10, 2006

I have a form with 4 text boxes that are populated from a query with an unbound cbo box to make the choice from. There are two fields that show on the dropdown, "Customer" and "New Product Name". I created it all with wizards. Most of the Customers have only one product name but some have several. If I choose a customer that has several products, it always selects the customer with the first product it finds. How can I get it to select the Customer by product? I have looked and seen what looked like what I needed on this forum, but I couldn't make it work. I am not good a VB. Thanks in advance for any help that you can give.

View 10 Replies View Related

Combo Box Selection

Dec 13, 2006

I have had some help on this previously but its not working the way i wanted.

I have a form called plan distribution which holds a record of all people who need a copy of a certain plan. There are several hundred contacts held in the database and i want to make this as easy as possible to choose the right ones.

To do this i want to have 3 combo boxes. One each for company, department and contact.

On the plan distribution form i want the user to select which company they want. The department combo box will then automatically show all departments within that company.

Once the user selects the department, the final combo box will hold all contacts within that department.

I have tried several ways of doing this but cant find one that works!

HELP!

View 1 Replies View Related

Combo Box Selection

Jan 22, 2005

Text is brought into a form field as a result of a combo box selection, is it possible to use the same combo box to select another value that could be appended to the same field as the first selection?

View 3 Replies View Related

Combo Box Selection

Apr 4, 2005

As you have read in my other posts I am creating a Rental database. Now I need to show ages in this database and the certificates they have on films. I have 2 problems

1) I have two fields one called date of birth and one called Age. I am wondering if I enter there date of birth, can I make the age appear automatically.

2) Also when im adding more films to my database I want one combo box saying with the options DVD/VHS/GAME and then the other saying the age certificates because Game and DVD certificates are different so I was wondering if the first combo box makes the other combo boxs options

View 5 Replies View Related

Combo Restricted Selection

Mar 8, 2005

Hello,

I have Customers recorded and a number of addresses associated with each customer.

They are in two tables linked by CustomerID and are shown in forms as frmCustomers & subfrmCustomerAddresses.

From frmCustomers, I open another form which records the orders maid by that customer. What I would like on this form is to select the customer address to be used for the order.

I can build a combo, but this shows all addresses on the database. What I would like for the combo to show only the addresses for that customer only.

I do not want two combos, one which selects the customer and then another which shows the addresses. This is not very user friendly to ask for the customer again. I would like one combo to show addresses for the customer on the form.

I would be grateful for any guidance on how I can achieve this.

Thank you.

View 1 Replies View Related

Combo Box (Filter By Selection)

Jul 24, 2005

hi all,
another quick question (last one for awhile I promise) but is there a way to add an option to a combo box (Filter by Selection) and Remove/Filter sort without right clicking on it?
cheers,
Andrew

View 1 Replies View Related

Combo Box Record Selection

Aug 1, 2005

I have a Main Form
Main Form [Event],[Category],[Judge Name]
The Main Form also has a subforms
Sub1[Event],[Category],[Judge Name],[Score],[Comments]

On the main form I have a combo box that is set to retrieve records that meet the combo selection. and thus the subform is related to display the corresponding records.

In my selection combo box I see all of the various selections as follows
[Judge Name],[Category],[Event]
and it is bound to the judges name

Problem Example:
Pull Down Values
1.[Sherry],[Fitness],[Mrs. Canada]
2.[Sherry],[Swim Suit],[Mrs. Canada]

In the above when i pull down the combo box to select and I select the first example above all is wonderful ; however if i want to select the second row the [Category] and [Event] Do not change and the information i input into the subform ends up being associated to the wrong category.

How do i bind a combo box to more than one [field] on the form

I have over 50 hours into this DB to this point and this is critical.

View 1 Replies View Related

Combo Selection Comes Up WRONG

Aug 30, 2005

I have a form with combo boxes for selecting products to be purchased.

When a product is selected, the price appears in a text box. This is working fine by making the bound column of the combo box the field containing the price.

When I have two different products that have the same price, however, the combo box always displays the name of the first item of that price.

For example, a jar of Lavendar Oil costs $12 and a Small Exercise Ball costs $12. The Combo list is sorted alphabetically. When I select the Small Exercise Ball, I get the $12 in the text box, but Lavendar Oil shows up in the combo.

How do I make the combo show what I'm selecting? I assume it's something with the bound column, but can't find the resolution.

I hope someone is able to help me with this................

View 14 Replies View Related

Combo Selection Does Not Show

Aug 31, 2005

I have a combo box set up to select a product and put the price into a text box. I am getting the cost showing up in the text box Therapy_Cost just fine, but the combo box itself does not display anything after I select something. If I remove the AfterUpdate, I do get the item displaying in the combo box, but, of course, don't get the price in the text box. Here are the details. Can anyone help?

Combo Box
Name:
Therapy_Type
Control Source:
Therapy_Cost
Row Source:
SELECT Purchase_Items.ItemID, Purchase_Items.Item_Name, Purchase_Items.Item_Cost
FROM Purchase_Items
WHERE (((Purchase_Items.Item_Type)="2"));
Number of Columns:
4 (In this order - ID, Type, Name, Cost)
Bound Column:
1
Column Widths:
0";0";1";0"
AfterUpdate:
Private Sub Therapy_Type_AfterUpdate()
Me!Therapy_Cost = Me!Therapy_Type.Column(3)
End Sub

View 4 Replies View Related

Combo Box Will Not Display Selection

Dec 14, 2005

I have a form that calculates freight costs. I'm using a combo box for the user to select the route of the shipment from shipping port to destination port. The combo box has 2 fields. The index which is stored in the database and the description which is displayed to the user. I used the wizard to create the combo box and set the index to be saved in a table. Everything works fine, the freight cost is calculated correctly and the data is stored as designed.

The problem is when if the user goes back later and opens the screen to view the freight information, the combo box doesn't display what was selected previously. Therefore, no freight is calculated and the user has to reselect the combo box again. When I check the table, the index value of what was selected is in the table, but it will not display in the combo box.

. . . . Additional info to this post. . . . The combo box is set to use a data from a standard table. The user can only select what is displayed in the list. However, the index is then stored in another table after the selection is made. I want the combo box to do something like, if a selection was previously made then displayed what was select, if not, then display the data from the standard table.

What did I do wrong during the set up? Thanks for your help solving this problem.

Jeff

View 1 Replies View Related

Combo Box Selection For Query

Feb 13, 2006

I'm new at Access 03. I have a combobox that is sourced by a table, Rowsource property is:

SELECT Classes.TableID, Classes.ClassID FROM Classes;

The name of my combobox is "ClassCombo".

I'm trying to generate a report based on my combobox selection, but I can't reference the value of the selection. The query tied to my report has the following SQL:

SELECT ClassesForInstructors.ClassID, Instructors.InstructorID, Instructors.FirstName, Instructors.LastName
FROM Instructors INNER JOIN ClassesForInstructors ON Instructors.InstructorID = ClassesForInstructors.InstructorID
WHERE (((ClassesForInstructors.ClassID)="[Forms]![School]![ClassCombo]"));

I'm trying to grab the "ClassCombo" value as input to my query, but it's not working. What am I doing wrong?

Thanks!

View 2 Replies View Related

Multiple Selection In A Combo Box

Oct 17, 2006

In form "sendmail" their is a combobox "to" which gets ur email address.
i want we could be able to select multiple entries thruogh this combo box and it should be seprated by a semi colon

View 2 Replies View Related

Multiple Selection In A Combo Box

Oct 17, 2006

In form "sendmail" their is a combobox "to" which gets ur email address.
i want we could be able to select multiple entries thruogh this combo box and it should be seprated by a semi colon

View 2 Replies View Related

Making The Selection In A Combo Box Stay.

Jan 28, 2008

Hi everyone,

I have created a combo box in my form, the options for the combo box are coming from a table. It all works fine expect for some odd reason, when I click on an option in the combo box then come out of access, when I go back into the database the one I had chosen has gone. Do you know how I do so what I put in the combo box will stay?

Thank you in advance for your help.

View 1 Replies View Related

Querying Based On Combo Selection

Jan 27, 2006

Right, I think this is a quick and easy one....just not for me!

I have a combo box with a list of names. I want to use whichever name a user selects as criteria for a query.

What do I need to type in the query criteria box or as SQL?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved