Add 'attachment' field with vba

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!


Post your answer or comment

comments powered by Disqus
How can I insert an object (image in this case) into a OLE object field with VBA code?

Thanks!

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

Hi,

I added three new fields in an existing table with VBA. These new fields where all added at the end of the table (after the other fields). Is it possible to add them (or move them) at the beginning or inbetween two other fields with VBA?
If so, how can this be done.

Thanks already.

Hi Every one

I have a table named Table1 and it has two fields 'ID' and an 'Files' (Attachment field with multiple records). I made a query By draging the ID and File name component of the attachment field. My form is based on That query. Now I have 2 fields on the form .The 'ID' field and 'Table1.Files.FileName' . I renamed the 'Table1.Files.FileName' to
'tbxFileName' and on the forms ID's on click event I have theis code Igot from this forum

This is the code window

Option Compare Database
Private Sub ID_Click()
Dim strFilePath
With Me.RecordsetClone
.Bookmark = Me.Bookmark
strFilePath = "C:Temp" & Me.tbxFileName
If Dir(strFilePath) "" Then VBA.Kill strFilePath
![Table1.MyDocs.FileData].SaveToFile strFilePath
VBA.Shell "Explorer.exe " & Chr(34) & strFilePath & Chr(34), vbNormalFocus
End With
End Sub

when i click the id i get runtime error
'Item not found in this collection'
and this line in the code window is yellow '![Table1.MyDocs.FileData].SaveToFile strFilePath'

Can some one please help me to work this out especialy by 'Super Moderator' who made the code
I would apreciate any help
Thank you

I use the code below to add a Text field to a table. I do NOT use a sub or function to store the code


	Code:
	 
Dim Db As DAO.Database 
Dim fld As DAO.Field 
Dim tdf As DAO.TableDef 

Set Db = Application.CurrentDb 
Set tdf = Db.TableDefs(strTableName) 

' First create a field with data type = Text 
Set fld = tdf.CreateField(strFieldName, dbText) 

' Append the field 
With tdf.Fields 
.Append fld 
.Refresh 
End With 

ExitHere: 
Set fld = Nothing 
Set tdf = Nothing 
Set Db = Nothing

It works the first time I use the code, but does not work the second time I include the code to add a second text field to the same table. The instances of the code are included in the the On Open event of a form and are run as part of an update process, depending on which version the user has. I do not get any messages. The second Text field is not added to the table. In the second instance of the code I use different names for the Db2, tdf2, fld2.

The second Text field is added if I use an Application.Quit in between the two field addition codes.

Any ideas?
John

Hi,

I am trying to add a field with a combo box which allows multiple values to the back end of a split database. When I do this and then open the form on the front end I get the following error:

"The search key was not found in any record."

The values are chosen from a value list that I created.

If I add a combo box field that does not allow multiple values I can open the form fine.

The database was created in Access 2007 and is now running on Access 2010.

How do I fix this?!

Thanks in advance!

Hi there,

I have a MS Access 2007 table that includes an attachment field with some documents. When I import the table into another database, the attachment field can not be imported. Is there a way to get that data into the new table without having to re-attach all of the documents again?

I have a query where I am attempting to add two fields with same name but from different queries eg. AM tx querie Sum of Cap + PM tx querie Sum of Cap. In the caluclated fiels I do not know how to differentiate in the fourmula between the two Sum of CAP

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

Hello again,

I have an email function that uses an .oft template however, I want it to reference an attached .oft template in a table. This DB will be sent career field wide for my job so I need it to be as user friendly for those who have no experience working with VBA for ex they wouldn't know how to change the filepath on their own in VBA. Hopefully this is a simple question to answer.

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



Hello folks,

I do have the following problem, and - please do tell me- if I don't see the obvious.

I changed to Office 2007 and am busy changing an Access Application. In the course of that, I included some .gif files in an attachment field, which were hitherto stored in external files and loaded when required for display.

Say we have a company table (OPR_Operator) and a country table (GEO_Country). The country table has a country flag (Field GEO_Country.Flat) as attachment file.

If I create the following query:

SELECT OPR_Operator.OPID, OPR_Operator.Operator, OPR_Operator.Country, GEO_Country.CountryName, GEO_Country.Flag
FROM OPR_Operator INNER JOIN GEO_Country ON OPR_Operator.Country = GEO_Country.CountryCode;

I can edit existing records in a form without any problem. If I change e.g. the Country field in OPR_Operator, the new flag is displayed.

The problem occurs, when I want to add a new record. No way to do that. As soon as I enter the Country, I get the following error message:

The current field must match the join key '?' that serves as the one side of one-to-many relationship. Enter a record in the "one" side table with the desired key value, and then make the entry with the desired join field in the "many-only" table.


I appreciate that this reads like a beginner's error. The funny thing, though is, that I entered a country code, which DOES exist in the GEO_Country table.

Any help?

Very much appreciated.

Best,

Jan

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.

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

Hi Every body
I would apreciate if any one would be able to help me do this. I am quet sure this is posible though not siple.I have a database with just one table named Table1. It has two fields an "ID' field and 'Files' (wich is the name of the the attachment field). I have build a form based on that table and on the Forms 'ID's on click event I have put a cord
to open the attachment. It opens the attachment just by one click on the 'ID' field. But the problem is it only opens the attachment file of the first record even if i click on the second records 'ID'. Can some Body help me make it open The attachment for the record's 'ID' Thats being clicked. here is the cord its on an access blog.
Can some body please help me. I mainly use macros and very new to VBA.

Thanks

Private Sub ID_CLICK()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Const strTable = "Table1" '

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

Hi

I am new to this forum and quite new to vba so if someone could help me with this code it would be appreicated.

What the code does is add's a new record to a table using the information it gets from a form and that works fine.

Below the first code is what I am trying to do.



Dim rs As ADODB.Recordset
On Error GoTo HandleError
Set rs = New ADODB.Recordset
rs.Open "tblRegister", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
With rs
![IDNHHMeter] = Me.txtIDNHHMeter
![Register] = Me.txtRegister
.Update
End With
rs.Close
Set rs = Nothing

ExitHere:
Exit Sub
HandleError:
MsgBox Err.Description
Resume ExitHere


What I would like to do is put a loop on this code so I can add multiple records depending on what number is in the text box txtRegister. In each record the IDNHHMeter would stay the same but the register would start at 1 and have an increment of 1 until it gets to the number in txtRegister. Below is my code which doesnt seem to work. Any ideas?

Dim R As Integer
Dim i As Integer

Do

For i = 1 To R
Dim rs As ADODB.Recordset
On Error GoTo HandleError
Set rs = New ADODB.Recordset
rs.Open "tblRegister", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
With rs
![IDNHHMeter] = Me.txtIDNHHMeter
![Register] = i
.Update
End With
rs.Close
Set rs = Nothing
Exit For
Next

Loop Until i = R

ExitHere:
Exit Sub
HandleError:
MsgBox Err.Description
Resume ExitHere

End Sub

Alright, this just doesn't make any sense.

I have two MDBs, one is the front end application, the other is the actual table database.

Some modifications have arisen that basically require me to add a few things to the "table" database, so the MDE application will perform according to client specifications. Well, I don't want to toast the data that already exists in the database, nor do I want to deal with copying the data, so I figured, I would write an Update script, that would simply add the columns and constraints to the existing table and add the primary key parent table to the table database, and then add the linked table definition to the mde database.

So, being more of a SQL Server fan, i perfer sql , so I use it to create the table.

set db = OpenDatabase(path & dbfile)

sql = "Create table X (...)"
db.Execute SQL
sql = "ALTER Table Y ADD Column X"
db.execute sql

this part works fine (other than my post concerning the add constraint but this is about the link table so, i won't repost the same question).

So now, I've DB a database variable that is set tot he the "tables.mdb" and then CurrentDB which is the current open MDE file.
I try:

set tdf = CurrentDB.CreateTableDef("X", dbAttachedTable, "X", ";DATABASE=" & path & dbfile);
CurrentDb.TableDefs.Append tdf

the createtabledef says invalid argument. okay. why, dunno, but access is twitchy and not very helpful on error mearning, so i try instead:

set tdf = CurrentDB.CreateTableDef("X")
tdf.Connect = ";DATABASE=" & path & dbfile
CurrentDb.TableDefs.Append tdf

on the Append() call, i get "No field defined -- cannot append tabledef"
Well, frankly that's stupid. Of course id didn't define any fields. the fields are defined in the linked database, not this one. When i use the GUI to link a table I don't have tell it ANYTHING about the fields. only what the access mdb file is, and the table i want.

So, where is that command, the simple: "Add Link to this table on that MDB" command. I mean, if the GUI can do it that simply, I should be able to too. It also begs the question, I can create the links and then have my App.mdb file reference the new tables and columns i've added. But if i send over the mde file which is going to be looking at the "old" tables.mdb (that is missing those tables/columns) is that going to muck up my queries and forms, from the get go, or will it not be concerned since my first action is to update the tables.mdb with the script before any of those new features are accessed?

Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner

Hi,

The Question
How do I create a form that adds all fields of it's record source on load (by VBA)?

The Background
I have multiple categories of items.
the common fields of all categories are located in the main table.
every category has it's own parameters and hence has a different table (category N will use both columns of tMain table and tCategoryN table)

I want to let the users the ability to add categories (and hence the fields will constantly change)

Thanks.

I have 2 tables that are linked via the personnel_ID field. Table 1 holds personnel information and table 2 holds training information. The link works fine with data that is already in the database.
When I add a new record in table 1 (personnel information), I have to manually add a new record, in table 2 (training information) in order for the new person to show up in the forms that use queries derived from table 2.
Is there a way to automatically add a new record to table 2 with VBA when a new record is added to table 1?

Hi all,
I am working on a multiple optional field search form, the query for which is created by a VBA setQry.
I have very little experience with VBA as I am working with a Web Database which forces one to rely on Macros.

The form and query are working great, except that in order to make one of the fields work, I have to manually edit the criteria in the query design window because I don't know how to make the VBA create it correctly. Which would be fine if I only had to do it once, but I hope to expand the capabilities of this search form, which means modifying the VBA, running to to set the query up, then pasting the criteria multiple times into the query design window, every time I change it!

The relevant line in the VBA code is

	Code:
	"( (CategoryIDs=[pCat1]) OR ([pCat1]="""") ) AND " & _

(pCat1 is a text parameter)

which places multiple instances of [pCat1] in the criteria for CategoryIDs.

Then I paste this criteria

	Code:
	Like [pCat1] Or Like "*," & [pCat1] Or Like [pCat1] & ",*" Or Like "*," & [pCat1] & ",*"

into each place [pCat1] appears.

This works perfectly, so please don't try to fix my actual criteria, all I want to know at this stage is how to have the VBA create the criteria I otherwise have to paste in, if possible.

The reason for the criteria is to pick a number out of a list of numbers like so: 95,96,1033,864,854,874 without giving me other numbers which contain the number, and taking into account the varying places the numbers can appear in relation to the commas.

I hope to eventually add the ability to optionally search for multiple CategoryIDs in one search, but before I can even start experimenting with that, I need to get to the point where I don't have to be pasting the criteria in every time I modify and run the VBA as it is terribly time consuming!

I needed a button to send information from the text fields in a form as the body of an email, and then attach the attachments that are in the attachment field "IssuePics." I have read hours of forums and found the code that turns the text fields into the body of the email, and I thought I found the code to attach the attachments. I'm having trouble merging the two.
This is the send attachments portion:


	Code:
	 
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Dim OutlookAttach As Outlook.Attachment
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
 
Dim db As DAO.Database
Dim rsParent As DAO.Recordset2
Dim rsChild As DAO.Recordset2
Set db = CurrentDb
Set rsParent = Me.Recordset
rsParent.OpenRecordset
Set rsChild = rsParent.Fields("Attachments").Value
While Not rsChild.EOF
If Dir("C:dbtemp", vbDirectory) = "" Then
MkDir ("C:dbtemp")
Else
'do nothing for the "C:dbtemp" directory already exists
'MsgBox "C:dbtemp directory already exists"
End If
rsChild.OpenRecordset
rsChild.Fields("FileData").SaveToFile ("c:dbtemp")
rsChild.MoveNext
Wend
With MailOutLook
.BodyFormat = olFormatRichText
'.To = "email address"
'.CC = " "
.Subject = "test"
Dim fso As Object, SourceFolder As Object, SourceFile As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set SourceFolder = fso.GetFolder("C:dbtemp")
For Each SourceFile In SourceFolder.Files
.Attachments.Add SourceFolder.Path & "" & SourceFile.Name
Next
'Send email
'.DeleteAfterSubmit = True 'This would let Outlook send the note without storing it in your sent bin
'.Send
Kill "C:dbtemp*.*" ' delete all files in the folder
RmDir "C:dbtemp" ' delete folder
End With

And this is the one I found to send the fields in the body:


	Code:
	 
Dim oFilesys, oTxtStream As Object
Dim txtHTML As String
Dim olApp As Object
Dim objMail As Object
DoCmd.OpenReport "ReviewWorkOrdeMROR", acViewPreview, , "WorkOrderID=" & WorkOrderID, acHidden
DoCmd.OutputTo acOutputReport, strReviewWorkOrdeMROR, acFormatHTML, "C:temp" & strReviewWorkOrdeMROR & ".HTML", False
Set oFilesys = CreateObject("Scripting.FileSystemObject")
Set oTxtStream = oFilesys.OpenTextFile("C:temp" & strReviewWorkOrdeMROR & ".HTML", 1)
txtHTML = oTxtStream.ReadAll
oTxtStream.Close
Set oTxtStream = Nothing
Set oFilesys = Nothing

'Set olApp = GetObject(, "Outlook.Application") 'See if Outlook is open
'If Err Then 'Outlook is not open
Set olApp = CreateObject("Outlook.Application") 'Create a new instance of Outlook
'End If
Set objMail = olApp.CreateItem(olMailItem)
With objMail
''.BodyFormat = olFormatHTML
.HTMLBody = txtHTML
'.Recipients.Add ""
.Subject = ""

'.Send if you want to send it directly without displaying on screen
.Display
End With
'you can delete the outputted file if you want
Kill "c:temp" & strReviewWorkOrdeMROR & ".HTML"
Set olApp = Nothing
Set objMail = Nothing

I really don't know how to merge them and when I ran the code to attach the attachment field, I got a compile error: "User-defined type not defined."

I'm using Access 2010 and Office 2010.

I appreciate any and all help most graciously! Thank you.


Not finding an answer? Try a Google search.