By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,191 Members | 1,067 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,191 IT Pros & Developers. It's quick & easy.

Update many fields in a subform

100+
P: 157
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
Share this Question
Share on Google+
4 Replies


ADezii
Expert 5K+
P: 8,638
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
Expert Mod 10K+
P: 12,366
To refer to a control on a subform you use Me.SubformControl.Form.ControlName
May 9 '07 #3

Expert 100+
P: 344

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

100+
P: 157
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.