Merge multiple records into 1 record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table from a Purchase Order notes system, that can have multiple lines of notes per PO
This table is a flat file from an AS400 and each line of notes is a seperate record
The Identifiers are the PO number and a sequence number for each record

I need to be able to merge each line of notes for a PO into a single record

Anybody got any thoughts on how to accomplish this

Thank
Rosco
 
place this code in a module and save it as modFunctions

--------------------------------------------------------------------------------

Public Function GetPOItems(ByVal inPO As Long)
With CurrentDb.OpenRecordset("tblPOItems")
While Not .EOF
AllItems = AllItems & !POItemLine & vbCrLf
.MoveNext
Wend
End With
GetPOItems = AllItems
End Function

--------------------------------------------------------------------------------

where ..

tblPOItems is the name of the table containing your PO items
POItemLine is the name of the field in the PO Item table that contains the individual item line


--------------------------------------------------------------------------------


then create a query based on the PO header table (tblPOheaders)
(where POnum is the field containing the PONum)

Goto View->SQL

and replace the sql text with ..

select GetPOItems([POnum]) as HeaderDesc,* from tblPOHeaders

Save this query and run it and you will have a concatenated Items field for each order called HeaderDesc.
 
YIKES ..

SORRY..
the function should look like this ...


--------------------------------------------------------------------------------

Public Function GetPOItems(ByVal inPO As Long)
With CurrentDb.OpenRecordset("select * from tblPOItems where [PONum]=" & inPO)
While Not .EOF
AllItems = AllItems & !POItemLine & vbCrLf
.MoveNext
Wend
End With
GetPOItems = AllItems
End Function
 
Won't work if u need more than 255 chars
then use a memo field and look up the appendchunk examples

Pieter
YIKES ..

SORRY..
the function should look like this ...


------------------------------------------------------------------------------

Public Function GetPOItems(ByVal inPO As Long)
With CurrentDb.OpenRecordset("select * from tblPOItems where [PONum]=" & inPO)
While Not .EOF
AllItems = AllItems & !POItemLine & vbCrLf
.MoveNext
Wend
End With
GetPOItems = AllItems
End Function
 
Back
Top