SteveL13

asked on 4/28/2018

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?

Microsoft Access

Last Comment

SteveL13

8/22/2022 - Mon

Gustav Brock

4/28/2018

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).

SteveL13

4/28/2018

ASKER

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?

Gustav Brock

4/28/2018

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.

Your help has saved me hundreds of hours of internet surfing.

fblack61

SteveL13

4/28/2018

ASKER

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

Gustav Brock

4/28/2018

It's the filter:

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

Open in new window

PatHartman

4/28/2018

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.

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

SteveL13

4/29/2018

ASKER

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.

ASKER CERTIFIED SOLUTION

Gustav Brock

4/29/2018

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

SOLUTION