Combo's And Queries

Feb 18, 2005

Quick question for you non-newbs out there...

I have two combo's on the same form, cboFName, cboLName. Trying to make some kind of a cascading combo thing, where when a cboLName is selected, my cboFName will be filled with All the firstname's that share that common last name. All of this data is on the same table, and here's my SQL for the queries at the moment:

SELECT DISTINCT tblContacts.LastName
FROM tblContacts
ORDER BY tblContacts.LastName;

SELECT tblContacts.LastName, tblContacts.FirstName
FROM tblContacts
WHERE (((tblContacts.LastName)=Forms!test!cboLName))
ORDER BY tblContacts.FirstName;

For some reason, the first query for my cboLName (although it works as a stand alone query) gives me a combo box full of blank names. Besides that, can anyone see anything wrong with my code?

Thanks,
Paul

View Replies


ADVERTISEMENT

Combo Box Always Re-Queries

Jul 14, 2005

So, I am a total newbie with a question that probably has an easy solution. I have a combo box on a form that is populated from a query. The issue is everytime I click on the combo box, it runs the query to populate it. This wouldn't be so bad if it wasn't querying like 500 employee numbers.
Please help.
Thanks!

View 6 Replies View Related

Combo Box For Queries

Jan 18, 2006

Hi,

I've found some similar issues by searching around here but none have been quite the same as my issue.

I have a form that display PC details. It's not set up in any special way and allows additions/edit etc. It refers to one table with all the PC details in it (tblDesktops). We're categorising the PC's by room, so I created a table (tblRooms) and created a field in tblDesktops that looks up the RoomID field in tblRooms.

I've created a combo box on the form that lists the rooms in tblRooms. I want to be able to select a room from the combo box and have it display all the PC's in that room on the current form.

What I've tried;

I created a query called qryDesktops that queries all the desktops. I set the query to look at the combo box so it know what room to look for using;

[Forms]![frmDesktops]![cmbRooms]

I went back to the form and changed its record source to qryDesktops.

From here I'm a little stuck. I run the form, select a room from combo box and obviously nothing happens. I basically don't know how to get combo box to run the query, then refresh the form to show the PC's in the room selected from the combo box.

Note that I also categorise by two other "things" excluding Rooms. There's also tblCategory and tblDepartment which I'd also like combo boxes for search purposes on the form. I'm guessing I'd need three queries for this...qryDesktopsByRoom, qryDesktopsByCategory and qryDesktopsByDepartment. In this case, I'm guessing that clicking on the related combo box would also need to change the forms record source to the needed query? That's a bit further down the line anyway...I'd be happy if it just worked with the rooms combo box at the moment!

Any help much appreciated.

Steve Swallow

View 2 Replies View Related

Queries :: Sql Table To Combo Box

Apr 14, 2015

I Would Like to Look up SQL server 2005 table as Tbl1 to my access combo box as combo 1...Total 2 columns in the SQL table as Item & Qty.

View 5 Replies View Related

Queries :: DLookup With Combo Value?

Feb 21, 2015

i have an access table.i m using Dlookup function for select data in dlookup , i want to sel field as combo box value, and i want when i change combobox value by select from list ,dlookup result will be change .

how can i do it.

View 3 Replies View Related

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 2 Replies View Related

Queries :: Run A Query From 2nd Combo Box

Nov 6, 2013

I have two combo boxes in a form. The 2nd combo box is influenced by the selection from the 1st combo box. The record source of the 1st combo box is a table and filters a query OK according to the parameter query I have written.

The 2nd combo box correctly lists data influenced by the selection in the 1st combo box via a record source from the same query, but I am struggling to execute/run the query to filter according to the 2nd combo box. I have tried a second parameter criteria in the same query but when I do that it blanks the selection of 2nd combo box.

View 2 Replies View Related

Populating Combo Box With Certain Select Queries

Jan 7, 2008

I have a form that has a combo box that contains every query in my database. This worked fine until i started having to create cascading queries to return the data I want. How can i populate my combo box with only certain queries in my database.

This is the code I was using to put all queries into my combobox.

FROM MSysObjects
WHERE MSysObjects.Name NOT LIKE "~*" AND
MSysObjects.Type = 5
ORDER BY MSysObjects.Name;

Upon clicking a button or clickin the query in the list, I want the query to run......So the main question is how to I include certain queries in my combo box but not all of them? I do not know SQL or any programming, so as specific as possible will help immensely. Thank you everybody.

View 1 Replies View Related

Refreshing Cascading Combo Box Queries

Apr 25, 2008

hi,
I have a db which uses the data taken from cboInstructors which is used in a query and a produces a list in cboExpertise. This procedure is repeated from cboInstructors for cboAssessors and then cboIVName.

This subform then shows all the modules each student have taken, who taught it, who assessed and who IV'd it. The problem is when one selection is made all the subsequent choices are the same as the cbo above it, i.e. the queries do not run again when a diferent selection is made in cboInstuctors.

I hope this clear.

Help please.

Iain :eek::confused:

View 7 Replies View Related

Forms, Combo Boxes And Queries

Mar 2, 2005

howdy all, ive never touched Access until 2 days ago so my experience is
sorely lacking but here is my question:

i want to create a form with a combo box from which a selection is
made (data in the combo box is simply a field list from the same table the query is searching, but
my stumbling block is that i want to include the query in the
same form as the combo box and have it dynamically updates based on the
selection in the combo box.

however for the life of me i cant get the query to update based on the
input (using [Forms]![Form]![Combo1] as the criteria in the query) i have set this criteria in the
CustomerID field of the query (which is also the primary key of the table)

View 1 Replies View Related

Combo Boxes And Queries Again - Two On A Form

Mar 4, 2005

I have re-written this question as I think I managed to cause confusion!

(I really do need help!) My problem involves three tables. The first table is NAMES with an auto-number key field called NamesID.

The second table is a list of Dance Classes, called CLASSES again with an auto-number key field called ClassesID.

The third table is a list of MEMBERS in each dance class.

Obviously, the MEMBERS can belong to many CLASSES, and each of the CLASSES can consist of many MEMBERS.



My aim is to add a new record in MEMBERS by using a ComboBox containing records from the NAMES table.

On the form, I have a combo-box which shows me the list of CLASSES

(ComboClasses). I click on the class.

I then click on the NAMES combo-box, click on a name and want it to appear in the table of MEMBERS for the class shown in the CLASSES combo-box.

The problem

I am selecting the name using “ Select * From NAMES where Names.NamesID=[Forms]![FormName]![ComboNames] (supplied by supersubra) but how do I get the ClassesID into MEMBERS record.

At the moment, if I add a name that member appears in every class, or I have to manually insert the ClassID.



Philip

View 5 Replies View Related

Queries :: Filtered Lookup In Combo Box?

Jan 28, 2014

Where MS Excel is concerned there is very little I can't do and I am now transferring that skill to MS Access and I'm stumbling on this one.

In Excel I would use the INDIRECT formula within the validation and named range to do this but in Access I'm struggling to get this to work.

I have in table TBL_NL_Structure a list of CLIENT_ID with NL_ACCOUNTS against them i.e.

CLIENT_ID....ACCOUNTS
900001.........4000
900001.........4001
900002.........4003

What I want to be able to do is in a sub form and a combo box named ACCOUNT that looks at the active CLIENT_ID and pulls into to combo box JUST the ACCOUNTS set to that CLIENT_ID example if the CLIENT_ID is 900001 is just brings in ACCOUNTS 4000 & 4001.

I can get the whole list to come in to the combo box but not a filtered list.

View 3 Replies View Related

Queries :: Combo Box Not Getting Recognized As Reference

Jul 18, 2013

I have a query that I referenced it to a combo box in a form. In my form I created a combo box which has a list of VendorName and I want my query to opens a VendorName report based on whatever chooses in the combo box so that I use the combo box name in the criteria of the VendorName in my query. Somehow I get an error which says "The Microsoft Office Access database does not organize "[Forms]![Form1]![Vendor_Name]" as a valid field name or expression".

View 10 Replies View Related

Queries :: Combo Box Result And IIF Statement

Mar 22, 2013

I have a combo box as a value list and all the values apart from one are valid data entries in the table but I want to combine two of the values and then get the query to run if the combined values are selected. The value list is this:

(All);Brand1;Brand2;Brand1 & Brand2;Brand3;Brand4

The criteria in my query is this:

IIf([Forms]![MyForm]![Brand]='(All)',[MyTable]![Brand],IIf([Forms]![MyForm]![Brand]='Brand1 & Brand2',"Brand1" Or "Brand2",[Forms]![FrmCustomListATM]![cbBrand]))

Its the OR part that doesnt work.

View 9 Replies View Related

Queries :: Filter From Combo Box Also Has IIF Statement?

Mar 15, 2013

Here is what I have in my Query
IIf([Forms]![Report Form]![Combo56]=0,1 or 3,[Forms]![Report Form]![Combo56])

in the combo box I have it setup like this
Yes = 1
No = 3
All = 0

it is pulling for a SQL database and what I am looking to do is if they want them all select 1 and 3 but when I save the Query is changes it to this and doesn't work.

IIf([Forms]![Report Form]![Combo56]=0,([BigAssQuery MTD].[Calc_ID])=1 Or ([BigAssQuery MTD].[Calc_ID])=3,[Forms]![Report Form]![Combo56])

View 2 Replies View Related

Queries :: Combo Box Query Parameter

Aug 27, 2013

I have a query where I want to use a combo box in the parameter. However, its not working.

SELECT Call_Details.Emp_ID, Call_Details.[Call Status]
FROM Call_Details
WHERE (((Call_Details.Emp_ID)=[Forms]![TTY_ID]![TTY_ID]));

I get this in the Parameter Value Dialoge

Forms!TTY_ID!TTY_ID

Should I be entering this in the criteria field at all? Do I have to build an event to make this work?

View 3 Replies View Related

Queries :: Distinct Values In Combo Box

Jun 13, 2014

I have a combo box that I want to display titles of discussion that fall within a date range. I set up the query as such;

SELECT DISTINCT Discussions.Discussion_Title, Discussions.Discussion_Date
FROM Discussions
WHERE (((Discussions.Discussion_Date) Between ([forms]![DiscussionsForm2]![txtStartDate]) And ([forms]![DiscussionsForm2]![txtEndDate])))
ORDER BY Discussions.Discussion_Title;

It works but not quite the way I want it too. I would like the combobox to display only unique titles but right now it shows the same title if it was used on another date. I guess its showing distinct titles and dates, while I want it to display just distinct titles.

View 2 Replies View Related

Queries :: How To Use Combo Box For Query Parameter

Oct 30, 2013

I need to apply a parameter to a query based on from combo box. I have added the ALL option to the combobox which that the user would select if they want to run the query to show all records;

E.g. Selecting ALL Countries would be the same as having no parameter or a wild card *

ALL Countries
UK
United States
Asia
Europe

I tried an iif statement like:

IIf([forms]![frmSelectReports].[location]="AllCountries",like"*",[forms]![frmSelectReports].[location])

but it didn't work...

View 2 Replies View Related

Queries :: Third Combo Box On A Form To Be Optional

Feb 24, 2015

How to get a third combo box on a form to be optional.

I have a form with two combo boxes which filter out the results on a query.

However I want the third combo box to be optional i.e. if it is blank then just the results of the first two combo boxes are shown. If the user selects all combo boxes then obviously the results are based on all combo box selections.

View 7 Replies View Related

Queries :: Filter Query From Combo Box

Nov 18, 2013

On my database, I have 2 forms. One is to enter data into a Master table, and the other is to specify search terms for a query, and I'm having a problem with the search form.

I've got 2 combo boxes, a tick box and some text boxes which I use to specify criteria for the query. All of the text boxes and the tick box work fine, however the combo boxes are giving me trouble.

I'm using the critera Like "*" & Form![SearchForm]![Combo/Text/Whatever1] & "*" for the text boxes and tick box and this worked fine. However, no matter what way I try to use this, it doesn't work for the combo box. I've tried manipulating the Like function as much as possible, with and without the wildcards, the & signs, everything.

What happens is that either they do not filter out results at all and don't affect the query, or they bring up a blank query if I leave the combo box blank.

View 4 Replies View Related

Queries :: Complex Query From Combo Box Value

Nov 19, 2013

I have a query that is working, but would like to add some extra complexity to it.

The query currently uses a search form for input with multiple fields, and displays all the results perfectly. I have a tick box which I can then filter the results so it only displays results where there is an entry in "PhoneMobile1". This is working, however I want to expand this filter so it can use a combo box if possible.

Currently I have the following code from a tick box:

IIf([forms]![ISISNavigationMain]![navigationSubform].[Form]![txtHasMobile]=-1 And [Candidates].[PhoneMobile1] Is Not Null,True,False)=[forms]![ISISNavigationMain]![navigationSubform].[Form]![txtHasMobile]

The issue is that there are three fields for Phone Numbers (PhoneMobile1, PhoneMobile2 and PhoneMobile3), and I'd like this query to be able to filter on these columns also.

e.g.
Combo box values:
Has Mobile, No Mobile, All

the "Has mobile" choice should display all results if there is an entry in any one or more of the PhoneMobile fields, "No Mobile" selected should display all results where there is no entry in any of the three Mobile fields, and All should display all results.

View 3 Replies View Related

Problem With Criteria In Queries Using Combo Boxes

Jul 20, 2007

Let me see if I can explain this.

I have two tables: TblValues and TblLOC.

TblValues is a table of stactic values where I have listed about nine field names with values under each that usually will not change. This way if there is a change to one of the fields later, I can just add it to the table.

Then there is tblLOC. Here I have similar field names that matches each field in tblValues where I can populate them in my form. I have several fields defined as combo boxes. In the properties for each field defined as a combo box, for row source type has Table/Query selected and for the row source I have the following code in the table, which is an example for the field named Profit Code in TblLOC.

SELECT tblValues.ID, tblValues.Pro1Code
FROM tblValues
WHERE (((tblValues.Pro1Code) Is Not Null));

Then I have my form where I have referenced my fields from the tblLOC. In the properties of each combo box, under the tab labeled DATA, the control source has the name of the the combo box field that is in tblLOC, in this case Profit Code.

Everything works fine. The user selects a value from the form and tblLOC is updated. I can create queries without criteria with no problem. However, in a query if I try to add any criteria, even as simple as using "like" to identify a value in a field of the table that has been populated by the combo box, I will get no results. However, if I use the same functionality in a column where a combo box is not used to populate the field, I get the results that I expect.

Here is an example of the SQL in the query that does not work for me that is applied against a field that is a combo box.

SELECT tblLettersOfCredit.[Profit Code]
FROM tblLettersOfCredit
WHERE (((tblLettersOfCredit.[Profit Code]) Like "Misc"));


If I use the same functionality with a field that is not populated by one of the combo boxes then everything works fine.

I am hoping that there's a simple solution for this, maybe syntax due to the use of a combo box or maybe a property that I need to set... So far I have been unsuccessful in finding the answer. If anyone has any ideas on this one, I would certainly appreciate it. Thanks :)

View 6 Replies View Related

Queries :: Combo Box Not Getting Recognized As Reference In A Form

Apr 4, 2014

Access 2010. I have a query that I referenced it to a combo box in a form.

If I rename the combo box, save it, and then rename it back to the same name it works fine for a while and then stops working again.

View 5 Replies View Related

Queries :: Cascading Combo Box - Filter By Field?

Mar 16, 2013

I want to create a cascading combo box - so far so simple, but in all the tutorials I can find the options in the second combo box are exclusive to the first e.g. combo box 1 selects "state", combo box 2 then displays "city". Each city can only be in one state.

In my scenario each city is in several different states - and so far I'm failing to come up with any sort of strategy as to how to deal with this. I tried giving each state a "yes/no" formatted field in the "city" table, figuring if I could use the first combo box to point access to the right field in the "city" table I could filter those records by "WHERE [fieldnominatedbycombobox1] =True" - but I don't know how to make a one combo box determine which field a subsequent combo box filters by.

View 13 Replies View Related

Queries :: Combo Box Won't Display Existing Values

Nov 28, 2013

I had a text box in a contacts DB form for the State field (Named txtState). It worked fine. I decided to replace it with a combo box. Now not only will it not display the existing values, but to rub salt in the wound it won't allow me to select from the drop down list. The error message that says that the field is too small for the value. I tried changing each of the yes/no properties one at a time but none of them change the outcome.

I have attached a couple of screenshots:

ErrorMessage.jpg
Properties.jpg

View 3 Replies View Related

Queries :: Using Multi List Box And A Combo Box In Query

Jan 23, 2014

I currently have a database of customer information and form set up with a MultiList box based on the regions those companies are based. You select the region(s) you require from the list box, click a button and it runs a query.

However I now want to be able to filter the results further using a combo box with business sectors in also.

The code used for the multilist box is as follows (I don't totally understand all the code if I'm honest, but it works )

Private Sub Command2_Click()
Const cstrQuery As String = "CAMPQRY"
Dim strNames As String
Dim strSelect As String
Dim StrWhere As String
Dim varItm As Variant

[Code] ....

View 2 Replies View Related







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