Modules & VBA :: Multiple Selections In List Box - Query To Accept Parameters?

Mar 27, 2014

How to modify my query to accept parameters from a multiple choice list box. This is the SQL code behind my search query:

SELECT AircraftOperators.RegistrationNumber, AircraftOperators.PassengersNumber,
AircraftOperators.ManufactureYear, AircraftOperators.EmailToOperator, AircraftOperators.ExteriorPhoto,
AircraftOperators.InteriorPhoto, tblListOfAircraftOperators.OpratorName, tblAircrafts.AircraftType, tblAirports.AirportName, InfoSource.SourceType, tblCountry.CountryName, tblAircraftCategory.AircraftCategory

[Code] ....

I have got an unbound multiple list box called List44 (Row Source: query based on table tblAircraftCategory, Multi Select - Extended) that needs to be passing parameters to my main query called AircraftSearch2. The multiple choice list box have the following fields:

1. Piston
2. Turbo Prop
3. Entry Level Jet
4. Light Jet
5. Super Light Jet
6. Midsize Jet
7. Super Midsize Jet
8. Heavy Jet
9. Ultra Long Range
10. Helicopter
11. Air Ambulance
12. Cargo
13. Vip Airliner
14. Airliner

The user will use the form for selecting search criteria (the form is called SearchForm2 and has 5 combo boxes, 3 text boxes and one multiple choice list box).

I'm very new to access and need to modify (or coding a separate module) my query to include my multiple choice list box in my query?

View Replies


ADVERTISEMENT

Access Query To Accept Multiple Check Box Parameters

Nov 9, 2007

Hi,

I really hope someone will be able to help me with this one as I am sure im just missing something simple.

I have an unbound form which has 20 yes/no unbound check boxes. The purpose of the form is to allow users to tick the various fields and a subform return the results. The subform, which does requery when a check box is ticked is based off a query. Initially, I wanted all the records to display before any check boxes are ticked so I have used the following criteria:

Like IIf([Forms]![Search]![Field1]=False,"*",[Forms]![Search]![Field1])

Which basically reads if field1 is no then display all records, else display all yes. Now that works fine but what I would like to have working is that if a client ticks field1, field2, and field3 it displays all records that have ‘yes’ in either field. Currently, if more than field is ticked the query treats it like:

Field1 And Field2 And Field3 And etc = true

I want to be able to select several check boxes and have the query return results for each check box that was checked. I would like to avoid doing this by having an append and delete query per checkbox.

Thank you

View 13 Replies View Related

Modules & VBA :: Multiple Selections In List Box

Oct 29, 2013

I have a form called frmNotInvoicedSearch and on that form i have an list box called listCompanyClient populated with our client's names.I then have a command button called cmdOK that brings up a search results form called frmNotInvoicedSearchResults.

Currently i can select one of the records and when i click ok it brings up the search results for that selected record.Is there a way that i can hold in the ctrl and select multiple options and the search results report as such?

View 4 Replies View Related

Running A Query With Multiple Selections From A List Box

Jan 10, 2007

Hello Everyone,
I presently have a form with a list box, I want to be able to select Multiple Items in this list box and then have a query run against a Table based on the items I selected in the List Box. Can you tell me what to do in Query Builder to achieve this?

Thanks

View 1 Replies View Related

Modules & VBA :: Make Multiple Tables Using Parameters From List

Jun 11, 2013

I've got a table of associate directors "t_ADnames" and want to build separate tables for each AD name that pulls a pass through query from our data warehouse. I'm thinking it's got to be done with a macro somehow? So it would run pass_query where AD name = "John" and insert into t_john, then it would check the next name in t_ADnames and run the same query for say "Mark" and insert all his data into t_mark and so on until the list (of about 12 people) has been completed.

View 13 Replies View Related

Making Multiple Selections From A List Box (Please Help)

Nov 4, 2004

I had a bigger question earlier and it was answered but I am still having problems with a small part of it. I am trying to track the details of a meeting. who requested it, for what project, date, duration, who attended. I have everything figured out except accounting for who attended the meetings. attendees also called "analysts" have a manay to many relationship with the meetings. Because they can be a part of many meetings and a meeting can have many analysts. So I created a seperate table called MR-Analyst which was supposed to help me track which meetings each analyst attended. I don't know how the data entery form should look like, right now I have everything on it and linked correctly to track everything excep the analysts. how can I create a list box or something that I can select multiple analysts for each meeting and have that recored linked to the MR-Analyst table? Please Help!!!

View 5 Replies View Related

General :: Multiple Selections In List Box

Dec 4, 2012

Basically I have a list box and I want to be able to pick several items from it (No relation except for the name in the Inital Assigned Field) then hit a button and then it opens another form and shows all the records selected. I want to this to see several records at once but also change the value of all the fields to the same value.

View 1 Replies View Related

Queries :: One Record From Multiple Selections In List Box

Aug 8, 2013

Short Version:

I have a table where one of the fields is a list box, where it is possible to select more than one option. The options correspond to records in another table. I have a query that includes this list box field. The problem is that if someone has made two selections in the list box, the query creates two records, each featuring just one of selections from the list box. Can I get this query to hold the two selections in one record? This might be especially challenging because the query field is pulling attachments.

Long Version:

I have a table [Master List] (primary key is TagNumber), where one of the fields is a list box, [Standard Methods]. You can select multiple choices in this list box, which corresponds to a table also called [Standard Methods]. In the Standard Methods table, there are two fields for naming the method and attaching the PDF which describes the method.

I have a query called [Get Attachments] that is set to bring in different attachment fields from different tables, including [Standard Methods]. The problem is that the query creates a different record for each selection made in the list box. For instance, if you select two standard methods for a certain tag number in the list box, this query will have two records for that tag number, each with one of the method attachments. Is there a way so that the query just creates one record, with both attachments in that record???

It should also be noted that I am using Outer Joins, so that the query includes TagNumber records from the Master List even if there are no attachments for it.

View 1 Replies View Related

Save And Retrieve Multiple List-Box Selections In / From One Field

Feb 21, 2005

Hi,
I’m trying to develop a form for coding data from historiocal documents into a dataset. The form is used to avoid errors when converting ‚questions’ in the codebook to the codes for later statistical analysis.
Some questions allow for multiple anwers – but need to be stored to one field in the data-table, separated by semicolon. Ideally I would have a list-box with the questions and write the codes into one field after selection. Another difficulty is, that later the data need to be read from the table and the selections should be visible in the form (if the table contains „1;6“ items 1 and 6 in the listbox should be highlighted).

I’d be grateful for tips or links to relevant sources.
Thanks!

View 2 Replies View Related

Forms :: Limit Number Of Selections In Multiple Value List?

Apr 8, 2013

Is it possible (without using VB) to limit the number of selections a user can make in a multiple value list?

My list is one a form and when in the form field it brings up a total of 15 names with checkboxes, allowing you to "check" each name you want. However, I want to be able to have as many names as I need in the list, but limit the user to selecting only two of them.

View 3 Replies View Related

Forms :: Query Does Not Show Selections From List Box

Jun 19, 2014

I have a list box (SUB CATEGORY) on my form where I can select multiple items. I have a query (Final) that has this field in it.

Problem - I cannot seem to get the list box selections to appear in the query results. What the "Criteria" should look like or maybe some code that I can try.

View 1 Replies View Related

Queries :: Copying List Box Selections Into Append Query

Dec 15, 2014

I've got a listbox on one of my forms (with multi select enabled). Essentially its a list of products, the idea is for the user to select as many products as he or she likes, then they click a button and the selected products show up in an append query, which will add records to a table.

How to copy the data from a list box to an append query (nothing that each selection should have its own row)...

i also wanna look into "Select All" and "Select None" buttons, is this possible?

View 4 Replies View Related

"Repopulating" Multiple Selections From A List Box

Jul 21, 2006

I have a form with a list box that allows multiple (extended) selections. When processing the selections, I save the data to an intersection table (B) which relates the keys of tables A and C:
A --> B <-- C

Does anyone know how to retrieve the data for the list box when the form is loaded so that I can display the list based on the data that had been previously selected?

If I can't come up with a solution to this problem I think will implement a subform that shows the current selections and refresh the list after processing the listbox selections. Messy, but it should work.

View 1 Replies View Related

Query With Where (Multiple Selections)

Mar 5, 2014

I have a list of codes that I would like to select among 50,000 codes in MS Access Database:

I can easily find one code but I don't know how to select multiple values:

My Query is:

SELECT [Codes for District].DistrictID, Univ2012.Schoolid
FROM Univ2012 INNER JOIN [Codes for District] ON Univ2012.DistrictID = [Codes for District].DistrictID
WHERE IN [((Univ2012.Schoolid)= 415030301, 415030303, 415030306, 415030309)]
GROUP BY [Codes for District].DistrictID, Univ2012.Schoolid
HAVING ((([Codes for District].DistrictID)=15));

When I run this query it gave me Syntax Error in Where clause...

View 3 Replies View Related

Modules & VBA :: Send Email To Outlook Distribution List - To / CC Or BCC Parameters

Jan 4, 2014

Is there a way of doing the above? I have several distribution lists (some >100 recipients) defined in Outlook, and would like to use the Docmd. Send Object command to send messages to one or several of them. I cannot find a way of defining a distribution list as any of the To, Cc or Bcc parameters.

View 2 Replies View Related

List Boxes To Set Query Parameters

Aug 10, 2006

My database contains information about events running in different areas. I want to make the filtering options for this information as flexible as possible and so want a query where most of the fields can have criteria set, but where they can optionally be left blank and so the data will not be filtered on that field. To do this, I am trying to create form which collects together the parameters required by the user which the query can then pick up.

I want to use list boxes which contain all the entries for a particular field, looked up from the original table (e.g. all the different town names from the town field of all the records). This will allow the user to select the one(s) they want to filter for.

However, using list boxes presents me with the following problems, with which I would appreciate help:

1) How do I refer to a list box from a query and get the correct code created? I could do this where I'm just refering to a text box, but then a text box only contains one value. How do I tell Access to look for any of the towns selected in a list box?

2) There will be options on my form to filter the data by any of the fields of which it is made up. Obviously, the user will not want to filter every field and so I need a way to tell Access that if the user has not made a selection for a particular field, then it should not filter that field when the query runs.

If anyone can help me with these two things I would greatly appreciate it. I've so nearly finished developing a new system and this is the last hurdle I need to overcome!

Thanks in advance,

Gary

View 1 Replies View Related

Modules & VBA :: Quotation Marks With DCount And Multiple Parameters?

Apr 14, 2014

I created a query in Query Builder which contains a DCount with multiple parameters and it runs as it should. I am trying to convert it to VBA, but my inability to put in the quotations marks correctly is frustrating me terribly.

Here is the SQL version from Query Builder:

UPDATE [Daily Status Update Table] SET [Daily Status Update Table].NumberOfChases = DCount("[ChaseOtherID5]","[Chases_View_ALL - TX_Mbr 9 Digit]","[ChaseOtherID5] = 'U - Initial Contact' AND [ChaseStatus] = 'A'"), [Daily Status Update Table].ChaseStatus = "A", [Daily Status Update Table].NewStatus = "A", [Daily Status Update Table].ChaseAssignment2 = "Unscheduled"
WHERE ((([Daily Status Update Table].ChaseOtherID5)="U - Initial Contact"));

View 3 Replies View Related

Using A Multi Select List Box To Pass Parameters To A Query

Apr 24, 2015

I have created a search form which I would like to use to run a query (so the data is in a spreadsheet form and I can export it).

In this search form I have a multi select list box (simple) that list the states in the US. I need to be able to pass 1 or more states as a search criteria at the same time. Also I need it to pull ALL states if there is nothing selected. The search form has a bunch of fields on it that won't all be used.

I know once you select more than one thing from a list you can't reference it directly, is there a way to accomplish what I want to do?

View 1 Replies View Related

Forms :: Filter List Box From Other List Box Selections

Jul 15, 2015

I have a form with 2 list boxes, part number and modification. There is a subform containing another list box that is supposed to show the part information (bpn,vendor,status,etc.) that corresponds to the selected part number/modification in the parent form list boxes.

The part info list box has multiselect enabled and what i want to is be able to select multiple line items and press a button which then sets all of the selected line items status to "Request Removal". This is my code for the button:

Private Sub removeButton_Click()
Dim varItem As Variant
With Me.acbModList
For Each varItem In .ItemsSelected
MsgBox (Me.Status.Value & Me.[Part Number].Value)
Me.Status = 6
Next
End With
End Sub

The msgbox was for debugging purposes. Here's my issue; the for each actually does iterate through each selected item but the value for the line item doesn't change along with it. For example, when I selected 3 items, the msgbox will pop up 3 times but each time will have the same information (first item in the table) even when that item isn't selected.

My next issue is that I am receiving an error message with "Me.Status = 6" stating "You cant assign a value to this object". 6 refers to the id of the status i want to set it to.

View 4 Replies View Related

Modules & VBA :: Multiple Selection List / Comma Separated String - Run Query And Create Report

Jun 18, 2013

I'm using Access 2007.

So far I have a Multi Select enabled list on which the user selects the serial numbers they want. They then click the "Report" button which will trigger a query based on the selected serial numbers to create a report on those serial numbers.

I have the code for the multi-select list working already. It creates a string of comma separated values that are the serial numbers which are selected in the list. Somehow I need to pass this string to my query so it can use it as a filter.

Here is some of my code:

Code:
Option Compare Database
Option Explicit
Private Sub Form_Current()
Dim oItem As Variant
Dim bFound As Boolean

[Code] ....

Here's my current query in SQL:

Then finally how to I get the query to execute and create a report based on all of this?

View 11 Replies View Related

Query Multiple Parameters, Help Pls!

Jan 4, 2006

I have this code of a command button, which would allow me to generate the result of the SQL. I think the code is wrong... Can someone help? I guess something wrong with the bracket...

strSQL = " SELECT NewsClips.RecordNumber, NewsClips.IssueDate, NewsClips.Title_Eng, NewsClips.Titile_Chi, NewsClips.NewsSource, NewsClips.[1CategoryMain], NewsClips.[1Sub-Category], NewsClips.[2CategoryMain], NewsClips.[2Sub-Category], NewsClips.hyperlink, NewsClips.FirstTwoPara, NewsClips.Notes, NewsClips.attachment FROM NewsClips " & _
"WHERE (NewsClips.[NewsSource] " & strNewsSource & _
strNewsSourceCondition & "NewsClips.[1CategoryMain] " & str1MainCate & ")" _
str2MainCateCondition & "(" "NewsClips.[NewsSource]" & strNewsSource & _
strNewsSourceCOndition & "NewsClips.[2CategoryMain] " & str2MainCate & ")" ";"

Indeed, I try to modify the SQL that works in a test query (as I want to know what went wrong with my code): the changes would be replace OR to a toggle option.

SELECT NewsClips.IssueDate, NewsClips.Title_Eng, NewsClips.Titile_Chi, NewsClips.NewsSource, NewsClips.[1CategoryMain], NewsClips.[1Sub-Category], NewsClips.[2CategoryMain], NewsClips.[2Sub-Category], NewsClips.hyperlink, NewsClips.FirstTwoPara, NewsClips.Notes, NewsClips.attachment
FROM NewsClips
WHERE (((NewsClips.NewsSource)=[Which News Source]) OR ((NewsClips.[1CategoryMain])=[Which Category?])) OR (((NewsClips.NewsSource)=[Which News Source]) OR ((NewsClips.[2CategoryMain])=[Which Category?]))
ORDER BY NewsClips.IssueDate DESC;

Your help will be greatly appericated.

View 3 Replies View Related

Query To List Multiple Records In A List From Multiple Queries

Jul 11, 2013

I have a DB where you there's 5 tables all linked together by one project ID

tables below

Project , Staff, Asset, allowances, travel, mark up

What I can do is create a new project, then add records to each of the other tables on what different items I require,

i.e. I create a new project - called project one, in the project table I create a record stating, name, time scale, client and location, then I add different records to each of the other tables on what I require all linked to the same project ID. (probably not explained that too well)

Now I want to create a query that lists all the requirements one after the other this will make it easier to create reports and to calculate costing's.

At the moment I have made 5 different queries listing all the data, then have one report containing 5 sub reports to display the data, no this does work.

View 7 Replies View Related

Modules & VBA :: Dynamic Query Change Field Selections

Jun 25, 2014

I am trying to build a function that will create a dynamic query for a chart on a Subreport.I am not exactly sure I am going about this the right way, but I need the user to be able to change selected fields for use in the query. I have a form with 3 combobox controls for selecting options to change the SQL statement. So far my code only deals with one of these comboboxes for simplicity. There is a button to call my function. Currently, the function is setting hidden text box values based on the combo controls, but I'm not sure if this is redundant.

I am using this as my guide for building the sql, but I am having trouble picking up the values in my text boxes for use in the SQL. [URL] .....

Code:
Option Compare Database
Option Explicit

[code]...

how do I get a value from an unbound textbox on an unbound form into a string to use as sql? The value in the textbox is a number.

View 3 Replies View Related

Union Query - Multiple Parameters

Oct 31, 2005

I am trying to run the below union query, but it keeps asking for FRGHT_BL!FB_CREAT_DTM as a parameter. I want it to only ask for the Start Date and End Date once.

SELECT * FROM qLOC_ID WHERE(((FRGHT_BL!FB_CREAT_DTM) Between [Enter Start Date] And [Enter End Date])) UNION SELECT * FROM qLOC_ID2 WHERE(((FRGHT_BL!FB_CREAT_DTM) Between [Enter Start Date] And [Enter End Date])) UNION Select * From qLOC_ID3 WHERE(((FRGHT_BL!FB_CREAT_DTM) Between [Enter Start Date] And [Enter End Date]));

View 1 Replies View Related

Multiple Parameters [for The Same Field] For A Single Query?

Nov 23, 2005

I have set up a parameter query in Access 2003 that asks the user for the "Section", such as "Admin", "Accounting" etc.

I need them to be able to respond to the prompt with more than one section if they want- sometimes just one, sometimes two or three or four.

So they can get "Accounting" and "Admin" both in the records that are returned.

The code:
SELECT T_ElainesMaster.Section, T_ElainesMaster.Login, T_ElainesMaster.Workstation, T_ElainesMaster.NT, T_ElainesMaster.Barcode, T_ElainesMaster.[PC model], T_ElainesMaster.[Emp Name], T_ElainesMaster.[swap or not], T_ElainesMaster.[Exp Date]
FROM T_ElainesMaster
WHERE (((T_ElainesMaster.Section) Like [What section would you like to include?]))
ORDER BY T_ElainesMaster.Section;

Thanks!

View 1 Replies View Related

Multiple User Specified Parameters For Search Query?

Mar 30, 2006

Hello

I have created a database for my department to log all of our jobs to keep track of them and I want to create a simple search for them but I am having difficulties.

I am familiar with creating queries to search tables for matching records, but is it possible to get search criteria from the user (ideally from a search form they fill in) to form the query?

For example, I would like the user to be able to query the jobs by month and/or business area and/or supplier...is this possible?:confused:

Any help gratefully received!!!
:)

View 2 Replies View Related







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