Type Mismatch Error

M

Moore71

DEVELOPER
Local time
Today, 20:30
Joined
Jul 14, 2012
Messages
158
Hi, I am trying to write a code into my small application but it keeps throwing errors when I compile it (Type Mismatch)
Below is the exact code throwing error at .OpenCurrentdb:

Code: 
Set dbs = CurrentDb
Set rs = CurrentDb.OpenRecordset("SELECT tblItem.QtyAvail, tblItem.UnitSale, tblClient.ClientAcc, tblOutbound.ClientID, tblOutbound.QtyOut, tblOutbound.AmountRecorded FROM tblItem INNER JOIN (tblClient INNER JOIN tblOutbound ON tblClient.ClientID = tblOutbound.ClientID) ON tblItem.ItemID = tblOutbound.ItemID WHERE (((tblOutbound.InvoiceNo) = '" & [Forms]![frmPoS]![InvoiceNo]) & "'"
Do While Not rs.EOF
            rs.Edit
            rs!ClientID = Me!Client
            rs!QtyAvail = rs!QtyAvail - rs!QtyOut
            rs!UnitSale = rs!ExtendedPrice
            rs!ClientAcc = rs!ClientAcc + rs!AmountRecorded
            rs.Update
            rs.MoveNext
Loop
            rs.Close
----------------------------------------------------------------------------------------------------------------
Please where am I making the errors?
 
Last edited by a moderator:
did the error message give you option to debug, do so and
it will highlight the line that has error.
post that line.

only hopeful wishing here.
if InvoiceNo is Numeric, you don't need to use delimiter on your sql query.

on your rs, try Casting then fields to Nz()

Do While Not rs.EOF
rs.Edit
rs!ClientID = Me!Client
rs!QtyAvail = Nz(rs!QtyAvail, 0) - Nz(rs!QtyOut, 0)
rs!UnitSale = Nz(rs!ExtendedPrice, 0)
rs!ClientAcc = Nz(rs!ClientAcc , 0) + Nz(rs!AmountRecorded, 0)
rs.Update
rs.MoveNext
Loop
rs.Close
 
I think
arnelgp said:
did the error message give you option to debug, do so and
it will highlight the line that has error.
post that line.

only hopeful wishing here.
if InvoiceNo is Numeric, you don't need to use delimiter on your sql query.

on your rs, try Casting then fields to Nz()

Do While Not rs.EOF
rs.Edit
rs!ClientID = Me!Client
rs!QtyAvail = Nz(rs!QtyAvail, 0) - Nz(rs!QtyOut, 0)
rs!UnitSale = Nz(rs!ExtendedPrice, 0)
rs!ClientAcc = Nz(rs!ClientAcc , 0) + Nz(rs!AmountRecorded, 0)
rs.Update
rs.MoveNext
Loop
rs.Close
Click to expand...
I thin the error is posting is in OpenRecordset
I don't know maybe improper variable declaration or something like that , I guess
Please check through and give me the right way (syntax) to present the code
Thanks
 
I don't see a declaration for a recordset object, something like this:-

Dim rs As DAO.Recordset
 
Moore71 said:
I think

I thin the error is posting is in OpenRecordset
I don't know maybe improper variable declaration or something like that , I guess
Please check through and give me the right way (syntax) to present the code
Thanks
Click to expand...
So do as arnelgp suggests and remove the single quotes from form invoiceno?
 
Set rs = CurrentDb.OpenRecordset("qryInvoices")

this way you can make sure that qryInvoices works correctly.
 
Hi. @Moore71. I agree with @Uncle Gizmo. Please show us your entire code. I am also guessing you may have declared the rs object as:
Code: 
Dim rs As Recordset
instead of:
Code: 
Dim rs As DAO.Recordset
 
I prefer querydefs myself since it makes the query easy to test outside of the code. BUT, if you use embedded SQL, ALWAYS save is as a variable so you can paste it to the immediate window to look at it after it is constructed and then copy it into the QBE and run it.

Dim strSQL as String

strSQL = "SELECT tblItem.QtyAvail, tblItem.UnitSale, tblClient.ClientAcc, tblOutbound.ClientID, tblOutbound.QtyOut, tblOutbound.AmountRecorded " FROM tblItem INNER JOIN (tblClient INNER JOIN tblOutbound ON tblClient.ClientID = tblOutbound.ClientID) ON tblItem.ItemID = tblOutbound.ItemID WHERE (((tblOutbound.InvoiceNo) = '" & [Forms]![frmPoS]![InvoiceNo]) & "'"
 
This is the whole code used
--------------------------------------------------------------------------------------------------------------------------------------------
Dim dbs As DAO.Database
Dim rs As DAO.Recordset

Set dbs = CurrentDb
Set rs = CurrentDb.OpenRecordset("SELECT tblItem.QtyAvail, tblItem.UnitSale, tblClient.ClientAcc, tblOutbound.ClientID, tblOutbound.QtyOut, tblOutbound.AmountRecorded FROM tblItem INNER JOIN (tblClient INNER JOIN tblOutbound ON tblClient.ClientID = tblOutbound.ClientID) ON tblItem.ItemID = tblOutbound.ItemID WHERE (((tblOutbound.InvoiceNo) = '" & [Forms]![frmPoS]![InvoiceNo]) & "'"
Do While Not rs.EOF
rs.Edit
rs!ClientID = Me!Client
rs!QtyAvail = rs!QtyAvail - rs!QtyOut
rs!UnitSale = rs!ExtendedPrice
rs!ClientAcc = rs!ClientAcc + rs!AmountRecorded
rs.Update
rs.MoveNext
Loop
rs.Close
-----------------------------------------------------------------------------------------------------------------------------------------------------
 
Moore71 said:
This is the whole code used
--------------------------------------------------------------------------------------------------------------------------------------------
Dim dbs As DAO.Database
Dim rs As DAO.Recordset

Set dbs = CurrentDb
Set rs = CurrentDb.OpenRecordset("SELECT tblItem.QtyAvail, tblItem.UnitSale, tblClient.ClientAcc, tblOutbound.ClientID, tblOutbound.QtyOut, tblOutbound.AmountRecorded FROM tblItem INNER JOIN (tblClient INNER JOIN tblOutbound ON tblClient.ClientID = tblOutbound.ClientID) ON tblItem.ItemID = tblOutbound.ItemID WHERE (((tblOutbound.InvoiceNo) = '" & [Forms]![frmPoS]![InvoiceNo]) & "'"
Do While Not rs.EOF
rs.Edit
rs!ClientID = Me!Client
rs!QtyAvail = rs!QtyAvail - rs!QtyOut
rs!UnitSale = rs!ExtendedPrice
rs!ClientAcc = rs!ClientAcc + rs!AmountRecorded
rs.Update
rs.MoveNext
Loop
rs.Close
-----------------------------------------------------------------------------------------------------------------------------------------------------
Click to expand...
So what did I left out please?
 
Pat Hartman said:
I prefer querydefs myself since it makes the query easy to test outside of the code. BUT, if you use embedded SQL, ALWAYS save is as a variable so you can paste it to the immediate window to look at it after it is constructed and then copy it into the QBE and run it.

Dim strSQL as String

strSQL = "SELECT tblItem.QtyAvail, tblItem.UnitSale, tblClient.ClientAcc, tblOutbound.ClientID, tblOutbound.QtyOut, tblOutbound.AmountRecorded " FROM tblItem INNER JOIN (tblClient INNER JOIN tblOutbound ON tblClient.ClientID = tblOutbound.ClientID) ON tblItem.ItemID = tblOutbound.ItemID WHERE (((tblOutbound.InvoiceNo) = '" & [Forms]![frmPoS]![InvoiceNo]) & "'"
Click to expand...
This was what I used before. But the problem here is there are 2 users, and when they run the same process from different PCs, one sometimes freeze especially when the same product is selected at both end
 
Moore71 said:
But the problem here is there are 2 users, and when they run the same process from different PCs,
Click to expand...

Do you have the correct setup?

You need EACH user to have their OWN front end dB and a shared back end dB. .

I suspect your users are sharing the same dB.

Please confirm....
 
Pat Hartman said:
I prefer querydefs myself since it makes the query easy to test outside of the code. BUT, if you use embedded SQL, ALWAYS save is as a variable so you can paste it to the immediate window to look at it after it is constructed and then copy it into the QBE and run it.

Dim strSQL as String

strSQL = "SELECT tblItem.QtyAvail, tblItem.UnitSale, tblClient.ClientAcc, tblOutbound.ClientID, tblOutbound.QtyOut, tblOutbound.AmountRecorded " FROM tblItem INNER JOIN (tblClient INNER JOIN tblOutbound ON tblClient.ClientID = tblOutbound.ClientID) ON tblItem.ItemID = tblOutbound.ItemID WHERE (((tblOutbound.InvoiceNo) = '" & [Forms]![frmPoS]![InvoiceNo]) & "'"
Click to expand...
I completely agree, but just throwing in there that sometimes even if a person doesn't do that, you can still just copy everything inside the literal quotes and paste to immediate, then ask the immediate window the same question regardless.
 
you still haven't commented on Arnel's suggestion in post #2 followed up by Gasman in post #5. It is the most likely reason for your problem and needs to be ruled out. The quotes are still there in your post #9
 
YYe
Uncle Gizmo said:
Do you have the correct setup?

You need EACH user to have their OWN front end dB and a shared back end dB. .

I suspect your users are sharing the same dB.

Please confirm....
Click to expand...
Yes it's a shared front end while the back end is split (and the 2 users connect to the same back end)
 
Moore71 said:
Yes it's a shared front end
Click to expand...

Then you need to change your setup. Each user should have their own front-end, and not be sharing the same front end.

All you do is make a copy of the front end and give each user their own copy.
 
Yes shared the front end by making individual copy
That's what I did
 
Uncle Gizmo said:
Then you need to change your setup. Each user should have their own front-end, and not be sharing the same front end.

All you do is make a copy of the front end and give each user their own copy.
Click to expand...
That's exactly what I did here, but when the 2 users updates the same item(s) at the same time, the program freezes
So I am looking for a walk around this issue, I don't know if there's a process to keep one user waiting when the other user is already engage with the process or maybe recordset will resolve this issue
Please give me more idea here
 
You must log in or register to reply here.

Similar threads

M
Please I need More Info on this my VBA code
Replies
6
Views
901
arnelgp
arnelgp
M
Sample VBA code to update Multiple tables/Querries from
Replies
2
Views
578
JHB
J
J
Data Type Mismatch Error
Replies
6
Views
851
Jonny
J
M
Synthax Error
Replies
4
Views
1,609
Moore71
M
D
Compile error Type mismatch
Replies
14
Views
2,324
NadiaVKhan
N

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom