Function for combining several records into 1

[SteveE]

I have a query which uses a combinition of fields to produce a string. This string is used as a "detail field " and is currently copied across into another system which produces notes. I wish to take all the records produced from a filter and combine and update them into a single field (which is the same as current happens when copied manually) I think this would involve creation of a record set and looping but I am lost here.

The query " LoadDetail " results (in this case) 5 records of 1 field " LDetail "as below
21663319 EAN : 5391507661043 P/N 159246
21665802 EAN : 5391507661036 P/N 25564
21665829 EAN : 5391507661036 P/N 25564
21665894 EAN : 5391507661036 P/N 25564
21665899 EAN : 5391507661036 P/N 25564

The database detail:
DB = Shipments.mdb
Query = LoadDetail
Combined Field = LDetail
The new Field to update to with all combined data = NLDetail
any advise appriciated

Steve

 

[jal]

Here's some sample code. Maybe you can modify it to handle your situaton.

Dim cn as Adodb.connection
Set cn = CurrentProject.Connection
Dim rsSource as New Adodb.Recordset
rsSource.Open "SELECT * FROM table1", cn, adOpenKeyset, adLockOptimistic
Dim str as String
Do While rsSource.EOF = false
'concatenate
str = str & rs("Col1") & rs("Col2") ....etc
rs.MoveNext
Loop
rsSource.close
set rsSource = Nothing
Dim rsDest as New ADODB.RecordSet
'pull zero records
rsDest.Open "SELECT * FROM DestTable WHERE 1 = 0", cn, adOpenKeyset, adLockOptimistic
rsDest.AddNew 'adds a blank row
rsDest("DestCol") = str
rsDest.MoveFirst
rsDest.Close
set rsDest = Nothing

 

[SteveE]

Jal,
Many thank for the speedy reply I have adjusted and tried to use your code as below:

Dim cn As ADODB.Connection
Set cn = CurrentProject.Connection
Dim rsSource As New ADODB.Recordset

rsSource.Open "SELECT * FROM Shipments, cn, adOpenKeyset, adLockOptimistic"
Dim str As String
Do While rsSource.EOF = False

str = [OrderNo] & " " & " EAN : " & [EAN] & " " & "P/N " & [RetPN]

rs.MoveNext
Loop
rsSource.Close
Set rsSource = Nothing

Dim rsDest As New ADODB.Recordset

'pull zero records

rsDest.Open "SELECT * FROM TempPO WHERE 1 = 0", cn, adOpenKeyset, adLockOptimistic
rsDest.AddNew 'adds a blank row
rsDest("Detail") = str
rsDest.MoveFirst
rsDest.Close
Set rsDest = Nothing

This results in error "connection cannot be used to perform this operation"
N.B I am using Access 2000, the source is a query not a table.
I know I am unclear but if you can help its very appriciated.
Steve

 

[jal]

silly syntax error on my part, sorry: the quottation marks are supposed to wrap the query.

"SELECT * FROM tableShipments", cn, adOpenKeyset, adLockOptimistic

If your query is called "Query5", you might able able to do:


"SELECT * FROM Query5", cn, adOpenKeyset, adLockOptimistic

I'll have to test this (I don't use queries often).

But your dest table probably can't be a query - it probably needs to be a table if you are inserting values.

 

[jal]

sorry, now I am thinking it's something like this:

rs.Open "query5", CurrentProject.Connection