Update all records in a continuous form

[deepcec9]

Hi,

Created a continuous form which lists records based on value from another form. Need to add a save button to the footer of continuous form which updates all the records in one go.
Currently it updates only one record.

TIA

 

[Uncle Gizmo]

How do you update the "one record"?

 

[CJ_London]

duplicate post
http://www.accessforums.net/forms/updating-continuous-form-56249.html

 

[JHB]

duplicate post

It is really coming an inflation of Cross posting in the last month or so!

 

[CJ_London]

my guess is start of new academic year - or students have now got jobs and need to apply theory to the real world

 

[deepcec9]

Sorry for the cross post...
Can you please help me in creating a save button to update all the records in a continuous form.

 

[sneuberg]

I don't understand. Tables are updated with the forms they are bound to pretty much automatically. What are you trying to update that's not covered by this automatic action?

 

[deepcec9]

Will make it clear. In a warehouse inventory when I select the job number system will list all the records with same job number in a continuous form, there are multiple products going out from warehouse with same job number. When I click on "SAVE" button i want to save these records and run an update query which will reduce the product quantity from the inventory.

 

[sneuberg]

Still don't understand. Save these records to where? Reduce the product quantity by what? Perhaps if you could say more about the continuous form I'd get it. What does the record source of this form look like? What fields from what table.

 

[deepcec9]

record source: "out" (tablename)
fields are from table: "out"
reduce the product quantity from inventory table "inventory"
Eg: there are 20 phone and 30 computer in the warehouse.
Today 10 phones and 5 computers are exporting
So once I save the form click a command button it should run an update query and reduce 10 phones and 5 computers from the inventory table.

 

[sneuberg]

I suggest creating an update query in the query builder first. You can then run this query with a button using the Docmd.Openquery statement once you have it working.

I can't tell you how to create the update query you need as I don't know the fields in the the tables, but hopeful you can figure it out. Provided there is some field that connects the "out" table with the "inventory" table like a product id this query should be relatively easy to create.

If you can upload a copy of your database or a stripped down version with at least these tables I'll put something together for you. My bedtime starts in a about an hour, so I may not get anything back to you until tomorrow.

 

[deepcec9]

Thank you for the rep
Created an update query which updates the "remquantity" field in inventory table based on the primary key. And it is working fine.
I created a command button on footer of the form which on click runs the update query.
As it is a continuous form and displays more than one record, when I click on the command button the update query updates only the fist records and ignores the remaining ones.
Eg:
productA 20 quantity
productB 10 quantity

it reduce only 20 quantity of productA from the inventory.

 

[sneuberg]

Please post the SQL of your update query. Maybe I can see what's wrong.

 

[deepcec9]

UPDATE [in] SET [in].remquantity_in = [in].[remquantity_in]-[Forms]![test1]![quantity_out]
WHERE ((([Forms]![test1]![inid_out])=[in].[ID]) AND (([Forms]![test1]![productid_out])=[in].[productid_in]) AND (([Forms]![test1]![ponumber_out])=[in].[ponumber_in]) AND (([Forms]![test1]![invoicenumber_out])=[in].[invoicenumber_in]));


where in is the inventory table

 

[sneuberg]

That would only work for the form values of whatever record happened to be current. Instead of using the values from the form use the underlying values in the record set. For example lets consider [Forms]![test1]![quantity_out]. What's the control source of the quantity_out text box? If for example it happens to be quantity_out from the "out" table then change [Forms]![test1]![quantity_out to [out].quantity_out. The same goes for all of the places in this query where you reference the form. The only exception to this would be some form control that's not bound to a table.

 

[sneuberg]

I'm curious. When you get a moment please tell me why you are updating the inventory table with an "out" table. This reminds me of batch systems from back in the sixties. Is the source of the "out" table something that has to be imported like a spreadsheet?

 

[sneuberg]

Something you may want to think about is what you want to do if the out quantity is greater than what's left in the inventory. Right now your update query would leave a negative quantity in the inventory. That's ok if that's what you want.

 

[CJ_London]

Re your table, IN is a reserved word - even with using the square brackets it may still result in unexpected results

 

[smig]

Run an update query and requery the form

 

[deepcec9]

Hi, please correct me if im wrong
doing an inventory management, created two tables in and out, which captures inward movement (cargo coming in) and outward movement (cargo going out).
When cargo goes out it reduces the product count from the in table