asked on

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?

Gustav Brock

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

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

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.

SteveL13

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

It's the filter:

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

Open in new window

PatHartman

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.

SteveL13

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SteveL13

ASKER

Gustav. Thank you.
Pat. Great information. Very useful. I'll keep for future.