Help auto-naming a file linked to a field. 1

[kakworks247]

I am not sure how to describe the problem so I will start at the beginning. I have an Access db/program. It takes an email and manipulates the data and places it into the appropriate files in the db. The last field is to be a link to external documentation. Herein lies the problem.

The external doc is a Word template. The template will have to be filled in by the people as they complete the project. I need to somehow have the file already created and linked at the same time the email is processed.

Basically I need to know how to have access populate the link field with a link like c:\doc1234. 1234, however, need to be the same number as the autonumber for that table entry. They need to be able to open the doc externally so they need the number as the cross reference.

Once I have the link, anyone know how to make it 'copy' the template to the name doc1234 so it is ready to be filled in?

If I haven't made any sense, please let me know. They of course want this done by Wednesday.

Any help is greatly appreciated.
Thanks so much,
Kimberly

I have an access db that I need to link to a word document. I have a field in the db just for the link. The table is populated automatically by a vba program that takes an email and puts the items into the db table. The Word file needs to be named xxx1234 where 1234 correspond to the autonumber so the Engineers can keep track of which table item goes with which word doc easily.
The word document needs to be the 'same' to start with as they will be filling in the information for each item so I figured I would use a template.
I'm not sure I have explained this well

 

[SgtJarrow]

In your code where you fill in all the appropriate fields, you only need a line to fill in this last field. Something like:

rs.fields("Reference&quot = "C:\doc" & CStr(rs.fields("AutoNumber&quot) & ".doc"

If you want to post the code, we can probably help you out more than that.

****************************
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[kakworks247]

mstrmage1768 --
Thanks. Here is the code --



Dim myolApp, myNameSpace, myfolder, myItem, myNewfolder
Dim SenderEmail As String, SenderName As String
Dim dbs As DAO.Database, iOrd As Integer, iMax As Integer
Dim rstUsers As DAO.Recordset
Dim rstSoftOrders As DAO.Recordset
Dim emailContents As String, postIt As Integer
Dim RequestorName As Variant, RequestorEmail As Variant
Dim ModelAffected1 As Variant, ModelAffected2 As Variant, ModelAffected3 As Variant
Dim ModelAffected4 As Variant, ModelAffected5 As Variant, ModelAffected6 As Variant
Dim iModel As Integer, Models As Variant, ModelTemp As Variant
Dim ReasonCode As Variant, DocumentationAvailable As Variant
Dim DateSubmitted As Variant, TimeSubmitted As Variant
Dim Description As Variant

On Error GoTo makeOrderEmail_error

Set dbs = CurrentDb

Set myolApp = CreateObject("Outlook.Application&quot
Set myNameSpace = myolApp.GetNamespace("MAPI&quot
Set myfolder = myNameSpace.Folders( _
"Personal Folders&quot.Folders(OrdersInFolder)
Set myNewfolder = myNameSpace.Folders( _
"Personal Folders&quot.Folders(OrdersDoneFolder)

'myfolder.Display - display the folder in Outlook

Set rstSoftOrders = dbs.OpenRecordset(OrderTable, DB_OPEN_DYNASET)

...

' note posted here is code to parse through email and save the appropriate information into the variables. If you need any of that code, just let me know but I didn't want to clutter the email.

postIt = MsgBox(RequestorName & ": " & ModelAffected1 & ": " & Description, vbYesNoCancel, "Post The Following&quot
If postIt = vbYes Then

On Error Resume Next
rstSoftOrders.AddNew ' Create new record.
rstSoftOrders("RequestorName&quot = RequestorName
rstSoftOrders("RequestorEmail&quot = RequestorEmail
rstSoftOrders("ModelAffected1&quot = ModelAffected1
rstSoftOrders("ModelAffected2&quot = ModelAffected2
rstSoftOrders("ModelAffected3&quot = ModelAffected3
rstSoftOrders("ModelAffected4&quot = ModelAffected4
rstSoftOrders("ModelAffected5&quot = ModelAffected5
rstSoftOrders("ModelAffected6&quot = ModelAffected6
rstSoftOrders("ReasonCode&quot = ReasonCode
rstSoftOrders("DocumentationAvailable&quot = DocumentationAvailable
rstSoftOrders("DateSubmitted&quot = DateSubmitted
rstSoftOrders("TimeSubmitted&quot = TimeSubmitted
rstSoftOrders("Description&quot = Description
...

The field in the table is "Attachments" with a datatype as Hyperlink.

The word doc is actually a template so the same information will go into each link.

Let me know if this makes any sense.
Thanks again for your help (Can you tell I'm rather new at this, trying to learn with books and trial and error and a tremendous amount of help & support from this site -- what a godsend!)
Kimberly

 

[SgtJarrow]

like I noted above, you should be able to use:

rstSoftOrders("Attachments&quot = "C:\doc" & CStr(rstSoftOrders("AutoIdFieldName&quot) & ".doc"

or something to that affect as the last line in the code you posted. You need to change thne "AutoIDFieldName" to the name of your field where the autoid is generated. This would give you something like:

C:\doc1.doc
C:\doc2.doc

and so on....

****************************
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[kakworks247]

That worked perfectly (your star is coming!). The problem I now have and I'm not sure where to find the answer is -- I can get the dynamically named link but there is no file. Any idea how (or where to find the answer to) to get the ecr.dot to become ecr1.doc, ecr2.doc, ecr3.doc without me manually creating all of these?
Thanks again

 

[SgtJarrow]

This should work:

place the following dim in you declarations area

Dim fso As Variant

and this at the bottom of your code after the attachment line

Set fso = CreateObject("Scripting.FileSystemObject&quot
fso.CopyFile "C:\ecr.dot", "C:\ecr" & CStr(rstSoftOrders("AutoIdFieldName&quot) & ".doc"

****************************
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[kakworks247]

Thanks, I get it to create the file and display the link name. The link, however, does nothing. The field "Type the file or webpage name:" is blank. Any idea how to get the file name to populate that field so when I click it actually opens the file?

my code so far is:

postIt = MsgBox(RequestorName & ": " & ModelAffected1 & ": " & Description, vbYesNoCancel, "Post The Following&quot
If postIt = vbYes Then

On Error Resume Next
rstSoftOrders.AddNew ' Create new record.
rstSoftOrders("RequestorName&quot = RequestorName
rstSoftOrders("RequestorEmail&quot = RequestorEmail
rstSoftOrders("ModelAffected1&quot = ModelAffected1
rstSoftOrders("ModelAffected2&quot = ModelAffected2
rstSoftOrders("ModelAffected3&quot = ModelAffected3
rstSoftOrders("ModelAffected4&quot = ModelAffected4
rstSoftOrders("ModelAffected5&quot = ModelAffected5
rstSoftOrders("ModelAffected6&quot = ModelAffected6
rstSoftOrders("ReasonCode&quot = ReasonCode
rstSoftOrders("DocumentationAvailable&quot = DocumentationAvailable
rstSoftOrders("DateSubmitted&quot = DateSubmitted
rstSoftOrders("TimeSubmitted&quot = TimeSubmitted
rstSoftOrders("Description&quot = Description
rstSoftOrders("Attachments&quot = "C:\my documents\ecr\ecr" & CStr(rstSoftOrders("ECRNumber&quot) & ".doc"
Set fso = CreateObject("Scripting.FileSystemObject&quot
fso.CopyFile "C:\WINDOWS\Application Data\Microsoft\Templates\ecr.dot", "C:\my documents\ecr\ecr" & CStr(rstSoftOrders("ECRNumber&quot) & ".doc"

I tried moving the fso lines above the attachment line to see if that made a difference and it did not.

Thanks again!
Kimberly

 

[SgtJarrow]

I am not sure I follow you...this works fine for me when I use the code in a test file....There is nothing to "link" as the hyperlink field merely tells the system to open the file or page at the specified text....can you give me the actual error you are getting???

****************************
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[kakworks247]

I do not get any error messages. It just does not open anything. The link stays blue. Right click on the link and open up Hyperlink/Edit Hyperlink the display name is correct but the actual link is blank. The field is set up as a hyperlink. Did that make any sense?

Kimberly

P.S. -- I love your quote at the end!

 

[SgtJarrow]

Don't know....I am not experiencing that. Are you sure the field is defined as a hyperlink at teh table level???

****************************
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[kakworks247]

I double-checked. Would you like me to offline email you the file to look at?
Kimberly

 

[SgtJarrow]

sure....my work addy please.

****************************
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com