Solved: Importing Hyperlinks into Access table

[sammy8932]

I'm importing HTML into an Access table and then allowing the user to view
this data through text boxes on a FORM. The HTML tags are currently being
stripped out. At times the HTML that's being imported in has HTML
bookmarks in them, where you click on a link and it takes you to a section
further down on the page.
For example the HTLM comes in as: <a href="#00454">Western Mexico - Sonora,
Baja California Norte, Baja California Sur</a>. This link would take you
to the following info further down on the HTML page: <a
name="00454">United Government Services Contractor# 00454</a></h4>(Sonora,
Baja California Norte, Baja California Sur). The user wants to be able to
use this functionality within the FORM of the Access application. One
issue is that the first part of the HTML may be in one text box and the
other may be in another text box. Is there any way to do this within
Access? I hope I've explained this well enough. If there are any
questions, please let me know and I'll answer them. I really appreciate
any help I can get.

Thank you in advace.

[xCav8r]

I think we need a little more detail about *how* you're doing this to offer any advice.

[sammy8932]

All right, let me try this again. Basically I want to be able to hyperlink from a particular word in a text box to another word in a different text box (on the same form). The same way 'Bookmarks' work on a web page.

We import this data from another database and it comes in as HTML, so all the tags and everything are in the data. Is there anyway I can insert these hyperlinks/bookmarks during the import, so they show up when the user views the data on the form?

Is that any clearer?

[xCav8r]

It wasn't that I didn't understand what you'd like to do with the bookmarks. I wanted to know more about how you were doing things. Let me try asking some questions.

1. How are you importing the data? TransferText? ADO?
2. Are you working with premade forms, or are you creating forms on the fly with controls that are named according to the data?
3. Do you need to go to another record to find the bookmark?

In reponse to yours, there is no way that I know to use HTML tags for bookmarks on an Access form, but there are several ways that you could mimic it. Here's a simple example:
[VBA] Private Sub txtReferenceToBookmark_Click()
DoCmd.GoToControl ("txtBookmark")
End Sub
[/VBA]

[sammy8932]

xCav8r,
thanks for the reply. I appreciate you helping me out on this.

Questions:
1. This is the code that's used to import the data. TransferTable() is called from a macro.

Function TransferTable()
DoCmd.TransferDatabase acImport, "ODBC Database", "ODBC;DSN=CMS_NGD;UID=SIEBEL;DBQ=CRMPROD.WORLD;DBA=W;APA=T;FEN=T;QTO=T;FRC= 10;FDL=10;LOB=T;RST=T;FRL=F;MTS=F;CSR=F;PFC=10;TLO=0;TABLE=SIEBEL.S_LST_OF_ VAL;PWD=SIEBEL", acTable, "SIEBEL.S_LST_OF_VAL", "S_LST_OF_VAL", NO
DoCmd.TransferDatabase acImport, "ODBC Database", "ODBC;DSN=CMS_NGD;UID=SIEBEL;DBQ=CRMPROD.WORLD;DBA=W;APA=T;FEN=T;QTO=T;FRC= 10;FDL=10;LOB=T;RST=T;FRL=F;MTS=F;CSR=F;PFC=10;TLO=0;TABLE=SIEBEL.S_RESITEM ;PWD=SIEBEL", acTable, "SIEBEL.S_RESITEM", "S_RESITEM", NO
End Function

'the code below is then called from the same macro to remove the HTML.
Function RemoveIssueTableHTML()
'Declare variables
Dim rsRecordset As DAO.Recordset
Dim strQuery As String
Dim qdfQDef As QueryDef
Dim strResults As String

strQuery = "SELECT S_RESITEM.RESOLUTION_TEXT, S_RESITEM.X_SPANISH_DESC, " _
& "S_RESITEM.X_SPANISH_DESC2, S_RESITEM.X_CMS_CSR_TIPS " _
& "FROM S_RESITEM"

'Create the query definition and open the recordset
Set qdfQDef = CurrentDb().CreateQueryDef("", strQuery)
Set rsRecordset = qdfQDef.OpenRecordset(dbOpenDynaset)

' Did we get any records? If we did, move to the first one
If rsRecordset.RecordCount Then
rsRecordset.MoveFirst
End If

' Walk through all records in the table
Do While (Not rsRecordset.EOF)
rsRecordset.Edit

'Strip the HTML from each field
rsRecordset!RESOLUTION_TEXT = StripHTML(rsRecordset!RESOLUTION_TEXT)
rsRecordset!X_SPANISH_DESC = StripHTML(rsRecordset!X_SPANISH_DESC)
rsRecordset!X_SPANISH_DESC2 = StripHTML(rsRecordset!X_SPANISH_DESC2)
rsRecordset!X_CMS_CSR_TIPS = StripHTML(rsRecordset!X_CMS_CSR_TIPS)

' Update the record and go to the next one
rsRecordset.Update
rsRecordset.MoveNext
Loop

' Close the recordset
rsRecordset.Close
End Function

'here is the StripHTML code that is called from the RemoveIssueTableHTML() function.

Function StripHTML(ByRef asHTML)
If IsNull(asHTML) = False Then
Dim loRegExp ' Regular Expression Object

' First get rid of all carriage returns in the
' text - we want to format it as closely to HTML as possible
asHTML = Replace(asHTML, vbCrLf, "")

' Create built In Regular Expression object
Set loRegExp = New RegExp

' Set the pattern to look for matches throughout string
loRegExp.Global = True
loRegExp.IgnoreCase = True

' Now replace hyperlinks w/their URLS and hyperlink text
'loRegExp.Pattern = "<A HREF=\"
loRegExp.Pattern = "<A HREF *= *""*"
Set loExpMatch = loRegExp.Execute(asHTML)

' Did we find any hyperlinks?
If loExpMatch.Count > 0 Then
Dim strNewHTML
Dim iLastPos As Integer
strNewHTML = ""
iLastPos = 1
For Each loExpMatched In loExpMatch
On Error GoTo lblSkipMatch
' Get the starting position of the URL
iLPos = loExpMatched.FirstIndex + Len(loExpMatched.Value) + 1 ' Need to get past the tag lead-in

' Find the ending position of the URL
If InStr(loExpMatched.Value, """") = 0 Then
' This URL was not contained in quotes - get first space
iRPos = InStr(iLPos, asHTML, " ")
Else ' Get first quote
iRPos = InStr(iLPos, asHTML, """")
End If

' Get the URL
strURL = Mid(asHTML, iLPos, iRPos - iLPos)

' Insert the URL before the hyperlink text
iRPos = InStr(iRPos, asHTML, ">") + 1
strNewHTML = strNewHTML & Mid(asHTML, iLastPos, iRPos - iLastPos) & _
"&LT" & strURL & "&GT - "
iLastPos = iRPos
lblSkipMatch:
Next

' Tack on the rest of the string now that we're done w/URL-fixing
asHTML = strNewHTML & Mid(asHTML, iLastPos)
End If

' Next, replace all instances of line breaks and
' paragraph symbols with carriage returns
asHTML = Replace(asHTML, "<BR>", vbCrLf) 'Line Break

'2 lines below added by Aron per Tracker #3091 - 03/28/05
asHTML = Replace(asHTML, "<blockquote>", vbCrLf & vbCrLf)

asHTML = Replace(asHTML, "</blockquote>", vbCrLf & vbCrLf)

loRegExp.Pattern = "<P[^>]*>" 'Paragraph
asHTML = loRegExp.Replace(asHTML, vbCrLf & vbCrLf)

loRegExp.Pattern = "</?[OU]L[^>]*>" 'Ordered or Unordered List
asHTML = loRegExp.Replace(asHTML, vbCrLf)

loRegExp.Pattern = "<LI[^>]*>" 'List Item
asHTML = loRegExp.Replace(asHTML, vbCrLf & "(*) ")

loRegExp.Pattern = "<HR[^>]*>" 'Horizontal Rule
asHTML = loRegExp.Replace(asHTML, vbCrLf & "--------------------------------------------------" & vbCrLf)

loRegExp.Pattern = "<DIV[^>]*>" 'Divider
asHTML = loRegExp.Replace(asHTML, vbCrLf & "--------------------------------------------------" & vbCrLf)

' Finally, return the original string stripped of
' all other HTML tags
loRegExp.Pattern = "<[^>]*>"
'StripHTML = loRegExp.Replace(asHTML, "")
asHTML = loRegExp.Replace(asHTML, "")

' Now replace any &LTs and &GTs with "<", ">"
asHTML = Replace(asHTML, "&LT", "<")
StripHTML = Replace(asHTML, "&GT", ">")

' Release object from memory
Set loRegExp = Nothing
Else
StripHTML = Null
End If
End Function

2. The forms are premade.

3. No, I wouldn't need to go to another record to find the bookmark. There are 4 text boxes on the form. Each text box contains a different field from the record. I will have to go to another text box to find a bookmark.

Edit to add: It's not enough just to go to the correct Text Box as one text box can contain many paragraphs. I really need to go directly to the exact 'word' that is being bookmarked. I'm not sure if there is a way to highlight the word, but that would be nice. The reason this has to be easy and fast is that Call Service Representatives working for 1-800-MEDICARE are going to be using this application and they will be using it while talking to a caller on the phone.

Just a side note to let you know that I didn't create this application. I'm just supporting it. I'm fresh out of College and still learning...so please bear with me.

I hope this helps. Thanks again.

[xCav8r]

Okay, now that I know you've got a form with four textboxes and need to go directly to some specific text inside the textbox, the idea of creating forms on the fly with names based on the data won't fly. I'll offer another idea, but I'm hopeful that others will weigh in on this matter so you get a few options.

As your stripping the bookmarks, you could replace the link with a special character of some kind and the anchor with a different special character. You could then trap a key event to look up a selected link character in a table to learn its matching anchor character, then search the current record for that anchor character. You would need to create a table of special characters, and your users would need to learn to recognize these characters and know what to do with them. It's not a perfect solution, but it would work.

By special character, I really mean unique string. It could be (1) points to (1.), (2) points to (2.), etc.

If I think of any better ways of doing this with premade forms, I'll post them.

PS. Enclose your VBA with the VBA tags to make your code easier to read.

[sammy8932]

Thanks xCav8r. I'll see if I can make it work.

I wanted to enclose my code in VBA tags, but wasn't sure how to do it. I think I know now.

Thanks again for your help.

[sammy8932]

I have another question for anyone who'd like to answer. Until I get the above to work, I'm trying to put something in place that would allow the users to do a search for the word (bookmark) they're looking for. I have a text box on the form where the user can type in the word they're looking for, along with a 'Search' button. I have this part working but I need a way to highlight the actual word (bookmark) they are searching for. Is there a way to do this...to highlight a specific word/string in a text box?

Thanks in advance.

[xCav8r]

Look at the documentation for SelText. I think that's what you're looking for.

[sammy8932]

Thanks xCav8r. I got that figured out. I used the code below.

[VBA] selStart = (InStr(edtEnglishPg1.Text, strSearchString))
If selStart > 0 Then
edtEnglishPg1.selStart = selStart - 1
edtEnglishPg1.SelLength = Len(strSearchString)
Exit Sub
End If [/VBA]

[xCav8r]

Will the interim solution become the permanent one, my southern brotha?

[sammy8932]

I think it probably will xCav. Unless I can come up with a way to do it how I initially wanted to do it without it being to much work. Adding another table is definetly out of the question. Management will never go for that. This tool is used only for back up for when the web-page goes down (which is quite often), so management doesn't like to put to much into it. Thanks for all your help. I appreciate it.

[xCav8r]

No problem. Glad I was able to help. If you consider this problem solved, would you mark the thread solved? Look at the top of this window, and you'll see a link called Thread Tools.