Pull data from unbound textbox on form into multiple records on subform

[Turn85]

Not sure if what I'm trying to acheive is possible in access but thought I'd ask!

I've been tasked with creating a system to generate a standardised report and store the data in a database for creating monthly stats and general manipulation. Currently people manually write the reports and follow a basic template then once complete manually enter the data into a spreadsheet.

Each of these reports contain basic information about a customer then list multiple payments followed by some other information. It sounds somewhat similar to a receipt, but a lot more detailed. These are then sent on to a manager for authorisation.

The bit I'm currently stuck at is the getting the payment data into access, it comes from a text based system which we can copy and paste. Each line is a separate payment and each line follows the same format, for example:

Acct no. Amount   Description          Date  Time
10000000     1.00 payment one          18.10 13.30
20000000   100.00 2nd payment today    17.10 12.10
30000000 10000.00 This is also a pymnt 16.10 09.30

In the example above the first 8 digits are the account number, characters 1-8, the payment amount is always characters 10-17 and so on. So each part of the data is always in the same place.

So I've created a form to enter all the data into to create the report and collect the data. To collect the payment data I have added an unbound multiline textbox that the data can be pasted into. The idea then being that you click a button and it will take each line of the text and break it down into its elements and enter them in to fields of the sub-form and do this for each line, each line being a new record in the payments sub-form.

I've looked for ways to do this online, and the closest I've found is this:

social.msdn.microsoft.com/Forums/en-US/7c3f5db5-72d7-427c-9fdf-f8a16d172ae8

I've not been able to get it to work, due to various errors, also this separates each line depending on spaces. Which means I'd have problems when it reads the description, which could have multiple spaces in and actually is not needed for the report.

Does anyone have any suggestions or about to point me in the direction of a better example I can work from.

 

[Ranman256]

why unbound?
Just bind the fields and you enter data directly into the tables.
or paste, or import.

 

[Turn85]

I chose unbound as the data that needs to be entered can only be pasted from the other system and I need some way of breaking each line down into the require fields and getting rig of the parts I don't need like the description.

It takes too long to manually copy and paste each part of each line manually into it's respective field, especially when there are 30 lines or so. The idea is by having a script to run through it and do it automatically it frees up time that can be used elsewhere and also reduce the chance of error when inputting the data.

Thanks

 

[Minty]

If the data is available as a fixed width text file you can link to it in access directly.
You can then import it to a master record. The only caveat is that you would need some identifier on the records to make sure they don't get imported twice. Is there a unique record identifier (Maybe account no and date) on the original data?

 

[Turn85]

unfortunately the system which holds the information does not out put in any format, the only way we can get the info we need is by copying and pasting. Lines can also be identical, there's no unique identifyer.

 

[Minty]

Well in that case your suggested solution, will work provided you know what data has already been entered.
I would be tempted to create a link to a staging text file locally, and paste your text into that. Then present that linked table data in a multi select list box, and then once you have selected records you want from that list import them into your main table.

 

[static]

Add a textbox (text1) to a form and paste the code in the form module.

Run the form. Paste text and press tab.

It should create a temp table and insert the records.

Private Sub Text1_AfterUpdate()
On Error Resume Next
    
    Const MY_TEMP_TBL As String = "TmpImport"
    
    Dim db As DAO.Database
    Set db = CurrentDb
    
    'if temp table doesn't exist create it
    
    If (Not TableExists(db, MY_TEMP_TBL)) Then
        If Not CreateTempTable(db, MY_TEMP_TBL) Then
            MsgBox "Error creating temp table."
            Exit Sub
        End If
    End If
    
    'split string and insert to temp table
    
    InsertData db, MY_TEMP_TBL, Text1
End Sub

Private Sub InsertData(db As DAO.Database, tbl As String, str As String)
    Dim dt As String
    Dim a(4) As String, ar() As String, s
    ar = Split(str, vbNewLine)
    For Each s In ar
        a(0) = Mid(s, 1, 8)
        a(1) = Mid(s, 10, 8)
        a(2) = Mid(s, 19, 20)
        a(3) = Mid(s, 40, 5)
        a(4) = Mid(s, 46)
        
        dt = fmtDt(a(3))
        If dt <> "" Then _
            db.Execute "insert into " & tbl & " values ('" & _
                a(0) & "','" & a(1) & "','" & a(2) & "',#" & dt & "#,#" & a(4) & "#)"
        
    Next
End Sub

Private Function fmtDt(s As String) As String
    a = Split(s, ".")
    If UBound(a) Then fmtDt = a(0) & "-" & MonthName(a(1)) '& "-" & Year(Date)
End Function

Private Function TableExists(db As DAO.Database, tbl As String) As Boolean
On Error Resume Next
    RefreshDatabaseWindow
    Dim t As TableDef
    Set t = db.TableDefs(tbl)
    TableExists = Err.Number = 0
End Function

Private Function CreateTempTable(db As DAO.Database, tbl As String) As Boolean
On Error Resume Next
    With db
        .Execute "create table " & tbl
        .Execute "ALTER TABLE " & tbl & " ADD Acctno text NULL"
        .Execute "ALTER TABLE " & tbl & " ADD Amount double NULL"
        .Execute "ALTER TABLE " & tbl & " ADD Description text NULL"
        .Execute "ALTER TABLE " & tbl & " ADD [Date] date NULL"
        .Execute "ALTER TABLE " & tbl & " ADD [Time] date NULL"
    End With
    CreateTempTable = Err.Number = 0
    RefreshDatabaseWindow
End Function

 

[Turn85]

Thanks Static, that's brilliant! how would I go about putting that data straight into an existing table which has an auto-increment primary key?

 

[static]

Change "TmpImport" to the name of your table.

Edit+

But you would need to change the order of the fields in the table to match the temp table my code creates. Put the ID field at the end.

 

[Turn85]

Do the fields in the createTempTable function also have to match my Table if it already exists?

I'm getting a error when running the script because it can't match the number of fields being inserted to the number of fields in the table. I also have another field which will only be updated at a later time which initially needs to be blank. I assume it's these two fields causing the issue.

Thanks again!

 

[Turn85]

I've managed to work out its the auto-increment field that's causing the issue, if i remove the field from the table it inserts the data with the blank columns, if I put in it says the number fields do not match the table, if I try and add a blank entry to the auto-increment field I get a data type mismatch.

Is there a specific way of dealing with auto-increment fields when inserting record?

Thanks again

 

[static]

When I said to move the fields I meant to literally edit the table design. But don't do that.


What you really need to do is change this line to specify the fields you are inserting to

db.Execute "insert into " & tbl & " values ('" & _
a(0) & "','" & a(1) & "','" & a(2) & "',#" & dt & "#,#" & a(4) & "#)"

i.e

insert into yourtable ([fld1],[fld2],[fld3]) values ('xxx','yyy','zzz')

 

[Minty]

Auto Increment / Identity fields values shouldn't be included with insert statements, the values will automatically be created when the record is added.

 

[Turn85]

Thanks to you both, managed to get it working in the end. Don't know why it wouldn't work but deleting the original table then building a new one as per Static's code and then manually applying the primary key seems to have sorted it all out.

Again big thanks to you both, hopefully the rest of the project will be straight forward!