Want to select components of Allen Browne's ConcatRelated() Function

[ScubaBart]

First, not a coder and don't know VBA.

Now, I'm using Allen Browne's ConcatRelated Function and it's doing exactly what I was looking to do. Bravo to Allen for that.



What I would like to do is take it one step further and be able to take the results and be able to pull each returned value, individually and use that to populate a standalone control. The form would allow for a maximum number of controls (arbitrarily picked based on the known design of the database and expected data) that would be visible if populated with a non-null string and not visible if the string is null or the end of the concatated <sp> results were reached.

So, say the results of the function returned a list containing "cat, dog, snake". I would like to use that result to populate three different controls with "cat", "dog", and "snake" respectively.

Not even sure if this is possible as again, I don't know or do code but figured someone may feel up for a challenge to see if this could be done.

The reasoning behind this is because of the inability to nest a subform within a form set to Continuous Forms. Yes, I know there are other ways to work around this as I have researched using linked datasheet views or the way outside of my zone Tree concept, but the datasheet option doesn't give me near the appearance I am going for and I need something I can easily work with and replicate for other fields in the forms.

To understand what I am doing, I am building an application to manage my honeybee apiary(s) and the hives located in each apiary. I want a main management screen that lists each apiary in a continuous form format and beside each apiary listed, a list of the hives with a few details about each one for that particular apiary. I will have command buttons to expand the apiary details and the individual hive details but it's the main top level form is that I'm trying to get set up here.

Thanks for any suggestions or attempts to provide a solution.

[Gicu]

You could try to use the Split() function in the Current event to get the individual items, but at the same time why not use a recordset based on a query that gives you the original entries (pre-concatenation)?

Cheers,

[Welshgasman]

Whenever you are doing something like combining values, only to then split them again, there has to be a better way, like the recordset method that @Gicu mentioned.

[ScubaBart]

Thanks for the input. I looked and could not find where the Split() function was located. Even then, not sure I understand how I would use it to pull out a given field in each row, respectively.

Been over 20 years since I worked with Access and the majority of my stuff was Macro driven with no actual coding to speak of.

Right now I have this (not fully formatted obviously) but I'm having to list the Apiary info to get the basic info for each Hive. The intent is to have an overview screen that lists each apiary in some sorted manner and to the right of that, each hive in each respective Apiary. I have one table for the Apiarys and one for the Hives with a one (apiary) to many (hives) relationship.





If I can use the Split() function to achieve this, that would be great but I'm gonna need someone to give me an example on how to do it.

Thanks.....

[Welshgasman]

I have Google on this laptop.

From
https://www.google.com/search?q=spli...hrome&ie=UTF-8

I get as first link
https://learn.microsoft.com/en-us/of...split-function

I would look at using subform for Hives, as you are just repeating the same info for Apiary over and over again.

[ScubaBart]

I have Google on this laptop.

From
https://www.google.com/search?q=spli...hrome&ie=UTF-8

I get as first link
https://learn.microsoft.com/en-us/of...split-function

I would look at using subform for Hives, as you are just repeating the same info for Apiary over and over again.

Yea, I found the result for microsoft but it really didn't help me understand how to utilize it. Plus, I still can't find it to experiment.

Ideally I would use a subform but then I can't have continuous forms for the multiple apiaries. If you look at the screenshot above, the third apiary is not the same as in the first two rows.

[Welshgasman]

You just type it in, and intellisense will do the rest. What do you do for the likes of Dcount() ?

Personally I would have a combo for your apiaries (unbound) and subform your for your hives.
Then I would select which one I want to work on, in turn.

I would also have decent captions for the fields in the table. Then when you create a form, they are automatically set. The labels do not have to be the same as the fieldnames.
I certainly would not be showing table names in them. All such should be hidden from the user, even if it is just you. You of all people know how they are all linked anyway?

[madpiet]

I'm completely baffled by most of your post. I find that once I normalize properly, the number of columns I need in a table tends to be very stable. Maybe post your table structures and then the questions you're trying to answer with your data. I don't think I've ever seen a time when I needed Split with a properly designed database.

[Welshgasman]

I'm completely baffled by most of your post. I find that once I normalize properly, the number of columns I need in a table tends to be very stable. Maybe post your table structures and then the questions you're trying to answer with your data. I don't think I've ever seen a time when I needed Split with a properly designed database.

@madpiet,
I think it just comes from being able to successfully use AB's function initially, as the output was in one control https://www.accessforums.net/showthread.php?t=89985

[ScubaBart]

There seems to be perhaps a little confusion as to what I'm looking to accomplish. I have compressed and uploaded my file here. Once unzipped, open form F_Main_Overview for the primary screen. Most functions at the higher levels work but as it goes deeper, some things may not work or be broken because of changes that I have not cleaned up.

This main screen is clearly not formatted well and that will be addressed but it is the content that I am trying to work on presently.

When you open this screen, you will see continuous forms that on the left, give basic Apiary details. On the right, you will see basic Hive details. Currently, there is a new row (form page) for each Hive and where there are multiple Hives in a given Apiary, the Apiary information is repeated.

What I am wanting to see is to have the Apiary details listed once and to the right, the information for the Hives to be listed in some order yet to be determined. I would like to allow for up to perhaps 30 or more Hives for each Apiary so it needs to be a solution that handles more than just a couple Hives.

I know I could probably use a datasheet format to accomplish this but that is not what I want to do for aesthetic reasons. I really want to use formatted Text Boxes.

Because I want the Apiary details to show up in a continuous form layout, I can't use a subform.

So, feel free to play and if anyone can come up with a solution, that would be awesome.

Thanks.....

[Welshgasman]

I was going to try an quickly create a form that shows an unbound combo for selecting the aviary and the continous subform for the hives, but your relations are all over the place.
You need to link via the autonumber, that is what it is there for, and that is what I have always used. That is called a surrogate key.

Here is a pic of a form of mine with much the same logic for my diabetes tracking.

In my case the date is your aviary, and the food is your hives.

No duplication of main form data. By changing the unbound combo, I see all related food for that date.

[ScubaBart]

I was going to try an quickly create a form that shows an unbound combo for selecting the aviary and the continous subform for the hives, but your relations are all over the place.
You need to link via the autonumber, that is what it is there for, and that is what I have always used. That is called a surrogate key.
.

If I use the autonumber from each table for the relationships, that would eliminate the many to one relationship(s).

So, I added these two relationships for experimenting, but they can be removed and do not affect any of the other functions,



That should simplify the relationships for this particular issue. The only two tables used for this top level overview form are T_Log_Apiary and T_Log_Hive. The appropriate relationship here is a One (Apiary) to Many (Hive) type.

I see what you did but that is not the format I am wanting to achieve. I relaize I may have to in the end but before I accept defeat, I'm hoping someone may have some coding or a vba routine that may get me to where I want to go. That is why I posted in this section of the forums.

[Welshgasman]

No.
A 1 to many is where the pk of the one, is the fk in the many.

Otherwise I would use @Gicu's suggestion and a simple recordset and counter.
Your controls would be called Hivexx where xx is a number 1 to however many you think you will have.
Then as you read the recordset, you set each control according to the record count.

Only problem with this, is when you have 5 and decide to have the 6th, you have to amend your form for another control. Or make 10 to start with and have 5 empty all the time.

Personally I have always been interested in the data being available, easy to see and produce.

[madpiet]

Don't you do inspections of a given hive, which belongs to an apiary? If that's true then your structure should be

Apiary--(1,M)--Hive--(1,M)--Inspection.

[davegri]

I would try and work with this...