Use a Button to Update A Table Field

[JakeMann41]

Hi Guys



I have a form where I would like to press a button to update a field in a table

On the form when an item is typed into the first text box, the second text box performs a dLookup and returns a location as per the table Stock and Location

In the third text box I want to enter a new location.

Then lastly a button that will update that location in the Stock and Locations table.

The field to update in that table is called Location

I am an amateur when it comes to access and queries but I presume a query needs to be writting to link the information in the third text box and the Stock and Location table.

Any ideas who I can do this?

A few pics attached

[Welshgasman]

I always start here
That will likely lead to here

From there is should be quite simple.
I would probably change the button caption to Update Location as well.

[xps35]

You are making it much to complicated. Just make a form bound to the Stock and Location table (better no spaces in names) and change the location there. The new location is changed directly in the table that way.

[NoellaG]

Reality can be a bit more complicated. With stock movements not all items in location A are moved to location B, some may stay in location A or, if location B is smaller than location A, some may be moved to location B and some to location C.
In existing warehouse software, you have a form to move stock items into locations where you have the possibility to distribute the items over several locations, the moved stock qty's are subtracted from the original location and added to the new locations, with a check on total quantities and a check that the max capacity of a location is not surpassed. We do it in a transaction within a procedure, if one of the checks fails, a rollback is done and the user is warned.

[JakeMann41]

Thanks Peter, i can see that doing it that way adds a new line to the table - what I want to do is actually update the line that is already in the table. I would update the table itself by just going into it but I would like a neat process on a form to do that for me.

[xps35]

Using a bound form you should be able to update an existing record directly by entering a new value into a field without adding a new record. I don't know what you did exactly.

[June7]

Whether you apply filter/search to bound form or run UPDATE action SQL, need criteria to locate existing record.

For SQL in VBA, like:

CurrentDb.Execute "UPDATE tablename SET fieldname=" & Me.textboxName & " WHERE ID=" & Me.anotherControlName

Modify as appropriate if fields are text or date/time type and for however many parameters are needed.

[NoellaG]

and don't forget to make the necessary checks, could be done in the beforeupdate event.
Question: is it possible the items are split and reallocated on more than 1 location?

[JakeMann41]

i think i've got it- if i change the filter lookup to never in the properties box that just changes the field on the table and doesn't input another line.

[ano]

i am not sure if you want change location name, which is what i not expect bec that is default in forms.
i think u like to add/insert a new location ( not sure if it need to exist in table or not , but typing suggest its new in the table as well , bec else u select from available locations)