Solved How to reference a Subform from the parent form (1 Viewer)

[nector]

Hi

I'm trying to reference the subform from the parent form so that a figure from the hidden form can be capture on the subform , but I keep on getting an error that control cannot be found : Forms![frmGrn].Form![SfrmPurchasesOrdersDetailslines Subform]![GRNID

Private Sub CboAttachGRN_AfterUpdate()
Forms![frmGrn].Form![SfrmPurchasesOrdersDetailslines Subform]![GRNID] = [Forms]![frmGrn]![GRNID]
End Sub

Where do I go wrong?

 

[arnelgp]

Private Sub CboAttachGRN_AfterUpdate()
Forms![frmGrn]![SfrmPurchasesOrdersDetailslines Subform].Form![GRNID] = [Forms]![frmGrn]![GRNID]
End Sub

 

[ebs17]

Me.[SfrmPurchasesOrdersDetailslines Subform].Form.GRNID = ...

First the name of the control, then its form property.
If you dispense with the unspeakable brackets, you gain clarity.

 

[nector]

Ok many thanks this works perfect, but its updating only one line, or first line, how do I update all active lines?

See where 41 is, I want the other remaining two line to updated as well.

 

[Josef P.]

The value of the control in the subform always refers to the current record only.
=>
a) Update statement or pass through all data records
b) Reconsider data model, if the value is always valid for all data records of a PO.

 

[nector]

The data is valid for all the model, that is why we select the correct number from the combobox, the problem is how to make it update all

 

[Josef P.]

Szenario a)
(schematic code only)

private sub YourCombobox_AfterUpdate()
      SetGrnIdToSubFormRecords me.YourCombobox.Value
      YourSubForm.Requery
end sub

private Sub SetGrnIdToSubFormRecords(byval NewGrnId as long)
    dim UpdateSql as String
    UpdateSql = "update Po_item_table ... where PoId = ... "
    currentdb.Execute UpdateSql, dbfailonerror
end sub

Szenario b)
Only the one record in the PO header table needs to be changed.

 

[arnelgp]

Private Sub CboAttachGRN_AfterUpdate()
With Me.[SfrmPurchasesOrdersDetailslines Subform].Form.RecordsetClone
    If Not (.BOF And .EOF) Then
        .MoveFirst
    End If
    Do Until .EOF
        .Edit
        !GRNID = [Forms]![frmGrn]![GRNID]
        .Update
        .MoveNext
    Loop
End With
End Sub

 

[nector]

arnelgp​

This is excellent programing you have solved my problem once and for all

many to thanks to

Regards

Chris

 

[Josef P.]

First of all: I don't want to question arnelgp's solution. This is a very easy to follow implementation of your requirement.

If I remember correctly, you are using MS SQL Server.
Compare the data transfer between the updates per record and an update statement. (Writing is more expensive than reading).
This will not be relevant for a few records. But you could come up with the idea to always work this way and maybe let several users work with the database at the same time.

BTW: I would give preference to normalization.