"Type mismatch" with recordset 1

[cjLiteStep]

holy cow!
i finally get to declare a database as a database (thanks) and follow examples to a "T" i think and i get a "type mismatch" error that i just don't see

here is the code:

Dim DB As Database, RS As Recordset, strTableName As _ String
strTableName = "tblCourseReg"
Set DB = CurrentDb
Set RS = DB.OpenRecordset("tblCourseReg", dbOpenTable)

tblCourseReg is a valid table (and not a linked table)that has an ID autonumber field and about 6 others

the error occurs when executing .. Set RS = DB.Openre ...

thanks again for any guidance

cj

 

[ghubbell]

I'm stubborn and like this fashion... Try this:

Dim DB As Database
Dim RS As Recordset
Dim SQL As String

SQL = "Select tblCourseReg.* FROM tblCourseReg"
Set DB = CurrentDb
Set RS = DB.OpenRecordset(SQL, dbOpenTable) Gord
ghubbell@total.net

 

[cjLiteStep]

hey gord

thanks for replying again

this time though, i get an error saying cannot find object that looks for an object named "Select tblCourseReg.* FROM tblCourseReg"

i like to look at things from different sides, but i'm really perplexed over this "type mismatch" error from the first effort i made

do you know why this happens?

cj

 

[ghubbell]

CJ;
I think you might have to step back to those references again.. Here's what I have and in a working order for Access 2000:
VB for Apps
Ms 9.0 Object library
Ms DAO 3.6 ...
OLE Automation...
Ms VB for apps Ext..5.3

Before doing anything try to compile your code. If it will, then I'm loosing here...If it won't, stack up your references similar to this and try again. Still won't? try shifting their priorities.

The error trip on that particular line could be caused by anything on that line, and sometimes I've seen VB lie about what is supposed to be wrong. (it should work as you had it to start with best as I can see, although you declared a variable and didn't use it)... keep going!

Gord
ghubbell@total.net

 

[cjLiteStep]

here i go again

after changing the pecking order of the reference libraries to look like gord's (*** thanks gord ***):

VB for Apps
Ms 9.0 Object library
Ms DAO 3.6 ...
OLE Automation...
Ms VB for apps Ext..5.3

my little bit of code like this:

Dim DB As Database, RS As Recordset, strTableName As String
strTableName = "tblCourseReg"
Set DB = CurrentDb
Set RS = DB.OpenRecordset(strTableName, dbOpenDynaset)
RS.AddNew
!REmemberNUM = intMemNum
!REcourseNUM = intCourNum
RS.Update

moves on to the next error ... "Invalid or unqualified reference" pointing at ... !REmemberNUM ... which is a field in the table and can be seen by looking at the properties of the recordset during execution as Item 3, with a name of REmemberNUM

i have added a record to the table (it was empty) after reading here that you can't go to an empty table

intMemNum and intCourNum are declared and show a value during execution

i plead for aid once again

cj

 

[ghubbell]

I honestly don't know if you can do this!
!REmemberNUM = intMemNum
!REcourseNUM = intCourNum

Should be:

Rs!REmemberNUM = intMemNum
Rs!REcourseNUM = intCourNum
Or
With Rs
!REmemberNUM = intMemNum
!REcourseNUM = intCourNum
End Eith
I believe! Gord
ghubbell@total.net

 

[cjLiteStep]

Well God .. oopps .. i mean Gord
that was it, i had mixed up the 2 expamples (one using the with statement, the other not) and my hybrid code didn't work!

i won't gush here in a public forum, but you have just opened up the rest of this project for me, as i will be automating a lot of record updating and additions.

je suis aussi canadien, habite a l'Edmonton
merci beaucoup pour tout vos efforts

bonjour!

cj

 

[ghubbell]

Bienvenue et merci à vous aussi! Gord
ghubbell@total.net