Unanswered: create a text field with results of a query
I have a situation where I would like to take the result from a query:
I would like to create a temp table that would look like this:
2 1, 2, 3
2 1, 3, 6, 10
Is there is any code that I can use to accomplish this?
Last edited by dbjim; 11-19-04 at 09:44.
Reason: new to system
Some of your options
You can create a temp table for each instance of this
Use the same temp table and purge records after use
Create a collection and reference that information from code
which one you choose depends on the application of the information in the form.
Ill go over the latter two options for you they may be a little easier to work with
first of you need either dao or ado. I am going to use DAO for this instance as it it a little easier for a n00b to understand.
if you need help finding the dll lemme know.
dim db as database: dim rs as recordset, rs2 as recordset
dim Col as new collection
dim str as string
set db= currentdb
'I am going to assume that your list of first values is a derivative from another table. and has values in that table
set rs = db.openrecordset(select firstvalue from tblTable)
do while not rs.eof
set rs2 = db.openrecordset(select secondvalue from tblTable2 WHERE foreignkey = " & rs.fields(0).value)
'again we assume it is going to return values
str = rs2.fields(0).value
do while not rs2.eof
str = str & ", " & rs2.fields(0).value
coll.add str, rs.fields(0).value
set rs2 = nothing
set rs= nothing
set db = nothing
You now have a collection filled with the information. The Key for each entry is the PK from the first table and the data is the information stored.
You can use for loops to cycle through the information etc. and display it how you want.
dim i as integer
for i = 1 to coll.count
debug.print coll.item(i).key & vbtab & coll.item(i)
How you would use this in a temp table.
simply replace the coll.add ...... with
db.execute("INSERT INTO tblTempTable VALUES (" & rs.fields(0).value & "," & "'" & str & "'' & ")" )
if none of these options meet your needs then we can look at the first.
Hopefully this is enough to get you headed in the right direction.