Hi Trevor,
I thought I would complete this thread as it is frustrating when a link is given and no details of the actual solution. Plus, this has been three weeks of my life now that I wont get back and has confirmed to me that the decision I made 20 years ago to become an analyst and not a developer, was the right one!!
Briefly. I have been converting the company accounts from Excel to an Access database. Ive done all the financial stuff and am working on the System Variables Screen and wanted to have a field hold the company website address, and for the user to browse the web to get it rather than just typing it in. This is used in correspondence as well as a link to the website.
Showing a Google page was fine, then if only one web page was open, I could easily bring back the web address. But if there was more than one IE window open there seemed to be no way of getting the active window so I always brought back the wrong one. I finally hit on the idea of showing all the web addresses to the user and asking them to confirm which was theirs.
Function RetrieveURL() As String
'Requires reference to Microsoft Internet Controls
'Should work with any web table, provided it is not in a frame!
'Gets user to select correct website from all open http: windows
Dim SWs As SHDocVw.ShellWindows, vIE As SHDocVw.InternetExplorer
Dim websitefound As String
On Error GoTo ExitRoutine 'Incoporate error handling
Set SWs = New SHDocVw.ShellWindows 'Establish link to IE application
For Each vIE In SWs
If Left(vIE.LocationURL, 4) = "http" Then 'avoid explorer windows/etc this way
If MsgBox("Is this your website? " & vbCrLf & vIE.LocationURL & vbCrLf & _
vbCrLf & " ", vbYesNo) = vbYes Then
RetrieveURL = vIE.LocationURL
Exit Function
End If
End If
Next
ExitRoutine:
If Err.number <> 0 Then
RetrieveURL = "Error"
Else
RetrieveURL = websitefound
End If
On Error GoTo 0
Set SWs = Nothing
Set vIE = Nothing
End Function
Your system will not allow me to post my main sub as it thinks it has links in it - this sub stores the web address in the system variables table and writes it to the screen and is based on:
'cannot save a hyperlink address in a textbox, it has to be a label but when you write the label.caption and
'hyperlink address these are cleared when form is reopened. Best to write it to the table (remembering # format)(using reliable and robust SQL)
'then pick it up into a text box as a field value. BUT, the caption is shoved into the text box and the link
'in the label. So forget a caption and hide the text box so its just storage.
‘And forget about using HYPERLINKPART... always strip down string and rebuild it
'refs MicroSoft Internet Controls
'
Also, the Access defined hyperlink field on the form, when clicked, will sometimes navigate to the already opened page or sometimes open a new page. I am sure that the problems I have encountered are resolved in future versions of this and that but I have only just got to grips with Windows 7 and have no desire to upgrade and have to rewrite a lot of stuff that no longer works.
Thanks for your help
AliBaz
