Looping Through Results Of A Query To Run A Report
May 29, 2013
I have a pretty complex database that uses several queries that all lead to a final query with a parameter (order number). I have a report linked to that query so every time I open the report it asks me for an order number. Once I enter the order number the query is run and I print the report. I am curious if I can create a query that pulls out order numbers (possibly for a date range) and then have a macro or something loop through that query and print a report for each order number. So if the query returned 50 order numbers then the report with the parameter (called Final results) would be printed 50 times.
I want to automate the distribution of individuals Vacation Balance and Usage via email. The data is being pulled from 2 tables related by an Employee ID#. tbl-Employees provides the Header Info for each employee and tbl-VacLog provides the detailed Usage data.
I want the output to appear as follows in the body of the email:
Name Start Date Vac. Bal TotVacToEOY Personal Bal. John Doe 1/1/99 120 160 8
Usage Date Hours Reason Code 1/1/13 8 V 2/15/13 12 V 3/6/13 8 V
I've got a handle on creating the email and sending but where I'm having trouble is in making the link between the two tables with the Employee ID# and printing the corresponding detail data with Parent Record.
Code below...
Option Compare Database Sub SendMails() Dim DB As Database Dim RecordSetA As DAO.Recordset Dim RecordSetB As DAO.Recordset Dim TotalRecordsA As Integer
I am relatively new to writing databases in access, I did some a long time ago, but cant remember what on earth I used to do.
I have created a database with all the information I need it and have a query set up that gives different results depending on the information the user has entered
The user decides what the query will display though a form.
I want access to generate a report with the information in it that the query chucks out.
However the information the query gives changes every time depending on the user input.
I have a query that prompts the user for input to generate a report. I would like to in essence copy that same record set and append it to a different table (archive table).
I have a query that pulls up information on employees when they receive warning notices. I would like the query to give me some type of warning (report, email, etc.) when an employee has three or more notices.
I have a report that displays the results of a query. The query and the report are both run from a submit button on a form. I use DoCmd.OpenQuery "name", followed by DoCmd.OpenReport "name". Due to slow network connection the query shows up before the form is displayed. I would like the query to be hidden or minimised. In other words, I do not want the user to see the results of the query, only the report.
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 currently have a combo box (combo121) on a form. I select the "Company" which is connected to "Company" in the query via [Forms]![courseinfo].[combo121]..That is working. When I press the "query" button on the form the selected company in the combo box is updated in the query and it updates the report templates.I also want to use the same system to populate a different report, using the same query. This works a bit differently.
Once the company is selected in the first combo box(combo121). I have other combo boxes which bring up students attached to that company. I have multiple combo boxes because I need select multiple students for the one report. These students are in fields "First Name" and "Last Name". So in the query I can only have those fields once. I have several links to the several combo boxes under "Last Name" as that is the "bound" selection for the combo box:
Problem I am finding is this is bringing up several lines in the query. So when I try to populate the report with all of the different Students I have selected for the query, it won't work properly because I can only put "First Name" & 'Last Name" fields on the report once.
Hi, not sure looping is the correct word so i will try to explain what i am trying to do.
i have a tbl and need to mark the records in groups of 12. the first group would be in group 1. The next group of 12 would be 2. and so on. the highest number is unknown as the data will grow.
is this posible with a qry? or do i need a for next loop and run a qry each time? if i do that how do I incriment the to the next level.
I am trying to add another record to a table. Pid and nsn are the keys. the table has 70 unique pid's. I need to add another nsn to each of the pid's. I tried the following code but no go:
Set db = CurrentDb Set rst1 = db.OpenRecordset("tblHandReceipt") Set rst2 = db.OpenRecordset("tblHandReceipt")
rst1.MoveFirst Do Until rst1.EOF rst2.AddNew rst2!PID = rst1!PID rst2!NSN = Forms!frmparts!NSN rst2!ISSUEDSOURCE = Forms!frmparts!subfrmEndItemParts.Form!cbxENDITEM 'Add like above line for each field rst2.Update rst2.MoveNext rst1.MoveNext Loop rst1.Close rst2.Close
I have built a custom search form in a MS Access 2010 database so that users can find specific records to edit. After entering the search criteria and hitting a Search button, another form opens up that shows the search results. This second form includes a command button for generating a report of the search results.
Right now, the custom search form and the search results form are both working properly, but the search results report is showing every record in the database instead of just the search results. This is true whether I access the report via the command button in the form or the navigation pane. I'm not sure if I need to correct my VBA code or the report's properties.
I have a database where part of it consists of an Item table and a Formula table. There is a many-to-many relationship between them so I created a junction table for this purpose.
Tables tblItem tblFormula tblFormula_Item_JNT
There is a column in tblFormula called [Priority] that is a lookup field. It has the values of "High", "Medium", "Low".
So here's what I'm trying to do: I have a query that searches against tblItem for all Items that have a value of No/False for a field called Item_Status. That's simple enough. But I also want to show the Priority of the Formula that the Item belongs to. When I add that to my initial query I get multiple results of the same Item if it belongs to more than one Formula, which I understand. But I really want an Item to show up only once.
I want the query to go through each Item (based on its Item_ID), look through all the Formulas it's in, and if at least one of those Formulas has a Priority of "High" then it will display "High" in a field (could be a calculated field) in the query results. If there are no "High" Priority Formulas, then look for if there are any "Medium" Priority Formulas, etc.
I have a report containing all the fields from my "main table". I want to create buttons on a from that will filter information from the report. For instance, I have a field titled "Priority". I want to create a button that will filter the report to only show records with a "Level 1" priority. Is there anyway I can do this using VBA?
Hey guys it's been a long time since i've been here but i again need your help
my job asked me if it's possible to have keywords highlighted in search results in report am i clear? meaning, when you search for keywords in one of the fields, and then your results come out in the report, can they be selected, like in Word, or in searches on the Internet, like when doing a search on Monster, all your keywords will come out in red, that way you can easily read the results
I have a database with some reports that show all records in a table. Some users want to only see certain rows, so they use filter option (clicking in the field, and using the funnel symbol feature at the top.
I have a print button, but I had only set it to print the report name. So when a user filters a report and clicks print, it still prints every record, instead of the filtered results that they have set.
How can I alter my VBA code for printing so that the button prints whatever results are shown? I don't quite understand how Me.Filter works, so every change I try still prints everything.
Right now its just back to
Private Sub btnPrint_Click() DoCmd.OpenReport "ALL REQUESTS", acNormal End Sub
I want to open a report with the results from a filtered form.
I want to use a similar format to the attached Allene Browne search2000 as the base to filter the records initially, but not sure how to get the filtered results into a report and the most efficient way.
I am trying to print a report based on the filtered results of a form where the data record source is generated from a query. What I have is five unbound comboboxes on a form that filter the results of the query on a subform which works fine in whichever combination I set, I then want the report to print out the results of the filter and the filter combination that I used - basically exactly as it appears on the form (I have used the same query / subform in the report with text boxes to show the filters used on the form). My VBA skills are quite limited (but improving!) and I have trawled the web trying different code examples but can't seem to get it nailed. Current filter code follows;
Code: Option Compare Database Option Explicit Private Sub PrntConfigReport_Click()
How to display only the first few records in a subreport but keep the grand total of the report itself. When I limit results in query; it gives me the records but only totals for that set. I thought about putting code in the on format in detail section like:
Code: If me.control.value >10 then me.control.visible =false
I use the follwing code to filter a report based on the listbox selection on a form. Below is the code I use, the problem it will error if the results have an apostrophe in the string.
Private Sub FilterDesc_Click() Dim strWhere As String Dim ctl As Control Dim varItem As Variant 'make sure a selection has been made If Me.ListCarrier.ItemsSelected.Count = 0 Then MsgBox "Must select at least 1 Carrier"
I used to queries ,1 to get items that are taken ( its all about sign in sign out for equipment) and other query is list of all items. How can i make 3rd query which will give me all but taken items from query1? (of course items from query 1 are in query2) thx in advance
I have a report with quite a few subreports in it. There are a number of calculated fields on the form, most of which use Dlookup to retrieve at least one of the figures required for the calculation. The Dlookup runs fine and the report opens but the calculated fields are devoid of data in Report view. When I switch to Print Preview view the fields are now populated. Below are two variations of the DLookup syntax I have used to try and alleviate this issue.
=DLookUp("[8]","qry_MonthlyTotalsByYearFirstAid","[ActivityType] = 'First Aid Injury (FAC) Reported in Safeguard'")/[sub_AllHours].[Report].[8]
=DLookUp("[8]","qry_MonthlyTotalsByYearFirstAid","[ActivityType] = 'First Aid Injury (FAC) Reported in Safeguard'")/[Reports]![rpt_AllFigures]![sub_AllHours]![8]
Note that the field [8] specified here is simply a month number and forms a column in the crosstab query for the corresponding query name.
I would add the query referred to in the DLookup to the source query for the report but the source report's data is derived from a Crosstab query, which only accepts one data field (Access terms this as the value field.
need help with looping to get a total for a specified time block. It needs to look at a time block and count the number of entries for a hour before to an hour after 7 am, 11am and 3pm on given 12 shifts. The list of shifts come from another sheet with the date and the time of the end of the shift.
I have the following which gives me a total for the 12 shift but now need to loop for the times given:
For rowz = 13 To 20 endtime = Sheets(sheetname).Cells(rowz, 1).Value begintime = endtime - 12 / 24 If IsDate(endtime) = False Or endtime > Now() Or endtime = Empty Then GoTo Exit End If
SQLstatement = "SELECT count(Field1), count(Field2),count(Field3), count(Field4), count(Field5), count(Field6)" & _ " FROM dbo.table WHERE [Date] Between '" & begintime & "' And '" & endtime & "' " rs.Open SQLstatement, , adOpenDynamic
If rs.EOF = False And IsNull(rs(0)) = False Then fieldcount= rs(0) + rs(1) + rs(2) + rs(3) + rs(4) + rs(5) Else fieldcount = "N/A" GoTo Exit End If
Hello all, thanks to Dennisk and Paul, I am able to move through my recordset and create a directory with each individual name. Now, I am attempting to "FileCopy" an excel template into each directory with the directories name.xls------got this fine.
Now I am attempting to copy and paste each individual's records by looping through the recordset RST1 and place that information into the appropriate directory/directory.xls file; close; and then save all workbooks.
Could someone point me in the right direction? Currenlty, the code is dumping all of the information into my first directory, and doing nothing for the rest of my recordsets.
Do I need another loop here? Example Do Until EOF RST1?
Here is the code:
Dim rst As DAO.RecordSet Dim DIRName As String Dim folder As String Dim Dir As String Dim strnewname As String Dim stroldname As String Dim Pause As Boolean Dim xlApp As Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet Dim blnExcelOpen As Boolean Dim strMacro As String Dim mysheetpath As String Dim rst1 As DAO.RecordSet Dim objdb As Database
' Processing each officer and creating a directory MkDir DIRName
Sleep 10 FileCopy stroldname, strnewname 'Name stroldname As strnewname Sleep 10 mysheetpath = strnewname blnExcelOpen = IsExcelRunning() If (blnExcelOpen) Then Set xlApp = GetObject(, "Excel.Application") Else Set xlApp = CreateObject("Excel.Application") End If
Set xlBook = xlApp.Workbooks.Open(mysheetpath, False, False) Set xlSheet = xlBook.Worksheets("Sheet1")
'Transfer the data to Excel xlSheet.Range("A2:r10000").ClearContents
Hello all, i am new so please bear with me. My question is: I have this access 97 database that opens with office 07 and I am TRYING to open the database and loop through it pulling data from columns in all tables that end with the word "Data". I am lost so your help is greatly appreciated!!
I have a series of fields on a form. Each field name is a number 1 thru 32. I use a loop to set field properties
Param = 1 For z = 1 To 32 If (Me(Me.ParamB & Me.Param).Visible = True) Then Me(Me![ParamB] & Me!Param).Visible = False Me(Me!ParamL & Me!Param).Visible = False Me(Me!ParamE & Me!Param).Visible = False Me(Me!ParamT & Me!Param) = Null End If Param = Param + 1 Next z
I also have on the same form a series of field whose names are alphabets "a" thur "t"
I would also like to loop thru these fields to do the same thing. Is there any way to do this? I have tried the following code but it doesn't work. I'm thinking I need to set up temp fields to reassign each of these fields a number then loop thru it. I havn't tried it yet. but I'm wondering if there is a way to loop through the alphabet.
Param = "A"
For z = "A" To "t"
If (Me(Me.ParamB & Me.Param).Visible = True) Then Me(Me![ParamB] & Me!Param).Visible = False Me(Me!ParamL & Me!Param).Visible = False Me(Me!ParamE & Me!Param).Visible = False Me(Me!ParamT & Me!Param) = Null End If
