update table from a form using a button

[Business]

Hi;


I m struggling for days now because i can not code properly the UPDATE button to update the stock quantity of an item selected. The result that i get update all the quantity in the stock level. Those are the codes i m using


Dim strSQL As String
Dim rst As DAO.Recordset
strSQL = "UPDATE [Stock] Set [produitQuantite] = " & me.txtRest "
CurrentProject.Connection.Execute strSQL

In this case productQuantity represent the stock level and txtrest the substraction between the productQuantity and the quantity issued. the problem is that the result update all the items quantity in the table.

can anyone help pleqse

[ssanfu]

Try

Dim strSQL As String
Dim rst As DAO.Recordset
strSQL = "UPDATE Stock Set [produitQuantite] = " & me.txtRest 
CurrentdB.Execute strSQL, dbfailonerror

You were mixing DAO and ADO syntax.


Is the field name really [produitQuantite]?? (maybe [productQuantity]?)

[Business]

Thanks Steve yes the field name is produitQuantite. the problem that i m facing is that it s update all the quantity in the stock table. or it has to update just the one selected.

[nhorton79]

If you only want it to update a particular record you will need to include a WHERE clause...


Sent from my iPhone using Tapatalk

[Business]

how do i go about it; i m still a novice in this. Could you help with this same close. where should be included in which way
Regards

[nhorton79]

You could try:

Dim strSQL As String
Dim rst As DAO.Recordset
strSQL = "UPDATE Stock SET [produitQuantite] = " & me.txtRest & " WHERE [StockID] = " & me.txtStockID
CurrentdB.Execute strSQL, dbfailonerror

This will depend on what the primary key for the stock is, and also on how this is tracked on your (form?).

So you would replace the StockID with your primary key and me.txtStockID with the textbox name you are using on the form.

Make a copy of your database before trying....

Hope this helps. Let me know how it goes...

Nick

[Business]

Thanks Nick but i still get an error message. on my form i do not track the productId but the product name that display in a combo box with the item name and quantity in stock. can i have an email adresse to send out the application for you to have a better look at the problem

[nhorton79]

Yeah, might be best if look directly at the database. I'll message you my email address. Send it through - although it may be a while as it's bedtime here where I live but could look at tomorrow for you.


Sent from my iPhone using Tapatalk

[ssanfu]

What Nick said.... Need a "Where" clause.

You could post the dB here so a lot more people could look at it..........I'm just saying.....

[nhorton79]

Okay...I've had a look at your database and have got the UPDATE function working and updating only the product line in stock referenced in the form.
This is done using a WHERE clause in your UPDATE statement.
I've sent this back to you via email - Hope this helps.

However, I believe you are going about this in the wrong way....
You shouldn't really be storing the stock on hand amount in your database directly on the product table.

Have a look at this page by Allen Brown which shows in great detail exactly how to manage stock and the necessary stocktakes.
http://allenbrowne.com/appinventory.html

Allen Brown has some great articles on his site about designing databases plus other functions and tips for novices.

Nick

[Business]

Thanks guys i have fixed the problem. Nick i ll be in touch if more troubles comes alongs.

Regards