error with count records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Set rst = dbs.OpenRecordset("SELECT Count (*) AS RecordCountTemp FROM tblItemDetailTemp")

I am getting a typemismatch error on th above - i don't knowhat's wrong with it? New to vb code,
more info...

I'm trying to count records added to tbl using sql above. save the # to use in if statement later on....(to update a different tbl)


mDim dbs As Database, rst As Recordset
Dim SQLAppendCNPartsTemp As String
Dim SQLAppendCNParts As String
Dim RecordCountTemp As Long
Dim RecordCountPart As Long


SQLAppendCNPartsTemp = "INSERT INTO tblItemDetailTemp ( ControlNo, ItemNo, QtyUsed, DateOut ) " & _
"SELECT DISTINCTROW tblControlNoParts.ControlNo, tblControlNoParts.PartNo, Sum(-tblControlNoParts!Qty) AS [Sum of Qty], Date() AS Expr1 " & _
"FROM tblControlNoParts INNER JOIN tblItemDetail ON tblControlNoParts.PartNo = tblItemDetail.ItemNo " & _
"GROUP BY tblControlNoParts.ControlNo, tblControlNoParts.PartNo, Date() " & _
"HAVING (((tblControlNoParts.ControlNo)=[Forms]![frmControlNoInfo]![ControlNo]) AND ((Sum(tblControlNoParts.Qty))<(Sum([tblItemDetail]![QtyRecd])))) "
DoCmd.RunSQL SQLAppendCNPartsTemp

Set dbs = CurrentDb
'Count records in TempInventory Details

Set rst = dbs.OpenRecordset("SELECT Count (*) AS RecordCountTemp FROM tblItemDetailTemp")

'RecordCountTemp = [RecordCountTemp]

dbs.Close

any help is appreciated!
 
You are probably using A2K or later and the ADO Library has higher priority
than the DAO Library. In this case, the Recordset rst has been dimensioned
as ADO Recordset while you needed DAO Recordset. You need to disambiguate
the Recordset in the Dim statement (or set DAO Library to higher priority or
remove ADO Library if you don't use ADO). Try disambiguating by declaring
as:

Dim dbs As Database
Dim rst As DAO.Recordset

--
HTH
Van T. Dinh
MVP (Access)



Sally said:
Set rst = dbs.OpenRecordset("SELECT Count (*) AS RecordCountTemp FROM tblItemDetailTemp")

I am getting a typemismatch error on th above - i don't knowhat's wrong with it? New to vb code,
more info...

I'm trying to count records added to tbl using sql above. save the # to
Click to expand...
use in if statement later on....(to update a different tbl)
mDim dbs As Database, rst As Recordset
Dim SQLAppendCNPartsTemp As String
Dim SQLAppendCNParts As String
Dim RecordCountTemp As Long
Dim RecordCountPart As Long


SQLAppendCNPartsTemp = "INSERT INTO tblItemDetailTemp ( ControlNo, ItemNo, QtyUsed, DateOut ) " & _
"SELECT DISTINCTROW tblControlNoParts.ControlNo, tblControlNoParts.PartNo,
Click to expand...
Sum(-tblControlNoParts!Qty) AS [Sum of Qty], Date() AS Expr1 " & _
"FROM tblControlNoParts INNER JOIN tblItemDetail ON
Click to expand...
tblControlNoParts.PartNo = tblItemDetail.ItemNo " & _
"GROUP BY tblControlNoParts.ControlNo, tblControlNoParts.PartNo, Date() " & _
"HAVING
Click to expand...
(((tblControlNoParts.ControlNo)=[Forms]![frmControlNoInfo]![ControlNo]) AND
((Sum(tblControlNoParts.Qty))<(Sum([tblItemDetail]![QtyRecd])))) "
DoCmd.RunSQL SQLAppendCNPartsTemp

Set dbs = CurrentDb
'Count records in TempInventory Details

Set rst = dbs.OpenRecordset("SELECT Count (*) AS RecordCountTemp FROM tblItemDetailTemp")

'RecordCountTemp = [RecordCountTemp]

dbs.Close

any help is appreciated!
Click to expand...
 
Thank you. I got that one to work but now

Set rst1 = dbs.OpenRecordset(
"SELECT * FROM tblControlNoParts", dbOpenTable

is giving me an error - says it can't find the object "Select * from ....yadda yadda... Make sure it exists, spelled correctly, etc. (I am going to put a Where clause in as well but the simple select won't work so I thought I'd start there

I don't know what else I can try. I've looked at examples in help file but nothing I try works.

Again help is appreciated.
 
I think the problem is that you specified dbOpenTable
from which Access *expects* a Table Name in the first
argument and NOT an SQL String.

Try without the second argument and let Access decides the
type for you like:

Set rst1 = dbs.OpenRecordset _
("SELECT * FROM tblControlNoParts)

HTH
Van T. Dinh
MVP (Access)


-----Original Message-----
Thank you. I got that one to work but now,

Set rst1 = dbs.OpenRecordset( _
"SELECT * FROM tblControlNoParts", dbOpenTable)

is giving me an error - says it can't find the
Click to expand...
object "Select * from ....yadda yadda... Make sure it
exists, spelled correctly, etc. (I am going to put a
Where clause in as well but the simple select won't work
so I thought I'd start there)
I don't know what else I can try. I've looked at
Click to expand...
examples in help file but nothing I try works.
 
Back
Top