473,327 Members | 2,069 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

How to Update Data into a Table (Merged)

Hi, I have some problem with my Microsoft Access 2003 coding. Can any expert please help to check out the problem and provide me the solution?

I have a Order_form, I need to update the totalQuantity into Product_table, Quantity_field.

the code is as below:-
Expand|Select|Wrap|Line Numbers
  1.    dim total, tQuantity, tOrder as long
  2.  
  3.    tQuantity = Form_frmProduct.Quantity.value
  4.    tOrder = Me! [totalQuantity].value
  5.  
  6.    [ total = ( tQuantity.value - tOrder.value) ] 
Then when I use the SQL UPDATE to update the Product_table, Quantity_field. It cant work....

SQL Code:
Expand|Select|Wrap|Line Numbers
  1.    SQLText = "UPDATE tblProduct SET TotalQuantity ='" & total & "' WHERE   
  2.                           ProductID ='" & ProductID & "'"
  3.    DoCmd.RunSQL SQLText
Error Message: Data type mismatch in criteria expression

Can anyone please tell me where is the error and how to correct it? Thank You!
Jun 6 '07 #1
14 1980
cyberdwarf
218 Expert 100+
Hi

Try:-
Expand|Select|Wrap|Line Numbers
  1.  dim total as long, tQuantity as long, tOrder as long
  2.  
  3. tQuantity = Form_frmProduct.Quantity.value
  4. tOrder = Me! totalQuantity 
  5. total = tQuantity - tOrder 
This should allow your SQL code to run OK.

Note that if you DIMension variables in code, you don't have to use the ".Value" property. Also, you should be aware that variables will be of type Variant, unless you DIMension them as in the code above.

HTH
Steve
Jun 6 '07 #2
NeoPa
32,556 Expert Mod 16PB
You have (accidentally) posted this question in the Access Articles section. This is NOT an article.
I'm moving this to the main Access questions forum.

MODERATOR.
Jun 6 '07 #3
Hi, I am a new people in Access. I am doing a system by using Access 2003.

I have a problem in updating the order quantity into the Product table. The case is as below.

In my Order_Form, I have a TotalQuantity_field to calculate the total, then this total have to be updated into another table called Product_table so that I know what is my product quantity that I still have in stock.

the coding is as below:-

Expand|Select|Wrap|Line Numbers
  1. Dim qProduct As Long
  2. Dim qOrder As Long
  3. Dim qTotal As Long
  4. Dim SQLText As String
  5.  
  6. qOrder = Me![txtTotal].Value
  7. qProduct = Form_frmProduct.txtQuantity.Value
  8. qTotal = ( qProduct - qOrder )
  9.  
  10. SQLText = "UPDATE tblProduct SET TotalQuantity ='" & qTotal & "' WHERE ProductID ='" & ProductID & "'"
  11. DoCmd.RunSQL SQLText
but then there is an error message : Data type mismatch in criteria expression.

can anyone tell what is the meaning for this error? How can I correct it?
Please.... Thank You!
Jun 7 '07 #4
Hi

Try:-
Expand|Select|Wrap|Line Numbers
  1.  dim total as long, tQuantity as long, tOrder as long
  2.  
  3. tQuantity = Form_frmProduct.Quantity.value
  4. tOrder = Me! totalQuantity 
  5. total = tQuantity - tOrder 
This should allow your SQL code to run OK.

Note that if you DIMension variables in code, you don't have to use the ".Value" property. Also, you should be aware that variables will be of type Variant, unless you DIMension them as in the code above.

HTH
Steve

hi, but it still cannot work for that.... the data type mismatch error still come out. :(
Jun 7 '07 #5
MSeda
159 Expert 100+
ProductID is being treated as text in the criteria (where) clause of your SQL string. The error you cited indicates that ProductID is not designated as a text data type in one or both tables. If the data type in the tables differ you will need to change one to match if productID is not text, in either table (and the data types match) then drop the single quotes.
Jun 7 '07 #6
........
Hi, Thank you for your suggestion. But I still cant get it. The same error still occur.
In my Product_table, the ProductID, I set it as Number - Long Integer.
Then in the Product_form, I use the coding to let it run automatically (the coding as below)
Expand|Select|Wrap|Line Numbers
  1.     Dim db As DAO.Database
  2.     Dim rs As DAO.Recordset
  3.     Dim num As Long
  4.  
  5.     DoCmd.GoToRecord , , acNewRec
  6.  
  7.     Set db = CurrentDb
  8.     Set rs = db.OpenRecordset("tblProduct", dbOpenDynaset)
  9.  
  10.     If rs.BOF Then
  11.         rs.AddNew
  12.         ProductID = 1
  13.  
  14.     Else
  15.         rs.MoveLast
  16.         rs.Edit
  17.         num = rs(ProductID)
  18.         Me![ProductID] = num + 1
  19.     End If
  20.  
  21.     Set rs = Nothing
  22.     Set db = Nothing
It should be in Long Interger?!? rite / not? (I also not sure)

Then in my Order_form, at the ProductID_field, I use the comboBox to call out the ProductID from Product_table to allow me to select. So it should not be any problem.... (I think...)

Can anyone help me, please.......
Jun 7 '07 #7
cyberdwarf
218 Expert 100+
hi, but it still cannot work for that.... the data type mismatch error still come out. :(
OK, use the debugger to establish which objects are causing the mismatch errors, then report back.
Jun 7 '07 #8
here is the full coding for Save_button

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSave_Click()
  2. On Error GoTo Err_cmdSave_Click
  3.     Dim qProduct As Long
  4.     Dim qOrder As Long
  5.     Dim qTotal As Long
  6.     Dim SQLText As String
  7.  
  8.     DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
  9.     MsgBox "Record saved!", vbOKOnly, "Saved"
  10.  
  11.     qOrder = Me![txtTotal]
  12.     qProduct = Me![txtQuantity]
  13.     qTotal = (qProduct - qOrder)
  14.  
  15.     MsgBox "Match Found For: " & qTotal, , "Congratulations!"
  16.  
  17.     SQLText = "UPDATE tblProduct SET tblProduct.[TotalQuantity] ='" & qTotal & "' WHERE [ProductID] ='" & ProductID & "'"
  18.     DoCmd.RunSQL SQLText
  19.  
  20. Exit_cmdSave_Click:
  21.     Exit Sub
  22.  
  23. Err_cmdSave_Click:
  24.     MsgBox Err.Description
  25.     Resume Exit_cmdSave_Click
  26. End Sub
i use the debugger to click from
--- qOrder = Me![txtTotal] to.... --- Resume Exit_cmdSave_Click

then when it move until remuse Exit_cmdSave_Click, this line, the error come out.

When the debugger move until (MsgBox "Match Found For: " & qTotal, , "Congratulations!"). It really tell me the total.
Then, I am so surprise that it can pass through the SQL line, but it didnt do the update into the Product_table.
Jun 7 '07 #9
OK, use the debugger to establish which objects are causing the mismatch errors, then report back.
I have accidently post the same question at this title too...
[Need Help! How to Update the Order Quantity in Product_Table? ]
Jun 7 '07 #10
NeoPa
32,556 Expert Mod 16PB
I have accidently post the same question at this title too...
[Need Help! How to Update the Order Quantity in Product_Table? ]
I will merge the two threads.
This may seem a little weird as the posts will be in date order.

MODERATOR.
Jun 7 '07 #11
NeoPa
32,556 Expert Mod 16PB
For help with debugging your project, check out Debugging in VBA.
Jun 7 '07 #12
hi, is me again... so sorry for keep on distrubing you all...

I think the problem, maybe come out from my SQL command....

Expand|Select|Wrap|Line Numbers
  1.     qOrder = Me![txtTotal]
  2.     qProduct = Me![txtQuantity]
  3.     qTotal = (qProduct - qOrder)
  4.  
  5. SQLText = "UPDATE tblProduct SET tblProduct.[TotalQuantity] ='" & qTotal & "' WHERE [ProductID] ='" & ProductID & "'"
  6. DoCmd.RunSQL SQLText
This morning, when I use the debugger to run the coding again, I found that when it pass the qOrder, qProduct and qTotal, it also can show me the correct answer.
Then when it reach the SQLText, the qTotal is correct, but the ('" & ProductID & "'") maybe got problem... Coz it show me "0", not ProductID "1"....

Can anyone tell me why like this or not?!? And how to solve this problem?
I cant left this part coz it is the main part for the whole system.
Please help......!!!
Jun 8 '07 #13
hi, cyberdwarf

the code that you mark in different colour, shall I input it or not to input?


Hi

Try:-
Expand|Select|Wrap|Line Numbers
  1.  dim total as long, tQuantity as long, tOrder as long
  2.  
  3. tQuantity = Form_frmProduct.Quantity.value
  4. tOrder = Me! totalQuantity 
  5. total = tQuantity - tOrder 
This should allow your SQL code to run OK.

Note that if you DIMension variables in code, you don't have to use the ".Value" property. Also, you should be aware that variables will be of type Variant, unless you DIMension them as in the code above.

HTH
Steve
Jun 8 '07 #14
NeoPa
32,556 Expert Mod 16PB
hi, is me again... so sorry for keep on distrubing you all...

I think the problem, maybe come out from my SQL command....

Expand|Select|Wrap|Line Numbers
  1.     qOrder = Me![txtTotal]
  2.     qProduct = Me![txtQuantity]
  3.     qTotal = (qProduct - qOrder)
  4.  
  5. SQLText = "UPDATE tblProduct SET tblProduct.[TotalQuantity] ='" & qTotal & "' WHERE [ProductID] ='" & ProductID & "'"
  6. DoCmd.RunSQL SQLText
This morning, when I use the debugger to run the coding again, I found that when it pass the qOrder, qProduct and qTotal, it also can show me the correct answer.
Then when it reach the SQLText, the qTotal is correct, but the ('" & ProductID & "'") maybe got problem... Coz it show me "0", not ProductID "1"....

Can anyone tell me why like this or not?!? And how to solve this problem?
I cant left this part coz it is the main part for the whole system.
Please help......!!!
Try this SQL :
Expand|Select|Wrap|Line Numbers
  1. SQLText = "UPDATE tblProduct " & 
  2.           "SET tblProduct.[TotalQuantity]=" & qTotal & 
  3.           " WHERE [ProductID]='" & ProductID & "'"
  4. DoCmd.RunSQL SQLText
I've removed the quotes from the qTotal value as I suspect that the [TotalQuantity] field is numeric rather than textual.
Jun 8 '07 #15

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Vanessa | last post by:
I need to read/extract data from an Excel file using ASP. However, the Excel file is not in regular tabular format; instead, it is actually a form. Therefore, it contains many checkbox and merged...
2
by: Shaun | last post by:
Hi, I have (amogst others) three tables in my database named Bookings, User, and Representative. A User and a Representative are different types of user, however I now want to merge these tables...
7
by: Andy Davis | last post by:
I have a table of data in Access 2002 which is used as the source table for a mail merge document using Word 2002 on my clients PC. The data is transferred OK but I've noticed that any dates which...
1
by: Huw Davies | last post by:
Firstly, please accept my apologies if this problem has already been answered in other posts / groups. I have searched as best I can, but I can't find the exact same problem elsewhere. I have an...
1
by: Mike Hnatt | last post by:
I'm baffled. My data store (database) is not updating with a refreshed dataset. Here's what is going on: 1) Create a dataset from a table in a database. 2) Create a copy of this dataset and...
2
by: Stephen Witter | last post by:
I am using WriteXml to output data to an xml file, and and XML web control to display it. The code runs fine, however my groups are being ignored. For Instance, the current output is: ...
22
by: pbd22 | last post by:
hi. I am having probelms with an update statement. every time i run it, "every" row updates, not just the one(s) intended. so, here is what i have. i have tried this with both AND and OR and...
6
by: Suresh | last post by:
Hi All, I am fetching a dataset from the database under some condition. After this I create a data table. Traverse in the original dataset & add each row to created data table as it is through...
1
by: mymoup | last post by:
hi, all. I want to verify data merged from access in sqlserver.but it has too many records .so i need a tool do this. someone maybe has it. thanks
1
by: ebenl555 | last post by:
i am writing code to import data from an .xls file into a db using an oledbadapter to fill a datatable. I can access most of the values easily using this method. i do however have problems with a...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.