MS Access Form problem in update vba code

[veeru8989]

Hi,
I created a form using form wizard and placed a UPDATE button.
and written code

CurrentDb.Execute "UPDATE tblQuotation SET tblQuotation.ItemCategory = ItemCategory,tblQuotation.ItemDescription=ItemDescription,tblQuotation.FOBFactoryCost = FactoryCost WHERE tblQuotation.ItemNumber ='" & ItemNumber & "'"

I am updating value in FCost(textbox) and before clicking on update button the ELC(textbox) value which is calculated Field in Database Table is getting updated.

so please provide the solution how to handle asap.

[twinnyfo]

veeru8989,

For your Query:

UPDATE tblQuotation 
SET tblQuotation.ItemCategory = ItemCategory, 
    tblQuotation.ItemDescription = ItemDescription, 
    tblQuotation.FOBFactoryCost = FactoryCost 
WHERE tblQuotation.ItemNumber = '" & ItemNumber & "'"

Where are you getting the values for ItemCategory, ItemDescription and FactoryCost (lines 2-4)? If these are from another Table or from a Form you must make it clear.

Concerning ItemNumber, it appears that this is coming from your Form? If it is a numerical value (Item Numbers could be either, depending on how they are formatted), then you would remove the single quotes.

However, you also want to be explicit with your references. If ItemNumber is a Variable that you have declared elsewhere in your procedure, then it is fine as is. But, if it is a control on a form, then you would want to use "Me.ItemNumber":

WHERE tblQuotation.ItemNumber = '" & ItemNumber & "'"

Hope this helps and gets you moving in the right direction.

[zmbd]

Just to build on TwinnyFo as he has you in the right direction..

1) Explicitly state in your code either DAO or ADO and set a variable to hold the pointer to your database and remember to release the pointer

DIM zDB as DAO.Database
...
Set zDB = CurrentDB
...
Set zDB = Nothing

2) Build your SQL string as TwinnyFo as suggested, just build it in a string variable first....

sSQL = "UPDATE tblQuotation " & _
   "SET tblQuotation.ItemCategory = " & ItemCategory & ", " & _
   "tblQuotation.ItemDescription = '" & ItemDescription & "', " & _
      (remaining string) 

You can now use a debug.print to print out your SQL string to see how it is resolving.

Call the string variable in the same place you placed the SQL in the execute statement.

ttfn