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:- - dim total, tQuantity, tOrder as long
-
-
tQuantity = Form_frmProduct.Quantity.value
-
tOrder = Me! [totalQuantity].value
-
-
[ total = ( tQuantity.value - tOrder.value) ]
Then when I use the SQL UPDATE to update the Product_table, Quantity_field. It cant work....
SQL Code: - SQLText = "UPDATE tblProduct SET TotalQuantity ='" & total & "' WHERE
-
ProductID ='" & ProductID & "'"
-
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!
14 1980
Hi
Try:- - dim total as long, tQuantity as long, tOrder as long
-
-
tQuantity = Form_frmProduct.Quantity.value
-
tOrder = Me! totalQuantity
-
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
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.
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:- - Dim qProduct As Long
-
Dim qOrder As Long
-
Dim qTotal As Long
-
Dim SQLText As String
-
-
qOrder = Me![txtTotal].Value
-
qProduct = Form_frmProduct.txtQuantity.Value
-
qTotal = ( qProduct - qOrder )
-
-
SQLText = "UPDATE tblProduct SET TotalQuantity ='" & qTotal & "' WHERE ProductID ='" & ProductID & "'"
-
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!
Hi
Try:- - dim total as long, tQuantity as long, tOrder as long
-
-
tQuantity = Form_frmProduct.Quantity.value
-
tOrder = Me! totalQuantity
-
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. :(
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.
........
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) -
Dim db As DAO.Database
-
Dim rs As DAO.Recordset
-
Dim num As Long
-
-
DoCmd.GoToRecord , , acNewRec
-
-
Set db = CurrentDb
-
Set rs = db.OpenRecordset("tblProduct", dbOpenDynaset)
-
-
If rs.BOF Then
-
rs.AddNew
-
ProductID = 1
-
-
Else
-
rs.MoveLast
-
rs.Edit
-
num = rs(ProductID)
-
Me![ProductID] = num + 1
-
End If
-
-
Set rs = Nothing
-
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.......
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.
here is the full coding for Save_button - Private Sub cmdSave_Click()
-
On Error GoTo Err_cmdSave_Click
-
Dim qProduct As Long
-
Dim qOrder As Long
-
Dim qTotal As Long
-
Dim SQLText As String
-
-
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
-
MsgBox "Record saved!", vbOKOnly, "Saved"
-
-
qOrder = Me![txtTotal]
-
qProduct = Me![txtQuantity]
-
qTotal = (qProduct - qOrder)
-
-
MsgBox "Match Found For: " & qTotal, , "Congratulations!"
-
-
SQLText = "UPDATE tblProduct SET tblProduct.[TotalQuantity] ='" & qTotal & "' WHERE [ProductID] ='" & ProductID & "'"
-
DoCmd.RunSQL SQLText
-
-
Exit_cmdSave_Click:
-
Exit Sub
-
-
Err_cmdSave_Click:
-
MsgBox Err.Description
-
Resume Exit_cmdSave_Click
-
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.
hi, is me again... so sorry for keep on distrubing you all...
I think the problem, maybe come out from my SQL command.... - qOrder = Me![txtTotal]
-
qProduct = Me![txtQuantity]
-
qTotal = (qProduct - qOrder)
-
-
SQLText = "UPDATE tblProduct SET tblProduct.[TotalQuantity] ='" & qTotal & "' WHERE [ProductID] ='" & ProductID & "'"
-
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......!!!
hi, cyberdwarf
the code that you mark in different colour, shall I input it or not to input?
Hi
Try:- - dim total as long, tQuantity as long, tOrder as long
-
-
tQuantity = Form_frmProduct.Quantity.value
-
tOrder = Me! totalQuantity
-
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
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.... - qOrder = Me![txtTotal]
-
qProduct = Me![txtQuantity]
-
qTotal = (qProduct - qOrder)
-
-
SQLText = "UPDATE tblProduct SET tblProduct.[TotalQuantity] ='" & qTotal & "' WHERE [ProductID] ='" & ProductID & "'"
-
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 : - SQLText = "UPDATE tblProduct " &
-
"SET tblProduct.[TotalQuantity]=" & qTotal &
-
" WHERE [ProductID]='" & ProductID & "'"
-
DoCmd.RunSQL SQLText
I've removed the quotes from the qTotal value as I suspect that the [TotalQuantity] field is numeric rather than textual.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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:
...
|
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...
|
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...
|
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
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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...
|
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...
| |