Recordset and SQL

[Guest]

Good morning
It was suggested that I consider recordsets with regard to building a table from a query with manipulations of the values. So, I'm starting out and have come into a small snag. I'm using the MS Access help file for this and get a Type Mismatch Error (#13). Can someone please help me out? I'm in A2k

Thank you
Derek (code below

Option Explici
Sub CigLogic(
Dim dbs As Database, rst As Recordse
Dim strsql As Strin
Set dbs = CurrentD
strsql = "Select * From tblOrderData Where [Dept] = 'CC';
Set rst = dbs.OpenRecordset(strsql) <-- this is where the code stops for debuggin
Set dbs = Nothin
End Sub

 

[Cheryl Fischer]

Derek,

Make sure that you have a reference set to the Microsoft DAO 3.6 Object
Library and, then, in your code make your declarations of database and
recordset explicit to DAO:

Dim dbs As DAO.Database, rst As DAO.Recordset


--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

Good morning,
It was suggested that I consider recordsets with regard to building a

table from a query with manipulations of the values. So, I'm starting out
and have come into a small snag. I'm using the MS Access help file for this
and get a Type Mismatch Error (#13). Can someone please help me out? I'm
in A2k.

Thank you!
Derek (code below)

Option Explicit
Sub CigLogic()
Dim dbs As Database, rst As Recordset
Dim strsql As String
Set dbs = CurrentDb
strsql = "Select * From tblOrderData Where [Dept] = 'CC';"
Set rst = dbs.OpenRecordset(strsql) <-- this is where the code stops for debugging
Set dbs = Nothing
End Sub

 

[Guest]

Cheryl
That's odd. The help file I was looking at was OpenRecordset Method Example (mdb). Nothing in there about DAO. I just tried it (I already reference DAO, just because I'm not familiar with the ADO and DAO libraries to know the difference <g>) and (of course) you were correct. Now that I have referenced DAO in the Dims, is it acceptable to assume that I do not need the DAO object identifier elsewhere in the code

Thanks
Dere

----- Cheryl Fischer wrote: ----

Derek

Make sure that you have a reference set to the Microsoft DAO 3.6 Objec
Library and, then, in your code make your declarations of database an
recordset explicit to DAO

Dim dbs As DAO.Database, rst As DAO.Recordse


-

Cheryl Fischer, MVP Microsoft Acces
Law/Sys Associates, Houston, T

Good morning
It was suggested that I consider recordsets with regard to building

table from a query with manipulations of the values. So, I'm starting ou
and have come into a small snag. I'm using the MS Access help file for thi
and get a Type Mismatch Error (#13). Can someone please help me out? I'
in A2k

Thank you Derek (code below
Option Explici

Sub CigLogic(
Dim dbs As Database, rst As Recordse
Dim strsql As Strin
Set dbs = CurrentD
strsql = "Select * From tblOrderData Where [Dept] = 'CC';
Set rst = dbs.OpenRecordset(strsql) <-- this is where the code stops fo debuggin
Set dbs = Nothin
End Su

 

[Cheryl Fischer]

That's correct - once you have explicitly identified the library (DAO or
ADO) in declaring your recordset, you need not refer to it again in that sub
or function.


--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

Cheryl,
That's odd. The help file I was looking at was OpenRecordset Method

Example (mdb). Nothing in there about DAO. I just tried it (I already
reference DAO, just because I'm not familiar with the ADO and DAO libraries
to know the difference <g>) and (of course) you were correct. Now that I
have referenced DAO in the Dims, is it acceptable to assume that I do not
need the DAO object identifier elsewhere in the code?

Thanks!
Derek

----- Cheryl Fischer wrote: -----

Derek,

Make sure that you have a reference set to the Microsoft DAO 3.6 Object
Library and, then, in your code make your declarations of database and
recordset explicit to DAO:

Dim dbs As DAO.Database, rst As DAO.Recordset


--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

Good morning,
It was suggested that I consider recordsets with regard to building

a
table from a query with manipulations of the values. So, I'm starting out
and have come into a small snag. I'm using the MS Access help file for this
and get a Type Mismatch Error (#13). Can someone please help me out? I'm
in A2k.

Thank you! Derek (code below)
Option Explicit

Sub CigLogic()
Dim dbs As Database, rst As Recordset
Dim strsql As String
Set dbs = CurrentDb
strsql = "Select * From tblOrderData Where [Dept] = 'CC';"
Set rst = dbs.OpenRecordset(strsql) <-- this is where the code

stops for
debugging

Set dbs = Nothing
End Sub

 

[Guest]

Thanks, Cheryl. I'm a tad stuck on a validation rule message and I don't know why. I don't have validation rules set up... I want to duplicate qryCigGet's CumArm field into the CumArm field of CumArm1 as a temporary table. Then, I want to use this information (or from the original qryCigGet) to build the rest of my field values one at a time through the recordset. I want to thank you (and everyone else who's helped me (Tony, Tom, Dev, Brandon, Sue -from Outlook MVPs-, and those I have forgotten, sorry!)) actually not want a drink whenever I see all this gibberish

Option Explici

Sub CigLogic(

Dim dbs As DAO.Database, rstInputtemp, rstOutputTemp As DAO.Recordse
Dim strsql, outputtable As Strin

strsql = "Select * From tblOrderData Where [Dept] = 'CC'
Set rstInputtemp = CurrentDb.OpenRecordset(strsql

DoCmd.RunSQL ("CREATE Table CumArm1(Cumarm double not null, Inter double not null, ToCase double not null, 6s (wrap) double not null, 2s double not null, 1s double not null)"

DoCmd.RunSQL ("INSERT INTO CumArm1 ( CumArm ) SELECT qryCigGet.CumArm FROM qryCigGet"
Set dbs = Nothin
End Su

Thanks again, Cheryl
Derek
----- Cheryl Fischer wrote: ----

That's correct - once you have explicitly identified the library (DAO o
ADO) in declaring your recordset, you need not refer to it again in that su
or function


-

Cheryl Fischer, MVP Microsoft Acces
Law/Sys Associates, Houston, T

Cheryl
That's odd. The help file I was looking at was OpenRecordset Metho

Example (mdb). Nothing in there about DAO. I just tried it (I alread
reference DAO, just because I'm not familiar with the ADO and DAO librarie
to know the difference <g>) and (of course) you were correct. Now that
have referenced DAO in the Dims, is it acceptable to assume that I do no
need the DAO object identifier elsewhere in the code

Thanks Dere
----- Cheryl Fischer wrote: ----
Derek
Make sure that you have a reference set to the Microsoft DAO 3.

Objec
Library and, then, in your code make your declarations of databas an
recordset explicit to DAO

Dim dbs As DAO.Database, rst As DAO.Recordse

-

Cheryl Fischer, MVP Microsoft Acces Law/Sys Associates, Houston, T

table from a query with manipulations of the values. So, I' starting ou
and have come into a small snag. I'm using the MS Access help fil for thi
and get a Type Mismatch Error (#13). Can someone please help me out I'
in A2k

Thank you Derek (code below
Option Explici

Sub CigLogic(
Dim dbs As Database, rst As Recordse
Dim strsql As Strin
Set dbs = CurrentD
strsql = "Select * From tblOrderData Where [Dept] = 'CC';
Set rst = dbs.OpenRecordset(strsql) <-- this is where the cod

stops fo
debuggin

Set dbs = Nothin
End Su