A
andypand
Hi I am getting started with VBA. I am trying to convert move 4 field
in a table another table, where they will re-appear as 2 fields(
concatenated).
The field that doesnt change can be duplicated - hence the need fo
concatenation in the export table.
Public Sub Inv_export()
' this proc is designed to do as above
' it assumes that all source records are in numerical order fo
NSN-this is acheived by Index(duplicates ok) being set in fiel
properties.
' this will be protected from user as table resides on back end of DB.
'sort source table by nsn
'dimension variables
Dim NSN1 As Integer
Dim NSN2 As Integer
Dim COMMENTS1 As String
Dim COMMENTS2 As String
Dim rstOutputTable As Recordset
Dim rstSourceTable As Recordset
Dim strExport As String
Dim strSource As String
strExport = "tbl_INV_EXPORT"
strSource = "tbl_INV_COMMENTS"
'define recordsets
Set rstOutputTable = CurrentDb.OpenRecordset(strExport)
Set rstSourceTable = CurrentDb.OpenRecordset(strSource)
'clear output table
If Not rstOutputTable.EOF Then rstOutputTable.MoveFirst
While Not rstOutputTable.EOF
rstOutputTable.Delete
rstOutputTable.MoveNext
Wend
'goto 1st record of source table
If Not rstSourceTable.BOF And rstSourceTable.EOF Then
rstSourceTable.MoveFirst 'if table is populated go to first record
Else
rstSourceTable.MoveNext
End If
'clever loop stuff to read from source and either concatenate wit
previous read, or write to target
NSN1 = rstSourceTable[NSN]
COMMENTS1 = rstSourceTable[COMMENTS] &
rstSourceTable[UPDATED DATE] & rstSourceTable[USERNAME]
' Source Next Record
NSN2 = rstSourceTable[NSN]
COMMENTS2=rstSourceTable[COMMENTS]
If NSN2<>NSN1 Then
Write NSN & COMMENTS
NSN1 = NSN2
Else COMMENTS1=COMMENTS1& COMMENTS2
End If
rstOutputTable.AddNew
rstOutputTable![NSN] = NSN1
rstOutputTable![cOMMENTS] = COMMENTS1
rstOutputTable.Update
I would appreciate any guidance anyone might offer me with writing thi
procedure.
End Su
in a table another table, where they will re-appear as 2 fields(
concatenated).
The field that doesnt change can be duplicated - hence the need fo
concatenation in the export table.
Public Sub Inv_export()
' this proc is designed to do as above
' it assumes that all source records are in numerical order fo
NSN-this is acheived by Index(duplicates ok) being set in fiel
properties.
' this will be protected from user as table resides on back end of DB.
'sort source table by nsn
'dimension variables
Dim NSN1 As Integer
Dim NSN2 As Integer
Dim COMMENTS1 As String
Dim COMMENTS2 As String
Dim rstOutputTable As Recordset
Dim rstSourceTable As Recordset
Dim strExport As String
Dim strSource As String
strExport = "tbl_INV_EXPORT"
strSource = "tbl_INV_COMMENTS"
'define recordsets
Set rstOutputTable = CurrentDb.OpenRecordset(strExport)
Set rstSourceTable = CurrentDb.OpenRecordset(strSource)
'clear output table
If Not rstOutputTable.EOF Then rstOutputTable.MoveFirst
While Not rstOutputTable.EOF
rstOutputTable.Delete
rstOutputTable.MoveNext
Wend
'goto 1st record of source table
If Not rstSourceTable.BOF And rstSourceTable.EOF Then
rstSourceTable.MoveFirst 'if table is populated go to first record
Else
rstSourceTable.MoveNext
End If
'clever loop stuff to read from source and either concatenate wit
previous read, or write to target
NSN1 = rstSourceTable[NSN]
COMMENTS1 = rstSourceTable[COMMENTS] &
rstSourceTable[UPDATED DATE] & rstSourceTable[USERNAME]
' Source Next Record
NSN2 = rstSourceTable[NSN]
COMMENTS2=rstSourceTable[COMMENTS]
If NSN2<>NSN1 Then
Write NSN & COMMENTS
NSN1 = NSN2
Else COMMENTS1=COMMENTS1& COMMENTS2
End If
rstOutputTable.AddNew
rstOutputTable![NSN] = NSN1
rstOutputTable![cOMMENTS] = COMMENTS1
rstOutputTable.Update
I would appreciate any guidance anyone might offer me with writing thi
procedure.
End Su