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 Replies
ADVERTISEMENT
Mar 20, 2015
I have a form with three text boxes and one button. I want to use these three text boxes as parameters for specific query, thus i want a code that has the ability to pass these three value to query parameters or another code to solve this problem.
View 2 Replies
View Related
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
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 4 Replies
View Related
Feb 14, 2008
Hi
I am really at the end of my teather with this problem so i really hope someone here can find a solution.
I have 2 tables; Client (Client general info, defined by their location), ClientHardware (Info on the hardware a client has and also it's condition).
I need to be able to select one or more clients and display one or more conditions of their hardware, e.g. London, York, Bury + Red, Amber, Green condition.
After the Query is working right i will need to output it to a report through a button on the form.
I attempted adapting This Method (http://www.databasedev.co.uk/query_using_listbox.html) but adding another list and query just resulted in the report showing the all the records of the selected client (e.g. london) then all the records with the selected condition (e.g. bad) it would be ok if i could merge the list box selections into one query but right now it looks like this
MyDB.QueryDefs.Delete "qry ClientName"
Set qdef = MyDB.CreateQueryDef("qry ClientName", strSQL)
MyDB.QueryDefs.Delete "qry RAGType"
Set qdef = MyDB.CreateQueryDef("qry ClientName", strSQL2)
Is it possible to put the variables in strSQL2 into the creation of qry ClientName somehow?
Any help, large or small will be appreciated as I'm really at a dead end with this.
Regards
Phil
View 9 Replies
View Related
Dec 15, 2014
I've created a form with two textboxes and a listbox. What I want to do is to show in the listbox the registers filtered by the 2 textboxes. But I would like that the listbox refreshes while typping in the textboxes.It was no problem to do filtering with only one textbox. The rowsource of the listbox is a query in which I set in the criteria Like [Forms]![Form1]![Textbox1].[Text] & "*"Then I write the code for the change event of the Textbox1:
Private Sub Textbox1_Change()
Listbox1.Requery
End Sub
With this I have no problem. The problem is when using the 2 textboxes. I write the same in the criteria, Like [Forms]![Form1]![Textbox2].[Text] & "*", on another field but it doesn't work. I also write the code for the second textbox:
Private Sub Textbox2_Change()
Listbox1.Requery
End Sub
The listbox doesn't filter with both textboxes. When you type in one of them, the listbox filters from it, but when you type in the other textbox, the listbox shows the whole table filtered with the characters of the second textbox.
View 10 Replies
View Related
Nov 8, 2004
I have a form with a List Box (List BoxA) that returns a value from a query. I then have a Text Box (Text BoxB) that uses the value from List BoxB and multiplies it by let's say 2. It works giving me the correct value but only becomes visible after I click inside List BoxA. How can I make it visible as soon as the form is opened? I tried refreshing the form data, didn't work and I tried a requery macro which didn't work either.
Thanks,
Lester
View 3 Replies
View Related
Nov 20, 2013
Allen Browne's "Use a multi-select list box to filter a report" solution, in particularly with two multi-select list boxes? The code works fine for me for either box so long as I code it for one box alone. Combining the two into one code results in a type mismatch error. I'm trying to use the code to pass the contents of both multi-select boxes as Where conditions to a report. Both boxes are based on number fields. To try to isolate the problem, I've removed Allen's setDescription and OpenArgs conditions. We're unfortunately still on Access 2003 as the company desires to squeeze every dime by using until end-of-life next year.
Code:
Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the list box.
Dim varItem As Variant 'Selected items
[Code] .....
View 14 Replies
View Related
Aug 25, 2004
I have a field in a table called reasons. Then I have a list of possible reasons in a drop down list for my form. I want to create a report that allows the user to select from a drop down of these reasons and do a query for only those records with that specific reason. I know you can ask that info be typed in by using brackets in the query but instead of typing the reason I want to be able to select from the drop down list to produce the report. Can this be done?
View 1 Replies
View Related
Apr 3, 2014
I'm wondering is it possible to create a multiple parameter query which will return results even if you leave some of the parameters blank ?. I'm trying to set up a Form which will allow users to select parameters using combo boxes but at the moment you need to fill them all in or you'll get no results...
View 10 Replies
View Related
Feb 28, 2007
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 7 Replies
View Related
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
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
May 26, 2005
How would I create not only a list box but a Cascade combo box in Acess could you help me out on this thread?
So when I selct J for J Richard Szeremany the next ABC box only lists the CD's by that Artist then I click on the Name of CD and the song onfo comes up.
mikevds@optonline.net
View 1 Replies
View Related
Aug 4, 2006
Im having a problem with a listbox/query/label. My DB is based on one that was posted in the sample database are of this forum. In itself it works really well. Clicking on the labels at the top of the list box applys a sort to the list box, each one of these sorts is based on a query. My porblem is that I want to be able to produce and print a report based on those queries. I know I could put a bunch of buttons on the form and do it that way. Or put a list box with all the my reports in and do it that way. I would prefer though, to have one button with one report reading the information from the different queries depending on what label is selected. Hopefully thats clear, the DB demonstartes it better. Any help appreciated. Thanks!
View 7 Replies
View Related
Sep 8, 2006
I am struggling to create the "hidden" control. I'm assuming it should be a text box? Where should the code given on the above page go in the text box properties? Do I need to do something to activate the code after selections are made in the list box?
View 2 Replies
View Related
Oct 18, 2006
Im tryin to move data from one list box to another ive seen some sample datebases and how they use command buttons to move the data back and forth from the list boxes (usually have <, >, <<, >> on the command buttons)
Does anyone know how i do this?
View 2 Replies
View Related
Oct 14, 2004
I have an unbound list box with it's row source a query whose select criteria comes from another list box once selected. When the form first opens the list box is empty until the user makes a selection. How do I prevent access from running the query in the first list box when the form first opens. It slows everything down.
View 1 Replies
View Related
Jun 24, 2005
I want to be able to specify the site of a disease. I had a combo box with pre-defined options (linked to a LU table) but now i find out there can be multiple disease sites. I changed it to a list box and chose multi-select (extended) but this does not retain the values in the form or record them in a table (even when I use the wizard and tell them what field i want the results in). What am i doing wrong or have I missed the whole gist of what a list box is for? Cannot find anything on line to help me here
View 5 Replies
View Related
Oct 19, 2006
I a list of data in a list box and i want to be able to choose diiferent pieces from the list. How can i ref an individual record in the list box do you use
ME.Listboxname.value something like that im lookin to move pieces of data from one list box to another for printing
i can move all the records by using
Private Sub MoveAll_Click()
Dim strSQL1 As String
strSQL1 = "Update projects SET projects.Selected = Yes Where projects.Selected = No"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL1
DoCmd.SetWarnings True
Form.Refresh
End Sub
and i can move them all back by using
Private Sub RemoveAll_Click()
Dim strSQL2 As String
strSQL2 = "Update projects SET projects.Selected = NO Where projects.Selected = Yes"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL2
DoCmd.SetWarnings True
Form.Refresh
End Sub
Any idea on how to move an individual record?
View 3 Replies
View Related
Mar 2, 2006
Is it possible to control the options available in one list box with an option made from a previous listbox? If so how do I do this?
Thanks
View 5 Replies
View Related
Dec 21, 2006
HI hope you can help me!
I'm wanting to create drop down lists that are filtered as a result of a previous field, but not having much luck!
I've tried using a query to do this, but not having any luck! am i going down the right path?
Do i need a new query for each list, or can they all run off one query?
How can i refresh the list if a previous field is updated?
Additionally where there is only 1 option (similar to a lookup in excel) how can i set this without using a list box?
Hope that in the xmas spirit, my job is safe!!
Merry xmas to all.
Andrew - Hull
View 2 Replies
View Related
Jun 25, 2007
Hi peeps
I have a list box that contains 4 fields and the list box is located on a form.
One of the fields on the form is [Date logged]. This is also colum 2 of the list box.
I am trying to write some code to say that if the Date logged on the form is different to the one in the list box, run an append query.
Basically, the principal behind this is to have an audit trail of when records have been ammended. The first time a record is created, a base record is created. It is this base record that will populate the list box along with all other record changes that occurr from that point on.
I am trying to say that if the [Date logged] does not match the last record in the list then it knows it needs to append the record to the history table again as a change has been made. I would really appreciate some help here folks. Thanks so much! :-)
View 2 Replies
View Related
Apr 26, 2005
Hiya guys,
I have got what seems to be a pretty straight forward task i need to do!
I have got a table named tblInput, with four fields Type, Collection, Value and Reason.
I need to give the Type field two possible options "a" or "b", i then need the Reason field to display a list of values depending on whether "a" or "b" was selected in the Type field.
Values for "a" need to be "Success", "Failure" or "Rejected"
Values for "b" need to be "Status Change" or "Amendment"
I have been playin for a couple hours now trying various differnent methods but i am commin up short, its this something i am able to do from a table?
Many thanks
Tim
View 3 Replies
View Related
Feb 26, 2005
Hi, im kind of new to access and im not sure whether this is possible or not, but i have a problem which needs to be solved by 4 list boxes on the same form.
The first list box will have 4 choices in it and depending on which one is chosen the second list box will be updated. This continues on to the 4th list box, which when a choice has been made a new form will open with the relevant information from the tables.
I hope this is enough information to enable someone to aid me with my problem, thankyou very much for your time.
Craig.
View 1 Replies
View Related
Mar 28, 2005
I am working on a database for a bowling tournament. I'm currently working on a form where the user selects from a combo box bowlers' names for doubles pairings. I thought that as a helpful tool I would also display a list of available remaining bowlers once the combo box information has changed.
I'm trying to write a module for the combobox_change trigger. What I'm having trouble doing is pulling the updated recordset from the base form and using that to update the availability box -- in other words, how can you query data from the recordset underlying an open form before the form is closed and the data is saved to the tables. The only way I can do this now is to temporarily close the form, manipulate a recordset from the table, then re-open the form. There's got to be a better way. Maybe I'm just missing something here.
Thanks in advance for any help.
View 1 Replies
View Related
