Updating sub form using main form

[deepucec9]

Want to update a field in subform (continous) using main form
mainform: jobout field: truckno
subform: outsubform field: truckno_out

tried the code
Me.out_Subform.Form.truckno_out = Me.truckno.Value

its works fine for the first record, as the subform is continous next line of record is not getting updated.
please help


TIA

[ItsMe]

I am not sure if you can use a Main-Form/Sub-Form scenario to manage cascading updates. I suspect you would have to create the correct master/child link properties of the Subform and also use the Relationship Wizard to define relationships and Referential integrity rules.

Having said that, if you are going to use VBA to update a field, you will likely bypass any intrinsic cascading tools. I do not use the Relationships Window in Access and I do not define Referential integrity rules in Access. I will depend on VBA to enforce most constraints and maintain referential integrity.

So, if you want to UPDATE many records, I would use an SQL statement and execute that statement. You can use VBA that would be similar to this.

Dim strSQL As String
Dim MyVariable as Long
MyVariable = Me.SomeField.Value
strSQL= "UPDATE tblName SET tblName.FieldToUpdate = 'Some New Value' WHERE ((tblName.PrimaryKeyName)=" & MyVariable & ");"

Currentdb.Execute strSQL

[deepucec9]

Thank you for the reply,
Is there any other way to update the record using macros?

[ItsMe]

Thank you for the reply,
Is there any other way to update the record using macros?

I do not use the Relationships Window in Access, but I believe you can manage Cascading Updates via the Edit Relationships dialog box when you first create a relationship.

Within the Ribbon and under the Design Tab, you will find the Relationships tool. Click it to open the Relationships Window. After you add tables to the window by dragging a table from the Navigation Pane or via the Show Table dialog, you can create relationships by dragging one field from a table to another field in another table. Double clicking empty space within the window will open the dialog box to edit existing relationships. Tick the 'Cascade Update Related Fields' to enforce referential integrity of your relationships.

As mentioned, I do not have experience with the intrinsic Relationships Tool in Access. However, with the correct properties set, you should be able cascade update related records within a Form/Subform arrangement. It sounds like you already have the appropriate Master/Child link properties set for your Subform. This can be done manually or via the Wizard when adding a subform to your Main form.

Let us know how things go.