Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rating: Thread Rating: 2 votes, 5.00 average. Display Modes
Old 03-30-2009, 10:23 AM   #1
SweetSolutions
Registered User
 
Join Date: Mar 2009
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
SweetSolutions is on a distinguished road
VBA for Attachments?

Good Day,
Does any body know of a VB code or Module to do Attachments automatic in Access 2007?
I have a DB set up with a Table containing StockCode and Attachment Fields, what I am currently doing is attaching each file manualy, and there are 11 thousand to do. it is all .jpg images renamed to the Stock Code in My Table.

Anything would help.

Regards

SweetSolutions is offline   Reply With Quote
Old 03-30-2009, 11:05 AM   #2
LPurvis
AWF VIP
 
LPurvis's Avatar
 
Join Date: Jun 2008
Location: North of England
Posts: 1,248
Thanks: 0
Thanked 26 Times in 26 Posts
LPurvis has a spectacular aura about LPurvis has a spectacular aura about LPurvis has a spectacular aura about
Re: VBA for Attachments?

Oli's kindly provided a complete example.
http://www.access-freak.com/tutorials.html#Tutorial07

Welcome to AWF.
__________________
Leigh Purvis | Microsoft Access MVP |
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
LPurvis is offline   Reply With Quote
Old 03-30-2009, 11:15 AM   #3
HiTechCoach
Problem Solver
 
HiTechCoach's Avatar
 
Join Date: Mar 2006
Location: Oklahoma City, OK
Posts: 3,803
Thanks: 2
Thanked 68 Times in 65 Posts
HiTechCoach is a jewel in the rough HiTechCoach is a jewel in the rough HiTechCoach is a jewel in the rough
Re: VBA for Attachments?

Welcome!

See if this gets you started:

Code:
On Error GoTo Err_AddImage

Dim db As DAO.Database
Dim rsParent As DAO.Recordset2
Dim rsChild As DAO.Recordset2

Set db = CurrentDb
Set rsParent = Me.Recordset

rsParent.Edit

Set rsChild = rsParent.Fields("AttachmentTest").Value

rsChild.AddNew
rsChild.Fields("FileData").LoadFromFile ("c:\Sunset.jpg")

rsChild.Update
rsParent.Update

Exit_AddImage:

Set rsChild = Nothing
Set rsParent = Nothing
Exit Sub

Err_AddImage:

If Err = 3820 Then
MsgBox ("File already part of the multi-valued field!")
Resume Next

Else
MsgBox "Some Other Error occured!", Err.Number, Err.Description
Resume Exit_AddImage

End If

__________________
Boyd Trimmell, Microsoft MVP - Access Expert, aka
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.




To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
HiTechCoach is offline   Reply With Quote
Old 03-30-2009, 01:20 PM   #4
SweetSolutions
Registered User
 
Join Date: Mar 2009
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
SweetSolutions is on a distinguished road
Re: VBA for Attachments?

Thanks for your replies,
Got some questions if you don't mind.

The Sample Code that you gave me, where do I put that code in?
My Table that I am using is Called "Items with Pictures"
the 2 fields that I have is Stock Codes and Attachment.
The Pictures are all located under C:\Desktop\Stock Pictures.
The Pictures have all got a Unique Number(9 digets) and that would be my link to the Stock Codes.

Thank you for your help, you are great!
Greatings from Namibia
SweetSolutions is offline   Reply With Quote
Old 04-01-2009, 01:10 AM   #5
SweetSolutions
Registered User
 
Join Date: Mar 2009
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
SweetSolutions is on a distinguished road
Re: VBA for Attachments?

Hi There,

I created a TestDb, and got it working from a Form,
Works nice, BUT, and here is my problem...
I need to Change the Code every time cause it only Attaches the Picture that was in the Code.

I need to do it with 11000 Pictures?, all as Unique records?

Regards
SweetSolutions is offline   Reply With Quote
Old 04-01-2009, 05:54 AM   #6
LPurvis
AWF VIP
 
LPurvis's Avatar
 
Join Date: Jun 2008
Location: North of England
Posts: 1,248
Thanks: 0
Thanked 26 Times in 26 Posts
LPurvis has a spectacular aura about LPurvis has a spectacular aura about LPurvis has a spectacular aura about
Re: VBA for Attachments?

Well you can't possibly hard code the image path.
You need to select your location and iterate through the files therein.
I don't know how you want to create the related record (if they're realted to anything else other than being a simple image storage row).

The simplest example would be using Dir to enumerate the files in a folder.
__________________
Leigh Purvis | Microsoft Access MVP |
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
LPurvis is offline   Reply With Quote
Old 04-01-2009, 06:08 AM   #7
SweetSolutions
Registered User
 
Join Date: Mar 2009
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
SweetSolutions is on a distinguished road
Re: VBA for Attachments?

Query2STOCK_CODEExpr1001BIN_CODEPictureData.Picture.FileDataGROUP CLASSESFLAG PICTURE423751514ACTUATOR:ELECTRO-PNEUMATIC423751514.JPG1
4810FLAG762155041ACTUATOR:ELECTRO-PNEUMATIC762155041.JPG1
4810FLAG423723235ACTUATOR:ELECTRO-PNEUMATIC423723235.JPG1
4810FLAG423751516ACTUATOR:HYDRAULIC-PNEUMATIC;ROTARY423751516.JPG1
4810FLAG423751519ACTUATOR:HYDRAULIC-PNEUMATIC;ROTARY423751519.JPG1
4810FLAG423751521ACTUATOR:HYDRAULIC-PNEUMATIC;ROTARY423751521.JPG1
4810FLAG423751520ACTUATOR:HYDRAULIC-PNEUMATIC;ROTARY423751520.JPG0
4810FLAG423751518ACTUATOR:HYDRAULIC-PNEUMATIC;ROTARY423751518.JPG0
4810FLAG423751512ACTUATOR:HYDRAULIC-PNEUMATIC;ROTARY423751512.JPG0
4810FLAG423751511ACTUATOR:HYDRAULIC-PNEUMATIC;ROTARY423751511.JPG0
4810FLAG423751517ACTUATOR:HYDRAULIC-PNEUMATIC;ROTARY423751517.JPG0
4810FLAG
Hi this is what I am doing at the moment, Manual copy field BIN_CODE, Double clicking on field Picture(Attachment). in the Next Screen(Choose File) I copy the BIN_CODE in, then Enter, then OK....
Thanks

SweetSolutions is offline   Reply With Quote
Old 04-01-2013, 03:09 AM   #8
elyamani
Newly Registered User
 
Join Date: Jan 2013
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
elyamani is on a distinguished road
Re: VBA for Attachments?

Hay Guys
The above link is great ! but how i create dialog box and select the file i want to attach ?

appreciate your support
elyamani is offline   Reply With Quote
Old 04-01-2013, 05:09 AM   #9
elyamani
Newly Registered User
 
Join Date: Jan 2013
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
elyamani is on a distinguished road
Re: VBA for Attachments?

I`m found a solution but the problem is it is attaching the file at the first record only ! i want to pick the record which i`ll attach for ex
i want to attach file at record 100 so, how could i do that ?
appreciate your help guys

elyamani is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Trying to recover corrupt VBA code kjcmas Modules & VBA 7 03-02-2011 10:32 AM
vba class mikevds Forms 5 02-25-2009 09:53 AM
Training in VBA taniadimitri Forms 1 07-04-2007 01:49 PM
Need VBA in a database - but cant think what for crazymarvin Modules & VBA 2 06-03-2007 08:06 AM
Stored Parameter Query vs. Built SQL Statement in VBA (FE/BE Environment) directormac Queries 3 01-06-2004 09:45 AM




All times are GMT -8. The time now is 03:35 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
(c) copyright 2010 Access World