Solved

How populate form field with values from sub-form

Posted on 2014-12-10
6
224 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
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 36

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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 36

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Search a table based on a "like" search 6 44
What if i make webbased alternative for MS Access 5 71
Run Stored Procedure uisng ADO 5 21
Search feature 14 26
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

829 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