List boxes and buttons, a great combo!

[Clamato]

Hello everyone,

I've been working on this for a bit and wonder if anyone can shed some light on my situation. My ultimate goal is to have a user select an item in a list box, have one column in the list box display in either a text box or preferrably a button based on what is selected. The form is being used currently as a rudimentary document retrieval system. The document that is saved on a shared drive is linked in a table by a hyperlink, each document is saved for a specific person. I have it set up now where the user types in the persons name in a combo box, when they tab out of the box 4 columns appear in the list box from a query based on the name in the cmbbox, the 5th column not showing in the listbox is the hyperlink. I would like it be where the user will click on the row of the document they'd like to open, and preferrably have the button take them to the document via a hyperlink once pressed. I'm not sure if this is possible through a button. Or, a text box would work fine. I have been able to get the text box to populate the hyperlink with afterupdate in the list box, but it doesn't display the Text link that is set up in the table, only the location. I am extremely new to Access, but do somewhat understand queries, relationships and some of the code. Any help would be EXTREMELY appreciated, so far it's been trial and error, and I've been racking my brain and google on this one. Below are the names of the objects being used in the form currently:

Combo box where persons name is entered: cmb_cmdoc
List box where info from query displays, column the hyperlink is in is (5): lstDoc
Button I'd like to open link based on item selected in listbox: btn_docsearch
Text box that now displays link, but not in link format: txtDoc

Thanks again!!

[Minion]

Sorry I don't have more time at the moment to delve into this with you, but I did find something that may help in the mean time. For the text box you have displaying the hyperlink make sure that under format on the properties tab (last on one list) the property Is Hyperlink is set to "True" this should let the hyperlinks work if they are stored as hyperlinks in the table.

Hope this helps.

- Minion -

[Clamato]

Thanks Minion, I appreciate the help. I changed the option to yes for is hyperlink and it appears to be doing the same thing as before, except now the text is in blue as if it were a hyperlink and showing the full location address as opposed to the display text as it is in the table. But, still not in hyperlink format. :/

[Minion]

I did a quick test and think I've found your problem. Goto the design of your table and make sure the field type is set to "Hyperlink" instead of "Text". If it's not set to a hyperlink Access seems to ignore it despite the form telling it is a hyperlink. Once this is set the text in table view should be blue underlined just like you'd expect.

Hope this helps.

- Minion -

[Clamato]

Thanks again Minion,

I do have the field in the table set as hyperlink as well. I wonder if it's the way the information is being passed to the text box. I have now on the list box where the user clicks the row they'd like to view and the column containing the link is passed to the text box as follows with an afterupdate:

Me![txtDoc] = Me![lstDoc].Column(4)

I think I'm really close to getting it to work with a button, I've added this on the button. I think my syntax may be a little askew:

Application.Followhyperlink Me![lstDoc].Column(4)

When I press the button it gives a hyperlink warning, but an error afterwords. What do you think?

Thanks!

[Clamato]

Alright, I got the button to work. I had to change the format of the hyperlink though, which means going through 150+ records and updating them :(

I think it may have had something to do with the "display text" in the link format, not sure why though. When I changed that to match the actual location I had the path display in the text box. Then I used

Private Sub btn_open_Click()
On Error GoTo Err_btn_open_Click
 
    Dim stlink As String
        stlink = Me![txtDoc]
        FollowHyperlink stlink, , True
 
Exit_btn_open_Click:
    Exit Sub
 
Err_btn_open_Click:
    MsgBox Err.Description
    Resume Exit_btn_open_Click
End Sub
 

on the button, so it's taking the string in the textbox as the link. GEESH! I have a lot of changing to do! Thanks for the help again Minion

[MMcCarthy]

I believe you need to include display text to get a hyperlink to work consistently. Not sure why, probably just an MS bug.

[Minion]

Glad that you go a least a part of it working. If you post a new thread with some of the details on what you have and what it has to be changed to I think we can come up with a quick script to help you out.

- Minion -

Alright, I got the button to work. I had to change the format of the hyperlink though, which means going through 150+ records and updating them :(

I think it may have had something to do with the "display text" in the link format, not sure why though. When I changed that to match the actual location I had the path display in the text box. Then I used

Private Sub btn_open_Click()
On Error GoTo Err_btn_open_Click
 
    Dim stlink As String
        stlink = Me![txtDoc]
        FollowHyperlink stlink, , True
 
Exit_btn_open_Click:
    Exit Sub
 
Err_btn_open_Click:
    MsgBox Err.Description
    Resume Exit_btn_open_Click
End Sub
 

on the button, so it's taking the string in the textbox as the link. GEESH! I have a lot of changing to do! Thanks for the help again Minion