Type Mismatch Error (1 Viewer)

M

Moore71

DEVELOPER
Local time
Today, 06:58
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:
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:58
Joined
May 7, 2009
Messages
19,247
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
 
M

Moore71

DEVELOPER
Local time
Today, 06:58
Joined
Jul 14, 2012
Messages
158
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
 
Uncle Gizmo

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 06:58
Joined
Jul 9, 2003
Messages
16,285
I don't see a declaration for a recordset object, something like this:-

Dim rs As DAO.Recordset
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 06:58
Joined
Sep 21, 2011
Messages
14,350
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?
 
gemma-the-husky

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 06:58
Joined
Sep 12, 2006
Messages
15,660
Set rs = CurrentDb.OpenRecordset("qryInvoices")

this way you can make sure that qryInvoices works correctly.
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:58
Joined
Oct 29, 2018
Messages
21,493
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
 
P

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:58
Joined
Feb 19, 2002
Messages
43,346
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]) & "'"
 
M

Moore71

DEVELOPER
Local time
Today, 06:58
Joined
Jul 14, 2012
Messages
158
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
-----------------------------------------------------------------------------------------------------------------------------------------------------
 
M

Moore71

DEVELOPER
Local time
Today, 06:58
Joined
Jul 14, 2012
Messages
158
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?
 
M

Moore71

DEVELOPER
Local time
Today, 06:58
Joined
Jul 14, 2012
Messages
158
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
 
Uncle Gizmo

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 06:58
Joined
Jul 9, 2003
Messages
16,285
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....
 
Isaac

Isaac

Lifelong Learner
Local time
Yesterday, 22:58
Joined
Mar 14, 2017
Messages
8,778
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.
 
C

CJ_London

Super Moderator
Staff member
Local time
Today, 06:58
Joined
Feb 19, 2013
Messages
16,629
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
 
M

Moore71

DEVELOPER
Local time
Today, 06:58
Joined
Jul 14, 2012
Messages
158
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)
 
Uncle Gizmo

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 06:58
Joined
Jul 9, 2003
Messages
16,285
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.
 
M

Moore71

DEVELOPER
Local time
Today, 06:58
Joined
Jul 14, 2012
Messages
158
Yes shared the front end by making individual copy
That's what I did
 
M

Moore71

DEVELOPER
Local time
Today, 06:58
Joined
Jul 14, 2012
Messages
158
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
823
arnelgp
arnelgp
M
Sample VBA code to update Multiple tables/Querries from
Replies
2
Views
497
JHB
J
J
Data Type Mismatch Error
Replies
6
Views
765
Jonny
J
M
Synthax Error
Replies
4
Views
1,535
Moore71
M
D
Compile error Type mismatch
Replies
14
Views
2,188
NadiaVKhan
N

Users who are viewing this thread

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