Displaying a list of a related field on a continuous form

[ScubaBart]

Been trying different approached to achieve what I am looking for and was posting in the coding subforum but wasn't able to resolve the desired result.

Basically, I'm working to create an overview form that would be the first form seen in an application to manage assorted honeybee apiaries and hives.

The form is designed to be continuous with one Apiary per for row on the form. Next to the basic info for the Apiary is a list of Hives and some basic info for each Hive.

The tables are T_Log_Apiary and T-Log_Hives and there is a many to one relationship (many Hives to each Apiary).

This is the form I am currently working with and I have two methods I am using to generate the Hive lists. First is using Allen Browne's ConcatRelated() Function with does pull the correct info for each Apiary and the second is a list box pulling the information from a query with a condition that the field Link_to_Log_Apiary_ID = Log_Apiary_ID (Basically calling on the relationship).



So, while the first option correctly lists the info, each text box is independent of the other and when the list extends past the bottom of the box, I would need to scroll and the three boxes do not scroll in unison.

The second option allows for each piece of info to remain on the same row and allows for scrolling but apparently there is an issue where list boxes (and combo boxes) only relate to the first row in a continuous forms environment. As you can see, the Hive details are the same for each Apiary with this option.

So, What I need to accomplish is either 1) Create a way for a single scroll bar to scroll the three text boxes that use the ConcatRelated() function or 2) figure out how to force the list box to properly link to each row in the continuous form respectively.



I can upload the entire database if anyone has any ideas that may work for this.

Thanks for any thoughts on this.

[Bob Fitz]

Can you post a copy of the db

[ScubaBart]

Database is attached.

Form F_Main_Overview is the form I am working on. Query Q_Hive_List is the query that the list box pulls from.

The only two tables used for this form are T_Log_Apiary and T_Log_Hive

Thx

[June7]

I use listbox version. This works great for form in Single View. Really won't work for continuous form.

Requery listboxes when navigating records. Example:

Private Sub Form_Current()
With Me
.lbxSectionPCIAge.Requery
.lbxBranchPCIAge.Requery
.lbxAirportPCIAge.Requery
.lbxUsePCIAge.Requery
.lbxInspections.Requery
.lbxRWname.Requery
End With
End Sub

As for scrolling textboxes simultaneously - I am not going there.

[Welshgasman]

When you requery the listbox, they all change to whatever that current record is.

O/P insists on using incorrect controls for what they want to do. A subform would be choice for this. They are easy to sync.

[ScubaBart]

When you requery the listbox, they all change to whatever that current record is.

O/P insists on using incorrect controls for what they want to do. A subform would be choice for this. They are easy to sync.

In order to be an overview, the multiple Apiary details are easiest to list in a continuous form design. Access doesn't allow a subform in a continuous form. Likely for the very reason I am running into the issue now. I tried several different ways to requery the control but to no effect in the results. The list box on every row still shows only what it corresponds to in the very first row.

[Welshgasman]

If you are prepared to accept the same in each listbox in each row, all you need is a Me.List74.Requery in the forms Current event.
You are not understanding that the query looks at what that ID field is when it runs.
It is unable to look at anything else, just the current record for that form.

[CJ_London]

Access doesn't allow a subform in a continuous form. Likely for the very reason I am running into the issue now

actually that is not true- you can have a subform on a continuous form, but it has to be placed in the header or footer

[ScubaBart]

If you are prepared to accept the same in each listbox in each row, all you need is a Me.List74.Requery in the forms Current event.
You are not understanding that the query looks at what that ID field is when it runs.
It is unable to look at anything else, just the current record for that form.

Yea, I pretty much get that, I was simply hoping that it would adjust to recognize that each row has a different value for the 'one' linked value and would adjust accordingly. Apparently there is not.

What I did find while searching was this tidbit of information:

You cannot reference controls "individually" ("at the line level") in a MS Access continuous form. Your only solution here is to bound these controls to an underlying recordsetrecordsource where corresponding fields hold the values to be displayed.

To that end, what I would think it must be possible is to generate a separate table / query that could be properly populated each time this form opens, with the correct values for each Apiary row that could then be entered into fields specific for each Apiary record. Unfortunately, I don't know how to go about this.

It still is only going to work if I have the Hive info fields in a container that has a vertical scroll feature that is synchronous with each of the fields.

If you look at the first row in the continuous forms version of what I uploaded, the list box corresponds to th t Apiary record and performs exactly as desired. Since the list box option is not going to work, I need to find an alternative. Populating an independent table/query or even long text fields in the Apiary table with the results when the form opens seems like it could be a solution if I can also achieve a common vertical scroll function. Perhaps some manipulation of the data into a properly spaced and rowed text field that would have a single scroll bar and I can simply overlay lines to provide a "table like" appearance,

[ScubaBart]

actually that is not true- you can have a subform on a continuous form, but it has to be placed in the header or footer

Well, yes but that really won't accomplish what I am looking for. I wish to view the list of Hives next to the list of each Apiary respectively.

[Welshgasman]

So have two subforms in a form

On the left is Apiary and on the right Hives.

[mike60smart]

Hi
The attached is the ideal layout where you have a Main Form based on the Apiary table
then a number of Subforms listing the Related details required.

[Welshgasman]

Already proposed that in his other thread Mike. He was not interested in that layout. That is why I have now suggested two subforms, synced .

[June7]

Review https://www.fmsinc.com/MicrosoftAcce...edSubforms.asp

[ScubaBart]

Hi
The attached is the ideal layout where you have a Main Form based on the Apiary table
then a number of Subforms listing the Related details required.

Appreciate the effort but not what I'm looking for.

I'm investigating the linked subforms as welshgasman has suggested.