CurrentDb() code works using 97 but not 2000??? Any ideas why? 3

[INeedAccessHelp]

Here is the code:

Dim rst As Recordset
Dim db As Database
Set db = CurrentDb()
Set rst = db.OpenRecordset("BCTbl", DB_OPEN_TABLE)

This is the error message:

Run-time error'3219':

Invalid Operation

My only guess is that "CurrentDb()" is no longer valid in Access 2000. Please help. Thanks in advance.

--Rob

 

[jebry]

Hi Rob!

CurrentDb isn't valid for ADO objects and that is the default in A2K. Change your code to read

Dim db As DAO.Database
Dim rst As DAO.Recordset

You will also need to make sure you have a reference set for the DAO object library. In the code window select Tool/References and scroll down until you find DAO 3.6 library and click on the check box and say okay. That should take care of the problem.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[INeedAccessHelp]

Thanks Jeff

Now I'm getting:

Run-time error '91':

Object variable or With block variable not set.

I used your code but I think I'm missing a statement to set the db variable to the current database.

 

[jebry]

Hi!

I meant that the Declaration statements are the only ones that needed to be changed. You still need the rest of the code including the Set statements for the Db and rst.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[INeedAccessHelp]

OK. Now I'm getting a different error:

Run-time error '3251'

Operation is not supported for this type of object.

This is the code:

IDStr = Me![ID]

With rst
.Index = "ID"
.Seek "=", IDStr
.Edit
![fld1] = "1"
![fld2] = "2"
![fld3] = "3"
.Update
End With
rst.Close

Code stops at ".Index = "ID"

 

[jebry]

Hi!

I'll need to see the rest of the code. If you opened the recordset using dbOpenTable then it should support the .Index method. But if you opened it without a designation then it may not.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[INeedAccessHelp]

This is all of the code:

Dim IDStr As Integer
Dim db As DAO.Database
Dim rst As DAO.Recordset

IDStr = Me![ID]
Set db = CurrentDb()
Set rst = db.OpenRecordset("Tbl", dbOpenTable)

DoCmd.OpenForm "Test", acNormal, , "ID =" & IDStr, , acHidden

With rst
.Index = "BCID"
.Seek "=", BCIDStr
.Edit
![Intro] = "1"
![Scope] = "2"
![BusNeed] = "3"
.Update
End With
rst.Close

 

[INeedAccessHelp]

The code stops here:

Set rst = db.OpenRecordset("Tbl", dbOpenTable)

with the Run-time error '3219':

Invalid operation.

 

[danvlas]

Is your table native or linked from another database? If it is linked, you can't open the recordset as tabletype-therefore you can't use Seek.

If the linked table comes from an Access database, you can use:

Set db = DbEngine.OpenDatabase("LinkedDbName&quot
and then
Set rst = db.OpenRecordset("Tbl", dbOpenTable)

If the table is linked from another source or you don't want to use OpenDatabase you'll have to open the recordset as dbOpenDynaset and use FindFirst instead of Seek...

Regards,

Dan

 

[INeedAccessHelp]

Ok. Here is my whole piece of code I'm attemting to use in an Access 2000 database. The Microsoft DAO 3.51 Object Library reference is checked. The table "BCTbl" is actually a linked table to an Access 97 database. How can I adjust this code to work? I have about 30 users waiting for me to fix this issue. Any assistance would be great! Thanks.


Dim BCTitle, BCdoc As String
Dim BShelp, BCIDStr As Integer

If IsNull(Me!Client) Then
MsgBox "You must choose a client.", vbExclamation
Exit Sub
Else
End If
If IsNull(Me!BCTitle) Then
MsgBox "You must enter a title.", vbExclamation
Exit Sub
Else
BCIDStr = Me![BCID]
BCTitle = Me![BCTitle]
BShelp = Me![SpecHelp]
DoCmd.Close acForm, "BCnewFrm", acSaveNo
DoCmd.OpenForm "TestBCcreate", acNormal, , "BCID =" & BCIDStr, , acHidden

If BShelp = 1 Then

Dim rst As Recordset
Dim db As Database
Set db = CurrentDb()
Set rst = db.OpenRecordset("BCTbl", dbOpenTable)

With rst
.Index = "BCID"
.Seek "=", BCIDStr
.Edit
![Intro] = "1"
![Scope] = "2"
![BusNeed] = "3"
.Update
End With
rst.Close
Else
'do nothing
End If

End If

 

[INeedAccessHelp]

Ok. Here is my whole piece of code I'm attemting to use in an Access 2000 database. The Microsoft DAO 3.51 Object Library reference is checked. The table "BCTbl" is actually a linked table to an Access 97 database. How can I adjust this code to work? I have about 30 users waiting for me to fix this issue. Any assistance would be great! Thanks.


Dim BCTitle, BCdoc As String
Dim BShelp, BCIDStr As Integer

If IsNull(Me!Client) Then
MsgBox "You must choose a client.", vbExclamation
Exit Sub
Else
End If
If IsNull(Me!BCTitle) Then
MsgBox "You must enter a title.", vbExclamation
Exit Sub
Else
BCIDStr = Me![BCID]
BCTitle = Me![BCTitle]
BShelp = Me![SpecHelp]
DoCmd.Close acForm, "BCnewFrm", acSaveNo
DoCmd.OpenForm "TestBCcreate", acNormal, , "BCID =" & BCIDStr, , acHidden

If BShelp = 1 Then

Dim rst As Recordset
Dim db As Database
Set db = CurrentDb()
Set rst = db.OpenRecordset("BCTbl", dbOpenTable)

With rst
.Index = "BCID"
.Seek "=", BCIDStr
.Edit
![Intro] = "1"
![Scope] = "2"
![BusNeed] = "3"
.Update
End With
rst.Close
Else
'do nothing
End If

End If

 

[INeedAccessHelp]

Here is how I corrected the problem. Thanks for all of your help.

Dim rst As Recordset
Dim db As Database
Set db = CurrentDb()
Set rst = db.OpenRecordset("BCTbl", dbOpenDynaset)

With rst
.FindFirst = "BCID =" & BCIDStr
.Edit
![Intro] = "1"
![Scope] = "2"
![BusNeed] = "3"
.Update
End With
rst.Close