Parsing pasted text into record(s) in table

[Leyton01]

Hi All

I'm a google search hacker when it comes to Access programming and am seeking some guidance about how best to achieve a goal so I can research in the right direction.

I have a database with a main table (DocumentList) and a primary key DocID. This is linked to a second table (SiteList) through a one to many relationship where the document can be associated with a few sites where it has its own local ID number (LocalDocID).

We have an existing application which has all the local doc IDs listed and staff can highlight and copy this information to paste elsewhere (can't be imported through HTML etc as it is a secure application). The data is semi-structured, as it it always has the structure {siteID:LocalDocID Nameofsite} but there may be one or many sites (the count is variable).

I am wanting staff to be able to paste that information into a form displaying the master DocID and have it create new entries in the SiteList table linked to that master DocID.

Example of pasted text:

ATH:044376  A Site's Name 
 GCH:111332  G Site's Name  
 MH:111222  M Site's Name
 PCH:451222  P Site's Name

(for some reason the first line never has a leading space but all others do. The output remains SiteID{colon}LocalDocID{space}SiteName). Each listing is always on a new line however there may be only 1 line or many.

I need to capture the SiteID and the LocalDocID into the SiteList table - the site's name can be discarded. The records created will all have to be linked to the master "DocID" of the record currently shown on the form.
eg if I paste the above into the form currently showing DocID D00012 it will create 4 records in the SiteList table (has it's own primary key which is not shown):

DocID  |   SiteID   |   LocalDocID
D00012 |    ATH     |   044376
D00012 |    GCH     |   111332
D00012 |    MH      |   111222
D00012 |    PCH     |   451222

How do I best achieve this? I have fiddled with an unbound text box and a button which takes the pasted text and attempts to process it but I am having trouble stepping through the lines of the text. I am not even sure if this is the best way to do it. I am happy to take suggestions!

 

[arnelgp]

simply create a Public function
in standard module that will do the extraction:

Public Function fncExtract(Byval StringToExtract As String, Byval Position As Byte) As String
Dim var As Variant
var = Split(StringToExtract, ":")
fncExtract = Trim(Choose(Position, var(0), _
Left(var(1), Instr(2, var(1), " ")-1)))
End Function

to get the "ATH":

fncExtract(yourStringHere, 1)

to get 044376:

fncExtract(yourStringHere, 2)

 

[Leyton01]

Thanks for that but I still come up against the original problem - how do I step to the next line to create the next record?

 

[arnelgp]

ok then.
so you have a form and want to paste
the HTML to a Textbox.

add a button that when clicked
will do the actual Insertion of
data.

on the Click Event of this
button add this code (change
the name of the textbox to what
you have:

Private Sub textbox_Click()
	' replace txtDocID with the name of textbox of DocID
	' replace txtHTML with the textbox name where
	' you pasted the HTML.
	Call subExtractAndSave(Me.txtDocID, Me.txtHTML)

End Sub

replace the code i posted a while ago:

Option Compare Database
Option Explicit

Public Sub subExtractAndSave(ByVal DocID As String, ByVal StringToExtract As String)
    Dim var1 As Variant
    Dim var2 As Variant
    Dim var As Variant
    Dim strFinalQuery As String
    Dim strInsert As String
    Dim intCount as integer
    strInsert = "INSERT INTO SiteList (" & _
                    "DocID, SiteID, LocalDocID) SELECT " & _
                    Chr(34) & DocID & Chr(34) & ","
                    
    var1 = Split(StringToExtract, vbNewLine)
    For Each var In var1
        
        If InStr(var & "", ":") > 0 Then
            intCount=intCount+1
            var2 = Split(var, ":")
            strFinalQuery = strInsert & _
                            Chr(34) & var2(0) & Chr(34) & "," & _
                            Chr(34) & Left(var2(1), InStr(2, var2(1), " ") - 1) & Chr(34)
            
            DBEngine(0)(0).Execute strFinalQuery
        End If
    
    Next var
    Msgbox trim(intCount) & " record(s) added to SiteList table"
End Sub

 

[Leyton01]

Ok great. So if I am reading that right it steps through the string looking for the colons and parses either side. That is an excellent solution, I'll give it a go and feed back. Thanks.

** I've spotted the vbNewLine code as well which I think is the solution that I was missing.

 

[arnelgp]

In case you git same records being saved, i already made changes (edit) my post so copy it again.

 

[Leyton01]

I'm going to try in the morning when I am back in front of the PC - thanks.

 

[moke123]

another version...

 

[Leyton01]

Both provided examples worked as expected - thank you both.

It did lead me down a rabbit hole regarding DBEngine(0)(0) vs CurrentDb but that is the joy (?!) of Access programming - there is more than one path to the destination.

 

[Leyton01]

Looks like I spoke too soon in a way - it worked in my test database very well but when I implemented it into the real database I hit a problem.

The "siteID" in the SiteList table is set up as a lookup from a table which contains the codes so users can easily select them when entering data (it contains the short name (SiteID), the numerical ID of the site as used by finance, the long name and a primary key).

When using the INSERT command it tries to insert "ATH" in the SiteID (as it should) but nothing is showing up because I think the table is expecting the primary key number not the actual text code.

What is the best way to fix this - avoid using the lookup (and then what)? Or can this be fixed in code?

 

[arnelgp]

before running, there are comments on the function.
try to rectify it first (on the DLookup portion).

Option Compare Database
Option Explicit

Public Sub subExtractAndSave(ByVal DocID As String, ByVal StringToExtract As String)
    Dim var1 As Variant
    Dim var2 As Variant
    Dim var As Variant
    Dim strFinalQuery As String
    Dim strInsert As String
    Dim intCount As Integer
    
    Dim lngSiteID As Long
    
    strInsert = "INSERT INTO SiteList (" & _
                    "DocID, SiteID, LocalDocID) SELECT " & _
                    Chr(34) & DocID & Chr(34) & ","
                    
    var1 = Split(StringToExtract, vbNewLine)
    For Each var In var1
        intCount = intCount + 1
        
        If InStr(var & "", ":") > 0 Then
            var2 = Split(var, ":")
            '
            ' CHANGE HERE!!!
            '
            ' change the tablename and fieldnames on this portion
            ' replace SiteID with correct field, "Table" with correct tablename
            ' "Field1" the field from "Table" that has values like "ATH"
            '
            ' it simply means:
            '
            ' GET the SiteID from Table where Field1="ATH"
            '
            lngSiteID = Nz(DLookup("SiteID", "Table", "Field1=" & Chr(34) & var2(0) & Chr(34)), 0)
            
            strFinalQuery = strInsert & _
                            lngSiteID & "," & _
                            Chr(34) & Left(var2(1), InStr(2, var2(1), " ") - 1) & Chr(34)
            
            DBEngine(0)(0).Execute strFinalQuery
        End If
    
    Next var
    MsgBox Trim(intCount) & " record(s), added to SiteList table"
End Sub

 

[Leyton01]

I've made this change but I can only get it to work for the first record (it correctly identifies "ATH" as site "6"), after that it returns a site of "0" for all subsequent records.

 

[arnelgp]

Question is do you have the others on your table.

 

[Leyton01]

OK I found the issue - the first record when pasted does not have a leading space (see original post) but all the other ones do.

It was trying to Dlookup " GCH" instead of "GCH" and was not getting a match. I need to trim the leading space.

 

[arnelgp]

Trim it here

Trim(Left(var2(1), InStr(2, var2(1), " ") - 1))

 

[Leyton01]

If I a using the watches right var2(0) contains the " GCH" so the above would not fix that?
Does that change the way that Trim works then if sometimes it gets a leading space " GCH" and sometimes it does not? "ATH"

 

[Leyton01]

I just used
var2(0) = Trim(var2(0))

and it worked.
Is that really bad programming??

Edit:

I added it directly to the DLookup instead:

lngSiteID = Nz(DLookup("SiteID", "Table", "Field1=" & Chr(34) & Trim(var2(0)) & Chr(34)), 0)

Everything seems to be working as expected now - thank you!