Solved

How populate form field with values from sub-form

Posted on 2014-12-10
6
232 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 37

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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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 37

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-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access creating new db - relationships 8 56
access vba 5 57
Access Need to add combo box to sub form 10 51
VBA Ref Table, use ID#, then ref column 2 3 23
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

751 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