470,614 Members | 1,549 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,614 developers. It's quick & easy.

Update many fields in a subform

157 100+
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
Expand|Select|Wrap|Line Numbers
  1. Do While Not [Form_kg varelinjer].NewRecord
  2. [Form_kg varelinjer].Lagbeholdning.SetFocus
  3. [Form_kg varelinjer].Lagbeholdning = DLookup("lagerbeholdning", "tbl lpst00pf tdo", "varenr=" & [Form_kg varelinjer].Varenr)
  4. DoCmd.GoToRecord , , acNext
  5. Loop
But this code does not work.

I need this code
Expand|Select|Wrap|Line Numbers
  1. [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
May 9 '07 #1
4 2246
ADezii
8,804 Expert 8TB
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
Expand|Select|Wrap|Line Numbers
  1. Do While Not [Form_kg varelinjer].NewRecord
  2. [Form_kg varelinjer].Lagbeholdning.SetFocus
  3. [Form_kg varelinjer].Lagbeholdning = DLookup("lagerbeholdning", "tbl lpst00pf tdo", "varenr=" & [Form_kg varelinjer].Varenr)
  4. DoCmd.GoToRecord , , acNext
  5. Loop
But this code does not work.

I need this code
Expand|Select|Wrap|Line Numbers
  1. [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:
Expand|Select|Wrap|Line Numbers
  1. Me![Order]![<your sub-form control name>.Form![Storage Quantity] = DLookup("lagerbeholdning", "tbl lpst00pf tdo", "varenr=" & [Form_kg varelinjer].Varenr)
May 9 '07 #2
Rabbit
12,516 Expert Mod 8TB
To refer to a control on a subform you use Me.SubformControl.Form.ControlName
May 9 '07 #3
Lysander
344 Expert 100+

I need this code
Expand|Select|Wrap|Line Numbers
  1. [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
May 9 '07 #4
MrDeej
157 100+
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


Expand|Select|Wrap|Line Numbers
  1. 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!
May 10 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Kunal | last post: by
1 post views Thread by Jason | last post: by
reply views Thread by Access Programming only with macros, no code | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.