Howdy, all!

I'm still having difficulty finding stuff in the searches...I need to get better at this with the new site.

How can I copy the value from a concatenated field on a subform (query) to a memo field on the main form?
I've tried a jillion different ways and none of them seem to work.
The subform query concat is a little clunky (doesn't like to update on the subform until main form_current), but seems to work.
In the subform_current event, I ran a recordset update, no main table update.
Also tried an update SQL there instead, but none of them update the main table.
Can you not update a table while it's open?
Maybe I have the wrong event somewhere; it's very confusing to know which event to assign it to.

Any suggestions?

db

You mention both the form and the table. It is only rarely necessary (or even a good idea) to store a calculated (e.g., concatentated) value in a table, since you can always use your query to recalculate it. This approach ensures that you don't need to worry about synchronizing after changes to the data which make up the calculation.

If you want to display a concatenated/calculated value in a form, you can do that quite simply. You can either do the calculation in a query underlying the form and have the form "point" to the calculated field in query... or you can do the calculation in the form itself. Note that neither requires you to store the calculation.

If you'll provide a more specific description, folks here may be able to help you determine if your situation is one of those rare ones for which it DOES make sense to store the calculated value...

Thanks, Jeff.
The concat fields in 5 subforms are (respectively) a person's education, affiliations, certifications, publications, and registrations. Each record has several fields that make up the record-level concatted field, then the subform concat puts it all together for each person into one memo field.
I do realize it's dumb to store such a calc, but...
I will use the concatted field in the base table for a complex search form I'm building and also for resumes (for over 1,000 employees). Storing the concat value in the base table saves me querying the concats while loading the search tables, which runs verrrrrrry sloooooooooooowwwwwwwwwwly. So if I can do it at the record-change level, it will help my search form tremendously.
I don't really care about displaying the field in either the form or subform; just that the info gets updated in the table.
Does this justify my request? You wouldn't believe how many weeks I've worked on this and how many queries, update SQLs, and recordset updates I've worked my way through getting to this point.
db

I can't tell if yours is one of those cases... what I've found is that a "price-at-sale" value does qualify, especially if item prices can change over time.

So there are a fair number of reasons why a query runs slowly ... have you looked into things like indexing?

I'm pretty sure the indexing is optimum--most have only one field to index, the KeyID for each person. Everything else on the subform tables gets concatenated. The only exceptions are education and registrations, which sort by a date. It runs slowly because there are so many fields to concatenate, and I don't want to slow the loading of the form waiting for the concats to process.

So, do you know why the table does not seem to be updatable? Is this not possible while it is open? If not, maybe I should store the current record's KeyID, close the table that is the source of my main form, update the table, requery the main form, and go back to my record? Speed is not really an issue while someone is updating the form.

Hi, Jeff, hope you're still with me...

I checked the indexing--had forgotten the query joins. So I went index-crazy. I'd read somewhere to limit indexes for speed.
I found 13,000 blank records in one table. Not sure how that happened. Surely that slowed things.
Each update query runs quickly now, and I added DISTINCTROW--even more efficient.
Now I can update the search form's temp table and delete it when the form closes.

I very much appreciate the prompt on the indexes--it helped speed up the query so I can work my way through this.

Thanks!

db