Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

MS Access XP/VBA

May 2, 2005 5:12AM PDT

Hello,

I have a query of a bunch of rooms with different color floor tiles in them. Ie.

Room 1 Red floor tile
Room 2 Blue floor tile
Room 3 Red floor tile

I would like to make a report with a field that sorts by tile color and then lists all the room in a single textbox. Ie.

Red floor tile Room 1, Room 3
Blue floor tile Room 2

Any ideas on how to dump the room names into a single text box instead of?

Red floor tile Room 1
Room 3

Blue floor tile Room 2

Thanks,

Dave

Discussion is locked

- Collapse -
Please explain.
May 2, 2005 5:55AM PDT

'report' and 'text box' don't seem to apply to each other.

The detail section of a 'report' consists of lines with fields in each line corresponding to one record of a query (that's why it's called 'detail'), or calculated run-time for each record.
I associate a 'text box' with a form, and a form definitely is something else as a report.

And why the association with VBA in your question?

Kees

- Collapse -
I mean what you know.
May 2, 2005 10:52PM PDT

Well, if you go to design a report, you can pull up the toolbox and slap down a textbox. Then you assign a control source to it or leave it unbound. While you do not enter any text into the report yourself, Access still calls it a textbox during design.

However, just replace textbox with field if you prefer to use that term.

I put VBA because there is probably a way to run a query and dump it into an array and then feed the entire array into the textbox/field. However, the exact syntax of which is currently beyond me.

- Collapse -
Thanks for the explanation. Quite clear now.
May 3, 2005 12:08AM PDT

It's not very difficult to program. You should have made a 'result' table beforehand.

1. Delete all records from the result table (using SQL)

2. Open the query as a recordset for input (it should be sorted on color, then on room-number) and open the result table as a recordset for output.

3. For all colors you encounter:
- for the first record of that color: fill color and first room
- for the next records: append room (string concatination)
- after the last record: write the whole string to the result table

4. Then run the report over the result table.

Any book or course of VBA in MS Access will teach you the language elements to use. I won't do a recommendation for a book; pick anyone you like that's suitable for your programming level.
Also note that group procssing (all records of the same color) is a basic programming concept teached to all programmers since Jackson defined it back in 1975 (but maybe not now, anymore).

You might want to add a field to sort on to the result table or to the query, unless you're content with a alphabetic sort on the color name (black, blue, green, red, violet, white, yellow). Your example shows the sequence red -> blue which isn't alphabetic (the rainbow sequence isn't a preprogrammed concept, of course).

Find someone to program this for you (but he might want money or a few bottles of wine for it, depending on your relation), if you don't feel like doing it yourself.

Hope this helps.


Kees