If you open up the database I've attached you'll see it opens up a spilt form list that will show all the projects that have been created. You click on a projectID it will open up the frmDetails to view all the information associated. What I want is for the text box that says "Tools Used:" next to it to display all the tools used for the project you select. It has already been suggested that I use concatenate to accomplish this but that won't work because of the 255 character limit. I've spent a lot of time searching and it seems like I should be able to make a query for all the tools associated with the project being viewed. This query can then be used as a recordset to loop through and put each tool name into a string variable that will be the value for the text box. I just can't seem to make it work.

I should also mention I don't want to use a subform because the detail form will be used to print out the information it contains for record keeping. If I can get all the tools used into a text box I should be able to minimize the amount of paper used.

This post has been edited by innate: May 31 2012, 09:52 AM

could you use a combobox or listbox instead of a textbox ??

Those would pose the same problem that a subform would. It would be a singular vertical list that would end up causing the need to print multiple pages.

what you display on a form does not have to be the way it looks when printing. Your report could be based off a query and print the way you want.

or am i missing something all together ?

Hi

PMFJI - I haven't looked at your attachment but from your question it sounds like Brent's combine records into a string in the Code Archive might be what you're looking for ?

Here is a quick example of what Bob was kind of saying. On the Form you can see one project at a time. Then usingthe combo box you can select a Project Number and then click on the Report button and it will show you in the Report only the Project Number you selected. If you want to see all the Project Numbers select the infinity, which is the star (*) from the combo box.

@raz

i am working on the new bob to english dictionary, will get you the latest copy.


thanks !

Bob

(IMG:style_emoticons/default/frown.gif)

If there's a way to make the report list the tools like this: tool1, tool2, tool3, etc.

instead of:
tool1
tool2
tool3

then it could work. I'm not aware of how to customize the report to do that.

my interpretation is that you have a form and a report. Your form could show the values in a column and then the report can show them as a row separated by a comma.

Did you look at what RAZ posted ?

There is. That's what Raz and Alan's links tell you how to do. Have you looked at those links?

In short, it's very difficult to do directly in a Report or a Query. You need a bit of very simple VBA code, a function that takes multiple records and concatenates them into a single text string. The links that have been posted include that function, which you will need to copy and paste into a new Module in your database, and then call in your query or from your report.

Let us know how it works for you, what you tried, and what worked and what didn't, and we'll be glad to help.