By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,662 Members | 1,790 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,662 IT Pros & Developers. It's quick & easy.

How to Update Data into a Table (Merged)

P: 8
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
Share this Question
Share on Google+
14 Replies


Expert 100+
P: 218
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
Expert Mod 15k+
P: 31,271
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

P: 8
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

P: 8
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
Expert 100+
P: 159
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

P: 8
........
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

Expert 100+
P: 218
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

P: 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

P: 8
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
Expert Mod 15k+
P: 31,271
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
Expert Mod 15k+
P: 31,271
For help with debugging your project, check out Debugging in VBA.
Jun 7 '07 #12

P: 8
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

P: 8
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
Expert Mod 15k+
P: 31,271
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

Post your reply

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