Runtime Error 3251 Help 1

[DawsonUK]

Hi, any help would be appreciated on this, and I suspect I know the reason as to why it is happening - I recently split my database into a front and back end.

I have the following code to delete records on alot of my forms....

Dim D As Database
Dim T As Recordset
If MsgBox("Do you wish to delete this supervisor?", 292) = 6 Then
Set D = CurrentDb()
Set T = D.OpenRecordset("Supervisors&quot
T.Index = "PrimaryKey"
T.Seek "=", Me![Full Name]
T.Delete
'Requery the form to make the deleted record disappear
Me.Requery
End If

Since I split the Database and added security, I get the error "Runtime error 3251 : Operation is not supported for this type of object". Upon pressing the debug button it goes straight to this line of code...
T.Index = "PrimaryKey"

I understand that it may be because my Tables are not local to the database file anymore, or something to that effect, but I'm not completely sure on how to fix it, I only started doing the basics of Access Basic coding a few weeks ago, and haven't done anything more complicated than changing the focus of things and changing visibility of items.

Any help would be greatfully appreciated, and if necessary I can email the database files to people if it would help.

Thanks in advance for any help,

David Dawson

 

[RickSpr]

Your analysis of the error is correct. When the table was in the same database as the code, your OpenRecordset defaulted to creating a table-type recordset, which has an Index property among other things. Since the split, OpenRecordset is by default creating a dynaset-type recordset, because table-type recordsets aren't supported on external tables. But dynasets don't have Index properties, or Seek methods, hence the error.

I'll assume the table's primary key column is called EmpName. To fix this, replace the Index and Seek calls with:

    T.FindFirst "EmpName='" & Me![FullName] & "'"
    If Not T.NoMatch Then T.Delete

In a dynaset-type recordset, you no longer have to select the index--Access chooses one at the time the query is compiled.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[DawsonUK]

Hi, Thanks for the quick reply Rick!

I worked out another way through some Googling...
From This....
Dim D As Database
Dim T As Recordset
Set D = CurrentDb()
Set T = D.OpenRecordset("Supervisors&quot

To This...
Dim D As DAO.Database
Dim T As DAO.Recordset
Set D = DBEngine(0).OpenDatabase("H:\Database\Access Files\New System\students_dats.mdb&quot
Set T = D.OpenRecordset("Courses", dbOpenTable)

Which works, but means when the database is eventually finished and moved to a shared area on the server, near every form would have to be updated.

Your way is far better for my needs that what I managed to find, thanks very much! A well deserved star for you!

If anyone else is interested, the code which worked was
T.FindFirst "Supervisor ID" = Me![Supervisor ID]
If Not T.NoMatch Then T.Delete

Which replaced...
T.Index = "PrimaryKey"
T.Seek "=", Me![Full Name]


Excellent, Thanks again

David

 

[RickSpr]

David,

I think you typo'ed there. The line
T.FindFirst "Supervisor ID" = Me![SupervisorID]
would make the first parameter of FindFirst the Boolean value of the expression "Supervisor ID" = Me![SupervisorID]. The first parameter must be a string.

Did you mean:
T.FindFirst "Supervisor ID=" & Me![SupervisorID]?

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[DawsonUK]

Yes, I think thats what I went. I just spotted that the one I typed in deletes the wrong record. Would

T.FindFirst "Supervisor ID=" & Me![SupervisorID]?

Work correctly?
Bearing in mind that Supervisor is a Unique Primary Key Field? (This code will also be used in loads of other forms to as both the code behind Delete buttons, and Cancel buttons.

Thanks again,
David


Thanks,
David

 

[DawsonUK]

Hi, your code...
T.FindFirst "Supervisor ID=" & Me![SupervisorID]
Doesn't want to work

I cant get anything to work either. Put simply, on the form there is an Autonumber field called Supervisor ID, and I want to delete that record, without using Access's standard Delete button, which I don't like.

David

 

[RickSpr]

Taking a guess: Do you have a control on the form bound to SupervisorID? If you do, it's the name of that control that should be in [brackets].

If you don't have such a control, you need to add one. You can make it invisible if you like.

Otherwise, I'm afraid I need more to go on than "doesn't want to work." (You might want to read the FAQ mentioned in my signature.)

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[DawsonUK]

Hi, I feel like such a dumbo, I just couldn't get the bracketing correct.

After reading up on the FindFirst method, I was trying to put "'" & "'" around values, and It just wouldnt work.

But all is working fine now, and I'm very grateful for the help :-D


Thanks again, This forum really is one of the greatest things on the web I've stumbled across! So good for reference!

David