export concatenated fields to other table

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
 
B

BruceM

You could concatenate them in a make-table query. See Help for more on this
type of query. Do you intend to get rid of the first table? Remember that
you can concatenate on the fly any time you want, but that storing the
concatenated field in addition to the source fields could lead to problems
down the road.
 
A

andypand

No the first table will remain and be added to daily.
Why would storing the concatenated fields be a problem? The 3 field
would appear as one in the export table, so why a problem.

Can you give me any help with the coding, as i want to do this in VBA?
Thank
 
B

BruceM

You could probably get away with storing the concatenated value if the source
record never changes, but even so, storing duplicate data is generally not
useful. However, if you are determined to do so you would probably do better
posting in a group other than Getting Started, which is oriented toward
newcomers to Access. Perhaps the modulesdaovba group would be more suitable,
but don't be too surprised if somebody there suggests that storing duplicate
data is not usually the best choice. Then again, perhaps I am completely
misunderstanding your intentions. Good luck.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top