A step further with Dlookup function

Dear Experts,

I have a filtered query, called query1 bound to Table A that returned dynamic rows of text data in a field(field1).

I have a form, called form A bound to Table B where a vba code will perform Dlookup to copy the data from field1 to a text box in form A when the form is activated.

Problem is, Dlookup is only return the first row of data from field1.

Is there a way in vba to lookup all the rows from the filtered query then combined all the data of each row and seperated by a comma into the text box in form A?

Thanks in advance.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I wouldn't use DLookup() to do this but then I wouldn't do it at all.

DLookup() requires a unique identifier and it returns 1 value.  If you knew all the unique identifiers, you would create a code loop to read them and do a DLookup() for each.  There are much better solutions if it turns out you really need to do this.

Are you simply trying to show a list of associated data on a form?  If so, a simple no-code solution would be to use a subform.  You have lots of formatting options to make the subform unobtrusive.

If you are trying to copy the data and save it in the current record, that would be poor practice.  Storing data that exists elsewhere duplicates it and creates a situation where the values may be different.  You can't run a business if you can't rely on your data so we "never" store data in multiple places without a seriously good reason.  And even then, we get other opinions on whether there is an alternate method.
kaysooAuthor Commented:
Tq for reply, actually I have no other way out in order to get the final data this way.

the project I am doing is a system for fish farm where the fry will be transfer to different incubators from the same batch.

I have to maintain the data in single row as it contained count of fry balance after a sale transaction or deceased, at the same time the user require the report to to show all the location of the remaining fry from various incubator, that is why I have to list out all the related incubators combined in a single text box seperated by comma,  plus the latest inventory count in a single row to show all the data of Original In Count, Deceased Count, Sold Count and Balance Count plus profit earned.

If I have to lookup all the incubator's name from a query like my earlier question, can you please shed some light on the coding?

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Jeffrey CoachmanMIS LiasonCommented:
Unfortunately there is no drop in solution here.

The code is explained fully in the documentation in the link...

What exactly are you having trouble with?
Jeffrey CoachmanMIS LiasonCommented:
...and lets not get ahead of ourselves here...
Please post a sample of your data and also post a clear graphical example of the *exact* output you are looking for...
kaysooAuthor Commented:
Tq for your patient, attached is the graphical example of my intention.

Hope there is a solution else i have no choice but to go through a messy process in order to gather the data.

Thanks you.
Jeffrey CoachmanMIS LiasonCommented:
Here is a sample based on your basic design...

You will have to study it carefully and thoroughly,..then adapt it for your database

(You will need to learn about VBA/Functions, and Group By queries)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The picture is confusing.  Are there only 35 fry or are there 105?  How can you take three tanks with 35 fry each and end up with only 35?  Shouldn't the first three rows show what is in each tank and the summary row summarize the counts?
Jeffrey CoachmanMIS LiasonCommented:
I think there are only 35 FRY

I think this is one of those cases where certain systems duplicate all the fields in one record (in this case only the Incubator Location) is different.
The dconcat() function seems provide the desired output.
You can see this in the sample I posted..


Jeffrey CoachmanMIS LiasonCommented:

Based on what I posted I see no need to "populate" a separate table, ...you can use the query instead.

So first verify that the output is what you wanted...
kaysooAuthor Commented:
Thanks everyone, sorry for late reply.

Boag2000, you were right, there are only 35 fry from the table and 35 records in table, each fry will be assigned with Biosecurity Chip Number in the future as design structure.

Patharman, as mentioned each fry can be relocated dynamically in any available Tanks eventhough there are from the same batch (sometime arowana fry fighting each other therefore they need to be seperated in real world)

When I did a group sum in query to calculate the In-Out-Balance for the particular Batch, if just so happen if a batch of fry is scattered to different tanks, hence the three rows of data shown due to different tank location.

I really appreciate for all the feedback and sample db provided.

Thanks again.
kaysooAuthor Commented:
Dear boag2000,

I hv tried out your sample db, during compilation of the module1, i encounter some compilation issue.  I have attaced the graphic show as error sequence in number, can you please tell me what should I do?

I am using Access 2003

Jeffrey CoachmanMIS LiasonCommented:
What version of Access are you using?
I used Access 2013 to build the sample.

Try unchecking the MISSING reference, and see if you can check ON the:
     Microsoft Access DAO 3.x Library
kaysooAuthor Commented:
Wow !!!

What a powerful solution!! Fantastic!!

It works exactly I intended. Tqvm for this solution.
kaysooAuthor Commented:
Saving me from going a long trip to get the results I want, thank you.
Jeffrey CoachmanMIS LiasonCommented:
...and just for my own education...
A "Fry" is a baby fish, ...correct?
kaysooAuthor Commented:
Yes, Fry is a baby fish roughly about 2 months old after hatching, their stages are from:
Wigglers, Fry, Juvinile, Adult then Maturity (inorder to spawn), Arowana Fry takes about 3 years before they reach Maturity stage, my client runs an Arowana Captive Breeding business that need a system to manage their operation.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.