Add attachment field with vba Results

Hi there,

I'm creating a tool to create tables from given data (name, field-type, format, etc.). Everything is working smooth so far, but I have absolutely no clue how to create a field with the datatype 'attachment' with VBA. I've searched for a while now, but couldn't find anything helpful.

A little tip to steer me in the right direction would be great.

Thanks in advance!

DB Setup
Backend: Table with attachment field
Frontend: Forms with linked table to backend

The app has been created and worked fine.
I was able to add the attachment to the table and retrieve the document.
All went fine.

Now once this was ready for multiuser access, I compiled the VBA code from the VB editor and created a accde file and distributed to the users for testing.

I first tested it on my system and a VM i have and all went well, without any issues.

However whenever another user uses the accde file, they are UNABLE to add a new attachment.

They can Fetch the attachments and View them without any problem.

I did a thourogh check and found that the Error raised was at the:
rs_parent.addnew command

Error:

This error occurs when an event has failed to run because Microsoft Office Access cannot evaluate the location of the logic for the event. For example, if the OnOpen property of a form is set to =[Field], this error occurs because Access expects a macro or event name to run when the event is fired.

Is there a way like insert into that can be used with the attachment?

Please assist!!!!

I have a table that is created by reading a list of file names from a folder, with these fields:

FileID (an autonumber created when the table was created)
FName - the name of the file (.jpg images)
FPath - the path to the file (not including the name)
DateCreated - the creation date of the file.

I have developed a query that parses these fields and using information obtained thus (from the path and file name I gather info needed in my table) can be used to create a new table. However, as the final step, I wish to create an attachment field and attach the .jpg image to the appropriate record (one record for each .jpg).

I have found several samples of VBA code to attach things to records, but I can't find anything that will allow me to do what I want to do (via a query, which means write a function that can be used as the value for the field in the make table query). What I want to do is using a function, concatenate the path and file name for each record, and add the .jpg to an attachment field from that information.

Is this possible?

hi evry one

I have a database with a table that has an attachment field. I would like to add attachments (more than one) in a folder in c: drive to a record in the attachment field. can any one help me do this. I want to do it using a code on a click event of a form.
I am new to vba , so please help
hope some one will help

Thanks

Hi-

I've created some VBA to add appointment information contained in my database to a user's outlook calendar. The code works perfectly until I tried to include an attachment in the appointment using an attachment field in my database. I've scoured Google and these forms to try and find a solution, but I have been unable to find anything. Below is my code which receives Run Time error 438, "Object does not support the property or method" when trying to run ".attachments.add (Me.Agenda)".


	Code:
	'Exit the procedure if the appointment has already been added
If Me.AddedToOutlook = True Then
MsgBox ("This appointment has already been added to Outlook.")
Exit Sub
'Add a new appointment
Else
    Dim objOutlook As Outlook.Application
    Dim objAppt As Outlook.AppointmentItem
    
    Set objOutlook = CreateObject("Outlook.Application")
    Set objAppt = objOutlook.CreateItem(olAppointmentItem)
    
    With objAppt
    .Start = Me.AppointmentDate & " " & Me.AppointmentTime
    .Duration = Me.Duration
    .Subject = [Forms]![Menu]![ProspectListing].Column(1) & " (" & Me.ContactName & ")"
    .Attachments.Add (Me.Agenda)
    .Body = BodyString
    .ReminderMinutesBeforeStart = 15
    .ReminderSet = True
    .Save
    .Close (olSave)
    End With
    'Release the AppointmentItem object variable.
    Set objAppt = Nothing
End If

'Release the Outlook object variable.
Set objOutlook = Nothing

'Set the AddedToOutlook flag, save the record, display a message.
Me.AddedToOutlook = True
DoCmd.RunCommand acCmdSaveRecord
MsgBox ("Appointment added to Outlook")



ok, so im trying to validate the values in a forms fields with vba. the function is called at the start of my onclick event as such:


	Code:
	Call amdValid(Forms![Input Form])

While the values in my If statements display the correct values when stepping through, it seems to ignore this, skips the message box and cancel = true, and proceeds to the next if all the way through the function. I thought that all i needed was if ....then msgbox "..." cancel = true end if. Is my If statement invalid? or what? Im kinda baffled here.

this is the function:

	Code:
	Public Function amdValid(vFrm As Form)

    If (vFrm!txtSSD - vFrm!txtDOR) > 3 And vFrm!txtComments = "" Then
        MsgBox "Turnaround Time is greater than 3 days, please comment."
        Cancel = True
    End If

    If (vFrm!txtSSD Or vFrm!txtDOR) = "" And vFrm!chkStatus = "" Then
        MsgBox "Please include Date of Reciept or Site Submission Date"
        Cancel = True
    End If
    
    If vFrm!objRequest = "" Then
        MsgBox "Please attach Request Document"
        Cancel = True
    End If
    
    If vFrm!cboPurchaseOrder = "" Then
        MsgBox "Please Select a PO number.  If you need to input a new PO number click the add button to the right of the PO
field"
        Cancel = True
    End If
    
    If vFrm!txtvalue = "" Then
        MsgBox "Please Include a Dollar Value for this Record"
        Cancel = True
    End If
    
End Function



I am under a time constraint and really need help. Access 2007, Win 7 64 bit, Development skills medium, No VBA involved except me.refresh. Sub form works until I add a field.

1. I have a main form, single form, where you select criteria. Meal name, Meal ID, Start Date and End date.

2. You click the button Find Meals which only has the code me.refresh.

3. The sub form is based on a select query that reads the criteria off the main form and refreshes the sub form (continuous form) with the correct data. See Pic 1.

4. The select query is based on one table. See Pic 2.

I add one additional field from the table to the query. I then add the field to the sub form.

When I reopen the main form the sub form has disappeared from the screen. I select the same criteria in the parent form and click the button and nothing shows up. See Pic 3.

I go to the query outside of the form. I add the one field I want. I open the form and select the criteria on the screen. I run the query outside of the form and it executes perfectly and pulls in the additional field. But if I run it inside the form the sub form vanishes.

I have included three screen snaps. Please ask questions. I am not sure what other information to add. I have recreated forms, queries, etc. all to no avail. It seems like all my properties are ok, but something is wrong.

Phred Attached Thumbnails       Reply With Quote 02-04-2012, 11:23 AM #2 June7 Super Moderator Windows XP Access 2010 32bit Join Date May 2011 Location The Great Land Posts 15,123 Can you provide the database for analysis? Copy, remove confidential data, run Compact & Repair, zip if large, attach to post. The attachment manager is below the Advanced post editor.

Hi every one

I have table with an attachment field and on my form i have put a code on the click event of the id field. here is the code

Const strRootFolder As String = "C:UserseveDesktopNew"
AddAttachmentsFromFolder strRootFolder, "Table1", "Files", _
"*.*", True
MsgBox "Done adding files from: " & vbCrLf & strRootFolder, _
vbInformation, "File Import Process Completed"

This code ads all the files in the foldder. what i want acomplish is to change the code so that it would not add a file if it already exists in the the table. I am very at vba so can some one please help me.

Thank you

I built an Access 2010 database about two years ago for a client. My client wanted to scan customer contracts and save them with the database. I knew there were two methods; save the scanned documents in an Attachment file or save just the path to the file as a Hyperlink. At the time, (maybe I didn’t do enough research), I decided to use the Attachment file method. I probably also underestimated the quantity of scanned documents the customer would have and the impact on the database file size. From the start, the database was split into a Front End and a Back End. At inception, the Back End was about 3MB in size. Today, it is over 660MB in size and growing with every scanned document.

I started doing research online to figure out how I could export all of the scanned documents from the Attachment field, save them to a folder, and then add their location to a new table and field so the customer could still access them. It’s important to note that each scanned contract is associated with a customer order and I would need to ensure that whenever my customer went to a customer’s order, they would see only those scanned contracts. I never found in one place a “How To Export Attachments” article so I decided to summarize here what I did in the hope that it helps others that need to reverse stored documents/pictures/etc. from an Attachment field and save them in a folder. Maintaining the relationship to the location in the database are now being saved was critical.

Before you begin, make sure you back up your Back End data as well as your Front End. Also, please hold the feedback on how I named my fields and/or tables. I’m comfortable with it and it works for me. Yes, I know sometimes I need to use brackets on my field/table names.

One sample code that I used was from the Microsoft Access 2010 Programmer’s Reference manual. I modified the code slightly and it is shown below:

Code: Public Function SaveAttachmentsTest(strPath As String, Optional strPattern As String = "*.*") As Long Dim dbs As DAO.Database Dim rst As DAO.Recordset Dim rsA As DAO.Recordset2 Dim rsB As String Dim fld As DAO.Field2 Dim OrdID As DAO.Field2 Dim strFullPath As String 'Get the database, recordset, and attachment field Set dbs = CurrentDb Set rst = dbs.OpenRecordset("Order Table") Set fld = rst("Scan") Set OrdID = rst("OrderID") 'Navigate through the table Do While Not rst.EOF 'Get the recordset for the Attachments field Set rsA = fld.Value rsB = OrdID.Value 'Save all attachments in the field Do While Not rsA.EOF If rsA("FileName") Like strPattern Then 'To Export the data, use the line below strFullPath = strPath & "" & rsA("FileName") 'Make sure the file does not exist and save If Dir(strFullPath) = "" Then rsA("FileData").SaveToFile strFullPath End If 'Increment the number of files saved SaveAttachmentsTest = SaveAttachmentsTest + 1 End If 'Next attachment rsA.MoveNext Loop rsA.Close 'Next record rst.MoveNext Loop rst.Close dbs.Close Set fld = Nothing Set rsA = Nothing Set rst = Nothing Set dbs = Nothing End Function Some key points.
The table that contains the attachments field is [Order Table]. The field for each order is [OrderNbr]. The Attachment field’s name is [Scan]. My Attachment field when viewed in the Query By Entry, (QBE), shows:


Scan.FileDataScan.FileNameScan.FileType

I created a temporary Form with two Command Buttons on it:


Export Attachments
This had a simple macro on the On Click event that was:
RunCode and the Function Name was: SaveAttachmentsTest(“R:Attachments”) where “R:Attachments” was the location of the folder where I was going to save the scanned contracts to. When the Function was executed, it exported each scanned document in the order they were input into the database to that folder. (Although I haven’t shown this, I modified the above code slightly to modify as the file name and show the [OrderNbr] just to be able to verify the order and the associations between the scanned contract file name and the [OrderID] were in the right order.)
Run Append Query
I created a new table that I called Attachments Table. It has three fields in it:
[AttachNbr] which is an AutoNumber field[OrderID] which is a Number field that will correspond/establish the relationship back to the [OrderID] field in the [Order Table][FileN] which is a Hyperlink field to store the location where the file is saved
The Append query used the following SQL:
INSERT INTO [Attachments Table] ( OrderID, FileN )
SELECT [Order Table].OrderID, [Scan].[FileName] & "#" & "R:Attachments" & [Scan].[FileName] & "#" AS FileN
FROM [Order Table] WHERE ((([Order Table].Scan.FileName) Is Not Null));

This Query appends to the [Attachments Table] the [OrderID] from the [Order Table], (to the [OrderID] field), and a string, “R:Attachments”, (see NOTE below), which is the folder location where I just exported the scanned contracts to, and concatenated to it is the file name from the [Scan].[FileName] part of the Attachments/[Scan] field. NOTE: In order to get the Hyperlink data to be correctly stored in the field, you must use the “#” symbols as show in the SQL string. A Hyperlink field contains three parts separated by pound signs “#”. The template is: Display Text # file name including the path # Any reference within the file, (i.e. a sheet name if you’re importing an Excel spreadsheet). I found a helpful reference to this at: http://allenbrowne.com/casu-09.html

I also found that in my VBA editor that I needed to go to Tools, References and check Microsoft Office 14.0 Object Library.

I then modified my Order Form to remove the Attachment field input/delete button and then added a new Command button to open up my new Attachment Form, (Default View: Continuous Forms). This form has a couple of events:


On Open it goes to a new recordOn Before Insert it triggers a macro to SetValue of the [OrderID] field to equal the [OrderID] field of the Order Form where this form was opened from. This links the hyperlink location and file with the OrderID.The form is based on a Query that uses as its only source the Attachments Table. The three fields from the Attachment Table are used in the query and the [OrderID] field has criteria that equals Forms![Customer Order Form]![OrderID] to make sure that only records for the customer in the form are shown. The form also has two Command buttons:
Attach: It uses this code:
Private Sub cmdPopulateHyperlink_Click()
'First, set a Reference to the Microsoft Office XX.X Object Library

Code: Dim strButtonCaption As String, strDialogTitle As String Dim strHyperlinkFile As String, strSelectedFile As String 'Define your own Captions if necessary strButtonCaption = "Save Hyperlink" strDialogTitle = "Select File to Create Hyperlink to" With Application.FileDialog(msoFileDialogFilePicker) With .Filters .Clear .Add "All Files", "*.*" 'Allow ALL File types 'Test line so I can debug/compile the code End With 'The Show Method returns True if 1 or more files are selected .AllowMultiSelect = False 'Critical Line .FilterIndex = 1 'Database files .ButtonName = strButtonCaption .InitialFileName = vbNullString .InitialView = msoFileDialogViewDetails 'Detailed View .Title = strDialogTitle If .Show Then For Each varItem In .SelectedItems 'There will only be 1 'Display Text + # + Address of Hyperlink (Display Text#Hyperlink Address) strSelectedFile = varItem strHyperlinkFile = fGetBaseFileName(strSelectedFile) & "#" & strSelectedFile Me![FileLocX] = strHyperlinkFile Next varItem End If End With End Sub

Delete: Simple Delete macro
Note: the above VBA code I found on a few different sites

I tested the functionality and once I was satisfied everything works, I deleted my [Scan] field from the [Order Table], compacted the database and went from 665MB to 4.6MB.

I hope this is helpful to all who need to reverse an internally stored attachment to saving it externally.

Chuck

Hello
I am working on a database for my family's small business. We have a small database ~2000 records in the main table. We repair small industrial production machines and they are shipped in from our customers across the globe. I am new to VBA programming, but I have an academic background in C++ so I understand basic to advanced programming concepts, as well as OOP.

We are replacing our white-board with our database. So to make the transition easier for our less computer savvy employees I have developed a form called the "whiteboard" which will visually represent the same data that they previously wrote/erased on the old whiteboard. So I have a button that runs a set query of our priority units/repairs.

The problem lies in a "power search" that I am developing which will be able to search our DB by specific or a range of criteria in multiple fields. Very very similar to the way that NewEgg.com does their power searches, in fact that is where I got the idea.

So I know I can make it work by adding a bunch of if/else statements and going through every possible scenario of combinations, but that will obviously add a huge amount of overhead to running the query. So I need to do it more productively.

My question is: With VBA is there an object property of queries that allows me to maybe pass a list of criteria that can be dynamically changed each time a user changes the search criteria. Maybe pass something

So the SQL would look something like (VERY SIMPLY):

SELECT * FROM tblRMA
WHERE
tblRMA.Status IN ("Completed", "Evaluation in Progress", "Shipped")
AND
tblRMA.Customer IN (Customer_A, Customer_S, Customer_X, Customer_Y)
AND
...
AND
...

but if they chose different parameters and clicked search again it would be something like:

SELECT * FROM tblRMA
WHERE
tblRMA.Status IN ("Shipped")
AND
tblRMA.Customer IN (Customer_B, Customer_C)
AND
...
AND
...


So the form would allow the user to choose to search all or any number of customers, or statuses, or other criteria, but I would need the code to dynamically allow for changes each time the search button is clicked. As of right now the code will go through each checkbox on the form and append a string that is passed in to the query such as:

[I havent written the code yet so some of this might have C++ influences, bear with me]

If chk_statusShipped.value = true
Then
strStatusCriteria = strStatusCriteria & " OR 'Shipped'"
Endif
If chk_statusCompleted.value = true
Then
strStatusCriteria = strStatusCriteria & " OR 'Completed'"
Endif
etc...
...
...

There's a picture of the form attached as a jpeg.


So what is the easiest way to do something like that or pass multiple parameters to a query using VBA without having a plethora of if/else statements and appending a string multiple times.

Thanks for your help Attached Thumbnails   Reply With Quote 02-01-2011, 03:27 PM #2 alansidman VIP Windows 7 Access 2007 Join Date Apr 2010 Location Lake County, IL Posts 1,778 This may be helpful

http://www.mrexcel.com/forum/showthread.php?t=525424

Alan

quoting and posting here

Quote: I have this code that stops the user from manually deleting the table objects. that it needs vba to delete the table object. Knowing that, I thought that maybe there is also this code that will stop manual open of database tables.

I have attached in here the code that stop manual delete of table. maybe someone know how to modify or know how to stop manual open of table.




'=================================




I have seen a number of post where members have asked how to regain a table after they have accidentally deleted it. I am sure each of us in time past might have deleted a table accidentally ourselves to..... well i know at least I have.

So here is a solution that will stop an access table from being deleted manually by pressing the delete button on the keyboard by accident or otherwise (sabotage). The only way to delete the table is by using DDL with VBA as example 1 shows or using DDL only as a query object.

e.g
Code:

currentproject.connection.execute "DROP TABLE YourtableName"



This code only works if you have an autonumber field in your table and the autonumber field does not necessarily have to be a primary key as well. For those of us who use the Surrogate Key approach (Autonumber's as primary keys) will have no problem adopting this into their applications.

What will make this code stop working?


* If you are developing an database and you run the code to stop the manual deleting of tables everything will work find. But when you split the database (FE & BE) the code stops working and you can go back to manually delete any table. However, this is not a problem but more of a point to note. Because all you have to do is run the code again but this time in the backend (BE) and everything is find again i.e the user cannot delete the tables manually.

* If you run the code in your current database but thereafter you import your tables into a new database the code becomes void in the new database and will not work. Therefore you will have to re-run the code in the new database with the old tables for it to work again.



How to use this Code!

* 1. Copy and paste the code into a new module in your database and save it.
* 2. Press (CTL + G) to open the immediate window.
* 3. In the immediate window you must enter the following ?StopManualTableDelete("Yes") and press enter on your keyboard to DISALLOW a user from manually deleting a table:
* 4. If you want to RE-ENABLE a user to delete the tables again run the following ?StopManualTableDelete("No") in the immediate window:



There is all the code to go into the module.
Code:



'

'---------------------------------------------------------------------------------------

' Procedure : StopManualTableDelete

' DateTime : 5/26/2008 08:05

' Author : Dane Miller - Dallr

' Purpose : Created to stop a person from Manually deleting a table

' Arguments : "Yes" OR "No"

' References: Microsoft ActiveX Data Objects x.x Library

' : Microsoft DAO x.x Library

' Returns : N/A



'---------------------------------------------------------------------------------------

'

Public Function StopManualTableDelete(YesOrNo As String)



Dim fld As DAO.Field

Dim db As DAO.Database

Dim tbl As DAO.TableDef

Dim SQL_CreateConstraint As String, SQL_DropConstraint As String

Dim strConstraint As String ' this variable holds the name of the constraint

Dim i As Integer

Dim tblNames As String, DeleteInfo As String



Set db = CurrentDb()



i = 0



For Each tbl In db.tableDefs

' Bypass system tables with autonumbers

' Also any hidden table that starts with "~"

If Mid(tbl.Name, 1, 4) "MSys" Then

If Left(tbl.Name, 1) "~" Then

For Each fld In db.TableDefs(tbl.Name).Fields

If dbAutoIncrField = (fld.Attributes And dbAutoIncrField) Then 'Find autonumber



DoCmd.Hourglass True

strConstraint = "con_" & fld.Name & "_" & tbl.Name 'Build constraint name



If YesOrNo = "YES" Then

i = i + 1

'Drop any existing autonumber field constraints if there is one.

If FindCheckConstraint(strConstraint) = True Then

SQL_DropConstraint = "ALTER TABLE " & tbl.Name & _

" DROP CONSTRAINT " & strConstraint



CurrentProject.Connection.Execute SQL_DropConstraint

End If



DoEvents ' await a while just in case



'create the new constraint to disallow the table from being deleted.

SQL_CreateConstraint = " ALTER TABLE " & tbl.Name & " ADD " & _

" CONSTRAINT " & strConstraint & _

" CHECK (" & fld.Name & " IS NOT NULL))"

'Debug.Print SQL_CreateConstraint

CurrentProject.Connection.Execute SQL_CreateConstraint



DeleteInfo = "CANNOT"

End If



If YesOrNo = "NO" Then

'Drop any existing autonumber field constraints.

If FindCheckConstraint(strConstraint) = True Then

i = i + 1

SQL_DropConstraint = "ALTER TABLE " & tbl.Name & _

" DROP CONSTRAINT " & strConstraint



CurrentProject.Connection.Execute SQL_DropConstraint



DeleteInfo = "CAN"

End If

End If



tblNames = tblNames & tbl.Name & vbNewLine



Exit For

End If

Next fld

End If

End If

Next tbl



db.Close

Set db = Nothing



DoCmd.Hourglass False



If i > 0 Then

MsgBox i & " tables have been set so they " & DeleteInfo & " be deleted manually. " _

& vbNewLine & "These tables are:" & vbNewLine & vbNewLine & tblNames

Else

MsgBox "There are no tables with Autonumber fields present in this database." _

& vbNewLine & "Therefore this code did not have any effect on this database."

End If



End Function




Code:



Public Function FindCheckConstraint(MyConstraint As String) As Boolean

'this function checks to see if a check constraint already exist on the autonumber field.



Dim fld As ADODB.Field

Dim rst As ADODB.Recordset

Set rst = CurrentProject.Connection.OpenSchema(adSchemaCheck Constraints)



Do Until rst.EOF

For Each fld In rst.Fields

If fld.Name = "CONSTRAINT_NAME" Then

If fld.Value = MyConstraint Then

'Debug.Print fld.Value

FindCheckConstraint = True

Exit For

End If

End If

Next fld

rst.MoveNext

Loop



End Function

Hi Guys,

Background - I want to send an email/s to various recipents email addresses based on a query result. I've created separate queries for each month but ideally would like to set the parameters to choose a month within the code, however although i cant find anything to do this yet the code below lets me send an email based on the query result for a single month. This is working great at the mo, but I now need to send an attachment with the email. I'd really appreciate it if someone would cast their eye over the code listed below and see if they can find a solution.

Private Sub email_attached_Click()

Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String

Set MyDb = CurrentDb

Set qdf = MyDb.QueryDefs("qryrecipients_emails_mar11")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next

Set rsEmail = qdf.OpenRecordset()

With rsEmail
.MoveFirst
Do Until rsEmail.EOF
If IsNull(.Fields(0)) = False Then
sToName = .Fields(0)
sSubject = "See Attachment"
sMessageBody = "Hi," & vbCrLf & vbCrLf & "Please see attached." & vbCrLf & vbCrLf & "Regards," & vbCrLf & vbCrLf & "Paul"

DoCmd.SendObject acSendNoObject, , , sToName, , , sSubject, sMessageBody, False, False
End If
.MoveNext
Loop
End With

Set MyDb = Nothing
Set rsEmail = Nothing
End Sub

Many thanks for looking.

Regards,

Paul

Hi there

I have some code that creates an html email and it works fine.

However, I have just tried to add a second line that includes a link to a web page. How do I make the web address "clickable"? Whenever I try an insert html tags, I get a compile error.

------------------------------------------
Private Sub Command0_Click()

Dim rst As Recordset
Dim dbs As Database
Dim strSql As String
Dim stEmail As String
Dim stDate As String
Dim stPath As String
Dim stSubject As String
Dim stBody As String
Dim OLApp As New Outlook.Application
Dim OLMsg As Outlook.MailItem
Dim oShell As Object
Dim olkApp
Dim SecurityManager As Object
Dim salName As String
Dim salMessage As String
Dim salFooter As String
Dim salPhone As String
Dim salCell As String
Dim salFax As String


On Error Resume Next
Set olkApp = GetObject(, "Outlook.Application") 'Gets Outlook if open
If Err.Number > 0 Then
Set oShell = CreateObject("WScript.Shell") 'Opens instance of Outlook
oShell.Run "outlook" 'Outlook is not open so open it
Else
'Outlook is already open
End If

Set olkApp = Nothing

Set SecurityManager = CreateObject("AddInExpress.OutlookSecurityManager" )
SecurityManager.DisableSMAPIWarnings = True

DoCmd.SetWarnings False

frmCreatingEmails.Visible = True


DoCmd.OpenQuery "qryMakeEmailTable", acViewNormal, acEdit
DoCmd.Close acQuery, "qryMakeEmailTable", acSaveYes


strSql = "SELECT DISTINCT ClientName, EmailAddress, Filepath, InvoiceEndDate FROM tblEmail"

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset(strSql)

If rst.RecordCount = 0 Then
frmCreatingEmails.Visible = False
MsgBox "Nothing to send, please check the dates", vbOKOnly, "Warning"

Else


rst.MoveFirst

Do While Not rst.EOF


stName = rst.Fields("ClientName").Value
stPath = rst.Fields("Filepath").Value
stDate = rst.Fields("InvoiceEndDate").Value
stEmail = rst.Fields("EmailAddress").Value


salMessage = Me.SalutationMessage
salName = Me.SalutationName
salFooter = Me.SalutationFooter
salCell = Me.Cell
salPhone = Me.Phone
salFax = Me.Fax
stSubject = "The Buying Group Fuelcard Invoice for month ending " & stDate

HTML = ""

HTML = HTML & "Hi there"
HTML = HTML & "Please find attached your Fuelcard invoice for the month ending" & " " & stDate & ""
HTML = HTML & "We have also updated out Terms of Trade. To get a copy of them, please visit the following page by copy-and-pasting this link into your browser: link_address"
HTML = HTML & "Kind regards
" & salName & "
The Buying Group Limited
Phone:" & " " & salPhone & "
Fax:" & " " & salFax & ""
HTML = HTML & "" & salMessage & ""
HTML = HTML & "" & salFooter & ""
HTML = HTML & ""


stBody = HTML
----------------------------------------------------------------

Thanks for your help.
Cheers
Glynne

Hello All,
I have an issue I am trying to resolve, I have a possible method to resolve this issue but I need help with the possible code. Also, if there is a better way to go about solving the issue please let me know.

Background: I am using Access 2007; if you look at the attached Relationship image you can see the database setup. This is a material control/tracking database; the material in question can be CD/DVD’s, documents, hard drives or other types of material.

The tblMaterial table consists of information unique to each piece of material. The tables on the left side are predefined information that is represented in the tblMaterial table as FK’s. The tblSentDetails and tbleReceivedDetails tables represent information of when material is sent or received. Each records in either the sent or received tables can have one or more pieces of materials, for example I can send a CD and five documents in one shipment as well as receive several pieces of material in another. The tblCarrier table consist a list of possible methods of shipment e.g. UPS FedEX ect, and the SentHow and ReceivedHow fields draw from this table. By the same token the Originator, SentTo and ReceivedFrom draw from the tblOrganization table.

Issue: When material records are added or edited I need a way to associate either the received and/or sent details with individual material records.

After a couple of days trying several different possibilities the following is what I came up with, I believe it is simple and efficient.

I created an add/edit form (frmEditAdd bound to tblMaterial table), the form it’s self functions very well and I have no problems with it as a whole. Since the sent details and received details functionally are the same I will concentrate on the received details and duplicate the process for the sent details.

On the EditAdd form (see attached image) the Received From, Received How, and Received Receipt Mailed draw their information from the ReceivedDetails table and are displayed using a sub-form. The process I came up with to solve my issue is an unbound combo box. The row source of the combo box is the attached query qryDetailSelect2 (see attached image). This query draws from the ReceivedDetails table. In the qryDetailSelect2 query the first column is a combination of the ReceivedDate and MailRegNum and is used as a visual reference for the user, the second column is the ReceivedDetailID and the third is again the ReceivedDate field. The reason for the second received date reference is allow me to sort the query with the “newest” record on top, less searching for the user. The combo box is setup to show the first column hiding the other two, column with 1”;0”;0” and column count 3. You can see the result of this part of the process in the frmEditAdd image far right see arrow.

Now here’s what I need help with, the information in the tblReceivedDetails table and subsequent qryDetailSelect2 query is input through another from/process and the user will/should be familiar with the received date and mail registration number. Below is what I want the combo box to do:

With the record selected in the form, the user would use the combo box to display a list of the available Received Details records. Because the query is sorted by newest received date the desired record should be at or near the top. Once selected, I want the ReceivedDetailID of the selected record (see qryDetailSelect2) to be written to the ReceivedDetails (FK) in the tblMaterial table. This will populate the fields in the EditAdd form associated with the ReceivedRetails table. Once saved this should work.

I am looking for the code to do this.

This is the process I came up with to solve my issue, if you feel there is a better way to accomplish this please let me know.

Thanks in advance

John

I had hoped that my first post would be a solution on how to utilise the OWC10 chartspace component but I've run into problems that I'm seemingly unable to resolve.

I've exhaust all the web resources I can find and overcome many problems such as using OWC10 instead off OWC11 as it doesn't appear to play nicely with Access 2003 when explicitly declared (if not explicitly declared then the intelisense feature doesn't work!).

Anyway, please the the attached example of what I have achieved so far. If you open the frmMaintAnalysis form I am wanting to add another data series to the existing one, which can easily be accomplished using the mouse by...Right clicking in the chart area.
Select field list
Then drag the "Breakdowns_Elec" field into the chart.
Simples (but not for some of my users)!
But how to achieve this using VBA? I'd like to use the "Add" button to add another series to the chart, selected using the combobox. I've spent a week getting this far as the web components help is enough to drive a person insane.
One useful tip that I've learnt whilst trawling the web is that changing the pivotforms record source has the effect of wiping the chartspace and so I do this when the form loads. I then set the chartspace data using setdata method but after that I'm unable to add any other data. If I select literal data instead of bound data then the series is added but with no values showing.

Am I being really stupid, as I feel that the answer is staring me in the face but I'm unable to see it? I'm posting here out of desperation, please kindly offer me some advice, it will save me even more hair loss!

Thanks in advance,
RandomCoder

PS. This is a work in progress, the code still needs some tidying up!

Hi,
I wrote some access vba code that retrieves data from access, then creates a mail in Lotus Notes 8.5. The content of the mail is formatted text. The mail is usually send from a group mailbox but it can also be the default (user's) mailbox. When mail is sent directly, then the formatting is lost somewhere. In the send folder of notes it looks correct, but on the receiving side the formatting is gone.
However, when mail is first saved in the draft folder of the mailbox and then send manually from notes, the formatting stays correct both on sending and receiving side. I guess it must be something within the vba code. Any ideas ?
Here's the code :
Private Sub MailViaLotusNotes()
Dim objNotesDB As Object ' Notes Database
Dim objNotesDoc As Object ' Notes Document
Dim objNotesRTF As Object ' Notes Rich Text Item
Dim objNotesStyle As Object ' Notes Rich Text Style
Dim objSession As Object ' Notes Session
Dim SndTo As String
Dim SndCc As String
Dim msgSubject As String
Dim AutoSend As Boolean ' True/False is Email automatically sent
Dim itm As Variant
Dim EmbedObj(0 To 100) As Object ' Attachments
Dim sSRV As String ' Notes Server
Dim sDb As String ' Maildb name
Dim ErrMes As String

Dim rs As New ADODB.Recordset
Dim sSql As String

Dim PlTxt1 As String
Dim PlTxt2 As String
Dim PlTxt3 As String

Dim varHd As String 'HD = value of whdoc id.
Dim varHdFx As String 'HdFx = fixed text for whdoc id.
Dim varHd1 As String 'Hd1 = header values part 1
Dim varHdFx1 As String 'HdFx1 = header fixed text part 1
Dim varHd2 As String 'Hd2 = header values part 2
Dim varHdFx2 As String 'HdFx2 = header fixed text part 2

Dim iiPrb As Integer 'Problem id number.
Dim ssPrb As String 'Problem description.
Dim ssDtl As String 'Detail values.
Dim ssVal As String 'Next Problem description.
Dim ssDtlFx As String 'Detail fixed text.
Dim cctrl As Boolean 'used for tracking end of data.

'Set Variables
AutoSend = GetAutoSend ' False saves to drafts folder True would send straight away
SndTo = ""
cc = ""

'Create Email

sSRV = "" 'Set default mail server
sDb = "" 'Set default mailbox

On Error GoTo Err_Handler

'If group mailbox chosen, then get server address and mailbox name.
If Me.mlGroup.Value = 2 Then
GrpMlbxSrv = Nz(Me.cboCreatedBy.Column(2), "")
GrpMlBxDb = Nz(Me.cboCreatedBy.Column(3), "")
sSRV = GrpMlbxSrv
sDb = GrpMlBxDb
If GrpMlbxSrv = "" Then
MsgBox "No group mailbox found. The default will be used.!"
End If
End If

Set objSession = CreateObject("Notes.NotesSession")

Set objNotesDB = objSession.GetDatabase(sSRV, sDb) 'Default Users Notes Account.
'Insert Server and Database details
'to use group mailboxes
'Set Notes Text Styles

Set bodytext = objSession.CreateRichTextStyle
Set bodytext1 = objSession.CreateRichTextStyle
Set headings = objSession.CreateRichTextStyle
Set bottom = objSession.CreateRichTextStyle
Set restrict = objSession.CreateRichTextStyle
Set disclaimer = objSession.CreateRichTextStyle

'Build Text Style
'Headings
With headings
.NotesFont = 4
.FontSize = 10
.Bold = -1
.Underline = 0
.NotesColor = COLOR_BLACK
End With
'Main Text
With bodytext
.NotesFont = 4
.FontSize = 10
.Bold = 0
.Underline = 0
.NotesColor = COLOR_DARK_BLUE
End With

With bodytext1
.NotesFont = 4
.FontSize = 10
.Bold = -1
.Underline = -1
.NotesColor = COLOR_DARK_BLUE
End With

'Bottom text
With bottom
.NotesFont = 1
.FontSize = 10
.Bold = 0
.Underline = 0
.NotesColor = COLOR_BLACK
End With

'Open Notes Mail

' If objNotesDB.IsOpen = True Then
' Else
' objNotesDB.openmail
' End If

If sSRV = "" Then
' Assign the current user db to the db object. In case of group mailbox this is
' done earlier. In that case the next line would generate an "already open" error.
objNotesDB.openmail
End If

If (objNotesDB.IsOpen) Then
msgSubject = "WhDoc 07 : " & Me.txtCaseNbr & " " & " [" & Me.txtBiTrip & "] ["
msgSubject = msgSubject & Me.txtShipmNbr & "]"

'Retrieve mail addresses.
Call RetrieveEmailAddresses(SndTo, SndCc)
If SndTo = "" Then
Call GetDefaultMailAddresses(SndTo, SndCc)
End If
'if no default mailaddress exist then mail will not be send straight away but
'saved in draft mailbox.
If SndTo = "" Then
AutoSend = False
End If

Call GetHeaderText(varHd, varHdFx, varHd1, varHdFx1, varHd2, varHdFx2)

'Create a new message
Set objNotesDoc = objNotesDB.CreateDocument
objNotesDoc.ReplaceItemValue "SendTo", SndTo
objNotesDoc.ReplaceItemValue "CopyTo", SndCc
objNotesDoc.ReplaceItemValue "Subject", msgSubject
objNotesDoc.Principal = objNotesDB.Title

Set objNotesRTF = objNotesDoc.CreateRichTextItem("Body")
Set objAttachRTF = objNotesDoc.CreateRichTextItem("File")

'Build Body of email
With objNotesRTF
'Write header of whdoc to mail body.
.AppendStyle (headings)
.AppendText varHdFx
.AppendStyle (bodytext)
.AddNewLine 1
.AppendText varHd
.AddNewLine 1

.AppendStyle (headings)
.AppendText varHdFx1
.AppendStyle (bodytext)
.AddNewLine 1
.AppendText varHd1
.AddNewLine 1

.AppendStyle (headings)
.AppendText varHdFx2
.AppendStyle (bodytext)
.AddNewLine 1
.AppendText varHd2
.AddNewLine 1
'Write detail of whdoc to mail body.
cctrl = True
Do While Not cctrl = False
Call RetrieveWhDocDetail(iiPrb, ssPrb, ssDtl, ssVal, ssDtlFx, cctrl)
.AddNewLine 1
.AppendStyle (bodytext1)
.AppendText ssPrb
.AddNewLine 1
.AppendStyle (headings)
.AppendText ssDtlFx
.AddNewLine 1
.AppendStyle (bodytext)
.AppendText ssDtl
Loop
.AddNewLine 1

End With

'Add Attachments
sSql = "SELECT tbPics.picsPath FROM tbPics "
sSql = sSql & "WHERE (((tbPics.picsID)='"
sSql = sSql & [Forms]![frmCaseRegWHG]![txtCaseNbr] & "'))"

Set rs = New ADODB.Recordset
rs.Open sSql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
If rs.EOF = True And rs.BOF = True Then
'...
Else
itm = 1
Do While Not rs.EOF = True
Set EmbedObj(itm) = objAttachRTF.EmbedObject _
(EMBED_ATTACHMENT, "File", rs.Fields(0).Value)
rs.MoveNext
itm = itm + 1
Loop
End If
rs.Close
Set rs = Nothing

'Get the mail to appear in sent items folder
objNotesDoc.SaveMessageOnSend = True
objNotesDoc.Save True, True

'Send the message
If AutoSend = True Then
'objNotesDoc.postedDate = Now()
Call objNotesDoc.ReplaceItemValue("PostedDate", Now())
Call objNotesDoc.Send(0)
End If
If AutoSend = True Then
MsgBox ("Mail send.")
Else
R = MsgBox("Mail has been prepared. See draft folder ! " & vbCrLf & _
"Change if needed before sending out.", vbInformation, "Notes Mail")
End If
'Markeer [MailSend] veld om aan te geven dat de mail verstuurd werd.
MailSend = -1
Me.frameMail.BorderColor = RGB(0, 255, 0)

Else
MsgBox ("Lotus Notes Could Not Be Opened."), vbInformation
End If

Set objNotesDB = Nothing
Set objSession = Nothing
Exit Sub
Err_Handler:
If Err.Number = 7225 Then 'Picture file not found or not existing
MsgBox Err.Description
Resume Next
Else
MsgBox Err.Number & " " & Err.Description
End If
ErrMes = "Lotus Notes could not create mail" & vbCrLf
ErrMes = ErrMes & "Maybe Notes screensaver is on or Alarm is displayed." & vbCrLf
ErrMes = ErrMes & "Check Lotus Notes first and try again."
MsgBox ErrMes, vbInformation

End Sub

Hi, well after many hours of trying with the standard:

"DoCmd.SendObject acSendQuery, "Appendix A Data Required", acFormatXLS, .Fields![SW Email], , , "Data Needed For Appendix A Forms", rstSubject.Fields![Subject],0"

For some reason the above wouldnt work, it basically is in teh middle of a Do statement, it cycles a recordset, gets the Staff Members Name and Email address, it was the supposed to send an email, with a Excel Spreadsheet attached containing data relevant to them. When i ran this, it sent the first email but would not send the others. At the time there were 52 unique staff members in the record source, but would not send the other 51! It ran the updates to place the relevant information per staff member into a query, but just wouldnt email it.

So, i then look on the Knowledge base, and came up with another way to email, below.

This works fine, it sends out the 52 emails spot on, BUT i cant get it to send the results of a query, as you can see below its looking for an attachment. Does anyone know how i could change this to send the object query with the email? I dont want to have to export the spreadsheets one by one, email it, delete it and do all that for each staff member. At times this table could get to over 200 staff members.


Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

With objOutlookMsg
' Add the To recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add("Scott Holmes")
objOutlookRecip.Type = olTo

' Set the Subject, Body, and Importance of the message.
.Subject = "Data Needed For Appendix A Forms"
.Body = rstSubject.Fields![Subject]
'.Importance = olImportanceHigh 'High importance

' Add attachments to the message.
Set objOutlookAttach = .Attachments.Add("Appendix A Data Required")

' Resolve each Recipient's name.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
Next

'.Save
.Send
End With

Hi Folks,

Have dug around in the forum for an answer to this question but to no avail.
i have a training database that is used to book training courses. On booking a course it sends out a meeting request in Outlook using the code below...

My question is that for each training course I have a zip file with course materials and pre-course work. What i want to do is add this Zip file from a std location on to the email, my initial thought was something like...

...to add a new field to tbl_Courses
I wanted to add a field [Coursework]
In which I would put the file location "G:EDAGTestTest.zip" etc
Then get the vba code for the meetingrequest to read this, something like...

Dim outobj As Outlook.Application
Dim outappt As Outlook.AppointmentItem
Dim stDocName As String
Set outobj = CreateObject("outlook.Application")
Set outappt = outobj.CreateItem(olAppointmentItem)
With outappt
.Start = [txtCourseDate] & " 09:00"
.Duration = [txtDuration]
.Subject = [txtSubject]
.MeetingStatus = olMeeting
.Attachments = [Coursework]

but this does not seem to work, does anyone have any ideas?

Cheers

I've search for the last couple days and found all sorts of info on images, but don't have the skills to make it all come together. I previously was inserting each file manually. Reading through all the info here has talked me out of that. So, I downloaded PictDatabase2000 from Access Word Forums > Microsoft Access Discussion > Forms > "Attach" images to Access 2003 DB. Now I've added a bunch of fields to my table and form and have everything setup how I want it. Now I have a folder C:UsersJeffDocumentsCaps with 1200+ bmp files. I need all those added to the Picture field in my table. Obviously, I do not want to add each path manually. I've seen some VBA script and read a bit about running a bat to make this happen, but don't know how to do either of those things. I have worked with VBA in access a bit so would be able to create modules, but it's been years since I've done anything w/ VBA. If possible, I would like instructions at a very basic step-by-step level. Thank you for your help

Okay. So for my A2 ICT Coursework I have to create a database and I've ran into some trouble with my final report close to the deadline. Below is the report itself and I need to fill in the blanks output boxes using the query and the two tables.

On the report there are 5 sections: 1 for total and 4 for each of the 4 products. Each product has 3 subproducts - A, B and C. And I have a field with either S, L, I or P so they can be grouped.

I need a calculation of some kind to add up all the points for all 4 products that have been sold and I then need to be able to do this for each individual product.


Report - http://www.dbforums.com/attachments/...-progress1.png
Query - http://www.dbforums.com/attachments/...rogressqry.png
Table - http://www.dbforums.com/attachments/...ields-prod.png
Table - http://www.dbforums.com/attachments/...dcatalogue.png

A person on another site said I should do one of these but I don't know what any of it means or how to do it. If somebody could clarify or say which is the best option for me (an in-experienced database user and maker) or explain it then that would be very helpful.

what you could do is use a dsum for each of the categrories, and assign the value of that to a control, it could even be the source for that control
limit the rows processed to whatever the group is

failinmg that I'd resoprt to some VBA behind the relevant events

declare 4 variables at the top of the report
zero those variables in each group header (if you have more than one header/footer pairing then you'd need another set of 4 variables per header footer pairing

add the points value in the reports format event

in the footer fromat event assign the value of the variabels to the relavnt controls

or
I'd drop 4 hidden control onto the reports detail section, one for each type of point
assign a value to each of those controls
eg
=iif(,15,0)
so say you had a column called RowType and it could be one of "I"nvestment,"S"avings and so om
=iif(RowType="S",15,0)
you could use the expression builder to set the control source if you wished

then in the footer add four controls one for each category and set the source for those controls to be the sum of the detail controls defined above
add another control to add those 4 values to give a total points score for this group
you can then repeat the sum bit in other footers

or you coudl use the IIF in the underlying query so thart uyou have the points tally in the query and use as normal on a report Thanks.


Not finding an answer? Try a Google search.