Queries :: Filter Records Depending On Subform Value?
May 20, 2013
I have a database that tracks Construction projects. Each project can have multiple Managers, so I have a many to many relationship set up with tblProjects, tblProjects_Managers and tblManagers. I created frmProjects and sbfrmProjects_Managers. sbfrmProjects_Managers has a combo box control and is continuous. Its record source is tblProjects_Managers, its control source is ManagersID (the foreign key in tblProjects_Managers) and its row source is SELECT tblManagers.ManagersID, tblManagers.Managers FROM tblManagers ORDER BY tblManagers.Managers;. The bound column is 1, the column count is 2, and the widths are 0";4". When adding sbfrmProjects_Managers to frmProjects I made the child-master link with ProjectsID, the PK of tblProjects and the other foreign key in tblProjects_Managers.
I need the Managers to be able to do a search that will return only the projects that they manage. I've tried making a query composed of tblProjects, tblProjects_Managers, and tblManagers. I used the field ProjectsID from tblProjects and ManagerName (with the criteria Like [Manager's Name] & "*") from tblManager. The query worked when run by itself, but when I use it as a filter in a macro to open frmProjects, I get two prompts to enter a value, one that asks for tblManagers.ManagerName and one that asks for Manager's Name. No matter what I do I can't get only the second dialouge box to appear, and regardless I no matter what I enter into the two dialogue boxes my results aren't filtered at all.
If I filter the date, e.g., from April 1st to 30th, I want to get as a result 4 records, one of each client and only the latest record from CLIENT 3 (04/22).
If I filter the date, e.g., from April 1st to 20th, I want to get as a result only the records of clients 4 and 3 from 04/16 (as every other contact is from beyond 04/20).
If I filter the date, e.g., from April 25th to 30th, the return must be only the top 2 records (the only ones after April 25th).
I tried using a query grouping the totals on "process key" and the other cells set as "first", but if I filter from April 1st to 20th, for example, no records from CLIENT 3 gets shown, since Access processed the "first" record request before the date filter, and therefore only considered the contact from 04/22, and as it is later than 04/20, wasn't shown on the query.
I have a combo box located on one of my forms, that is getting its information from a field in a table.This table has all its records displayed in a subform. I would like the combo box to basically requery the subform to only show records where the name matches what is in the combo box.
It is basically a list of customers, so if I was to select Person1 in the combo box, I would only want to see records for Person1 in the subform.I would also need a way of clearing the search results, in order to get the subform to display all records again.
I have a form wich includes listbox of employees. When I click on them, it shows his or her data (date of birth,address,etc...). For every employee there are also three diferent checkboxes - if he is regularly employed, temporaly employed or student.So now what I need is to filter listbox of employees - only student or only regular or only temporar or all.I was wondering if I can do that with combobox, but I really dont know how. I am prety new to VBA.
It have several fields, and the fields that I want to filter is ItemNo and SellingPrice.The problem is ItemNo has many different SellingPrice. I put a parameter in SellingPrice field as >=75 only for ItemNo "49565", but still i want to show other record in ItemNo and do not have filter in SellingPrice field.
I have a few queries which are used to create reminder email on training which is due for renewal.Some training required reminder 6 months before 2 year expiry. I use this in the criteria for the training date within query:
Between DateSerial(Year(Date()),Month(Date())-18,1) And DateSerial(Year(Date()),Month(Date())-17,0)
Some training required reminder 3 months before 1 year expiry. I use this in the criteria for the training date within query:
Between DateSerial(Year(Date()),Month(Date())-9,1) And DateSerial(Year(Date()),Month(Date())-8,0)
My problem is with training requiring reminder 6 months before 3 year expiry. Using this criteria:
Between DateSerial(Year(Date()),Month(Date())-30,1) And DateSerial(Year(Date()),Month(Date())-29,0)
Doesn't show any results (although there is training which was done 30months ago, expiring in 6 months time).
Changing the -30 (months) and -29 (months) in above down to -22 & -21 shows records as expected, but anything below -22/-21 doesn't show any records.
I have a query that pulls scores for this month only for each class member. Problem is, I only need a count of these scores (per person) and because the date is in the query, it doesn't group the scores together and count them as one. i.e. it sees score 1 and score 2 as separate because they have different dates so they won't count together.
The only purpose of date in this query is to filter out only this month's dates. Is there some advanced query expression something or other that will tell it to leave date out of the count and only use it to filter?
In my simple database (attached), I need to mass duplicate Tasks and their Notes.
I have three tables: tbTasks (PK: Task_ID), tbNotes (PK: Note_ID), jtbTaskNotes (FKs: Task_ID and Note_ID). jtbTaskNotes is my many-to-many junction table that ties Tasks to Notes.
The main form (fmTasks), bound to tbTasks, has a subform (sbfm_TaskNotes) that displays notes associated with each Task. On themain form,you select which Tasks you want duplicated via a checkbox. The append query (quCopyTasks) will duplicate all tasks that have the checkbox checked. All good there. However, I can't figure out how to also duplicate each task's Notes.
I found Allen Browne's solution [URL] ....., but that only handles duplication of one record at a time, whereas I need to duplicate many records at a time (sometimes 10+ records). How do I go about duplicating multiple Tasks and their associated Notes?
Before you ask "why are you duplicating records?": There are times when tasks need to be re-accomplished and therefore need to have a new record. It's easier to duplicate records than it is to hand-jam everything again.
I want to filter the records in a combo box (cboSupplier) using the In() function.
On the form I have an unbound control called "intFilter". It contains the following string: 23, 58
The SQL statement for the combo box is
Code: SELECT SupplierID, SupplierName FROM tblSupplier ORDER BY SupplierName;
In the criteria for field SupplierId I want to use the In() function so that the only records returned by the combo box are those in "intFilter". I have tried
Code: In (Forms![frmSupplierReport]![intFilter]
but this does not work.
If I put: In (23, 58) in the criteria it works, but I cannot hard code it because the string in "intFilter" will change on the fly!
I m trying to make form which filters my records and generates a report..here's where I am
Code: Like "*" Or Between [Forms]![Form1]![Text6] And [Forms]![Form1]![Text8] & "*"
but this doesn't work I would like to show all records if textbox 6 is null and textbox8 is null this part of code works perfect but below but I'm struggling to get the between in with the code
I am trying to make a crosstab query to filter my records from my table.
Here is the scenario.
I want to make a query that will return me my Rep ID, Rep Name, his Bonus and his GV-Q (another value) based on every month.
Now I make a crosstab query and here is the syntax.
Code: TRANSFORM First([TBL Qualification Data India].[Bonus Rank]) AS [FirstOfBonus Rank] SELECT [TBL Qualification Data India].[Rep #], [TBL Qualification Data India].[Rep Name] FROM [TBL Qualification Data India] GROUP BY [TBL Qualification Data India].[Rep #], [TBL Qualification Data India].[Rep Name] PIVOT [TBL Qualification Data India].Period;
This resulted in a column for Rep Number, one column for Rep Name and columns for all the period of Bonus I am going to have., so there are basically 9 columns for this till this month for each month and bonus value shows as values for all these month (period) columns.
Now in this same syntax, I want to have my Rep GV-Q value as well as his bonus to show in the same query, I read and came to know that it's not possible to directly have two values or two column headings in a crosstab query, I must have to make a new crosstab query and then use a normal select query to display records from these two crosstab queries, so I went ahead and made a new similar but with one value field changed crosstab query and here is the syntax for that.
Code:
TRANSFORM First([TBL Qualification Data India].[GV-Q]) AS [FirstOfGV-Q] SELECT [TBL Qualification Data India].[Rep #], [TBL Qualification Data India].[Rep Name] FROM [TBL Qualification Data India] GROUP BY [TBL Qualification Data India].[Rep #], [TBL Qualification Data India].[Rep Name] PIVOT [TBL Qualification Data India].Period;
Now after this how to make a select query to show the data from these two queries.
I can make a normal query based on these two crosstab queries and manually add all fields and then I would have my result but then after every month I have to manually enter these two extra month details from both crosstab queries to my final query and that's not what I want.
Is there any method to do this by gathering data from these two queries into one and achieve the result I want or if there is any other approach to tackle this.
To explain my database and my need for output, I am attaching few pics to make things easier if I made some mistakes in explaining my problem. It's included in attached zip since I am not able to post images or links.
I have a Table table_clients and a connected table (1-->many) table_invoices.
I have two forms:
form1(table_clients): all clients with subform1(table_invoices): all invoices for every client
the form2 and subform2 are the same, just objects are placed differently on page.
I want when I have opened a record on form1/subform1, to be able to click a button and open form2/subform2 filtered by the invoice_id and the client_id.
I am creating a database to manage multiple mobile devices. The table structure is complete and I'm trying to build a set of forms.
Simplifying things, I have tblUser, tblSim and tblDevice. I also have a tblJoin where you can select a UserID, SimID and DeviceID to create an item of inventory.
I have created a parent form (frmCreateInventory) base on tblJoin, so it has JoinID and combo boxes for UserID, SimID and DeviceID in the lefthand side.
I have also set up 3 sub forms sfrmUser, sfrmDevice and sfrmSim, based on their respective tables, which I want to display on the right hand side of frmCreateInventory.
I'd like to be able to create a subform control in frmCreateInventory, but change the subform it displays based on what combo box is clicked, e.g. if I click the UserID control, the subform displays sfrmUser, and the relevant data based on the UserID that is linked to the JoinID. if I click the DeviceID control, the sfrmDevice is displayed and the relevant info etc. etc.
Is this possible? if so, how would I go about doing this?
I'm trying to hash two scripts I've found into 1 functioning filter, however I'm still relatively new to vba and can't figure out how to get this working.
I'm trying to use Allen Browne's Search Criteria:
with another snippete of code I found here:
Code: 'Purpose: This module illustrates how to create a search form, _ where the user can enter as many or few criteria as they wish, _ and results are shown one per line.
[Code]....
It's the date part I'm having trouble with, the rest of the search criteria work fine without the date, but I can't get it working when I try to modify and merge the date sections of each code.
Also I'm using a listbox for the "Yesterday";"Last 4 days";"Last 9 days" and not a combo box.
How can I create a "Filter Button" on a form and filter my records? I create a textbox on a form and a filter button on the right. Then I click the filter futton, the filter function will search/match the content in the box through the datasheet. And then the results of the filtering will be pop up on the split form datasheet.
Hi I'm a real newbie at this but basically I have a form displaying three lots of contacts Expediting, Quotation & Miscellaneous contacts.After choosing the company name from a list box it displays the Expediting, Quotation & Miscellaneous contacts info - but doing it this way there isnt much space on the screen to show all the Contact names,Telephone fax & email addresses clearly& at a decent size.Apart from the company list box I want to have a list box/combo box with:
Expediting Quotation Miscellaneous
and then after choosing Expediting, Quotation or Miscellaneous from the list/combobox i want it to take the relevant data from the Expediting, Quotation, or Miscellaneous contacts sections and only display the information relating to those sections only, therefore allowing me to have bigger text and not so cramped display so everyone can read it ok and get more info on the screen for each contact group.
If anyone could help it would be much appreciated!!!! Thankyou in advance for any help. Be gentle with me I am a newbie,lol.
I need to add new records to a table depending on what the cbo box displays. I figure this is the way to do it but I it's not working, I have only writen the first select,
Private Sub Comando6_Click() Dim dbPalavraChave As DAO.Database Dim rstchaveCFEPC As DAO.Recordset Set dbPalavraChave = CurrentDb Set rstchaveCFEPC = dbPalavraChave.OpenRecordset("chaveCFEPC")
I am working with Access 2010, on vista. What I have is a query made up of two tables, one product the other inventory. (see below) query.jpg
In the product table i have a field called "minimum reorder level". In the inventory table i have two fields one called "number in stock" and "number on order". What i want to happen is "number on order" to be filtered by the result, if the "number in stock", is less than "minimum reorder level", if it is, have the result placed in the "number on order" field. EG. if the "number in stock" = 2 and the "minimum reorder level" = 5 then 3 would be placed in the field "number on order" and only the second record from the query would be visible (see below) Query result.jpg The result of this would mean that the field "number on order" would be populated with the result and the and query would also use this to filter the record.
I am in the of designing an access 2010 database for data entry. Is it possible to create a button on a form in which a prompt asks a user for which records to export. Then depending on the entry export specific fields (First Name, Last Name, DOB) to a specific excel 2010 workbook (setup) and worksheet (template)?
For example, if the user entry is 1, only record 1: First Name, Last Name, DOB is exported to the setup.xlsx (more specifically the template worksheet of the setup.xlsx). However if 2,3,4 are entered then records,2,3,4: First Name, Last Name, DOB are exported to the setup.xlsx (more specifically the template worksheet of the setup.xlsx).
The following code should sum a field depending on the date. The field IncidentYear is created and set to Last, This or "-" depending on the date. I should get three rows, one for each of the three values..
I get..... you tried to execute a query that does not include the specified expression as part of an aggregate function
I'm sure I've missed something obvious and I can't see it! If I remove the group by clause and the sum field..... it works.
SELECT IIf([Incident Date]>=#01 July 2012#And [Incident Date]<=#30 June 2013#,'Last', IIf([Incident Date]>=#01 July 2013# And [Incident Date]<=#30 June 2014#,'This','-')) AS IncidentYear, Sum(tbl_2c_Acc.[Vehicle Off Road Time]) AS VOR_Acc_Days FROM (tbl_2c_Acc LEFT JOIN tbl_Value_Proposition ON tbl_2c_Acc.Lessee = tbl_Value_Proposition.[Lessee No]) LEFT JOIN tbl_Vehicle_Type ON tbl_2c_Acc.[Vehicle Reg] = tbl_Vehicle_Type.[Reg No] GROUP BY IncidentYear;
I have a form where we fill in information for supply of PPE to employees.
Each item must be signed for on a printed report.
I am encountering problems trying to create enough rows for my report detail for each signature of the number of items supplied.
For example, on the form I will select the "equipment" - 4 hats supplied and 3 boots. On the report I want the equipment set as the group and the detail to be a number or rows which equals the number of selected items. therefore under the Hats group heading I want 4 blank rows which are made up of 3 text boxes - Print Name, Signature & Date and another group heading for boots but with 3 lines.
The Query gives the results below: (Item name is linked to a PPE table containing Item ID, Item Name, Cost etc..)
IssueIDDateProvidedItem_Name AmountIssued 0001 01/11/2013 Gloves (Orange) 10 0001 01/11/2013 Hard Hats (Black) 2 0002 02/11/2013 Hi Viz (Large) 5
The report I aim to generate from this should look as attached ...
The query/report is set up as a parameter report so I will enter the issueID such as 0001 and only those items will appear on the report to print and sign.
