Queries :: Refresh Values In A Form According To A Query
Oct 16, 2013
I have a form that has a dropbox list and I want the values of the form to change according to the value in the dropbox.For example:
When the dropbox contains the value A,
The form would display x=1, y=2, z=3.
When the dropbox contains the value B,
The form would display x=4, y=5, z=6.
I managed to create a query that opens the QUERY's results according to the value in the dropbox but I can't managed to run the query in the form, return the values according to the query and display them in the form.
I searched the internet and found many options including the Dcount command but I don't think it fits my case..I know I have to write something in the AfterUpdate field of the dropbox but I just can't figure out what it is...
View Replies
ADVERTISEMENT
Oct 29, 2014
I have 2 form controls one a combobox and the other a text box. The text box select the site (txtLocation) where the user can enter part of the name of the site and all sites with those characters are returned. I've done this by adding:
Code:
Like [Forms]![frmSearchDB2]![txtLocation] & "*"
into the criteria on the Site field in the query design editor.I also want the combobox to have an affect on the query. I want it to query on user status. However if the combobox reads "All Users" I want it to return all status's and all null values. In the criteria field I put:
Code:
iif([Forms]![frmSearchDB2]![cbxUserStatus] = "All Users",like "*",[Forms]![frmSearchDB2]![cbxUserStatus])
It kind of works but no null values are pulled back. Should it be an expression?Do I need to do it in VBA?
View 1 Replies
View Related
Feb 26, 2014
I have a table listing about 20 elements as field names eg FE, CR, NI, TI and so on.
I have built a form which has a combo box listing these elements by selecting "fields" in the property settings of the combo box & next to this combo box i have 2 text box's where the user can input Min & Max values to pass on to the query.
E.g., FE (chosen from the combo box) value between (Text box1) and Text box 2.
I can run the query to give me values between the 2 text box's by using the following formula in the criteria (Between textbox1 and textbox 2).
The issue i have is to be able to select the element from the listbox, input the min & max values identified and be able to pass this to a query so the query can filter based on the field and values passed?
View 3 Replies
View Related
Mar 5, 2014
I have a database which has a table for the quotes prepared, each record has a quote amount. In a query linked to that table I have the ability to get just a specific month view which shows all totals in that month. I want to calculate a grand total for the amounts shown in quote amount:
Record 1 : 100.00
Record 2 : 100.00
Record 3 : 50.00
Grand total : 250.00
Then I want to display this grand total on a form which is visable on a screen based in the office that has an auto refresh on it so after 2 minutes any new records added to the table will increase the grand total on the form on the screen.
I have worked out how to do the totals bit, but now I need getting this figure (I.e. 250.00) on a form without having to run the query and have it looking in the background.
View 3 Replies
View Related
May 5, 2015
I am trying to create a query that takes values from a search form and provides records. I was having issues with getting results when some boxes on the form were left blank. I found a solution to that and it worked with a small number of fields. However when I make the full form query (about 8-10 fields) and run it says the query is too complex. I wrote the sql as I could not get designer to do what I wanted. Attached is the sql that works and that which is "too complex".
View 9 Replies
View Related
Jul 22, 2014
I have a form (DropDown form) that has 3 drop down fields, you select your values from the drop downs and you would push a command button that runs an event procedure which runs a query (DropDown qry test). The user should have the option of picking any combination of fields to filter by. Or no combination, which would return all values in all fields. So I am basically using the form as parameter's for the query.
The problem I'm having is that my query is returning values for one field AND values for another field. Even if the other values selected are not in the same record. It's not combining the fields together to filter. For example: you pick a Project name and Supplier name, the query will return records that have the project name you selected but it will also return records with the supplier name you selected that have a different project name.
I've attached screen shots of the form and the design view of the query (the screen shot cut off the last column name. It is meant to say "Expr3: [Forms]![DropDown form]![Combo7]").
Using Windows 7,
Access 2010
Is there a way to select multiple values from the drop downs?
View 14 Replies
View Related
Sep 28, 2014
I have a form that has a check box on it. For some reason the check box is not being checked when an update query is run prior to load. I issued the 'Me.Requery' command after the update query is run (via 'On Load' and 'On Open' in the 'Event' property . . . neither worked) of the form. Is this the correct way to refresh a form after an update query is run ?
View 8 Replies
View Related
Oct 14, 2013
I am making a booking system where a user enters
StartDate, EndDate (Form Header)
House , Room , UserID (Form Footer)
The Header and footer are not linked. The Footer simply displays all the existing bookings for said ouse/Room/Date combination. (Date being all dates between the StartDate and EndDate) BUT (surprise, surprise)users don't look at this to check if a booking already exists.
Also - It seems pointless to have the users enter the same data (House, Room, UserID) in 6 times (one for each day that they want to book the room.
So I am trying to automate the process.
Therefore, I append each 'new' record (that the users adds in this session) into a temporary table. (House / Room / UserID / StartDate)....note, no end date because I need a separate record for each day.
I have a query that is supposed to check the EXISTING bookings with the temp table.
My code (paraphrased here for simplicty) says
do while StartDate < EndDate
- if Qry_CheckForClash returns 0 records then '(uses a dlookup)
- append from TEMP to BOOKINGS.
- - else
- compile an alert message to the user (appending the Room No and Date each iteration)
- In Qry_CheckForClash, increase the StartDate by +1
- end if
loop
My problem is that after the last step (increase StartDate by +1) the call to Qry_CheckForClash still returns the initial StartDate. But when I check the TEMP table, the StartDate has changed.
So how do I REFRESH or REQUERY a query that is not associated to a form or report?
View 8 Replies
View Related
Jun 18, 2013
I'm trying to determine the SQL to return only those records in a table which have duplicate values in each of two fields, but different values in a third field. Here's an example:
Code:
AcctNum FoodType FoodClass
------- -------- ---------
A123 Apple Fruit
A123 Apple Fruit
A123 Grape Fruit
A456 Potato Vegetable
A456 Potato Perishable
A789 Carrot Vegetable
A001 Banana Fruit
For the above table, I'm trying to return records which have multiple entries for AcctNum + FoodType, but DIFFERENT values for FoodClass. So for the above table, the query would return:
Code:
AcctNum FoodType FoodClass
------- -------- ---------
A456 Potato Vegetable
A456 Potato Perishable
It returns these two records because there is more than one record with for the AcctNum + FoodType (i.e. 'A456' + 'Potato'), but DIFFERENT values for FoodClass (i.e. one record has 'Vegetable' while the other has 'Perishable').
View 5 Replies
View Related
Mar 28, 2005
This should be fairly simple, but it is driving me nuts trying to get it to work right. I feel like I'm chasing my tail.
Anyway, I have a query with three fields: "PersonnelID", "QualificationID", and "QualificationStatus". This is a query off of my large table for tracking qualifications; however, I am using a form with three combo-boxes for the same information. The first two combo-boxes (personnelID and QualificationID) are used to filter the query. This gives the result of one and ONLY one row in the query. A unique personnelID number, a qualificationID number and the qualification status of the record in question. I WANT to be able to automatically retreive the last field (qualificationStatus) from the query and display in the form (and then save in my new table - I know, shouldn't save calculations and such).
I have it working somewhat. I can select the first two combo-box fields and then when I click the third combobox for qualificationstatus, I only have one item in the dropdown menu and then I select it. So far, so good, but this only works for the first record that I am working on. If I do another record without closing the form first, the combobox selection for qualificationstatus still shows the previous entry. However, if I switch the form to design mode and then back to form mode, the combobox shows the correct value. I just can't seem to get the form to refresh prior to selecting the third combobox. I have tried "DoCmd.RepaintObject" after update on the second box and on focus for the the third box, but it still doesn't work unless I switch the views back and forth.
Any suggestions would be ever so gratefully appreciated...
Brian :eek:
View 1 Replies
View Related
Jul 1, 2013
I have a table Pmt in Ms Access with following information:
Table Pmt
Cust no Inv no T no Date Amt
123 ABC T1 20110131 1000
123 ABC T2 20130228 1000
123 ABC T3 20130331 1000
789 XYZ A1 20130131 2000
789 XYZ A2 20130228 2000
Fields "Cust no", "Inv no" and "T no" are in Text format, whereas "Date" and "Amt" are in Number.
My question: how can I concatenate row values using a query. The query results that I wanted are as follows:
Cust no Inv no T no Date Amt
123 ABC T1, T2, T3 20130131, 20130228, 20130331 3000
789 XYZ A1, A2 20130131, 20130228 4000
View 2 Replies
View Related
Mar 11, 2014
I want to add a parameter to a query that lets the user enter a number to filter on the 'Top Values' section of the query.
I.E the query shows sales by product, the user enters 50 and it shows the top 50 records.
View 1 Replies
View Related
Aug 14, 2015
I Want to Create an Query to Find Different Values in A Table/ Query
Like
Month Year Name School Post
Jan 2012 Ankur School 1 Post 1
Feb 2012 Ankur School 1 Post 1
Mar 2012 Ankur School 2 Post 1
Apr 2012 Ankur School 2 Post 2
May 2012 Ankur School 2 Post 2
June 2012 Ankur School 3 Post 2
Now i want in result of Query is only like
Month Year Name School Post
Jan 2012 Ankur School 1 Post 1 (Starting Ledger)
Mar 2012 Ankur School 2 Post 1 (Change in School)
Apr 2012 Ankur School 2 Post 2 ( Change in Post)
June 2012 Ankur School 3 Post 2 (Change in School)
View 1 Replies
View Related
Dec 18, 2014
I have created a query that counts the AppSizerPerUD for every User Drive (UserDriveID). Now I want to assign these values to the other query (UserDrive Usage) for every UserDriveID. So I want to create a new column in the UserDriveUsage query that will contain the AppSizePerUD. In this case in the first two rows must be 23.6, then one row 18.38, the third one 45,39 etc.I need something like a VLOOKUP in Excel. I have tried DLOOKUP but probably I am not uisng it correctly.
View 3 Replies
View Related
Aug 2, 2014
I have a table that has a list of tasks and checkboxes attached to them to be checked once the task is completed. I need to run a query on the table that will only bring me back the tasks with a completion that is false.
Everything that I read online indicates that this is a difficult task for access. Maybe I can accomplish this in SQL view instead of design? If I put false is all of the yes/no fields, the query brings back nothing.
View 14 Replies
View Related
Nov 6, 2014
I am trying to create a query to append new records from my NEW database into my old excel database.....
The old DB has 4-5 extra tabs that the NEW database does not have so when I append, in those extra columns the new database will just have blank records since the column doesn't exist.
Usually I do a append query in design view. but sometimes it gets funny because it creates duplicates...
How would I go about it, so its quicker and persistent like creating a macro excel.
View 1 Replies
View Related
Sep 3, 2013
I have a query which uses values in two hidden text boxes, in order to populate a sub form.Unfortunately some times the data in the text box contains brackets within it as follows:
'120/60 ZR17 (55W)'
When this occurs the query returns no data, even though records with a matching code exists in the table I am working with where Speed is 'FR'.
Code:
WHERE (((stockdyn.SPEED)="FR") AND ((stockdyn.DESCRIPN) Like '*' & [forms]![frmSearch]![Text10] & '*'))
How can I get around this, as surely if I used quote marks it would take "[forms]![frmSearch]![Text10]" as the value I am searching for.
View 3 Replies
View Related
Jun 21, 2014
modify the code below to Show the LocationName in the Schema Column instead of the MPID? I attached a pic showing the relationship between the two tables which contain the data I'm trying to query.
SELECT [Locations Query].LocID, Qry_MPLoc.MPID AS Qry_MPLoc_MPID, [Locations Query].Location, [Locations Query].Schema
FROM [Locations Query] INNER JOIN Qry_MPLoc ON [Locations Query].[LocID] = Qry_MPLoc.[LocID];
View 11 Replies
View Related
Dec 20, 2013
I have employee attendance in Excel i.e Emplpyee Number, Day1, Day2, Day3....Day31
I have a table in access that have columns Employee Number, Day1, Day2, Day3...Day31, Presents, Annual Leaves etc
Excel file data needs to be transferred in Access table but the number of Presents, Annual Leaves etc needs to be calculated in Access during transfer not in Excel.
Now due to limited knowledge I do not understand how to resolve this issue and how to start. How to calculate the number of Present and Leaves.
View 3 Replies
View Related
Jun 1, 2013
I have a manufacturer that has sent me payments from 8/2/2012 to 05/20/2013 but there was a wide gap where we did not do business from the end of 2012 to April 2013.
How can split the date ranges up in the query so when I create my report, I can get two ranges for the same manufacture.
For example one list of payments from <#12/31/20112# and the other from >#4/1/2013#
View 5 Replies
View Related
May 8, 2013
I created a simple calculation query to add the values of three fields:
Program_Cost, Auditorium_Cost and Millage_Fee.
I followed the steps found here: [URL] ....
But it doesn't work. The query pulls the values for the relevant fields but doesn't actually calculate the total. What am I doing wrong? Here's the query's SQL:
SELECT [Event Information].Event_ID, Sum([Program_Cost]+[Millage_Fee]+[Auditorium_Cost]) AS Total_Cost, [Event Information].Program_Cost, [Event Information].Auditorium_Cost, [Event Information].Millage_Fee
FROM [Event Information]
GROUP BY [Event Information].Event_ID, [Event Information].Program_Cost, [Event Information].Auditorium_Cost, [Event Information].Millage_Fee;
View 2 Replies
View Related
Aug 30, 2014
My problems is that when I use below setup without the "Notes" tabel, they query works fine, and returns the amount of spares and time used by a single WBS ID. (Customer). But when I add another tabel and link it together. Its returns load of lines with the same data - it looks like its copying its self, over and over again.
View 4 Replies
View Related
May 13, 2014
I have a text field that contains a comma delimited list of numbers.
I am trying to read one of these numbers from OLEDB through to an access database.
I am trying to do the following:
SELECT SPLIT(CSVData, ValueNo) as ReturnValue FROM MyCSVDataTable
Where CSVData is a split field containing values like "123,234,345,..." and ValueNo is a value to identify which one i want.
Any way of achieving this using the standard SQL functions. I am reading the data via OleDB and therefore cannot use a user defined VBA function?
View 1 Replies
View Related
Jul 2, 2015
What I have a a form that my lab supervisor would use. That person selects the records that are to be modified and assigns work, completes work or otherwise updates the status of the records. One of the options is to mark a record as not having a sample here if it has already been marked as having been here. So essentially, I want to modify the record to change the sample arrival date and sample number field (which is a foreign key field) to null or empty. I have created a delete query that deletes the corresponding record just fine.I just am having difficulty updating the two fields mentioned above. When the supervisor selects the record the primary key for that record is also picked up so it is easy to know exactly what record to adjust. The query returns the information to be updated just fine, it just doesn't do that. Here is the sql of the query. As I said, I don't care if the fields are empty or null. Also the fields aren't required.
Code:
UPDATE TestRequestTable SET TestRequestTable.SampleLocation = "", TestRequestTable.SampleArrivalDate = ""
WHERE (((TestRequestTable.TestRequestNumber)=[Forms]![LabScheduleForm]![TRNumberCombo]));
View 3 Replies
View Related
Sep 15, 2014
I have a table of standard Circuit Breaker (CB) sizes. I then calculate a minimum CB size in a query field. I want to use that calculated minimum value to look up the next largest CB size from the table and fill a field in the query with it.
For now, I am going to add a field to the CB size table with the smallest size CB which would be assigned that standard value. I will then use a Dlookup with conditions of greater than "smallest size" and less than "standard size" fields from the CB size table.
I think this will work fine, but there must be a better way.
View 12 Replies
View Related
Apr 11, 2013
I'm trying to create a query that can sum values of different fields in different tables...Can I sum values of a field and put the result into another field in different table?
View 3 Replies
View Related
