Attempting to show data from related table in form with continuous rows

[ScubaBart]

I'm back with the bee application.



This time I'm trying to show some related data on a form set to continuous forms and the data will be multiple fields from multiple rows in a separate table.

So, I can't use another sub-form since I'm already using continuous view.

I tried using Allen Browne's ConcatRelated function but even if it would allow concatating multiple fields together, there is apparently a 255 character limitation bug that would come into play.

He gives a synopsis of this along with a potential workaround here:

http://allenbrowne.com/bug-16.html">Microsoft Access Flaws - Concatenated fields yield garbage in recordset (allenbrowne.com)

I attempted to make the Union query and surprisingly, got it to more or less work but it requires a manual input of the target ID field to get the proper row(s). Q_Function_Query (named just for testing)

This may work but I don't know how to integrate it into my form. Or, there may be a more efficient way to present the data. To see what I am trying to accomplish, you need to run through my app as follows:

Start with the form F_Main_Overview. Select the button to "Edit / Add Hive Details". This form is a mess right now as I play with certain things but is where I want to present the data. But before we go there, click on the "Edit Details" button on the left of the first row (next to where it says Apiary ID and Hive ID).

On this form, there is a sub-form for Change / Event Records. I can ass an Event or Edit an existing Event. The pop-up that appears then allows you to Save or Delete the Event, regardless whether it's a new or existing Event. When done playing with that, select Save or Cancel to return to the main Hive form.

So the data that is shown on the Event sub-form is what I want to show on the Main Hive Log form but in a condensed format and it will not be edited on that form.

I considered using two ConcatRelated fields but two issues, one, the one with the Event description would likely wrap the text throwing the alignment between the date and the description out of whack and two, the character count in the description would likely exceed that 255 character limit with some of the descriptions.

The Union query seems to be my best bet but I have never worked with advanced queries and am unfamiliar with some of the additional syntax required for them to work properly. The one I made, Q_Function_Query was managed just because I copied the workaround from Allen Browne's page in the link above.

If someone can tell me how to integrate this query into my form, that would be great. The link for the data is "Link_Hive_ID from the main Hive form (and underlying data source) and "Link_to_Log_Hive_ID_Evt" from the Event table.

Any thoughts on whether the Union query is a good way to go or is there a better approach I should consider (and likely need help with) ??

Latest version of file is attached dated 2025-03-16. Previous versions do not contain the required tables/code/queries/forms.

Thanks !!!

[madpiet]

TLDR

"So, I can't use another sub-form since I'm already using continuous view."

Use a popup form and set the filters in the Open event.

[June7]

Certainly can't have an SQL statement as ControlSource of textbox.

Definitely can concatenate fields with ConcatRelated function.

"[Date_Recorded_Evt] And [Notes_Evt]" - if you want to concatenate fields use & operator not AND, like:

"[Date_Recorded_Evt] & ' : ' & [Notes_Evt]"

Now the expression works.

Since ConcatRelated is calculated in textbox pulling from table, not in query used as source for another recordset, should not have issue with 255 character limit. I tested with your data. I added more than 255 characters into a record for Notes_Evt field. Entire string is retrieved in textbox.

Should set ScrollBars property to Vertical for these two textboxes.

[ScubaBart]

Certainly can't have an SQL statement as ControlSource of textbox.

Definitely can concatenate fields with ConcatRelated function.

"[Date_Recorded_Evt] And [Notes_Evt]" - if you want to concatenate fields use & operator not AND, like:

"[Date_Recorded_Evt] & ' : ' & [Notes_Evt]"

Now the expression works.

Since ConcatRelated is calculated in textbox pulling from table, not in query used as source for another recordset, should not have issue with 255 character limit. I tested with your data. I added more than 255 characters into a record for Notes_Evt field. Entire string is retrieved in textbox.

Should set ScrollBars property to Vertical for these two textboxes.

Yes this works great. THANKS !!!

Just part of me not having a good grasp on how to utilize the various syntax.

I even managed to get the few spaces between the date and the event not using just a pair of single quotes with no brackets. But just saw you have that in your example above as well.

And I did add the vert scroll bar as needed (had figured I would need that just hadn't got to the final formatting stage yet.)

[ScubaBart]

TLDR

"So, I can't use another sub-form since I'm already using continuous view."

Use a popup form and set the filters in the Open event.

I may add a pop-up to expand the list but I still want a condensed list to let the user know there are entries and give a hint of the most recent one(s) as I will have it ordered by date descending.