Solved

How populate form field with values from sub-form

Posted on 2014-12-10
6
238 Views
Last Modified: 2014-12-10
I have a form and a sub-form.  On the main form is a field named "txtClasses".

On the sub-form (datasheet view) is a field named "Class".  

When the main form is opened I want the field "txtClasses" to be populated with the UNIQUE values from the subform field "Class" separated by commas and a space.

So using this example of the sub-form...

Classes Example
I would want the field on the main form to read "Football, Baseball, Basketball, Hockey"

How can this be done?  

--Steve
0
Comment
Question by:SteveL13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 22

Expert Comment

by:rspahitz
ID: 40491770
How about building a query on a hidden combobox with the data source "Select Distinct Class From {table}" then run a quick command to iterate through the entries and add commas after them to put into the text box?
0
 
LVL 38

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
ID: 40491775
Create a query that selects DISTINCT just the Classes field for the ID of the current record.
Select DISTINCT Class
From YourTable
Order by Class
Where PrimaryKey = Forms!yourform!PK;

Then in the current event of the form, open a recordset and loop through it concatenating the values from the Class field.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 250 total points
ID: 40491870
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:SteveL13
ID: 40492210
Pat,

I have the query created and it is giving me the distinct values.  But I don't know how to:

"Then in the current event of the form, open a recordset and loop through it concatenating the values from the Class field. "

What would the code be to do that?
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 40492242
Jeff posted a link to a function that shows you how to do the concatenation.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40492801
Yes, Pat
I think Patrick's function is one of the most popular Articles ever published here...

But like you, I sometimes still loop in case I have to check for something at each record...
...and sometimes just because it is fun...
;-)

Jeff
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

631 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question