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.

View Replies


ADVERTISEMENT

Modules & VBA :: Email Detailed Report By Looping Through Related Tables

Dec 9, 2013

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

[Code] .....

View 1 Replies View Related

Reports :: Run A Report From Query With Different Results

Jun 6, 2013

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.

View 5 Replies View Related

Use Query Results From Report To Make Table

Aug 2, 2006

Hello,

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).

Not quite sure how to go about it.

Thank you

View 5 Replies View Related

Counting With A Query To Give Results On Report

Dec 18, 2007

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.

Can someone help me with this?

- Thanks in advance!

View 10 Replies View Related

General :: Report That Displays Results Of A Query

Mar 16, 2013

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.

View 3 Replies View Related

To Display Parameter Query Criteria On Report Even For Nil Results?

Nov 10, 2005

Hi,

Need advise on how to display on my report the criteria that i had specified in the parameter query even if the result is nil.

How can this be done??

Thanks!

View 5 Replies View Related

Query Showing Different Results When Moved To Chart In Report

Dec 23, 2014

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;

View 1 Replies View Related

Multiple Query Results Not Populating Report Properly

Sep 29, 2011

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:

[Forms]![courseinfo].[comboname1]
[Forms]![courseinfo].[comboname2]
[Forms]![courseinfo].[comboname3]

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.

View 4 Replies View Related

Looping Query

Aug 3, 2006

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.

Thanks.

View 14 Replies View Related

Looping Query

Oct 31, 2006

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

Thanks for any help.

View 3 Replies View Related

Reports :: Search Results Report Shows All Database Records / Not Just Search Results

Apr 29, 2014

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.

View 4 Replies View Related

Queries :: Looping Through Query To Find Highest Priority Item

Aug 20, 2013

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.

View 2 Replies View Related

Reports :: Filter Report Results With VBA?

Jun 7, 2013

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?

View 3 Replies View Related

Highlight Keywords In Search Results In Report

Aug 5, 2006

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

so can this be done in Acess?

View 11 Replies View Related

Reports :: Printing Filtered Results That Have Set In Report

Aug 5, 2015

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

View 6 Replies View Related

Forms :: Open A Report With The Results From A Filtered Form?

Nov 25, 2014

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.

View 8 Replies View Related

Reports :: Printing Report Based On Filtered Results Of Form

Jun 26, 2013

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()

[Code].....

View 3 Replies View Related

Modules & VBA :: Display Some Results In Subreport But Keep Grand Total Of Report Itself

Oct 25, 2013

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

but doesn't work.

View 4 Replies View Related

Forms :: Filter A Report Based On Listbox Selection - Apostrophe In Results

Feb 17, 2014

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"

[Code] ....

View 7 Replies View Related

Query Results Minus Query Results = New Query?

Apr 1, 2008

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

View 7 Replies View Related

General :: Results Of DLookup Not Showing In Report View But Are Visible In Print Preview

Jun 25, 2013

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.

View 4 Replies View Related

Looping

Jan 16, 2007

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

Thanks for all the help,
SLH

View 1 Replies View Related

Looping Cont

Mar 16, 2007

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

Dir = Me.Combo19
folder = "H:" & Dir & ""

'DoCmd.OpenForm "frmWait"
'DoCmd.RepaintObject acForm, "frmWait"

Set rst = CurrentDb.OpenRecordset("tblofficers")
Set rst1 = CurrentDb.OpenRecordset("tbl_Q1")

Do Until rst.EOF
DIRName = folder & rst!OFFICERS_NAME
stroldname = "H:Asset Verification Template.xls"
strnewname = folder & rst!OFFICERS_NAME & "" & rst!OFFICERS_NAME & ".xls"


' 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

xlSheet.Range("A2:r10000").CopyFromRecordset rst1
'rst1.Close
xlBook.Save

' back to the top...

xlApp.CutCopyMode = False





rst.MoveNext


Loop
' if we started Excel, then close it now…

If (Not blnExcelOpen) Then
xlApp.Quit

End If

' clean up...
Set rst1 = Nothing
Set objdb = Nothing

Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

View 3 Replies View Related

Looping Thru DB Tables

Aug 10, 2007

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!!

View 7 Replies View Related

Looping Thru Alphabet

Apr 10, 2008

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

Param = Param + 1
Next z

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved