Combining multiple fields into one

[ragnarok75]

I have a form which allows users to select any number of records and then load them into this form. This form allows them to add priorities to these records. I need to take one of the fields of each of the chosen records and combine them into one other field in another table. The fields are memo fields. Can anyone think of a simple way to do this. The number of fields that need to be combined will always be different so I need something that will loop through the data until all the loaded fields have been read.

 

[IGPCS]

is the form based on a table or query?

you can make an update or append querie using the in formation in the table/query base to what fields you want in the second table

 

[ragnarok75]

Example - I have 4 records in table1. They are being added to a 'collection' which consists of one record in table2. FieldA for each record in table1 needs to be combined into one record\field in table2. When I use an append query it wants to add 4 records\fields - one for each record\field in table1. If these were seperate cells in Excel, I would concatenate them, how do I do this through VBA in Access?

 

[randy700]

If these were seperate cells in Excel, I would concatenate them
Have you tried concatenating them in Access?
NewField = FirstOldField & SecondOldField


Randy

 

[ragnarok75]

How do I specify the field since it is the same field?

Table1
ID
Notes

Table2
NID
Notes

The user uses a form to choose multiple records from table1 and then assigns those records to one record in table2. I need to take all selected records from table1 and combine the Notes field then copy the combined field to Notes in Table2.

 

[PHV]

Perhaps of interest for you: faq701-4233

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[snayjay]

How about a combination of concatenation and dlookup??

dim x,y,z
x = dlookup("[Notes]","Table1","[ID] = " & ##)
y = dlookup("[Notes]","Table2","[NID] = " & ##)
z = x & y

Notes: requires a way to identify which ID/NID you are wanting, and also you don't have to declare as I did, I just did it this way to illustrate what I meant.

vs.

x = dlookup("[Notes]","Table1","[ID] = " & ##) & dlookup("[Notes]","Table2","[NID] = " & ##)

oh and if you are trying to combine all the notes from 1 table to 1 field in another table then just do a loop:

Open a form with the controlsource being Table 1
On the Form Open Event do the following

dim x 
x = ""
recordset.movefirst
do until recordset.eof
  if not isnull(Notes) then
     x = x & notes & vbcrlf 'add the vbcrlf if you want to add a line
  end if
  recordset.movenext
loop
'then figure a way to take x and apply it to the field in table 2... I usually use SQL
dim addSQL
addSQL = "INSERT INTO Table2 (Notes) Values ('" & replace(x,"'","''") & "')" WHERE ....(figure out your own WHERE clause)
docmd.setwarnings (false)
docmd.runsql (addSQL)
docmd.close

There is probably another way to do it without using a hidden form, but I'm just a beginner programmer and this is the way I would do it.