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 Replies
ADVERTISEMENT
Apr 4, 2014
I have VBA to send multiple reports to a group of people depending on a toggle button selection. what I'm wanting to do is have it loop through the e mail process until all 5 toggle buttons have been selected and the reports have been E Mailed. Is this possible. Toggle buttons are named toggle1, toggle2 and so on.
View 4 Replies
View Related
Sep 4, 2013
I have a table that holds company information for the user - eg farm name, company name, manager, phone number and email address etc. Its simply used to provide headers for reports, so that the same generic database can be used on several farms. There is only one record in this table tblfarmdetails.The second table tblorder is for orders and receipts - I currently have code that allows the user to send an order request by email to the manager, and to also send notice of receipt of goods through to the manager. I would like the code to get the email address from the unrelated table.Can I reference this field in the vba?
Private Sub cmdemailorder_Click()
Dim orderdate As String
Dim stafford As String
Dim item As String
Dim itemamnt As String
[code]....
View 8 Replies
View Related
Nov 7, 2005
Folks,
i have a report that i designed to capture some detailed information and my users say its too detailed and they cant use it that way,so they want it scaled down to that very brief format as in the attached pdf file.I conquer with them but i cant seem get beyond what i did as every table seems to be a sub table of the another thus the cause of grouping and sectioning.
Has any one done something like this before!!, or are there any ideas,tricks, examples i would look at as to meet this requirement!!!
Evey Idea is highly appreciated.
Thanks in Advance
Kea
View 3 Replies
View Related
Jun 13, 2013
I have a parameter query for looking up a specific lot number in my database. The lot number table has 4 related material tables that deal with material issued, reworked, and rejected for that lot number. They lot number table is in a one to many relationship with the related tables.
I have a report based on this parameter query with 4 subreports. I want the report to display the lot number data and sub reports to display the related data in the material tables (all linked by the lot number defined in the parameter)
View 1 Replies
View Related
Jul 17, 2013
Using Access 2007 and Lotus Notes 8.5 ...I've got some code to output a report to email as an attachment: it looks like this
DoCmd.SendObject acSendReport, "rep09emailnotification", acFormatRTF, "(Recipients)", , , "email subject", "email body text", 0
For MOST of the people who use the database, this works fine, and they send the email with the RTF attachment which all the recipients can open - but for some users, it generates a .tmp file which if you 'Open' it is just a load of gobbledegook, but it you 'View' it looks ok - but is truncating the report.what could be causing Access 2007 to create .tmp files rather than .rtf?
View 5 Replies
View Related
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 1 Replies
View Related
Nov 16, 2014
With this very simple command, I have the ability to send emails to several clients, reporting many things, quotations etc.
Private Sub Command84_Click()
DoCmd.SendObject , , , , , , "SOMETHING", [SalesGroupingField] & "" & [1], True, ""
End Sub
Now, i want, on the body of the emai, to display many records, it is from Nothwind, how can i display on the body of the email all records from this report?
View 10 Replies
View Related
Aug 9, 2013
A command button on a form that can:
1- Open the Report called : ConsentForm
2- Print it to PDF printer (report is currently defaulted to that printer driver)
3- Save to folder: C:SOSConsentForms
4- Save as: Consent_[Last]_[First] (these are field names on the form)
Result i.e.: C:SOSConsentFormsConsent_Doe_John
5- Then send the file by email (Outlook) to : abc@hotmail.com
View 14 Replies
View Related
Nov 2, 2014
getting to grips with the code a bit more now, but now I cannot get the report which is generated to attach to the email
running ms access 2010
View 1 Replies
View Related
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
Nov 4, 2013
I am using Access 2007 and are a newbie.
I am using the following code (Sendobject) to create a PDF of a report and send it via email to a customer's email address.
Private Sub EmailWorkOrderQuote_Click()
On Error GoTo Err_EmailWorkOrderQuote_Click
Dim stDocName As String
Dim mFilename As String
Dim mEmailAddress As String
[Code] ....
It works fine except that the I want to to able to utilize the ability to send the email with either both the CC and BCC or either or neither as well.
I have a table called "My Company Information" that contains the email addresses for the CC and BCC. If I have values in these fields, everything works great! If I have no values in either of these fields, I get "Invalid use of Null"....
I want to be able to use either both CC and BCC, either CC or BCC, or neither used and it still work.
View 9 Replies
View Related
Dec 5, 2014
I have a subroutine that works well for sending emails. A need has arisen to, in some cases, send an Access report as a PDF attachment. I know how to do this using the SendObject method, but that won't work in this app. I also don't want to have to first save the report as a pdf file. I think I'm close, but I don't know the syntax for passing the report name as the Source parameter in the Attachments.Add method.
Code:
If strEmailAddress = "jsmith" Then
.Attachments.Add source:=MyReportName, type:=acFormatPDF
End If
On Error GoTo SendErr
.Send
On Error GoTo 0
View 4 Replies
View Related
Jun 17, 2013
I'm running the following code to generate an email from a report.
Quote:
Function ExportHTML3()
Dim strline, strHTML
Dim OL As Outlook.Application
[Code].....
But I don't really know much VBA and I found that code on the internet, so I can't figure out how it's doing that and if I can stop it. Or is there another way to get the text from the HTML file into the Email body, which brings the bold formatting with it, like the following.
Incident Reference: AA99999
View 4 Replies
View Related
Jul 8, 2014
I have learned the basic looping technique for going through a record set from the following link. I need to know if my logic is on the right track. URL...
My question is a followup to a thread that was opened on this forum: URL....I want to do the following:
1) Use record set looping technique to fix a variety of incorrect naming conventions to a standard format
2) Update the table (or create a new table) from the updated record set values. (Is my logic going to update the table selected in the code I used to dimension the record set or will I need to do something else to make the changes available for other tasks after record set is closed ? After the naming conventions are fixed this data will be available for excel automation that I am working on and posting questions on another thread in this forum. HAHA I'm going code BANANAS)
3) rs.fields![fleetlocation] is used so many times, how can I make this a variable (what do I dimension the variable as?)
4) Use an AND statment with an if statment (how to do this with correct syntax)
Code:
sub loopandfix()
on error goto errorhandler:
strSQL = "tblUnionQueryResults" 'table was created from a union query but has inconsistant naming conventions for the fleet location name
[code]....
View 9 Replies
View Related
Sep 15, 2013
I have the following example of code I have previously used which loops through columns where a column is called "Page1", "Page2", Etc.This is fine when the number of columns is fairly small. But in a new case I have 585 250 columns which may or may not contain some data which is the key.If data exists in column do something and after that or of not move to the next column and do the same thing until there are no more columns?
Code:
Dim rst As DAO.Recordset ' recordset for category data
' open the recordset for categories
Set rst = CurrentDb.QueryDefs!QryRMA.OpenRecordset
' loop through the rows in the recordset
rst.MoveFirst
Do Until rst.EOF
[code]...
View 5 Replies
View Related
Jun 18, 2014
Im still struggling with working with recordsets.What i want to achieve is to loop through a continuous form recordset, Using the OrderDetailFK from the record set and the OrderDetailPK in the OrderDetail Table
Looking at the Status FK in the OrderDetail Table. IF the OrderDetail Status = 2 then i want to make the textbox enable property = False. I have made the bellow SQL string which is placing all the criteria i just need to link it into a recordset?
Code:
mySQL = "SELECT [StatusFK] FROM [tblOrderDetail] WHERE [OrderDetailPK] = " & rst!OrderDetailFK & " AND [StatusFK] = 2 "";"""
View 4 Replies
View Related
Aug 1, 2014
I have a looping error on this code. It all works fine until the very last row, on the last row it brings over the Claim_Number and the FacID, but leaves the Paid_Amount blank.
If I have 10 or 10,000 rows, its always leaves the Paid_Amount on the last row blank.
Code:
Function RunLWSPA()
Dim cnn As ADODB.Connection
Dim rstUplo As New ADODB.Recordset
Dim rstDiag As New ADODB.Recordset
Dim rstTemp As New ADODB.Recordset
[Code] ....
View 6 Replies
View Related
Apr 5, 2015
I have managed to amend records based on the user selecting multiple items in a list box by using the following code.
Set db = CurrentDb()
Set rs1 = db.OpenRecordset("Usage", dbOpenDynaset, dbAppendOnly)
Set ctl = [Forms]![frmsearch]![lstSelector]
[Code].....
what I need to do now, is to loop(I think) through and amend all the records in a populate listbox, with no selections, similar to above, but without selection.
View 3 Replies
View Related
Nov 20, 2013
This query returns a running asset balance for 60 months. Each month is 1 column.
1) I need to create a query based on those results that can cycle through each column and determine if it is NEGATIVE. Then if it is negative, I need to perform some math on it to determine how to get it back up to being positive.
2) I need to loop through X columns based on the assets lead time. Each lead time varies and I have a master table that contains it. So if the assets lead time is 3 months, I need it to move over 3 columns and return that value. If it is 6 months then I need it to return the value in the 6th column.
View 9 Replies
View Related
Apr 24, 2014
I have 3 tables; tblProjects, tblTesting, and tblEmployees.
The issue I'm having is that I've created a form which collects testing information from testers that test a particular project. Each project can have multiple testers. tblProject and tblTesters have a one to many relationship and the form (frmTesting) consists of a main form for project info and a subfrom for testing info.
For each project tested there are 4 possible outcomes. 1st not all testers have tested to project and that case project status is "InTesting", 2nd all testers pass testing in that case project staus is "Waiting Final Approval", 3rd all testers fail testing in that case project status is "Maintenance" and last some testers pass and some fail in that case project status is "IPR".
Here is my code for a button I've placed on the sub form.
Private Sub BtnTest_Click()
Dim db As DAO.Database
Dim rsSQL As DAO.Recordset
Dim intMax As Integer
Dim strSQL As String
Dim csSQL As String
Set db = CurrentDb()
[Code] ....
My select statement for my recordset isn't working and I've tested it by putting a specific project number in the statement and I still only pulling one recordset.
View 4 Replies
View Related
May 23, 2014
Im v new to VBA and been having trouble getting the following code to loop through the records in my subform:
Private Sub cmdComplete_Call_Click()
Dim rs As dao.Recordset
Set rs = Me.fsub_Call_Off_Quantities.Form.RecordsetClone
rs.MoveFirst
[code]....
It only seems to update the first records txtQuantity_Called_Off value. I have tried all the 'looping' code variations i can find and they all seem to only update the first .
View 6 Replies
View Related
Jan 15, 2015
When I run the following sql (in a query) I enter information once for [enter cty id]. and it all works fine.
What I would like to do now, is write the vba code so that the sql runs multiple times looping through a list of items.
For instance instead of the popup window asking "enter cty id", I'd like to automatically have the code loop through a list. For example a list of 1 through 10.
Then, in the loop vba code:
after item 1 is run, do an export of the results.
after item 2, do an export.
etc.
Code:
select a.south_index_id, c.cnty_cd, a.southwest_name_source_cd, sp.south_ssn, sp.estimated_birth_dt,
[a.LAST_NAME] & ', ' & [a.FIRST_NAME) PERSONNAME],
a.BIRTH_DT, a.GENDER_CD, sp.ms_pmi, cnty_person_id
FROM SW_alias_name AS a, SW_south_person AS sp, SW_county_pid AS c
WHERE EXISTS (select * from SW_alias_name a2 where ucase(a2.last_name) = ucase(a.last_name)
[Code] .....
View 1 Replies
View Related
Aug 6, 2015
I follow a ritual of moving last to first before looping through a recordset. I started following this because in the way back (I think on office 2003). I used to get an error if don't do it. Is the problem still exists? Is this ritual scientific still?
Code:
Sub mac()
Dim myRset As Recordset2
Dim AttachmentField As Field2
Set myRset = CurrentDb.OpenRecordset("Table1")
With myRset
.MoveLast
[Code] ....
View 2 Replies
View Related
May 9, 2014
Show me the selected item in a Listbox without looping in the whole list, because my Listbox is multiselect and I want only the item that selected recently.
View 8 Replies
View Related
Jul 22, 2015
I am trying to write a VBA Module that will look in a directory for Excel files with a specific file prefix, and then import specified cells into a temp table. Once it processes the first file, it copies the file to another folder, and then kills the file. However, when I run my current code, it goes through the loop the first time, but fails on the second attempt, because it is still looking for the first file it came across even though it has been moved. The code is pasted below:
Sub Count()
Dim xlwrksht As Excel.Worksheet
Dim xlWrkBk As Excel.Workbook
Dim nIndex As Integer
Dim strMvPath As String
Dim mvPath As String
Dim strFile As String
[Code] ....
View 6 Replies
View Related
