Links from combo or list box?

[NascarBaritone]

I was so confident that it was working...I'm really hoping this is an easy fix. I tested the database and everything for all the records worked great. I saved the database. I closed Access completely. When I open the form back up the combo box starts out with a blank. When I click on the drop down arrow I see my years (2006, 2007, 2008) as I should. However, when I click on 2006, for example, nothing happens. No file opens, no error message, nothing.

This is where it gets weird, however. When I open VB and change the Combo197 command from whatever it is (Click --> Change, Change --> Click, Change --> AfterUpdate, it doesn't matter which) and go back into Form View it works fine again. However, when I close and open the database, it goes back to no response.

Curious...

 

[georgedwilkinson]

I was an Opera singer in the 1980's. Not much money in music so I eventually ended up in IT (1984). I sometimes wonder if I made the right choice. I am still a semi-professional musician.

In order to tell if your file exists before you try to open it, you should use the Dir() function to validate it.

Feel free to post your database here if you want somebody to look at it for the other problem.

How To Upload A Database To The Forum

 

[NascarBaritone]

I just recently decided to "take some time off" from the crazy world of opera. It's a very different world than when I first started singing (granted that wasn't too long ago).

I'll take a look at the Dir() function to see if I can wrap my brain around it.

As for the other problem...I think that is fixed. I was doing everything straight in VB from the get-go and I forgot to add a [Event Procedure] command in Design View. I did that and that seemed to fix it.

 

[georgedwilkinson]

Give a yell if you need help on the Dir stuff.

 

[NascarBaritone]

Actually...just look at the Dir() function I'm not sure if it is what I was thinking. Of course, I could be WAYYYY off as to what the function does.

What I am looking for is when a user goes into this database and selects a year from the drop-down box if the file isn't there a message box just comes up to tell them that the report does not exist rather than what happens now (Run-time error 490: Cannot find specified file).

So something like this:

If 2006 is selected, the file "214 Nascar Baritone 2006 SAS.pdf" opens up.
If 2007 is selected, the file "214 Nascar Baritone 2007 SAS.pdf" opens up.
If 2008 is selected, because there is no "214 Nascar Baritone 2008 SAS.pdf" file yet, a vbOkOnly message box will come up where the user simply hits "Ok" and it goes back to the form.

I though I understood that it would be an "If" statement within the combo box and MsgBox would be used, but feel free to correct me if I am wrong. Also, feel free to throw any coding out there that might help me, even if it is Dir().

 

[georgedwilkinson]

Post your current event procedure and I'll modify it to check the file name first/display a message box if it doesn't exist.

 

[NascarBaritone]

Private Sub Combo197_Change()
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

 

[georgedwilkinson]

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

 

[NascarBaritone]

Couldn't be more perfect!

That was EXACTLY what I needed. I promise I will keep coming up with random questions so that you won't miss me too much, George.

Thanks again!!

 

[georgedwilkinson]

Feel free to spread the joy around some, too. There are some pretty smart people here who may have better methods of achieving your goals.

Always happy to help.