Filter Access Report To Email - Relevant Records Only

Jun 24, 2012

Microsoft Access 2007.Background: I have a query that received work orders from out client system and runs them through access. Once in Access, an employee goes through the query output and organizes the records (mentally) by product_code. There can be be 1 or more records of a certain product_code and there are 129 total different product_codes. Say, a typical time the employee runs this query they might see 25 different records consisting of 6 different product_codes.Each product_code designates who (an email address) should be sent the work orders to approve.

The employee currently copy/paste the relevant records in an Outlook 2010 email body and uses an external excel sheet with the email addresses to see who it should be sent to. I'm automating this whole process.I've gotten an email to generate in vba, and to send an 1 email to each person who has one of their product_codes in the query output.

My problem is that I'm having trouble figuring out how to filter the emails so that instead of seeing all 25 records (from above example) they only see the records with their product_code.Another thing to mention is that as of now, I have a script that converts the report to HTML and sticks it in the body of the email. So I'm not sure how to edit that script to allow to filter the report or if that's even where I need to do the filtering (another procedure maybe?)

This is my html conversion script

'product_code = Mfg_Cd in this scriptFunction exporthtml(str_Sender As String, str_DataMsg As String)
Dim strlin As String, strHTML As StringDim objOutlook As Outlook.ApplicationDim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.RecipientDim varX As Variant
Dim RS As Recordset Set RS = Me.Recordset Set objOutlook = Outlook.Application
Set objOutlookMsg = Outlook.Application.CreateItem(olMailItem)

[Code] ....

View Replies


ADVERTISEMENT

Reports :: Relevant Record Entries Into Report And Email

Jan 20, 2014

I'm struggling to produce a report that pulls out pre defined fields from the active record displayed on a form and attach / embed to an email.

View 1 Replies View Related

Making Forms Show Relevant Records

Apr 3, 2005

I have been able to successfully solve the problem of my pages showing relevant records by using form control.
How should I make my form display a new record everytime I open it? So, that when a lame user opens it , he wouldnt change the existing data on an existing record. How to solve this problem?
Kindly help. thanks

View 11 Replies View Related

Dynamic Query To Show Each User Only Records Relevant To Them On Logging In

Dec 17, 2014

I have an access 2010 web database, modified from a microsoft template (Desktop inventory database) that several users will need to log in to. The database contains a table of products.

The challenge is, every user needs to only see a subset of these products and never see the whole list.

At the moment i have some code to modify an existing query based on the logged in user's details. As they log in, some tempvars are created and these are used to modify the query criteria.

This works well when the first user logs in, but the moment the next user logs in, the query is modified again and the product list refreshes and now his products are shown and not the first users! Im thinking i need to dynamically create a permanent query for each user on log in?

Here is my code so far:

Button on login form has the following code that collects the user's details

Code:
Private Sub cmdLoginMine_Click()
Dim ID as long, strEmpName as string,strZondsc as string,strgrpdsc as string
ID = DLookup("ID", "Employees", "Login='" & Me.txtUser.Value & "'")
strEmpName = DLookup("FullName", "Employees", "Login='" & Me.txtUser.Value & "'")
strgrpdsc = DLookup("MyGrpdscs", "Employees", "Login='" & Me.txtUser.Value & "'")
strzondsc = DLookup("MyZondscs", "Employees", "Login='" & Me.txtUser.Value & "'")
TempVars.Add "tmpEmployeeID", ID
TempVars.Add "tmpEmployeeName", txtUser.Value

I then call a function that modifies the existing query, populating it with this users details for the criteria

Code:
qryEdit strgrpdsc, strzondsc, ID
Sub qryEdit(strgrpdsc As String, strzondsc As String, ID As Long)
Dim qdf As DAO.QueryDef
Dim qdfOLD As String
Set qdf = CurrentDb.QueryDefs("InventoryQryforDS")

[Code] ....

The results of the query are shown on a form, which is what is currently requerying and showing the wrong data. The data is shown on a form, linked to one of the new style navigation buttons as shown.There cord source property of the form is the query that's populated as described above.

View 1 Replies View Related

Modules & VBA :: How To Display All Records From Report On Email Body

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

Filter A Report So It Doesn't Display Certain Records

Oct 18, 2005

hey everyone . .

i've got a report which pulls its data from a query. it pulls these fields:TestCode...Price...HID...Month...etc . . .

There are names of "TestCode" that we don't want to display, is there a way to filter certain "TestCodes" out? if so, how?

Here's my current SQL Code:
I have a query which pulls certain records from a table, here is my SQL statement:
SELECT [qryGroupByAMCount].[TestCode], [qryGroupByAMCount].[Price], [qryGroupByAMCount].[HID], [qryGroupByAMCount].[Month], [qryGroupByAMCount].[CountOfAutoNumber], IIf([TESTCODE]="PTCGCD",[CountOfAutonumber]*2,IIf([TESTCODE]="LSHABC",[CountofAutonumber]*4,IIf([TESTCODE]="HPVPNL",[CountOfAutonumber]*2,IIf([TESTCODE]="TOXOAB",[CountofAutonumber]*2,[CountofAutonumber])))) AS Extended
FROM qryGroupByAMCount

View 1 Replies View Related

MS Access Email Report

Oct 7, 2003

Hi,

I would like to ask a question regarding MS Access reports. I used the following code to email the report created in Access.

DoCmd.SendObject [ObjectType],[ObjectName],[OutputFormat],[To],[CC],[BCC],[Subject],[MessageText],[EditMessage],[TemplateFile]

I send a report in an email in rich text format and it works. But when you open the report there is no image, lines or rectangles. The format is ok but image on top of the report and lines are missing which I used for column captions. If anyone know how to handle this problem, I will really appreciate.


Thanks

Naveed

View 5 Replies View Related

Filter Query - Report To Only Show Records With Most Recent Date?

Feb 26, 2014

I am having trouble getting a query or report to show only the most recent data.

We have salesmen that use a handheld data collector scanners to count inventory in stores. The scanner data is imported to a Access table. Each record line is one scanned item. I have a query with totals that counts the records and gives me a total count of each item at the store on that date.

I then need to filter the data to only show the most recent date. Using Max Date I get the most recent date but the count fields are showing totals for all dates. I am also getting the unique item from the earlier date in this query which I do not want.

Here is my data table: Inventory Scans from stores.

Scan Date
Item Scanned
location

1/1/2014
item123
Store ABC

1/1/2014
item123
Store ABC

......

Here is my Query with Totals that counts the item records:

Scan Date
Item Scanned
location
(Item Scanned) count

1/1/2014
item123
Store ABC
2

1/1/2014
item 456
Store ABC
3

1/1/2014
item 789
Store ABC
1

2/1/2014
item123
Store ABC
2

2/1/2014
item 456
Store ABC
1

This is what I am trying to get - only the most recent date of counted items:

Scan Date
Item Scanned
location
(Item Scanned) count

2/1/2014
item123
Store ABC
2

2/1/2014
item 456
Store ABC
1

View 5 Replies View Related

Modules & VBA :: Unable To Attach A Report To Access Email

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

Auto Email PDF Report Daily From Access 2007

Oct 17, 2011

I am working on Access2007. I designed a report that I need to email every night automatically to my client. The report resides in the Access database. I currently use a macro to auto email this report for me but it still requires me to double click the macro to make it run. I actually want the report to be email at night without having me running the macro manually. Is there any way to make this happen?

View 7 Replies View Related

Access 2010 - VBA Code - Add Report To Body Of Email?

Feb 25, 2013

Access 2010 - add report to body of an email

i have VBA code to create an email and attach an excel file, what i would like is code to add a report called REPORTMISSINGDATES to the body of the email.

this is the code i have so far, which works and adds everythng i wqant except the main body of the email.

Private Sub Command31_Click()
On Error GoTo Command31_Click_Err
Dim Email As String
Dim name As Variant
Dim EMPloy As Variant
Dim month As Variant
month = MonthName([Forms]![STAFFATTENDANCEMenu]![StaffMonth])
EMPloy = Forms!staffattendancezone!Staff
name = DLookup("[STAFFNAME]", "[QRYSTAFFNAME]", "[ASA] = Forms!staffattendancezone!Staff")
Email = (Forms!STAFFATTENDANCEAdjust!Email)
DoCmd.SendObject acQuery, "STAFFATTENDANCEZONECheckEmployee", "ExcelWorkbook(*.xlsx)", [email], "", "", "" & "Attendance Errors", "THIS IS WHERE I WANT THE REPORT TO GO" _
End Sub

View 1 Replies View Related

Modules & VBA :: Attach Internal Access Report To Email Item

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

Filter Access Report Based On Check Box Selection

Oct 31, 2011

I have a query that gives field values like name, age, department, etc for all the staff members in our company. These fields are obtained from many other tables and queries. Now I am planning to generate a report, which would display the results of this query. The catch is I am trying to do it in such a way that I want the user to have the option to select for which staff members he wants to run the report. Example: if I have 10 staff members, the user should be able to select staff3, staff6, staff7 and run the report such that it displays all the field values from the query related ONLY to these 3 staff members.

I could put up some labels and checkboxes for each staff on the form, and then select some checkboxes and get the report to read which checkbox is selected, and then display the concerned results through the report.

View 3 Replies View Related

General :: Access 2010 - Email Records In Body From Table

Oct 1, 2013

I've set up a piece of VBA code to loop through and email various recipients data, in the form of records within the email body. This works fine, but occasionally produces an error whereby the email output body lines are duplicated spuriously. This only seems to happen when running a large number of email loops.

The data source for the email body content is a table, which for each 'loop' is refreshed with new data by 1) deleting records from that table, and then 2) appending data to the table.

The portion of code for the loop & email:

Do
'Build outputbodytext
DoCmd.OpenQuery ("EmailQ2")
DoCmd.OpenQuery ("EmailQ1")
'set up email
Set rec2 = CurrentDb.OpenRecordset("outputbodytext")
strSendTo = rec2("Email")

[Code] .....

View 3 Replies View Related

Reports :: Access 2003 - Filter Report Based On Combo Box

Feb 3, 2015

My Access 2003 Database contains the following objects:

1. tblTrackerData
- this contains over 1,000 student enrollment records.
One of the fields "QualCourseName" (text) contains the name of the Training Course that the student has enrolled in.

2. qryCourseNamesGrouped is a query based upon the above table with one field only - QualCourseName. This includes the names of the training courses and has been grouped so that only 17 training courses appear, not over 1,000.

3. frmParameter is a form that includes a combo box cboFiltered based on the above query AND a command button CmdFiltered to open a report rptCourseNamesGrouped and filter the report based on the selection made from the combo box.

I have added the following code to the OnClick event attached to the cmd button"
================================================== ======

Private Sub cboFiltered_Click()
On Error GoTo Err_cboFiltered_Click
Dim stDocName As String

[Code].....

When I click on the cmd button, the entire report is opened, instead of the selection that I made in the dropdown list.

View 2 Replies View Related

How: VBA, Filter Specific Records, And Access Information

Sep 4, 2006

First time member, Hello.

And sadly i am saying hello with a problem. I am a relatively new user, and i have a amateur(i think) problem.

Doing a booking system, and working in VBA
I want to check if certain records exist by date.
My "bookings" table has 'roomname' and 'date' and 'timeslot' as fields.

My query is:
strSQL = "SELECT * FROM bookings WHERE (((bookings.room)=" & roomName & ") AND ((bookings.date)=#" & dateStr & "#));"

I need to get all those records, and be able do certain checks on them, but I dont know how to execute that in VBA, so i check the records to see which time slots have been booked.

In pseudocode:
If(bookings.date == #xxx# AND bookings.timeslot == xx)
then 'some sort of action'

In short how do i filter specific records using my query and how do i access record information directly in VBA? research suggests recordsets, but not sure how to implement that

Thanks in advance

View 2 Replies View Related

Reports :: Option Button To Filter Report With Dates In Access 2010

Jun 9, 2014

I have a retort that I want to use an option button to filter the report is a specific date is 48 hours before the current time. I'm not having any luck. Below is the code I have been playing with, but no go.

Private Sub NoMove_Click()
Select Case Me.NoMove.VALUE
Case 1
Me.Filter = "CLM = 'Now() - 48'"
Me.FilterOn = True
End Select
If Me.Filter = "" Then
MsgBox "The filter returned Null"
Else
End If
End Sub

View 14 Replies View Related

Modules & VBA :: Filter Records - Adding Unbound Date Listbox To Filter String

Feb 10, 2014

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.

View 2 Replies View Related

Filter A Report USING A Forms Filter Results

Oct 25, 2006

I am using MS Acess2000 and need to make a report that will be passed around with production work. My primary key is the invoice number of the work order. Currently to open an invoice i have a macro, attached to a query with the following qualifiers; Like [Enter invoice]

This pulls up the current record fine.
for the form and flags the folloing in the property filter sectin of the form

(((([CustomerTableMasterRef].[Invoice]) Like [Enter invoice])))

PART 2

Now I am trying to use microsofts how to filter a report using a forms filter...
This picks up on the Invoice query as shown above but does not just insert the query results...

Is there better code or another way to approach this... Currently i am using:

Name:cmdOpenReport
Caption: Open Report
OnClick: [Event Procedure]

Private Sub CmdOpenReport_Click()
If Me.Filter = "" Then
MsgBox "Open an Invoice First"
Else
DoCmd.OpenReport "rptCustomers", acViewPreview, , Me.Filter
End If
End Sub

Using this code not only does my report not detect the correct fields to import data (no data is filled in) but it requerys the invoice or atleast should, which I could do with out all of that code...

Where should i go from here?

View 1 Replies View Related

How To Create Filter Button On Form And Filter Records

Nov 26, 2012

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.

View 3 Replies View Related

Placing A Report Filter At Startup Of A Report?

Jul 25, 2013

The company I work for has branches in multiple states and we use Access to organize all their tax requirements. We print out reports for every month, and my company would like to create a pop-up window that will let us filter the report when it starts up so that it automatically shows only the dates that we wish it to. How do I create a filter that pops-up when the report starts?

View 1 Replies View Related

Relevant Data Not Cascading Through

Jun 14, 2005

As part of my data structure I have a Fault table, Transaction table & Resolver table. The Resolver's are dependant on the Transaction that are selected in the fault table. What I am after (and it's not happening) is when selecting a Transaction in the Fault table the only resolver that is available to the user is the one related to the choosen Transaction.

At the moment after selecting a Transaction all Resolver's from the Resolver table are available. When creating my Fault table I selecting the Resolver field as a Lookup and choose the Transaction table and the Resolver field.

View 1 Replies View Related

Insert Relevant Value Based On Selection

Jun 15, 2005

I have a form with a combo box this combo box looks up values in my STC table. The STC table has two fields STC & Resolver and each STC has a unique associated Resolver. What i want to happen is after the user has selected the STC from the combo box on the form the associated Resolver be displayed in a textbox on the form. I am ussing the following code but nothing seems to be happening:

Private Sub cboSTC_AfterUpdate()
Dim ResolverSource As String

ResolverSource = "SELECT tblSTC.[Resolver] " & _
"FROM tblSTC " & _
"WHERE tblSTC.[STC]='" & Me.cboSTC.Value & "';"

Me.txtResolver.RowSource = AgentSource
Me.txtResolver.Requery


End Sub

View 1 Replies View Related

Displaying Relevant Field Names

Oct 4, 2007

Hi there!

I would like to know whether it's possible to retrieve the field names, in order to display them in table format within a html page. I would like to dress the table with the cell data together with field names as 'headers' for each column.

Here is a snippet of what i have manages to produce. Currently, it displays all the entries that coincide with thier field names. The inly thing i wish to do now is to display the field names:

<html>
<head>
<title>date</title>
</head>
<body >
<h3>Try It Out - Sailors Table With a Counter</b></i></font></h3>

<p><br>
<%
Dim oRSeofc
Set oRSeofc=Server.createObject("ADODB.recordset")
oRSEOFc.Open "People", "dsn=20527796a"
oRSeofc.MoveFirst
response.write "<table border='1'>"

Dim PersonCounter
PersonCounter = 0
Do While Not oRSeofc.EOF
PersonCounter =PersonCounter + 1

response.write oRSeofc.fields.item(counter).name

Response.write "<tr><td>" & PersonCounter & "</td>"
Response.write "<td>" & oRSeofc("PeopleNameFirst") & "</td>"
Response.write "<td>" & oRSeofc("PeopleNameLast") & "</td>"
Response.write "<td>" & oRSeofc("PeopleDOB") & "</td></tr>"
oRSeofc.MoveNext
Loop
response.write "</table><br>"
response.write PersonCounter & " Sailors in this list"
%>


</body>
</html>


All help will be greatefully appreciated, thanx!!

View 1 Replies View Related

Best Way To Email Report?

Jun 11, 2007

I have a report that I'm looking to email as opposed to fax. What's the easiest way to achieve this?

Any and all advice appreciated.

Thanks!

d

View 9 Replies View Related

Automatic Update Of Information In Relevant Fields

Sep 20, 2007

Morning everyone

I have made up a database to record generations of birds; along with others I have the following fields:

Ring number (primary key)
Sex
Year
Colour

Data entry via a form view.

For new entries I pick up from a combo box the parents, what I need to do now is create a relationship between 1, 3&4 and 2, 5&6 so that the data will automatically slot in the relevant fields.

1Parent Cock
2Parent Hen
3Grand Parent Cock C/S
4Grand Parent Hen C/S
5Grand Parent Cock H/S
6Grand Parent Hen H/S

would I do it via a query.’ And would one cover relevant generations

Thanks for any help

Norma

View 8 Replies View Related







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