Create string showing all records from one field

[mikehoot]

I need to create a string showing all records from one field in a table. For example:

In a table - tblCars

with fields - fldCarID, fldCarName

I wish to show all car names seperated with ", " in a control.

Any help is much appreciated. B-)

 

[nickjar2]

Mike, this may work in the form On_Current


dim db as database
dim rs as dao.recordset
dim sRec as string

set db = currentdb
set rs = db.openrecordset("select * from tblCars where fldCarID = " & txtID,dbopendynaset)

'where txtID is the name of the field on the form that holds the fldCarID info

if rs.recordcount > 0 then
rs.movelast
rs.movefirst
else
' do something else
ie. exit sub
end if

sRec = rs.fields("fldCarID&quot
sRec = sRec & " " & rs.fields("fldCarName&quot

txtFieldName = sRec

exit sub

Try this, it may work,

Nick

 

[nickjar2]

Mike,

I just thought of another way, possibly easier.

Build a query based on your cars table. Create a new field ie.

expr:[fldCarID] & " " & [fldCarName]

In your form, create a new textbox and make its control source = to this new field.

Nick

 

[mikehoot]

Thanks for your help but i'm not sure I understand the first answer. The second will only concantenate the two fields from one record.

What I would like to be able to do is to concantenate one field from all records, ie
Record 1 - Ford
Record 2 - BMW
Record 3 - FIAT

I would like to make a control appear as "Ford, BMW, FIAT"

Thanks

 

[nickjar2]

Mike,

Am I right in assuming you have a report. In this report all you want is a field displaying all cars that you have in your table ie. BMW, Ford etc.

Nick