possible to concatenate fields from continuous subform.

[cdoyle]

Hi,
Not sure if this is possible, been searching and not seeing any threads with this same request.

Here is what I have.

On my main form, I have a continuous subform where the user selects some phrases from a dropdown. This text will eventually be used on a report.

What I would like to have, is on the mainform have an unbound text box, then as they make a selection from the dropdown in the subform and creates new records. The textbox on the mainform, concatenates those so the user can see how it would look on the report.

It would just be a preview basically.

Is this possible?

 

[Pat Hartman]

Create a public sub in a standard module to open a recordset and loop through the records to concatenate the string and place it in the display area of the main form.

Call this code in the current event of the main form (for existing records only) to fill the field as you scroll to an existing record. Call the code in the AfterUpdate event of the text field in the subform to "recalculate" the value as each subform record is added or changed. You will also need to call the sub in the AfterDeleteConfirm event of the subform, if you allow deletes.

 

[LPurvis]

Just a small note - I'd suggest you use a clone (of one type or another) of the subform's recordset rather than open a new one.

 

[cdoyle]

Create a public sub in a standard module to open a recordset and loop through the records to concatenate the string and place it in the display area of the main form.

Call this code in the current event of the main form (for existing records only) to fill the field as you scroll to an existing record. Call the code in the AfterUpdate event of the text field in the subform to "recalculate" the value as each subform record is added or changed. You will also need to call the sub in the AfterDeleteConfirm event of the subform, if you allow deletes.

Thanks for replying, I've been trying to search for examples of this but haven't found any. Do you know of any examples anywhere, where I can see the code

I'm not really sure how to do this.

 

[Pat Hartman]

Dim db As DAO.Database
Dim rsDAO As DAO.Recordset
Dim qdDAO As DAO.QueryDef
Dim sTableItems As String

    Set db = Currentdb()
    Set qdDAO = db.QueryDefs!qYourQuery
        qdDAO.Parameters![YourID] = Me.txtYourID    'set a parameter if your query needs it
    Set rsDAO = qdDAO.OpenRecordset
    
    sTableItems = ""
    If rsDAO.EOF Then
    Else
        Do While rsDAO.EOF = False

           sTableItems = sTableItems  & rsDAO!YourData & vbTab		'remove the tab if you don't need the items separated.
   
        rsDAO.MoveNext
        Loop
     End If

     If sTableItems = "" Then 		'none were found
	.....
     Else
	.....
     End If

 

[LPurvis]

And just to follow with my earlier mentioning of using the subform recordset itself...

You could assign the list value with
Me.txtList = fConcatForm(Me.subForm.Form, "FieldA")

where fConcatForm is be defined as...

Function fConcatForm(frm As Access.Form, strFieldName As String, Optional strDelim As String = ",") As String
    
    Dim rst As DAO.Recordset
    
    Set rst = frm.Recordset.Clone
    With rst
        Do Until .EOF
            fConcatForm = fConcatForm & strDelim & .Fields(strFieldName)
            .MoveNext
        Loop
        Set rst = Nothing
    End With
    
    If Len(fConcatForm) > 0 Then fConcatForm = Mid(fConcatForm, len(strDelim) + 1)
    
End Function

 

[cdoyle]

Dim db As DAO.Database
Dim rsDAO As DAO.Recordset
Dim qdDAO As DAO.QueryDef
Dim sTableItems As String

    Set db = Currentdb()
    Set qdDAO = db.QueryDefs!qYourQuery
        qdDAO.Parameters![YourID] = Me.txtYourID    'set a parameter if your query needs it
    Set rsDAO = qdDAO.OpenRecordset
    
    sTableItems = ""
    If rsDAO.EOF Then
    Else
        Do While rsDAO.EOF = False

           sTableItems = sTableItems  & rsDAO!YourData & vbTab		'remove the tab if you don't need the items separated.
   
        rsDAO.MoveNext
        Loop
     End If

     If sTableItems = "" Then 		'none were found
	.....
     Else
	.....
     End If

Ok, I put this in a module correct?
YourID= would this be the ID of the main record?
Me.txtYourID= Is this the name of the text box on the main form?
qYourQuery= what query is this?

Then on the main form, I call this on the oncurrent?

I'm trying this on a sample db right now.
I have the tables, and the forms setup. but not really sure how to make this work.

I created the module, but not sure what query to base it off of?

 

[cdoyle]

And just to follow with my earlier mentioning of using the subform recordset itself...

You could assign the list value with
Me.txtList = fConcatForm(Me.subForm.Form, "FieldA")

where fConcatForm is be defined as...

Function fConcatForm(frm As Access.Form, strFieldName As String, Optional strDelim As String = ",") As String
    
    Dim rst As DAO.Recordset
    
    Set rst = frm.Recordset.Clone
    With rst
        Do Until .EOF
            fConcatForm = fConcatForm & strDelim & .Fields(strFieldName)
            .MoveNext
        Loop
        Set rst = Nothing
    End With
    
    If Len(fConcatForm) > 0 Then fConcatForm = Mid(fConcatForm, len(strDelim) + 1)
    
End Function

Thanks for trying to help me with this.


Where would this code go?

 

[LPurvis]

I've ammended your example with the method I offered.
I just did mine (I was going to do Pat's too for completeness) but I'm pressed for time and it was very quick to implement mine.

 

[cdoyle]

Thank You for doing this.
It's almost working how I need it too.

The only thing I noticed is, since the combo box stores the record ID into the table, the preview box is showing just the ID's.

So is that what the query in Pat's is used for? To pull the actual text into the preview?

 

[LPurvis]

Yes in a way that loading the recordset you could choose what table and field you want.
However I'd extend the recordsource of the subform slightly to include the comments table so you can access the required field anyway.

However it looks to me (looking at the results) that your subform implementation is erroneous.
Your subform chooses rows from comments_TBL_drp (the comments table) and adds them to the junction table comments_holder.
But the combo is bound to the PK of that junction table - and it should be the CommentsID FK field.

See attached.

 

[cdoyle]

Ya I just noticed that myself, I made it in a hurry and didn't realize it until after you had replied.

This new version works really well, and not much coding needed either.
The only thing I have left to do, is make it so the preview window updates after a selection is made.

On the afterupdate of the combo, do I need to refresh/requery the main form?
am I on the right track?

 

[cdoyle]

OK, got it to work using this on the afterupdate of the combo
Private Sub Combo0_AfterUpdate()
Forms("Formmain").Form.Requery


thanks again for all your help.

 

[cdoyle]

ugh, I guess it doesn't quite work the way I want.
It requeries the form, but takes you back to record 1 of whatever.

How do I just refresh the current record?

 

[khawar]

Try this one

 

[cdoyle]

Try this one

Thanks, this updates the field afterupdate but I noticed that it doesn't list the comments in the order right order.

On the other example I was able to sort the query by accending by the ID of the junction table and that fixed it. I'm guessing I could do it here too?
Another issues I found was if a user accidently choses an option twice. It only displays once in the preview window.

Is there a way to make it so it shows everything?
That way, it doesn't get pulled to the report?

between both ways of doing this, which way is better or more effeicient?
The database is pretty large, so I want to make sure I chose an option that doesn't slow the db anymore then needed.

Thanks.

 

[dayna]

Fantastic sample, khawar! I’m having a similar issue, and this method has gotten me farther than any other I’ve tried. I do have some questions, but I don’t want to hijack this thread since I’ve already got one of my own.

By the way, cdoyle, you can get the sorting action that you’re after by adding an Order By clause to the end of the SQL statement. (Don’t forget to move the semicolon! D’oh!)

 

[khawar]

If you want to have all similar comments you can remove word "Distinct" from the sql statement in the module
then it will also show duplicates

 

[cdoyle]

Thanks!
I've been able to get it all working and ordered the way I need it!

Thanks to everyone who helped with this.


I have another question tho, but maybe I should start a new thread..
Here is my latest test db.

Take a look at the report, I'm basically doing the same thing. I believe I found this method on the MS site.

Anyways, it's all working but I'm running into the same problem with the order in which the comments are displayed. I need the comments to display by the comments_used_ID in the comment holder table.

I'm not seeing how to do this here.

Hopefully it's possible

 

[cdoyle]

Hi Again,

Been trying to modify this just a little, on my real database I have a 'main_form' then a subform, and the drop downs are on a subform of the subform.

So I'm trying to duplicate this in the example, and reference the forms correctly, but I can't seem to get it.

In the module where it currently says
Forms!formmain!main_ID

I need to have that bump down to a subform level.
I've been trying different things, but I'm just not getting it right.

I've tried
Me!frmmain!main_ID
Forms!startform!mainform.Form!main_ID
and a few other variations and just cant' get it to work.

Here is my latest DB example, so you can see how I have my forms