Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 267
  • Last Modified:

How populate form field with values from sub-form

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
SteveL13
Asked:
SteveL13
2 Solutions
 
rspahitzCommented:
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
 
PatHartmanCommented:
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
SteveL13Author Commented:
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
 
PatHartmanCommented:
Jeff posted a link to a function that shows you how to do the concatenation.
0
 
Jeffrey CoachmanCommented:
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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now