Hyperlinks Stored in Table After User Saves Snapshot File

[Vylent Fyre]

Hi all!

I was not sure where to post this so please forgive me if I posted this
question in the wrong forum.

I have scoured this website and wasn't able to find what I am hoping is a
possible feat to create.

I currently have a macro in place where the user clicks the control button,
it opens the form, they update the form accordingly, then it gets sent to
excel, the user uploads to our system, the user obtains the JE # and inputs
that on the form that they updated previously, then the user can preview and
print this report, and then the macro prompts for them to save this snapshot
report to a location they specify.

My ultimate goal is once they save this file, it will store this path name
and file name into a hyperlink field inside a specific table. Is this
remotely possible?

 

[Bonnie]

Sounds like a pretty cool process that will save some body lots of time.

I don't see why you can't capture the file name and path - you have it when
you save the file don't you? Use an update query to update a field whatever
particular record you filter with that info. Use the hyperlink data type
when you design the table.

Bonnie

http://www.dataplus-svc.com

 

[Vylent Fyre]

Yes, it would definitely save all of us a lot of time!

Maybe I'm not grasping what you're saying. I don't know exactly how to tell
MS Access to "capture" the file name and path name when they save it. I'm
sure there's got to be some VBA coding that will do this, though. This is
the piece I'm missing. Well, perhaps I should say the biggest piece I'm
missing...

 

[Bonnie]

What are you using to prompt the user to save and where are they keying the
path and file name? If it is a control on a form, you can capture it from
there.

Bonnie

http://www.dataplus-svc.com

 

[Vylent Fyre]

As part of the macro, it's the "OutPutTo" command, a Report as a Snapshot
file. What is/are the command(s) to capture the UNC Path Name and File Name
from there?

Thank you so much for your assistance on this; you have no idea how much
this will help everyone here in my department!!

 

[Bonnie]

Do it in VB - you can pass a variable for the file name like this:

dim mvar as string
mvar = format(now(),"mmddyy"&.xls)

DoCmd.OutputTo acOutputTable, "Employees", _
acFormatXLS, mvar, True

Whatever button or control you were going to run the macro from, run the VBA.

Bonnie

http://www.dataplus-svc.com

 

[Vylent Fyre]

This is most definitely what I needed! I just have one small "snag" - I want
to force the users to save the file as a Snapshot - Is this possible? RTF
doesn't keep all the formatting and everything I have in the report.

I wouldn't normally care if it did or not, but these reports are Journal
Entries and auditors will be auditing them, so I need to keep it as close as
to the original Report as possible when I save it....

 

[Bonnie]

I'm so glad it's helpful.

Change the acFormatXLS to acFormatSNP which is a snapshot. Remember that
the users will need a snapshot viewer on their desktops.

Bonnie

http://www.dataplus-svc.com

 

[Vylent Fyre]

Bonnie, thank you SO much for your time and assistance on this! You're the
reason why I haven't gone bonkers the past two days!

That's exactly what I did and I'm still having issues - Here's a copy of my
code; what could I be doing wrong? I'm thinking it's this part - mvar =
Format(Now(), "mmddyy" & snp)

Here's a copy and paste of my code. This is connected to the Save JE button
that triggers this event via the On Click event procedure.


Private Sub btn_Save_JE_and_Exit_Form_Click()
On Error GoTo Err_btn_Save_JE_and_Exit_Form_Click

Dim mvar As String
mvar = Format(Now(), "mmddyy" & snp)

DoCmd.OutputTo acOutputTable, "TEMP HYPERLINKS", _
acFormatSNP, mvar, True



Exit_btn_Save_JE_and_Exit_Form_Click:
Exit Sub

Err_btn_Save_JE_and_Exit_Form_Click:
MsgBox Err.Description
Resume Exit_btn_Save_JE_and_Exit_Form_Click

End Sub




Also, will this put the hyperlink they've typed/saved the file as into a
table inside the database? I keep thinking OutputTo is something akin to
exporting to an external file...? I apologize for being one of those...
inquisitive idiots.