Update many fields in a subform

[MrDeej]

Hello guys!

I have a masterform "kg" and a subform "kg varelinjer" (order and order details in english)

In the subform i have a field named "lagerbeholdning" (storage quantity) and my problem is that i need to make a button for users which updates this field with data from another tabel on demand.

I have tried to make some code myself

Do While Not [Form_kg varelinjer].NewRecord
[Form_kg varelinjer].Lagbeholdning.SetFocus
[Form_kg varelinjer].Lagbeholdning = DLookup("lagerbeholdning", "tbl lpst00pf tdo", "varenr=" & [Form_kg varelinjer].Varenr)
DoCmd.GoToRecord , , acNext
Loop

But this code does not work.

I need this code

[Form_kg varelinjer].Lagbeholdning = DLookup("lagerbeholdning", "tbl lpst00pf tdo", "varenr=" & [Form_kg varelinjer].Varenr)

to update several records in my subform by a button on the mainform.
The reason for this is because it is very important to just update the field "lagerbeholdning" at the right time because of some fluctuating storage quantities..

Please help my find a solution

[ADezii]

Hello guys!

I have a masterform "kg" and a subform "kg varelinjer" (order and order details in english)

In the subform i have a field named "lagerbeholdning" (storage quantity) and my problem is that i need to make a button for users which updates this field with data from another tabel on demand.

I have tried to make some code myself

Do While Not [Form_kg varelinjer].NewRecord
[Form_kg varelinjer].Lagbeholdning.SetFocus
[Form_kg varelinjer].Lagbeholdning = DLookup("lagerbeholdning", "tbl lpst00pf tdo", "varenr=" & [Form_kg varelinjer].Varenr)
DoCmd.GoToRecord , , acNext
Loop

But this code does not work.

I need this code

[Form_kg varelinjer].Lagbeholdning = DLookup("lagerbeholdning", "tbl lpst00pf tdo", "varenr=" & [Form_kg varelinjer].Varenr)

to update several records in my subform by a button on the mainform.
The reason for this is because it is very important to just update the field "lagerbeholdning" at the right time because of some fluctuating storage quantities..

Please help my find a solution

To Update the [Storage Quantity] Field in your Sub-Form from a Command Button on the Main Form using DLookup() to retrieve data from another Table, not guaranteeing the foreign language part:

Me![Order]![<your sub-form control name>.Form![Storage Quantity] = DLookup("lagerbeholdning", "tbl lpst00pf tdo", "varenr=" & [Form_kg varelinjer].Varenr)

[Rabbit]

To refer to a control on a subform you use Me.SubformControl.Form.ControlName

[Lysander]

I need this code

[Form_kg varelinjer].Lagbeholdning = DLookup("lagerbeholdning", "tbl lpst00pf tdo", "varenr=" & [Form_kg varelinjer].Varenr)

to update several records in my subform by a button on the mainform.
The reason for this is because it is very important to just update the field "lagerbeholdning" at the right time because of some fluctuating storage quantities..

Please help my find a solution

Rather than trying to update the (many) fields on the subform, try updating the subforms recordset and then refreshing the subform.

i.e. When the user clicks on the button, in the on_click event run something like docmd.RunSQL "UPDATE mytablename SET Lagbeholdning = some_value_here WHERE conditions_for_the_subform_recordset;"

Then subformname.refresh

This will update all the selected rows from your subform

[MrDeej]

Rather than trying to update the (many) fields on the subform, try updating the subforms recordset and then refreshing the subform.

i.e. When the user clicks on the button, in the on_click event run something like docmd.RunSQL "UPDATE mytablename SET Lagbeholdning = some_value_here WHERE conditions_for_the_subform_recordset;"

Then subformname.refresh

This will update all the selected rows from your subform

This solution should be the most proffesionale i think

DoCmd.RunSQL "UPDATE varelinjer SET Lagbeholdning = dlookup("lagerbeholdning", "tbl lpst00pf tdo", "varenr=" & me.varenr WHERE "ordrenummer =" & me.ordrenummer;"

But how do i solve the "Lagbeholdning = (a dynamic value that it should look up in a another table by the product number)


Thank you for all your answers!