Queries :: Access 2010 / Query Not Showing Results From Combo Box?
Aug 13, 2014
Access 2010. This has worked before but I don't understand why this is not working for me now. When i select an item in a combo box in a form and click on a button to run a query with the results, the query is blank. If I run the query alone, it prompts for an item, I can type it in and it works. I have even tried the DoCmd on the combo box but still same results. Attached is a dummy down DB. If you run the query, it will prompt, select Paper or Rock, see results. But run the form, the drop down will not show the results. What am I missing?
I have an unbound combo box that I set up in a form using the combo box wizard in Access 2010. I selected the option that says, "Find a record on my form based on the value I selected in my combo box." The problem is that the combo box will not always display the correct record. It seems to only display the first record of a group of similarly named records in a table.
Example:
Code: Last Name First Name SSN Smith Alex 123-45-6789 Smith Jane 234-56-7891 Smith Mary 345-67-8910
If I select Smith Jane, the record for Smith Alex will display. I know that this has something to do with the bound column property, but I am unsure what to do to fix the issue.
What I have tried: I tried setting the primary key as the first field, but then I could only search by the primary key, which is unrealistic for this database. Users will be searching based on last name. How do I make the combo box select whichever record I select from the drop down list?
I have a query that is showing two results for one product and I have no clue why. I have my tables in a one to many relationship and if I click on the + it shows the correct data for the product in question. Im guessing I have my table set up wrong its the only thing I can think of.
The table tbl_Carton has two entrys for a product (Flex Tape 2-1/16") (Product_PKEY #21) mabe I have it set up wrong? If I run my (qry_Switchboard ) and enter (21) its giving me an extra result for each of the two results thats suposed to be there.why?
Sales will make calls and record data using a form (frmEnquirySpeak) which will save to tblSpeak. If the call results in a meeting being booked then it also updates tblMeeting with a date of the meeting (simple stuff so far!).
A sales person might record up to 14 types of action per day, qrySpeakPerAction counts the different types of call made on a day by day basis for reporting. qrySpeakGroupedAction summarises this down to 8 categories and applies a date filter selected on a reporting form (and this works well).
I now want to include the qryMeetingsBookedPerDay (counts m_date_raised and groups by m_date_raised...eg 2 on 24/12/14, 4 on 28/12/14 etc etc) with the qrySpeakGroupedAction...
My effort so far has resulted in only dates which appear on BOTH queries showing. This may not always be the case. Every day calls will be made, this may or may not result in a meeting. So I see there being 2 options:
1 - Query to show the count of calls on a date or date range and number of meetings booked each day that calls are made (prefer this method, it sounds simpler) 2 - qryMeetingsBookedPerDay should auto populate days which dont appear as 0 (this sounds messy and not sure what capabilities/speed are like when managers want to spot patterns/trends over multiple years?)
I have a query (that gets it's data from several other queries) with a column called "max." The data in the column is correct, but when I call on the query in VBA, it shows me incorrect data.Here is where I call the query:
Code:
Dim db As DAO.Database, qdf As DAO.QueryDef, rs As DAO.Recordset Dim strReport As String Set db = CurrentDb() Set qdf = db.QueryDefs("7-ErrorsReport") qdf.Parameters(0) = Forms!frmmain!dt1.Value qdf.Parameters(1) = Forms!frmmain!dt2.Value qdf.Parameters(2) = Forms!frmmain!d2.Value
[code]....
The query looks at a table of employees and finds out if they have been issues a warning letter before, then prints out a corresponding report based on the "max" warning level they are at.The problem arises when an employee graduates from a 6 month probation period - all letters in that period should be ignored. As i said, they are ignored correctly when i run and view my query ("7-ErrorsReport") because they are filtered out at that point, but for some reason when this code runs, it somehow sees the previously issued letters which are stored in a table and likely in some of the other queries.
I am not sure if there is some issue with the query tree I have set up which is necessary to get the results I need, or if something is wacked with the was I am using it as a recordsource.
I have a query that i can type in an item number and it Sums all the locations and tells me how many i have in the building. If i do not have any in the building then there is no record of it in the table and comes back blank. How do i make it show a show 0 instead of blank if there is no records to sum?
We have a recent issue on client PC. After application running for a while, all the sudden the calculated field on the form not showing the details, though there is data on the control. After restart the PC, the calculated field display correctly. We guess this may be due to the theme we use.
I am running Access 2013. I have created a search form with about 10 different categories from a single table. I will only show 5 fields for this example The form is called FrmSearch with
Description CarNum SerialNum Category Condition
In the query, I have the criteria, Like "*" & [Forms]![FrmSearch]![description] & "*" and have this same criteria for all fields with the proper text field entry. (CarNum, serialnum, category, etc)
The problem I have is that I have over 200 items listed in the Description column but only 2 entries in the CarNum column. When I run the search with nothing in the FrmSearch, it should show all data but it only shows the 2 CarNum. If I put something in the Description, only the two items that show CarNum. I tried to use Is Null and Is Not Null but still doesn't work. I take out the "Like" criteria from the CarNum and it works. So, it seems that it does not pull up all the records because the CarNum has empty fields.
and I have several queres using the same table. I was trying to add 2 columns and fields to my 1 querie. I do not use program much but I have it for my cattle. SO I was clicking around. Went to Table and went to add the field.I changed my Primary Key field then realized I should not have done that.I changed it back to "ID" and "Number" and added my fields. THEN I went to the one querie and added my columns and fields.However NOW the one querie I messed with has 30,888 rows as it just repeats the 20 or so rows over and over .The other queries do not and they use some of the same data "ROWS" from the table
I have played with this problem for 3 days and have come close but not quite solved it. My problem, I have several drivers delivering several orders, the orders are named 101, 102 and so on lets say to 150. Due to locations of the drivers, some deliver more orders then others. I want to be able to create a report that looks like
"Driver #1 101 - 106" "Driver #2 107 - 110"
Driver 1 delivered 6 orders. Driver #2 delivered 4 orders and so on.
I have tried the 'count" which gives me the number of orders per driver but having trouble figure out the design of the calculation in the query.
I am currently using INNER JOIN and GROUP BY to narrow down the RowSource of a combo box based on the value selected in the active combo box where the code is located "onClick".
The value being selected is a StoreID, This ID is matched against the AssetRegister to find all group names, The GroupID and GroupName are then retrieved from the AssetGroup table, then finally the list is grouped on the Group ID and Group Name.
I have tested this query in SQL Server Management Studio and it works without any issues, however when I use it as an inline query to adjust the row source of the combo box it returns no values.
Here is the SQL query:
Code: SELECT AssetGroup.ID, AssetGroup.GroupName FROM AssetGroup INNER JOIN (SELECT AssetRegister.AssetGroup, AssetRegister.Store FROM AssetRegister WHERE AssetRegister.Store=7) AS ar ON (ar.AssetGroup = AssetGroup.ID) INNER JOIN Store ON Store.ID = ar.Store GROUP BY AssetGroup.ID, AssetGroup.GroupName ORDER BY AssetGroup.GroupName
And here is the inline query applied to the RowSource:
Code: AssetGroup.RowSource = "SELECT AssetGroup.ID, AssetGroup.GroupName FROM AssetGroup INNER JOIN " _ & "(SELECT AssetRegister.AssetGroup, AssetRegister.Store FROM AssetRegister WHERE AssetRegister.Store=" & StoreID & ") AS ar ON (ar.AssetGroup = AssetGroup.ID) " _ & "INNER JOIN Store ON Store.ID = ar.Store GROUP BY AssetGroup.ID, AssetGroup.GroupName ORDER BY AssetGroup.GroupName" AssetGroup.Requery
I have just upgraded from Access 2003 to 2010. Now I'm trying to relearn some of the small things I used to be able to. In 2003, I could just have a table highlighted and select "Insert, Query". It would then open a query design with that table. Is this possible to do this in 2010? Or do you have to open query design then add the table manually?
I have a table that has one field and I want to use this field as a query criteria . when i click on CRITERIA & BUILD, Access lets me select the table field and shows [tblBillRun]![Bill_Run] for the criteria but when I click RUN, it prompts me to ENTER PARAMETER VALUE. What am i doing wrong?
Does anyone know how to do a query so that the user can find all the things that will expiry at the end of the current month? The user will click on a command button and it will show the results of a product that will expiry at the end of a current month. Have tried with parameters to which the user manually types the end date in and then it will show the results but having problems. Cheers
SELECT Training.ID, Training.StaffPIN, Training.Module, Training.DatePassed, Training.DateExp, Training.Comments FROM TrainingModules INNER JOIN Training ON TrainingModules.[TrngModule] = Training.[Module] WHERE (((Training.StaffPIN)=[Forms]![TrainingFrm]![StaffPIN]));
Does anyone know why this query isn't working? It doesn't throw an error, it's just not showing any records.
The only thing I can think of is: Training.StaffPIN is a long int When it prompts me for [Forms]![TrainingFrm]![StaffPIN] I enter '12177' in the message box, is that taken to be an int or is it actually a text string?
It might not be this as when I use the form [TrainingFrm] to pass the perameter, it's taken from a combobox ([StaffPIN]) where it IS an int, and the query is still blank. :confused:
TrainingModules is a table that stores all the possible modules or subjects that a member of staff can be trained in Training is a table that stores which staff member has passed which module or subject. I want the query to pull all the info on a staff member from Training to be later used as a subform.
I am relearning Access and having a little difficulty. I am creating a Access database that links tables from an I series system which I have done trouble free. In the database I have pulled up for example a patient demographic file (hospital). I have form frmSearch that prompts the user to enter the medical record number OR they can search by name. I have a query that takes that information then loads a 2nd form to display the query results in a list box. I would like to be able to click on one of the results from the 2nd form to then load another form that will display all of the patient information that was selected.How do you set this up so that with the results shown, I can click on one of them and it will load that patient into another form pulling the rest of his or her information from the table file?
I've been writing queries in the following format for years in Access 2003, but having recently transistioned to Access 2010, I've found the following sql doesn't work.transform
sum(s.value) SELECT s.sn, s.ln, s.pn, s.id, s.lat, s.point, s.supply_type, s.used, s.real, s.code, c.name FROM supply_points s , codes c WHERE s.code = c.code and s.id is not null and s.code = 1075 and s.month >=[forms]![main]![gppstart ] group by s.sn, s.ln, s.pn, s.id, s.lat, s.point, s.supply_type, s.used, s.real, s.code, c.name pivot s.month
In Access 2010, this query returns the following error message:the Microsoft Access database does not recognize '[forms]![main]![gppstart]' as a valid field name or expression
I'm have quite a difficult time getting a form in Access 2010 to perform the way I would like it to. I have multiple tables that I've created, and a query that contains the data from the various tables. I then ran the form wizard to select the data from the query that I wanted to populate in my form and I've added 2 combo boxes.
What I want to do: 1. I want users to be able to select a category in combo box #1 (example: "Bag") 2. I want users to be able to select a detail in combo box #2 based on the category they selected in combo box #1 (example: Combo box #1, "bag" would populate the following selections for combo box #2: "sandwich" and "tool") 3. I want users to then receive a list of suppliers that provide the product they have selected, either "Bag: Sandwich" or "Bag: Tool"
I have combo box #1 populating a list of categories already. However, I am not able to get combo box #2 to provide choices based on the selection chosen for combo box #1.
I am fairly new to access, but do have some experience with SQL & PHP.
I am trying to do something which I would have thought was quite straight forward. But I keep hitting a brick wall and all I get is #name? error on my form.
I have a table "tbl_Job_Spec" and a table "tbl_Tool_List".
I would like to have on the form for tbl_Job_Spec a drop down for selecting the tool number from the "tbl_Tool_List", which then automatically puts into a text box on the form the tools location, which is stored alongside the Tool Number Field on tbl_Tool_List.
I've tried loads of things, I have a query setup ("qry_tool1loc") which seems to correctley identify the relevant information, but when I try to reference that query on the form if just comes up with #Name? in the box on the form.
Anybody got any ideas? I'm only at an early stage in what is quite a big project, but to be honest this is about as complicated as it's going to get, so once I can get past this hurdle it should be plain sailing!!!!!
I am trying to use this query. It gives me correct results as query. However when I make chart with query on a report it doesn't show correct data and eventually stops making chart
SELECT qry.txtRC, Count(tbl.txtRC) AS CountOftxtRC FROM tblMain AS tbl, qryRC AS qry WHERE (((tbl.txtDepartment)=[Forms]![frmRC]![cboDepartment] Or [Forms]![frmRC]![cboDepartment] Is Null) AND ((tbl.txtZone)=[Forms]![frmRC]![cboZone] Or [Forms]![frmRC]![cboZone] Is Null) AND ((tbl.txtRC )=[qry].[atnRC ID]) AND ((tbl.date) Between [Forms]![frmRC]![startDate] And [Forms]![frmRC]![endDate])) GROUP BY qry.txtRC , tbl.txtRC HAVING (((Count(tbl.txtRC )) Is Not Null)) OR (((Count(tbl.txtRC )) Is Not Null)) ORDER BY Count(tbl.txtRC ) DESC;
I am trying to create a Cascading Combo Box for my Form in Access 2010. I ahve written the code:
Private Sub Combo11_AfterUpdate() cboCombo13.RowSource = "Select TblAcc.SubFamily " & _ "FROM me.TblAcc " & _ "WHERE me.TblAcc.Family = '" & cboCombo13.Value & "' " & _ "ORDER BY me.TblAcc.SubFamily;" End Sub
But keep getting the same "Run time error message 424: Object required"
And when I define the error it says "Identifier under cursor is not recognized" (the yellow arrow comes up and is pointing at the "ORDER BY" line.
I would like Combo Box 13 to give me the SubFamilies of the certain Families choosen from Combo Box 11. (Each Family has different SubFamilies). But if I ignore the error, Combo Box 11 lets me choose a Family and Combo Box 13 gives me options to choose any SubFamily in my DB.
