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?
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 ?
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.
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.
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:
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?
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.
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?
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.
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.
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'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.
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 ?
: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!
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!
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.
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!!
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.
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
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
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?
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.
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
