Populate text box with selections in a datasheet subform

I want to populate a text box on a main form with entries and selections from a datasheet subform.  The datasheet subform looks like this:

Quantity                            Object
1 (entered value)             Football (selected object from combobox)
4 (entered value)             Baseball (selected object from combobox)
etc.                                     etc.

I have this code which works for one row entry/selection in the datasheet:

Private Sub Form_AfterUpdate()

    Me.Parent.txtOjectDefinition = Me.txtQuantity & " " & Me.cboObject.Column(1)

End Sub

Open in new window


But if I were to select the 2nd row in the datasheet followed by the first row, I want the text box on the main form to look like this:

4 Baseball + 1 Football

(note the + mark between the selections)

How can this be done?
SteveL13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
You could start with:

Private Sub Form_AfterUpdate()

    Me.Parent.txtOjectDefinition = (Me.Parent.txtOjectDefinition + " + ") & Me.txtQuantity & " " & Me.cboObject.Column(1)

End Sub

Open in new window

But you will get into trouble if you try to modify the selection(s).
0
SteveL13Author Commented:
Works!  But you are right, if I modify anything it gets messed up.

Maybe a better idea would be to have a command button on the main form that would loop through the datasheet entries and populate the textbox on the main form?  What would that code look like?
0
Gustav BrockCIOCommented:
That could be:

Private Sub FormAfterUpdate()

    Dim rs As DAO.Recordset
    Dim Definitions As String

    Set rs = Me.RecordsetClone
    While Not rs.EOF
        If Definitions <> "" Then
            Definitions = Definitions & " + "
        End If
        Definitions = Definitions & rs!txtQuantity.Value & " " & DLookup("ObjectName", "Objects", "ObjectID = " rs!Object.Value & "")
    Wend
    If Definitions <> ""
        Me.Parent.txtOjectDefinition.Value = Definitions
    Else
        Me.Parent.txtOjectDefinition.Value = Null
    End If
    rs.Close

End Sub

Open in new window

where Objects is a table holding your descriptions.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

SteveL13Author Commented:
Here is what I have.  Not working.  Get an error "Item not found in this collection".  Yellow highlight on:

Me.txtProofDefinition = Me.txtProofDefinition & rs!txtQuantity.Value & " " & DLookup("ProofType", "tblEstimateProofs", "EstimateID = rs!EstimateID.Value")
 
Using:

Private Sub Command491_Click()

    Dim rs As DAO.Recordset
    Dim ProofDefinition As String

    Set rs = Me.RecordsetClone
    While Not rs.EOF
        If Me.txtProofDefinition <> "" Then
            Me.txtProofDefinition = Me.txtProofDefinition & " + "
        End If
        Me.txtProofDefinition = Me.txtProofDefinition & rs!txtQuantity.Value & " " & DLookup("ProofType", "tblEstimateProofs", "EstimateID = rs!EstimateID.Value")
    Wend
    If Me.txtProofDefinition <> "" Then
        Me.Parent.Me.txtProofDefinition.Value = Me.txtProofDefinition
    Else
        Me.Parent.Me.txtProofDefinition.Value = Null
    End If
    rs.Close

End Sub

Open in new window

0
Gustav BrockCIOCommented:
It's the filter:


        Me.txtProofDefinition = Me.txtProofDefinition & rs!txtQuantity.Value & " " & DLookup("ProofType", "tblEstimateProofs", "EstimateID = " & rs!EstimateID.Value & "")

Open in new window

0
PatHartmanCommented:
Whenever I see a question like this, my first reaction is why?  What is it you are trying to accomplish?  You should not be saving the value in the parent table.  What happens if someone later goes back and changes or deletes an item?  You would be hard pressed to update the text box correctly.  If this is a reporting requirement, there are ways to accomplish this by using a function at the time you run the report.  If we know where you are going with this it will be easier to come up with a good solution.
0
SteveL13Author Commented:
Gustav, still getting error... "Item not found in this collection".  When I hover over rs!txtQuantity.Value

Pat, I need to display the result in the textbox on the form but also display on a resulting report and an export to Excel.
0
Gustav BrockCIOCommented:
Ah, I guess that's the name of the textbox, it must be the field name, probably: rs!Quantity.Value
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
It is important that you do not store this concatenated data.  There is no reasonable way to update it so as soon as something changes, the concatenated value will be incorrect.  Here is a function that can be used to concatenate the list when you need it so it will always be fresh.

I haven't actually used this function but it looks like it does what you need.
ConcatFunctionExample.docx
DConcat.mdb
0
SteveL13Author Commented:
Gustav.  Thank you.
Pat.  Great information.  Very useful.  I'll keep for future.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.