Links from combo or list box?

[NascarBaritone]

I have searched high and low on the forums to try and find the answer to my question, but to no avail.

Here is essentially what I am trying to do:

I have a form with hundreds of users that have the typical information included (i.e., address, phone #, e-mail, etc.) I am trying to link to files that I have on a central server through a combo box or list box. What would happen is, when going to a particular record, the person would have a link to three tax files in a combo or list box (one for 2006, one for 2007, one for 2008). Ideally, the text in the box would not be the full file name (as it is quite lengthy), but just the word "2006," "2007," "2008." I would select the year, hit a command button and the .pdf file for that year would open. Hopefully this makes sense, but I could try and elaborate more if need be.

 

[maxmangion]

Do you mean that there will be a 2006 pdf file different for each record ?

If you simply want to open a file when a particular year is selected, try searching for Application.FollowHyperlink

 

[NascarBaritone]

That is correct. Each person in the database has their own 2006 file. For example: C:\...\3050 User Name1.pdf, C:\...\3050 User Name2.pdf, etc. The same is true for 2007 and 2008.

 

[maxmangion]

Therefore you can try something like the following:

Application.FollowHyperlink "C:\" & [UserNameFieldName] & ".pdf"

Substitute UserNameFieldName with an actual control name on your form which has the user name

 

[NascarBaritone]

I guess I should have been more specific as to how experienced I am with Access. Literally I started and completed this first database last week. This was my first time working in Access. That being said...I do grasp things quickly.

Let me see if I can be more specific:

I have 541 records in my form "Reps." I go to John Smith's record. In this record there is a list box with Row Source Type "Value List" and Row Source "2006;2007;2008" (which I entered when creating the box. When I select "2006" in Form View and click the command button, the file "H:\PLUS\SALES\COMP\Reps 2008\Reps\Last Name O-Z\Smith, John 214\SAS\214 John Smith 2006.pdf" will open. Likewise, if I go back and select "2007" and click the command button, the file "H:\PLUS\SALES\COMP\Reps 2008\Reps\Last Name O-Z\Smith, John 214\SAS\214 John Smith 2007.pdf" will open.

Now, if I go to Sally Jones's record the same information will be there, but when I click her 2006 from the list box and the command button, the file "H:\PLUS\SALES\COMP\Reps 2008\Reps\Last Name A-G\Jones, Sally 214\SAS\214 Sally Jones 2006.pdf" will open.

Hopefully this makes more sense and the more specific the response the better. And for what it's worth...I'm still reading the searched posts waiting for a response!

 

[NascarBaritone]

I'm bumping this to see if anyone new might have some tips as to how I would go about doing this.

 

[georgedwilkinson]

maxmangion gave the basic process. What don't you understand about it?

Do you need one of us to write it for you from scratch? Or do you need just a little more help getting the concept? It is unclear what you need help with.

From a high level, you'll have code in the "On Change" event of your combo-box that will build the string for your file name, then you'll open that "string" (FollowHyperlink is one of the ways to do that).

Which part of that process do you need more information about?

 

[NascarBaritone]

I think I understand the concept it is the hardwiring of it all that I don't understand. Esentially what I mean is where everything goes. Perhaps if you could write an example with the information that I have given about would get me going visually.

 

[NascarBaritone]

Here is what I have:

Private Sub Combo186_Change()
vardoc = Me!Branch.Value & " " & Me!FirstName.Value & " " & Me!LastName.Value & " " & "2006" & " " & "SAS"
Application.FollowHyperlink "H:\OP_PLUS\EQSALES\COMPLIANCE\Registered Reps 2008\Reps\" & vardoc & ".pdf"
End Sub

Now when I change to any of the other years it opens this file. What I want to know is how to assign this particular code to when I select 2006 and a different file to when I click 2007 and a different file when I select 2008.

 

[georgedwilkinson]

Well, you've got "2006" hard-coded into your "vardoc" variable. Didn't you say you have a combo-box with the year data? If so, you just replace "2006" with Me.Combo186.

 

[NascarBaritone]

This code works just fine. The reason I have it hardcoded is because vardoc is the entire file name. For example, a file would be:

214 Nascar Baritone 2006 SAS.pdf

214= Branch field
Nascar = FirstName field
Baritone = LastName field

The "2006" and "SAS" identify the year and the type of report. Every file name will be built this way except the year will be different. So...on Nascar Baritone's record when I click "2006" it should open "214 Nascar Baritone 2006 SAS.pdf," but when I click "2007" it should open "214 Nascar Baritone 2007 SAS.pdf." Sally Jones will be different because on her record when I click "2006" it will open "352 Sally Jones 2006 SAS.pdf" and "2007" will open "352 Sally Jones 2007 SAS.pdf".

Like I said, the structure of this code works fine. I just don't know how or where to add another code that will link "2007" in the combo or list box to the "....2007 SAS.pdf" file for the individual record.

 

[NascarBaritone]

I THINK I GOT IT!!!

My apologies...I should have tried to figure our your method before making my previous post.

I knew I could get it!

Don't go far though...something's bound to go wrong.

Thank you for the clarification.

 

[NascarBaritone]

Okay...it does work beautifully. However, I want to see if I can complicate things a bit.

Is it possible for me to add a string so that Access looks into a specific folder for the file, but the folder isn't the same for each record?

For example, the "214 Nascar Baritone 2006 SAS.pdf" file is located in "H:\OP_PLUS\EQSALES\COMPLIANCE\Registered Reps 2008\Reps\Last Name A-G\Baritone, Nascar 214," but the "352 Sally Jones 2006 SAS.pdf" file is located in "H:\OP_PLUS\EQSALES\COMPLIANCE\Registered Reps 2008\Reps\Last Name H-N\Jones, Sally 352."

Is it possible to edit this string so that it Access knows where to look?:

Private Sub Combo197_Click()
'Create variable to hold value in textbox control, the name of the help file
vardoc = Me!Branch.Value & " " & Me!FirstName.Value & " " & Me!LastName.Value & " " & Me.Combo197 & " " & "SAS"
vardoc2 = Me!Branch.Value & " " & Me!FirstName.Value & " " & Me!LastName.Value & " " & Me.Combo197 & " " & "SAS"
'open the file.
FollowHyperlink ("H:\OP_PLUS\EQSALES\COMPLIANCE\Registered Reps 2008\Reps\" & vardoc & ".pdf")
FollowHyperlink ("H:\OP_PLUS\EQSALES\COMPLIANCE\Registered Reps 2008\Reps\" & vardoc2 & ".pdf")
End Sub

If not, is it possible to create a structure that looks like this:

"H:\OP_PLUS\EQSALES\COMPLIANCE\Registered Reps 2008\Reps\Jones, Sally 352\SAS Reports\352 Sally Jones 2006 SAS.pdf"

Where

H:\OP_PLUS\EQSALES\COMPLIANCE\Registered Reps 2008\Reps\ is hard-coded and the same for all records, but

Jones=LastName field
Sally=FirstName field
352=Branch field

SAS Reports will be a folder in each reps individual folder

 

[georgedwilkinson]

It is entirely possible to do all this, and more.

So, if the last name starts with A - G, it would go into
H:\OP_PLUS\EQSALES\COMPLIANCE\Registered Reps 2008\Reps\Last Name A-G\

and if the last name starts with H - N it would go into
H:\OP_PLUS\EQSALES\COMPLIANCE\Registered Reps 2008\Reps\Last Name H-N\

and so forth?

The fastest way is to hard-code that directly into your VB string building routine:

Dim x as String
x = Left(Me!LastName.Value,1)
If x >= "A" And x <= "G" Then
'Build string with A-G
ElseIf x >= "H" And x <= "N" Then
'Build string with H-N
Else
'so forth
End If

At least, this SHOULD work. If not, you can convert the first digit of the last name to ASCII and do your compare with that.

 

[justlcy]

just thinking of it

 

[NascarBaritone]

Brilliant!

This code:

Private Sub Combo197_Change()
'Create variable to hold value in textbox control, the name of the help file
vardoc = Me!Branch.Value & " " & Me!FirstName.Value & " " & Me!LastName.Value & " " & Me.Combo197 & " " & "SAS"
Dim x As String
x = Left(Me!LastName.Value, 1)
If x >= "A" And x <= "G" Then
    FollowHyperlink ("H:\OP_PLUS\EQSALES\COMPLIANCE\Registered Reps 2008\Reps\Last Name A-G\" & vardoc & ".pdf")
ElseIf x >= "H" And x <= "N" Then
    FollowHyperlink ("H:\OP_PLUS\EQSALES\COMPLIANCE\Registered Reps 2008\Reps\Last Name H-N\" & vardoc & ".pdf")
Else
'so forth
End If
End Sub

get me this far in my director...

"H:\OP_PLUS\EQSALES\COMPLIANCE\Registered Reps 2008\Reps\Last Name A-G\214 Nascar Baritone.pdf"

However, to complicate it just a few more steps, canI code it to go to:

"H:\OP_PLUS\EQSALES\COMPLIANCE\Registered Reps 2008\Reps\Last Name A-G\Baritone, Nascar 214\SAS Reports\214 Nascar Baritone.pdf"

With the bolded two folders looked in to find the file "214 Nascar Baritone.pdf" Again, the first bolded folder will always be LastName.Value and FirstName.Value and Branch.Value. And the "SAS Reports" folder will be in each users sub-folder. So...just to give visual examples, it would look like this:

"H:\OP_PLUS\EQSALES\COMPLIANCE\Registered Reps 2008\Reps\Last Name A-G\Baritone, Nascar 214\SAS Reports\214 Nascar Baritone.pdf"

"H:\OP_PLUS\EQSALES\COMPLIANCE\Registered Reps 2008\Reps\Last Name H-N\Jones, Sally 352\SAS Reports\352 Sally Jones.pdf"

"H:\OP_PLUS\EQSALES\COMPLIANCE\Registered Reps 2008\Reps\Last Name O-Z\Phillips, Bob 444\SAS Reports\444 Bob Phillips.pdf"

Thanks again for the patience and the wisdom! I'll keep working to look for my own answer while waiting for a response.

 

[georgedwilkinson]

Yes, you can do that but I'm becoming like JustIcy, above. It has gotten a little complex for my ADD addled brain. Can you dummy down the question a little?

I have a feeling that the answer depends on the VB function Dir(), but I'm acting like a kitten today (can't focus on anything for too long).

 

[NascarBaritone]

I think I can dummy it down a bit.

As the code stands right now, it works great. However, I have already built a folder hierarchy in Windows and the code isn't quite taking it all the way to where the file is. I could move the files, but there are hundreds of them and, for Windows hierarchy purposes, I like what I have now. Because of this, I would love Access to adjust -- not Windows folders.

All of my files are located in the same directory up to this point:

H:\OP_PLUS\EQSALES\COMPLIANCE\Registered Reps 2008\Reps

After that, they start to split up going into more specific folders:

H:\OP_PLUS\EQSALES\COMPLIANCE\Registered Reps 2008\Reps\Last Name A-G
H:\OP_PLUS\EQSALES\COMPLIANCE\Registered Reps 2008\Reps\Last Name H-N
H:\OP_PLUS\EQSALES\COMPLIANCE\Registered Reps 2008\Reps\Last Name O-Z

This is where we have access going thus far.

From there, I was hoping to go a few more directories deeper. So that Access would know to look into:

H:\OP_PLUS\EQSALES\COMPLIANCE\Registered Reps 2008\Reps\Last Name A-G\Baritone, Nascar 214\SAS Reports\214 Nascar Baritone 2006 SAS.pdf"

As opposed to what we have now:

H:\OP_PLUS\EQSALES\COMPLIANCE\Registered Reps 2008\Reps\Last Name A-G\214 Nascar Baritone 2006 SAS.pdf

Basically, I am trying to get Access to look 2 folder deeper into the directory for the .pdf file rather than stopping at the "Last Name A-G" folder and looking.

Imagine how confused I am! I just started Access two weeks ago.

 

[georgedwilkinson]

vardoc = Me!LastName.Value & ", " & Me!FirstName.Value & " " & Me!Branch.Value & "\SAS Reports\" & Me!Branch.Value & " " & Me!FirstName.Value & " " & Me!LastName.Value & " " & Me.Combo197 & " " & "SAS"

 

[NascarBaritone]

George,

This opera singer turned Access database builder cannot thank you enough!

I'm almost positive I tried that method, but I think it was as simple as forgetting my "\" before and after the "SAS Reports."

One more thing and I SWEAR I am done for a while.

Where and how do I code a message box in this code so that when one of the years is selected and there is no file in that folder (we don't have 2008 scanned yet) I get a message box saying "SAS Report does not exist."

Private Sub Combo197_Click()
vardoc = Me!LastName.Value & ", " & Me!FirstName.Value & " " & Me!Branch.Value & "\SAS Reports\" & Me!Branch.Value & " " & Me!FirstName.Value & " " & Me!LastName.Value & " " & Me.Combo197 & " " & "SAS"
Dim x As String
x = Left(Me!LastName.Value, 1)
If x >= "A" And x <= "G" Then
    FollowHyperlink ("H:\OP_PLUS\EQSALES\COMPLIANCE\Registered Reps 2008\Reps\Last Name A-G\" & vardoc & ".pdf")
ElseIf x >= "H" And x <= "N" Then
    FollowHyperlink ("H:\OP_PLUS\EQSALES\COMPLIANCE\Registered Reps 2008\Reps\Last Name H-N\" & vardoc & ".pdf")
ElseIf x >= "O" And x <= "Z" Then
    FollowHyperlink ("H:\OP_PLUS\EQSALES\COMPLIANCE\Registered Reps 2008\Reps\Last Name O-Z\" & vardoc & ".pdf")
End If
End Sub

Again...you are the greatest!