Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #13108

Re: Splitting a fields data into one or more new recordset(s)

Show all headers | View raw

on 5/7/2018, noodnutt supposed :
> Hi Patrick & Ron
>
> I appreciate your responses. I think I need to go into some deeper 
> explanation as to what I need v. if possible.
>
> tblImport_Orders is purely for importing the csv file which could contain up 
> to 250 rows of data, and each row can contain PO Number or multiples. ( Ron, 
> you are correct in the comma being the delimiter ).
>
> Patrick, I think (s) would need to be a variable in that it would not be the 
> same string length each time.
>
> I think I may have confused both of you with the period separating the 
> customer name. I merely did that to try and keep registration. The actual 
> customer name along with the other 10 fields need to be copied to the new 
> record(s) in their own respective fields.
>
> So, I was thinking ( and this is nowhere what the syntax should be, but it's 
> the best way I can explain it, I hope.. ).
>
> Before continuing, tblImport_Orders data will append it's data to 
> tblOrders_Inbound and then data will be deleted after this process, this will 
> be the Rinse & Repeat each day.
>
> So, something like this:
>
> For each Record in tblImport_Orders
>  If txfPONo has more than one PO then
>    With txfPONo  
>     Split each PONo into new Record
>     Copy Fields( 1,3,4,5,6,10,11,12,13,14 ) from this record to new record(s)
>    End With
>  Else: Exit Sub
> Next Record
>
> Thanks again for your time guys, very much appreciated.
>
> Cheers
> Mark.
>

Completely untested air code, but this should give you the general idea 
of how I would attack this sort of process.  The only weird thing that 
many Access programmers will see here is the use of ADODB recordset 
instead of the more common DAO type.  I have been using ADODB for soe 
long in so many different programming environments that i long ago 
decided to just "stick" with it for all projects.  Anyway here is 
something to get you started with.

Public Sub MoveFromImportToImbound()

    Dim rsImport As ADODB.Recordset
    Dim i As Integer
    Dim POs() As String, strSql As String

    Set rsImport = New ADODB.Recordset
    rsImport.Open "tblImport_Orders ", CurrentProject.Connection, 
adOpenDynamic, adLockPessimistic, adCmdTable
    With rsImport
        Do While Not .EOF
            POs = Split(!FieldPOs, ",")
            For i = 0 To UBound(POs)
                ' We are creating a Sql Insert statement here that we 
will execute for each record we move
                ' I am assuming all fields are text as wrapping them in 
single quotes
                ' and doubling any Single quotes found in the data
                ' Another way to do this would be to  open another 
recordset to the tblImport_Orders
                ' table and AddNew for each record.
                strSql = "Insert Into tblImport_Orders 
(fld1,fld3,fld4,fld5,fld6,fieldPOs,fld11,fld12,fld13,fld14 ) " _
                    & "Values('" & Replace(!fld1, "'", "''") & "', '" _
                    & Replace(!fld1, "'", "''") & "', '" _
                    & Replace(!fld3, "'", "''") & "', '" _
                    & Replace(!fld4, "'", "''") & "', '" _
                    & Replace(!fld5, "'", "''") & "', '" _
                    & Replace(!fld6, "'", "''") & "', '" _
                    & Replace(POs(i), "'", "''") & "', '" _
                    & Replace(!fld11, "'", "''") & "', '" _
                    & Replace(!fld13, "'", "''") & "', '" _
                    & Replace(!fld14, "'", "''") & "', ')"
                ' Execute the sql
                CurrentDb.Execute strSql, dbFailOnError
            Next
            .MoveNext
        Loop
        .Close
    End With
    Set rsImport = Nothing
End Sub

Watch out for line wrap.  Hope this helps.

Rdub

---
This email has been checked for viruses by AVG.
http://www.avg.com

Back to comp.databases.ms-access | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Splitting a fields data into one or more new recordset(s) noodnutt <noodnutt@gmail.com> - 2018-05-05 23:11 -0700
  Re: Splitting a fields data into one or more new recordset(s) noodnutt <noodnutt@gmail.com> - 2018-05-05 23:50 -0700
  Re: Splitting a fields data into one or more new recordset(s) Patrick Finucane <patrickfinucanetx@gmail.com> - 2018-05-06 06:32 -0700
  Re: Splitting a fields data into one or more new recordset(s) Ron Weiner <rw@domain.com> - 2018-05-06 10:19 -0400
  Re: Splitting a fields data into one or more new recordset(s) noodnutt <noodnutt@gmail.com> - 2018-05-07 05:38 -0700
    Re: Splitting a fields data into one or more new recordset(s) Ron Weiner <rw@domain.com> - 2018-05-07 10:20 -0400
      Re: Splitting a fields data into one or more new recordset(s) Ron Weiner <rw@domain.com> - 2018-05-07 10:41 -0400
        Re: Splitting a fields data into one or more new recordset(s) noodnutt <noodnutt@gmail.com> - 2018-05-08 05:26 -0700
          Re: Splitting a fields data into one or more new recordset(s) Ron Weiner <rw@domain.com> - 2018-05-08 10:04 -0400
            Re: Splitting a fields data into one or more new recordset(s) Ron Paii <ron81pai@gmail.com> - 2018-05-08 10:57 -0700
              Re: Splitting a fields data into one or more new recordset(s) noodnutt <noodnutt@gmail.com> - 2018-05-10 05:55 -0700
                Re: Splitting a fields data into one or more new recordset(s) Ron Paii <ron81pai@gmail.com> - 2018-05-10 06:58 -0700
                Re: Splitting a fields data into one or more new recordset(s) noodnutt <noodnutt@gmail.com> - 2018-05-11 05:54 -0700
                Re: Splitting a fields data into one or more new recordset(s) Ron Paii <ron81pai@gmail.com> - 2018-05-11 10:57 -0700
                Re: Splitting a fields data into one or more new recordset(s) noodnutt <noodnutt@gmail.com> - 2018-05-11 19:49 -0700
                Re: Splitting a fields data into one or more new recordset(s) Ron Paii <ron81pai@gmail.com> - 2018-05-14 05:17 -0700
                Re: Splitting a fields data into one or more new recordset(s) noodnutt <noodnutt@gmail.com> - 2018-05-18 05:35 -0700
                Re: Splitting a fields data into one or more new recordset(s) Ron Weiner <rw@domain.com> - 2018-05-10 09:58 -0400
                Re: Splitting a fields data into one or more new recordset(s) noodnutt <noodnutt@gmail.com> - 2018-05-11 06:17 -0700
                Re: Splitting a fields data into one or more new recordset(s) Patrick Finucane <patrickfinucanetx@gmail.com> - 2018-05-11 06:40 -0700
                Re: Splitting a fields data into one or more new recordset(s) Ron Weiner <rw@domain.com> - 2018-05-11 15:26 -0400
                Re: Splitting a fields data into one or more new recordset(s) noodnutt <noodnutt@gmail.com> - 2018-05-11 19:51 -0700

csiph-web