List Box In Query
Apr 27, 2008
I have created a database showing the results for a number of events with long and complicated names.
In the database is a query which select the first, second and third for each event, I would like to have it show each event seperately unfortunatly the number of events is to large to create a seperate query and report for each one.
Is it possible to have a parameter query asking for the event name where instead of the user just entering the name of the event, can they pick the event from a dropdown list?
View Replies
ADVERTISEMENT
Mar 28, 2014
In my access form I provide the user a list of locations from various countries in a listbox . But the list is too long so I provide him a combobox for selecting a country. Selecting the country should update the listbox showing only the locations in that specific country.
So my SELECT from the listbox must cover the unselected state and show all entries and when a country is selected it must narrow the selection.
I tried to get this happen with the following SELECT statement containing a variable. Choosing a country in the Combobox results in a change of the variable and in a requery. This works after the first country is selected and for each country change, but the initial list is empty.
VBA in the loadform
'Application.TempVars.Add "varcountryselect", "*"
SELECT in the listbox "lstlocationsperproject"
SELECT tbllocations.locationID, tbllocations.country, tbllocations.localstreet, tbllocations.localcity FROM tbllocations WHERE ((tbllocations.country) Like [TempVar]![varcountryselect]);
VBA in the combobox
Application.TempVars("varcountryselect") = [Form]![kombcountryselect].Column(0)
Me.lstlocationsperproject.Requery
The values in [kombcountryselect].Column(0) are texts like "SPAIN", "MEXICO", etc.
Any hints, how I have to use the * for getting the complete list on the initial view ?
View 5 Replies
View Related
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
May 25, 2006
Hello,
Ok, let me preface this with I'm a newbie at Access, and I'm currently using Access 2003. I'm just hoping someone can point me in the right direction. Right now I have multiple tables with set data in them that doesn't change linked to another table where I have a drop down menu (combo box I think it's called in Access) to easily select the given data from. I want to have a drop down box (combo box) to have a set of given variables I have in a table to put in the search criteria. That way a user doesn't have to type the exact criteria into a query. That way I can easily put in the variables I want to search from in the query with less chance of an error. I'm hoping this is possible.
Ideally if I could get help in how to make a form that would allow me to have the set of columns from the corresponding tables with combo boxes to select from and then be able to have a "subimt" button and have it organize the selected results into that form as well.
Thank you for taking time to read this.
View 2 Replies
View Related
Nov 30, 2005
I have a table with records Name1, Name2, through Name6 I need to have a query or something that will join all theese records under one list. :confused:
Thank you in advance. :)
Rene
View 1 Replies
View Related
Oct 24, 2006
I'm very new to access - perhaps this is an easy one...
I have a master table 2 columns wide. I want to extract data from the master table for values that appear in a second table.
That is...
Master Table:
1 a
1 b
3 c
4 d
4 e
6 f
6 g
5 a
My second table
1
6
5
I want to return:
1 a
1 b
6 f
6 g
5 a
I basically want to enter a list of values in the criteria section of the query design view.
Any suggestions?
View 1 Replies
View Related
Dec 7, 2006
Hi all,
I'd like to modify a query of mine a little ...
Currenlty, when I click on my query and have it begin ... I have it set up where I then enter three seperate pieces of distinct data so that the exact information I'm in need of, which is stored in 2 seperate tables, can then be pulled together and displayed into one final combined table ...
I'd like to modify this beginning point where ... once I click on the query to begin, I can then just choose from a Drop Down list which has all the various rows of information displayed via these 3 unique indentifiers ... I then just scroll through and pick what I need and it then populates the final combined table as before ...
How do I add the drop down effect at the start of the query?
thanks,
TIM
View 1 Replies
View Related
Mar 7, 2007
Hi, Anyone got any ideas how I can create a query that runs from the selection in a list box. What I need to do is when the user selects an option in the List box for them to select a button to the right which acknowledges the secletion and then runs a query with the selection as the criteria.
Any ideas???
euan Hampton
View 3 Replies
View Related
Dec 12, 2007
I have a query which is based off all fields of the main table. I want to run the query based on one or more choices made from a list box on one field: category.
tbl_ClientMain
qry_ClientCategory - all fields of tbl_ClientMain with criteria under Category as =[Forms]![frm_CategoryList]![lstCategory]));
frm_CategoryList is a listbox form based on qry_Category
qry_Category is a group by query from tbl_ClientMain of the Category(s) collected
When I run the query, I get Enter Parameter Value: Forms![frm_CategoryList]![lstCategory]));
Based on what I've read in various postings and other sources - I must be close, but I'm missing something. Any ideas?
View 3 Replies
View Related
Dec 8, 2004
Please help, I have a form with 7 option buttions and a calendar. i would like to be able to run a query from the selection that is made. the way this would work is:
7 option buttions - listing different locations
Calendar - select date
the query would list any event and the start time and end time that fell on that date.
my problem is that I dont know were to start. i have been told to use SQl statements. i have tried this but only failed miserably. the events and times are in 2 different tables, i cant get a SQL stastment to work like that.
I have tried to use a List box, using the Row Source to display a query, but only 1 row showed. it still was unselectable.
Please help, dont know were to go with.
John
View 1 Replies
View Related
Oct 1, 2006
I have a Select query that lists, among other things, two fields:
ID_Clients
EndDate
Each entry in ID_Clients may be listed several times
I want to generate a Select query based on the above query that lists each value of ID_Clients just once, by selecting the record for that ID_Clients that has the maximum value in the field EndDate.
So, sample source data
ID_Clients......EndDate
AAA.............2005-04-30
BBB............. 2005-06-30
AAA.............2006-04-30
BBB............. 2004-06-30
CCC.............2006-09-30
Desired end result:
ID_Clients......EndDate
AAA.............2005-06-30
BBB.............2006-04-30
CCC.............2006-09-30
Any ideas how I should achieve this would be appreciated.
Thanks
View 2 Replies
View Related
Nov 1, 2006
I was wondering was it possible to have my queries in a pull down list and I hit a button and the query/report is ran? Or do I have to stay with about 6 or 7 buttons on a form?
I know it sounds crazy.....
View 2 Replies
View Related
Jun 12, 2007
Quote: Originally Posted by ansentry Have a look at the attached sample.
Hi John,
I am new to this site. I can't seem to find where the attachment being referred to is. I have a similar question. Basically, I have this data:
Parcel No. ......Soil Type......Area
001-001 flathead 75
001-001 whitefish 33
001-001 kiwanis 45
001-002 kiwanis 22
001-002 flathead 33
001-002 chamokane 74
002-005 flathead 20
002-005 kiwanis 53
002-005 chamokane 27
The desired result is:
Parcel No. Soil Type Area
001-001 flathead 75
001-002 chamokane 74
002-005 kiwanis 53
Can you please help me? Thanks.
View 8 Replies
View Related
May 31, 2006
I'll admit from the start that I'm a newbie at Access 2003 and I've tried looking in the help file and internet searches. But I've become stumped on how to create what I'm looking for.
I would like to create a form that performs a query that has drop down menus with info from each specific column in a table. That way I don't have to necessarily need to know off the top of my head what data is entered in that column, but can click the drop down menu and get all of the data listed in that particular column. Once have a particular set of info selected, then have a button like "submit" and then the query results appear in a form and click through each page or results.
Is this possible?
View 1 Replies
View Related
Jul 27, 2006
On my main screen I have a button that I press and it pulls up a
pop up box...in the box you type in one item and hit ok then
the query goes and gets the results.
My quesion:
how do it make it so when I click the button the pop up box
has lets say four areas where I can enter different items and
then it goes and gets all four...or three or whatever I need ?
thanks...
View 1 Replies
View Related
Jun 21, 2005
:confused: I've tried everything I can possibly think of to get this to work. I've also looked at other threads and I still couldn't get this to work. The code for the list box in my form is Forms!Display_Engineering_Jobs_Report_Inputs!Repor t_Column1. I am trying to set the value in this list box as the input for the field on my query. This value also corresponds to a heading within a table I have. The information under the heading in this table is what I am trying to show in the query. Please help! Also, I'm pretty new to Access, so explain it as if you were writing a page in "Access for Dummies." Thanks!
View 9 Replies
View Related
Jul 28, 2005
Hi,
I've been trying to solve this problem for almost a full day now and i'm starting to get desperate.
Can't seem to find any help on the net, so i hope maybe one of you will be able to save me.
I have 2 list boxes :
1. MainBox - shows a list of records from one table.
2. FilterBox - this is actually a query that shows a list of records from another table, filtered by the record i choose in the MainBox.
I added the MainBox a custom line displaying "All" by adding this code to its RowSource :
UNION SELECT "(All)" from MainTbl
So now i see the list of records like before, with a "(All)" as the 1st line.
Now i want to set up the FilterBox query so that if i select the "(All)" line, it will not filter the table and just show me all records, so i entered this code as the appropriate column condition :
IIf(([Forms]![frmTest]![MainBox])="(All)",([tblFilter]![FilterColumn]) Is Not Null,[Forms]![frmTest]![MainBox])
That's supposed to check if i have selected the "(All)" line in the MainBox.
If i didn't - then the FilterBox gets filtered by the MainBox value - this is working fine.
If i did - then the column filter value should become "Is Not Null" which, according to my understanding, means that the column won't get filtered - that's not working! When i select the "(All)" line, i just don't get any records in the FilterBox.
Does anyone have any idea what i can do to fix this ?
Sorry for the long explanation.
Thank you very much!
View 3 Replies
View Related
Nov 10, 2005
Hi...I have the following requirement:
I have a table called "tblselectrso" with the following fields:
stockno rso1 rso2 rso3 rso4 rso5
s1 1 3
s2 2 4 5
stockno is a text field and rso1 through rso5 is a Number field.
Given the value of "stockno" I want to run a query to list all the other fields (rso1 through rso5) that are NON-EMPTY.
Is this possible at all? Can anyone help me please?
Thanks
Please find attached a sample db.
View 2 Replies
View Related
Apr 14, 2006
Hi,
I need to output data in a specific format for a certain client..
for example where
Select * from Falls - would normally give me:
01/01/05 Joe Smith ICU, Assisted
I need in this intance to get:
01/01/05 Joe Smith 5, 2
where locations, or types etc are converted to a corresponding number.
I can imagine a query like:
Select FallDate, PatientName, somefunction([Location],(MS2,ED,OB,CAFE,ICU),(1,2,3,4,5)), somefunction([Fall_Type],(Accidental,Assisted,Unanticipated),(1,2,3))
Is there some more powerful version of IIF or way to write this in the query without having to create a report with a bunch of functions?
View 5 Replies
View Related
Aug 15, 2006
I have a query that reports on records according to date. The criterea line of the date field has [Enter Date]. I have confirmed that there are dates from 8/1/06 through 8/10/06 in the table, but when I enter the date 8/10/06 the query returns no records. But it does return records for all other dates (8/1/06 through 8/9/06 inclusive).
So then I tried to make the date a range:
Between [Enter Start Date] And [Enter End Date].
I entered 8/9/06 and 8/11/06 in these fields and there in the report were the records for 8/10/06. Then I went back to [Enter Date] as criterea and entered 8/10/06 and no records are returned.
View 3 Replies
View Related
Jan 15, 2007
Hi, any help will be very welcome.
I have two tables, and I link them(make a relationship) by field, then I create a Query where I choose the link field and all the others of one of the tables. This gives me the a list of records that are linked by the linked field.
Now, How can I get a list of the ones that are not included in that query?
I guess that the solution will be very easy, but I don't know it. So please help me!!
Thanks and regards,
Bigger from London
View 2 Replies
View Related
Jul 22, 2007
I have a list box with a query as its row source (screenshot attached).
The query gets data from my Contracts table with information on a Consultant ID, Contract ID, and a Contract Status indicator. This effectively represents each contract with a specified status.
What I'm trying to do is allow the user to select a specific contract from this list box, and for my main form to open and display the actual information on the contract they just selected.
A little background on my main form, the main form consists of a main page followed by several Pages, with a subform in each. Thus, my contract screen is designated as Forms![frmConsultantList]![sbfrmContracts].
So, what I'm trying to do right now is collect multiple fields from the selection the user makes (on the listbox with the query row source). To further clarify, each line has a Consultant ID and a Contract ID. How do I pull each from the user's current selection on the listbox and assign them to new variables? As of now, I am only able to select the first field in the list (Contract ID) and assign it to a variable.
How do I do this, or is there a better way?
I apologize if I'm unclear about my situation, so please feel free to ask any questions. And of course, thank you to all those who spend time reading this.
View 3 Replies
View Related
Jul 24, 2007
so i have two listboxes that have the values i want for the query parameters. I slightly modified one code i found so that i can query using one of them, however i cannot figure out how to get the second listbox to put criteria into another field. The working code i'm using is:
Private Sub Command_Click()
On Error GoTo Err_Command_Click
On Error GoTo Err_Handler
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
For Each varItem In Me.PartyBox.ItemsSelected
strCriteria = strCriteria & "counterparties.counterparty =" & Chr(34) & Me.PartyBox.ItemData(varItem) & Chr(34) & " Or "
Next varItem
strCriteria = Left(strCriteria, Len(strCriteria) - 4)
strSQL = "SELECT counterparties.[Counterparty Entity], Fund.[Fund Name], products.Product, combine.[Available?] " & _
"FROM products INNER JOIN (Fund INNER JOIN (counterparties INNER JOIN combine ON counterparties.[Counterparty ID] = combine.[company id]) ON Fund.[Fund ID] = combine.[fund id]) ON products.[Product ID] = combine.[product id] " & _
"WHERE " & strCriteria
CurrentDb.QueryDefs("1").SQL = strSQL
DoCmd.OpenQuery "1"
Exit_Handler:
Exit Sub
Err_Handler:
If Err.Number = 5 Then
MsgBox "Must Make A Selection First", , "Make A Selection First"
Exit Sub
Else
MsgBox Err.Number & " " & Err.Description
Resume Exit_Handler
End If
Dim stDocName As String
stDocName = "combqry"
DoCmd.OpenQuery stDocName, acNormal, acEdit
Exit_Command_Click:
Exit Sub
Err_Command_Click:
MsgBox Err.Description
Resume Exit_Command_Click
End Sub
I am trying to be able to search by Product and counterparty.
Any help is greatly appreciated, thanks!
View 8 Replies
View Related
Jan 24, 2008
I've just been upgraded to Office 2003 (from 2000) and I'm trying to create an append query. I have three tables but only two are displayed on the table list drop down. I can type in the missing table and the query works but I don't understand why all the available tables are not being shown. Does this happen for anyone else and what can I do?
TIA
Karen
View 14 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
Feb 23, 2008
Hello,
I have been searching the forum for this scenario but could not find similar case, which is as follows:
I have a Search Form with many Text and Combo boxes, linked to search criteria in a query. There is also a sub-form showing the search results.
I am trying to replace one of the combo boxes on the form, with a List Box, so I can make multiple selections. For example instead of showing only "buy" transactions via the combo, I want to see both "buy" and "sell" transactions, which I believe could be done via a List Box.
For the combo box I have the following:
In the Field section I have:
[TransactionType]=[Forms]![frmSearchTransHist]![cboTransType] Or [Forms]![frmSearchTransHist]![cboTransType] Is Null
For Criteria I have - "True"
So, my question is what should I put on the query in order to make it work with a list box.
I also want to give credit here to Jon K who has an example of database with combos and texts - very helpful example!
http://www.access-programmers.co.uk/forums/showthread.php?t=103312
Any help is greatly appreciated!
Rgds,
Atol
View 2 Replies
View Related
