Move parsed fields into a table

[detrie]

Access 2003

I'm trying to build a command button to parse each line into a new record of my table [TBLRECORDS]

I am able to parse a single line using this code but don't know how to insert the data into the table and loop through for all lines in my [memFile] control

On a form I have a text control [memFile]
I populate it by pasting text from the body of an email into it and it is stored in a tables memo field [TBLFILES].[TEXTFILE]
Each email can have countless rows of data in this format

H E74 Warranty end not prior to Serv start for XRC Status: M RecordId=897*7756*33*1D1PMB*20140418
H E74 Warranty end not prior to Serv start for XRC Status: M RecordId=897*7916*68*A1C558*20140418

Each line of data will always begin with "H " and each line of text will always have the same character count

For each line in me.memFile I want to create a new table record and populate this way
[TBLRECORDS][TXTTYPE] = strType
[TBLRECORDS][TXTCODE] = strCode
[TBLRECORDS][TXTDESCRIPTION] = strDescription
[TBLRECORDS][TXTRECORD] = strRecord
[TBLRECORDS][TXTMODEL] = strModel
[TBLRECORDS][TXTCOUNTRYCODE] = strCountryCode
[TBLRECORDS][TXTSERIAL] = strSerial
[TBLRECORDS][DTERRORDATA] = strErrorDate

Private Sub btnParse_Click()
Const strDelimiter As String = "H    E74    Warranty end not prior to Serv start for XRC Status: M RecordId=897*7756*33*1D1PMB*20140418" 'This will be replaced by me.memFile
Dim strType As String
Dim strCode As String
Dim strDescription As String
Dim strRecord As String
Dim strModel As String
Dim strCountryCode As String
Dim strSerial As String
Dim strErrorDate As String

    strType = Left(strDelimiter, 1)
    strCode = Mid(strDelimiter, 6, 3)
    strDescription = Mid(strDelimiter, 13, 51)
    strRecord = Mid(strDelimiter, 77, 3)
    strModel = Mid(strDelimiter, 81, 4)
    strCountryCode = Mid(strDelimiter, 86, 2)
    strSerial = Mid(strDelimiter, 89, 6)
    strErrorDate = Right(strDelimiter, 8)
    

    Debug.Print strType
    Debug.Print strCode
    Debug.Print strDescription
    Debug.Print strRecord
    Debug.Print strModel
    Debug.Print strCountryCode
    Debug.Print strSerial
    Debug.Print strErrorDate
    
End Sub

thanks

 

[Cronk]

I'd use a recordset to add records and stored the parsed data.

You could start with
http://allenbrowne.com/ser-29.html
to see how to open a recordset, add new records and save data.

BTW is there any particular reason you use all upper case for table names and fields? I find it harder to read in code.

 

[detrie]

I think I'm close
The text I want to process is pasted into me.ctlDropBox

For testing purposes I am pasting this into me.ctlDropBox

H E74 Warranty end not prior to Serv start for XRC Status: M RecordId=897*7756*33*1D1PMB*20140418
H E74 Warranty end not prior to Serv start for XRC Status: M RecordId=897*7916*68*A1C558*20140418
H E74 Warranty end not prior to Serv start for XRC Status: M RecordId=897*7756*33*1D1PMB*20140418
H E74 Warranty end not prior to Serv start for XRC Status: M RecordId=897*7916*68*A1C558*20140418

Code is failing at Const strDelimiter as String = me.ctlDropBox (Compile Error: Constant Expression required)

Private Sub btnParse_Click()
Const strDelimiter As String = Me.ctlDropBox
Dim myRecord As Recordset
Dim db As DAO.Database
Dim varSplit As Variant
Dim var As Variant
Dim strType As String
Dim strCode As String
Dim strDescription As String
Dim strRecord As String
Dim strModel As String
Dim strCountryCode As String
Dim strSerial As String
Dim strErrorDate As String
Dim strSQL As String
Dim intFileID As Integer
Dim InputString As String

    InputString = Me.ctlDropBox
    Set db = CurrentDb()
    
    varSplit = Split(InputString, strDelimiter)
      
       strType = Left(strDelimiter, 1)
       strCode = Mid(strDelimiter, 6, 3)
       strDescription = Mid(strDelimiter, 13, 51)
       strRecord = Right(strDelimiter, 26)
       strModel = Mid(strDelimiter, 81, 4)
       strCountryCode = Mid(strDelimiter, 86, 2)
       strSerial = Mid(strDelimiter, 89, 6)
       strErrorDate = Right(strDelimiter, 8)
       
 For Each var In varSplit
 If Len(var) Then
    db.Execute "INSERT INTO TBLRECORDS(TXTTYPE,TXTCODE,TXTDESCRIPTION,TXTRECORD,TXTMODEL,TXTCOUNTRYCODE,TXTSERIAL,TXTERRORDATE) VALUES('" & strType & "', '" & strCode & "', '" & strDescription & "', '" & strRecord & "', '" & strModel & "', '" & strCountryCode & "', '" & strSerial & "', '" & strErrorDate & "');"
End If
Next

End Sub

 

[Cronk]

Don't declare strDelimiter as a constant. I

Instead declare it as a string, and then get the value into the string

Dim strDelimiter as string
 
strDelimiter  = Me.ctlDropBox

 

[detrie]

Thanks Cronk

That worked but now each record is replicated many (14) times in the table. Am I using 'Next' incorrectly?

Private Sub btnParse_Click()
Dim strDelimiter As String
Dim myRecord As Recordset
Dim db As DAO.Database
Dim varSplit As Variant
Dim var As Variant
Dim strType As String
Dim strCode As String
Dim strDescription As String
Dim strRecord As String
Dim strModel As String
Dim strCountryCode As String
Dim strSerial As String
Dim strErrorDate As String
Dim strSQL As String
Dim intFileID As Integer
'Dim InputString As String

strDelimiter = Me.ctlDropBox
' InputString = Me.ctlDropBox
Set db = CurrentDb()

' varSplit = Split(InputString, strDelimiter)

varSplit = Split(strDelimiter)

strType = Left(strDelimiter, 1)
strCode = Mid(strDelimiter, 6, 3)
strDescription = Mid(strDelimiter, 13, 51)
strRecord = Right(strDelimiter, 26)
strModel = Mid(strDelimiter, 81, 4)
strCountryCode = Mid(strDelimiter, 86, 2)
strSerial = Mid(strDelimiter, 89, 6)
strErrorDate = Right(strDelimiter, 8)

For Each var In varSplit
If Len(var) Then
db.Execute "INSERT INTO TBLRECORDS(TXTTYPE,TXTCODE,TXTDESCRIPTION,TXTRECORD,TXTMODEL,TXTCOUNTRYCODE,TXTSERIAL,TXTERRORDATE) VALUES('" & strType & "', '" & strCode & "', '" & strDescription & "', '" & strRecord & "', '" & strModel & "', '" & strCountryCode & "', '" & strSerial & "', '" & strErrorDate & "');"
End If
Next


' Debug.Print strType
' Debug.Print strCode
' Debug.Print strDescription
' Debug.Print strRecord
' Debug.Print strModel
' Debug.Print strCountryCode
' Debug.Print strSerial
' Debug.Print strErrorDate
'
End Sub

 

[Cronk]

Um, if you want to execute an append query which adds a record with data in 14 fields, 14 times, you'd put it in a loop which executed 14 times.

If you only want to append the record once, put the query in a loop that only executes once, or better still, drop the loop.