I am trying to delete a record from an access database.

This code is written in a word userform...

Dim dbDatabase As Database
Dim rs As Recordset
Dim e As Integer
Dim d As Boolean

Set dbDatabase = OpenDatabase("C:\mydata1.mdb")
Set rs = dbDatabase.OpenRecordset("SELECT * FROM expressions ORDER by heb;",
dbOpenSnapshot)
Dim eStr As String
Dim answer As String
d = False
e = 0

With rs
Do Until .EOF
If ComboBox2.Text = ![heb] Then
answer = MsgBox("Do you want to delete this record: " &
ComboBox2.Text, vbQuestion + vbYesNo)
If answer = vbNo Then
Exit Sub
Else
answer = MsgBox("Are you shure you want to delete
this record: " & ComboBox2.Text, vbQuestion + vbYesNo)
If answer = vbNo Then
Exit Sub
Else
rs.Delete
MsgBox "the record " & ![heb] & " has been
deleted"
End If
End If
End If
.MoveNext
e = e + 1
Loop
End With
rs.Close
dbDatabase.Close



I get an error the rs.delete....

Can someone tell me what I am doing wrong please?

What is the error message?

--
Danny J. Lesandrini
(E-Mail Removed)
www.amazecreations.com


"iris" <(E-Mail Removed)> wrote in ...
>I am trying to delete a record from an access database.
>
> This code is written in a word userform...
>
> Dim dbDatabase As Database
> Dim rs As Recordset
> Dim e As Integer
> Dim d As Boolean
>
> Set dbDatabase = OpenDatabase("C:\mydata1.mdb")
> Set rs = dbDatabase.OpenRecordset("SELECT * FROM expressions ORDER by heb;",
> dbOpenSnapshot)
> Dim eStr As String
> Dim answer As String
> d = False
> e = 0
>
> With rs
> Do Until .EOF
> If ComboBox2.Text = ![heb] Then
> answer = MsgBox("Do you want to delete this record: " &
> ComboBox2.Text, vbQuestion + vbYesNo)
> If answer = vbNo Then
> Exit Sub
> Else
> answer = MsgBox("Are you shure you want to delete
> this record: " & ComboBox2.Text, vbQuestion + vbYesNo)
> If answer = vbNo Then
> Exit Sub
> Else
> rs.Delete
> MsgBox "the record " & ![heb] & " has been
> deleted"
> End If
> End If
> End If
> .MoveNext
> e = e + 1
> Loop
> End With
> rs.Close
> dbDatabase.Close
>
>
>
> I get an error the rs.delete....
>
> Can someone tell me what I am doing wrong please?

I don't know how to translate it - but its error 3251

"Danny J. Lesandrini" wrote:

> What is the error message?
>
> --
> Danny J. Lesandrini
> (E-Mail Removed)
> www.amazecreations.com
>
>
> "iris" <(E-Mail Removed)> wrote in ...
> >I am trying to delete a record from an access database.
> >
> > This code is written in a word userform...
> >
> > Dim dbDatabase As Database
> > Dim rs As Recordset
> > Dim e As Integer
> > Dim d As Boolean
> >
> > Set dbDatabase = OpenDatabase("C:\mydata1.mdb")
> > Set rs = dbDatabase.OpenRecordset("SELECT * FROM expressions ORDER by heb;",
> > dbOpenSnapshot)
> > Dim eStr As String
> > Dim answer As String
> > d = False
> > e = 0
> >
> > With rs
> > Do Until .EOF
> > If ComboBox2.Text = ![heb] Then
> > answer = MsgBox("Do you want to delete this record: " &
> > ComboBox2.Text, vbQuestion + vbYesNo)
> > If answer = vbNo Then
> > Exit Sub
> > Else
> > answer = MsgBox("Are you shure you want to delete
> > this record: " & ComboBox2.Text, vbQuestion + vbYesNo)
> > If answer = vbNo Then
> > Exit Sub
> > Else
> > rs.Delete
> > MsgBox "the record " & ![heb] & " has been
> > deleted"
> > End If
> > End If
> > End If
> > .MoveNext
> > e = e + 1
> > Loop
> > End With
> > rs.Close
> > dbDatabase.Close
> >
> >
> >
> > I get an error the rs.delete....
> >
> > Can someone tell me what I am doing wrong please?
>
>
>

On Sun, 15 Feb 2009 11:17:01 -0800, iris
<(E-Mail Removed)> wrote:

You are opening a Shapshot-type recordset, and those are ALWAYS
readonly. Rather open a Dynaset-type using dbOpenDynaset.

You can write a lot less code if you invoke a Delete query instead.

Another problem with your code is that you load ALL records from your
table whereas you only want to delete a single one (or possibly a
few). You should really add a WHERE clause to your sql statement
limiting the recordset to the interesting rows.

I'm puzzled by your Do Loop. If I read it correctly you are looping
over all records, checking each one to see if your combobox value is
the same as the "heb" field, and then ask if user wants to delete that
one. If yes, delete, loop around, and possibly find another one.
Since the user can't know which row we are on (you are not displaying
that information, and you don't have an ORDER BY clause) how would she
know which row to delete?

You are also exiting the loop after the first No. Presumably if user
says Yes we go find the next one, but if one No is issued that's it.
Very curious. Are you sure a delete query removing all heb's would not
be better?

-Tom.
Microsoft Access MVP



>I am trying to delete a record from an access database.
>
>This code is written in a word userform...
>
>Dim dbDatabase As Database
>Dim rs As Recordset
>Dim e As Integer
>Dim d As Boolean
>
>Set dbDatabase = OpenDatabase("C:\mydata1.mdb")
>Set rs = dbDatabase.OpenRecordset("SELECT * FROM expressions ORDER by heb;",
>dbOpenSnapshot)
>Dim eStr As String
>Dim answer As String
>d = False
>e = 0
>
>With rs
> Do Until .EOF
> If ComboBox2.Text = ![heb] Then
> answer = MsgBox("Do you want to delete this record: " &
>ComboBox2.Text, vbQuestion + vbYesNo)
> If answer = vbNo Then
> Exit Sub
> Else
> answer = MsgBox("Are you shure you want to delete
>this record: " & ComboBox2.Text, vbQuestion + vbYesNo)
> If answer = vbNo Then
> Exit Sub
> Else
> rs.Delete
> MsgBox "the record " & ![heb] & " has been
>deleted"
> End If
> End If
> End If
> .MoveNext
> e = e + 1
> Loop
> End With
>rs.Close
>dbDatabase.Close
>
>
>
>I get an error the rs.delete....
>
>Can someone tell me what I am doing wrong please?

I want to delete only the recordset that is = to combobox2.text....



"Danny J. Lesandrini" wrote:

> What is the error message?
>
> --
> Danny J. Lesandrini
> (E-Mail Removed)
> www.amazecreations.com
>
>
> "iris" <(E-Mail Removed)> wrote in ...
> >I am trying to delete a record from an access database.
> >
> > This code is written in a word userform...
> >
> > Dim dbDatabase As Database
> > Dim rs As Recordset
> > Dim e As Integer
> > Dim d As Boolean
> >
> > Set dbDatabase = OpenDatabase("C:\mydata1.mdb")
> > Set rs = dbDatabase.OpenRecordset("SELECT * FROM expressions ORDER by heb;",
> > dbOpenSnapshot)
> > Dim eStr As String
> > Dim answer As String
> > d = False
> > e = 0
> >
> > With rs
> > Do Until .EOF
> > If ComboBox2.Text = ![heb] Then
> > answer = MsgBox("Do you want to delete this record: " &
> > ComboBox2.Text, vbQuestion + vbYesNo)
> > If answer = vbNo Then
> > Exit Sub
> > Else
> > answer = MsgBox("Are you shure you want to delete
> > this record: " & ComboBox2.Text, vbQuestion + vbYesNo)
> > If answer = vbNo Then
> > Exit Sub
> > Else
> > rs.Delete
> > MsgBox "the record " & ![heb] & " has been
> > deleted"
> > End If
> > End If
> > End If
> > .MoveNext
> > e = e + 1
> > Loop
> > End With
> > rs.Close
> > dbDatabase.Close
> >
> >
> >
> > I get an error the rs.delete....
> >
> > Can someone tell me what I am doing wrong please?
>
>
>

The error message might map to this ...

Object or provider is not capable of
performing requested operation

Here's my guess. You have an Access XP database, which sets the ADO library
by default. You added the DAO library afterwards, but the ADO library shows up
higher in the references list, so this declaration ...

Dim rs As Recordset

is being interpreted as
Dim rs As ADODB.Recordset

Try doing an explicit declaration, or removing the ADO library reference.
Change the recordset declaration to this and try again ...

Dim rs As DAO.Recordset

--
Danny J. Lesandrini
(E-Mail Removed)
www.amazecreations.com


"iris" <(E-Mail Removed)> wrote ...
>I don't know how to translate it - but its error 3251
>
> "Danny J. Lesandrini" wrote:
>
>> What is the error message?
>>
>> --
>> Danny J. Lesandrini
>> (E-Mail Removed)
>> www.amazecreations.com
>>
>>
>> "iris" <(E-Mail Removed)> wrote in ...
>> >I am trying to delete a record from an access database.
>> >
>> > This code is written in a word userform...
>> >
>> > Dim dbDatabase As Database
>> > Dim rs As Recordset
>> > Dim e As Integer
>> > Dim d As Boolean
>> >
>> > Set dbDatabase = OpenDatabase("C:\mydata1.mdb")
>> > Set rs = dbDatabase.OpenRecordset("SELECT * FROM expressions ORDER by heb;",
>> > dbOpenSnapshot)

Identifying the row isn't the problem. Your code will work, though there's a better way.

Dim sSQL As String

sSQL = DELETE FROM expressions WHERE heb = " & ComboBox2.Text
dbDatabase.Execute sSQL

If the field [heb] is a text field, you need to delimit the combo box text with single quotes.
If it's a date, delimit it with Pound Signs (#).

This removes the need to loop through records and simply deletes the desired row,
provided the user has OKd it.

--
Danny J. Lesandrini
(E-Mail Removed)
www.amazecreations.com


"iris" <(E-Mail Removed)> wrote ...
>I want to delete only the recordset that is = to combobox2.text....
>
>
>
> "Danny J. Lesandrini" wrote:
>
>> What is the error message?
>>
>> --
>> Danny J. Lesandrini
>> (E-Mail Removed)
>> www.amazecreations.com
>>
>>
>> "iris" <(E-Mail Removed)> wrote in ...
>> >I am trying to delete a record from an access database.
>> >
>> > This code is written in a word userform...
>> >
>> > Dim dbDatabase As Database
>> > Dim rs As Recordset
>> > Dim e As Integer
>> > Dim d As Boolean
>> >
>> > Set dbDatabase = OpenDatabase("C:\mydata1.mdb")
>> > Set rs = dbDatabase.OpenRecordset("SELECT * FROM expressions ORDER by heb;",
>> > dbOpenSnapshot)
>> > Dim eStr As String
>> > Dim answer As String
>> > d = False
>> > e = 0
>> >
>> > With rs
>> > Do Until .EOF
>> > If ComboBox2.Text = ![heb] Then
>> > answer = MsgBox("Do you want to delete this record: " &
>> > ComboBox2.Text, vbQuestion + vbYesNo)
>> > If answer = vbNo Then
>> > Exit Sub
>> > Else
>> > answer = MsgBox("Are you shure you want to delete
>> > this record: " & ComboBox2.Text, vbQuestion + vbYesNo)
>> > If answer = vbNo Then
>> > Exit Sub
>> > Else
>> > rs.Delete
>> > MsgBox "the record " & ![heb] & " has been
>> > deleted"
>> > End If
>> > End If
>> > End If
>> > .MoveNext
>> > e = e + 1
>> > Loop
>> > End With
>> > rs.Close
>> > dbDatabase.Close
>> >
>> >
>> >
>> > I get an error the rs.delete....
>> >
>> > Can someone tell me what I am doing wrong please?
>>
>>
>>

Ouch!

Darn-it Tom, why didn't I notice that! Great catch!

--
Danny J. Lesandrini
(E-Mail Removed)
www.amazecreations.com


"Tom van Stiphout" <(E-Mail Removed)> wrote in ...
> On Sun, 15 Feb 2009 11:17:01 -0800, iris
> <(E-Mail Removed)> wrote:
>
> You are opening a Shapshot-type recordset, and those are ALWAYS
> readonly. Rather open a Dynaset-type using dbOpenDynaset.
>

On Sun, 15 Feb 2009 12:38:14 -0700, "Danny J. Lesandrini"
<(E-Mail Removed)> wrote:

Not in this case, because OpenDatabase is only valid for DAO.
-Tom.


>The error message might map to this ...
>
> Object or provider is not capable of
> performing requested operation
>
>Here's my guess. You have an Access XP database, which sets the ADO library
>by default. You added the DAO library afterwards, but the ADO library shows up
>higher in the references list, so this declaration ...
>
> Dim rs As Recordset
>
>is being interpreted as
> Dim rs As ADODB.Recordset
>
>Try doing an explicit declaration, or removing the ADO library reference.
>Change the recordset declaration to this and try again ...
>
> Dim rs As DAO.Recordset

All of you are right about the looping problem...

actually, their can be only one correct value in "heb" - so there is no need
to loop through all the database...

I will try your suggestion with the
sSQL = DELETE FROM expressions WHERE heb = " & ComboBox2.Text
dbDatabase.Execute sSQL

and update U.

Thank You!

"Tom van Stiphout" wrote:

> On Sun, 15 Feb 2009 11:17:01 -0800, iris
> <(E-Mail Removed)> wrote:
>
> You are opening a Shapshot-type recordset, and those are ALWAYS
> readonly. Rather open a Dynaset-type using dbOpenDynaset.
>
> You can write a lot less code if you invoke a Delete query instead.
>
> Another problem with your code is that you load ALL records from your
> table whereas you only want to delete a single one (or possibly a
> few). You should really add a WHERE clause to your sql statement
> limiting the recordset to the interesting rows.
>
> I'm puzzled by your Do Loop. If I read it correctly you are looping
> over all records, checking each one to see if your combobox value is
> the same as the "heb" field, and then ask if user wants to delete that
> one. If yes, delete, loop around, and possibly find another one.
> Since the user can't know which row we are on (you are not displaying
> that information, and you don't have an ORDER BY clause) how would she
> know which row to delete?
>
> You are also exiting the loop after the first No. Presumably if user
> says Yes we go find the next one, but if one No is issued that's it.
> Very curious. Are you sure a delete query removing all heb's would not
> be better?
>
> -Tom.
> Microsoft Access MVP
>
>
>
> >I am trying to delete a record from an access database.
> >
> >This code is written in a word userform...
> >
> >Dim dbDatabase As Database
> >Dim rs As Recordset
> >Dim e As Integer
> >Dim d As Boolean
> >
> >Set dbDatabase = OpenDatabase("C:\mydata1.mdb")
> >Set rs = dbDatabase.OpenRecordset("SELECT * FROM expressions ORDER by heb;",
> >dbOpenSnapshot)
> >Dim eStr As String
> >Dim answer As String
> >d = False
> >e = 0
> >
> >With rs
> > Do Until .EOF
> > If ComboBox2.Text = ![heb] Then
> > answer = MsgBox("Do you want to delete this record: " &
> >ComboBox2.Text, vbQuestion + vbYesNo)
> > If answer = vbNo Then
> > Exit Sub
> > Else
> > answer = MsgBox("Are you shure you want to delete
> >this record: " & ComboBox2.Text, vbQuestion + vbYesNo)
> > If answer = vbNo Then
> > Exit Sub
> > Else
> > rs.Delete
> > MsgBox "the record " & ![heb] & " has been
> >deleted"
> > End If
> > End If
> > End If
> > .MoveNext
> > e = e + 1
> > Loop
> > End With
> >rs.Close
> >dbDatabase.Close
> >
> >
> >
> >I get an error the rs.delete....
> >
> >Can someone tell me what I am doing wrong please?
>