Delete a record from a database

I

iris

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

This is my code:

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?
 
D

Doug Robbins - Word MVP

The following works for me:

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

Set dbDatabase = OpenDatabase("C:\access\db1.mdb")
Set rs = dbDatabase.OpenRecordset("SELECT * FROM `P and IDs`")
Dim eStr As String
Dim answer As String
d = False
e = 0
Dim rec As Recordset
With rs
Do Until .EOF
If rs.Fields("FileName").Value = "3113-J-1030.PDF" Then
answer = MsgBox("Do you want to delete this record: ", vbQuestion
+ vbYesNo)
If answer = vbNo Then
Exit Sub
Else
answer = MsgBox("Are you shure you want to delete
this record: ", vbQuestion + vbYesNo)
If answer = vbNo Then
Exit Sub
Else
rs.Delete
End If
End If
End If
.MoveNext
e = e + 1
Loop
End With
rs.Close
dbDatabase.Close

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP
 
I

iris

Thank you very much!

It works for me to.

Doug Robbins - Word MVP said:
The following works for me:

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

Set dbDatabase = OpenDatabase("C:\access\db1.mdb")
Set rs = dbDatabase.OpenRecordset("SELECT * FROM `P and IDs`")
Dim eStr As String
Dim answer As String
d = False
e = 0
Dim rec As Recordset
With rs
Do Until .EOF
If rs.Fields("FileName").Value = "3113-J-1030.PDF" Then
answer = MsgBox("Do you want to delete this record: ", vbQuestion
+ vbYesNo)
If answer = vbNo Then
Exit Sub
Else
answer = MsgBox("Are you shure you want to delete
this record: ", vbQuestion + vbYesNo)
If answer = vbNo Then
Exit Sub
Else
rs.Delete
End If
End If
End If
.MoveNext
e = e + 1
Loop
End With
rs.Close
dbDatabase.Close

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

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

This is my code:

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?
Click to expand...
Click to expand...
 
I

iris

Thank you very much!

Iris

Peter Jamieson said:
AIUI you cannot alter the data in a snapshot, i.e. don't use
dbOpenSnapshot if you want to alter data.

Peter Jamieson

http://tips.pjmsn.me.uk
I am trying to delete a record from an access database.

This is my code:

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?
Click to expand...
Click to expand...
 
I

iris

Hi Doug

I have another problem... hope you can help me...

In a Word Userform I have textbox1.

I need to populate this textbox with the currrent date when I open the
userform... so the user will not have to choose the current date from a
calsendar...

do you have an idea hoe to do that?

Thank You for everything

Iris

Doug Robbins - Word MVP said:
The following works for me:

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

Set dbDatabase = OpenDatabase("C:\access\db1.mdb")
Set rs = dbDatabase.OpenRecordset("SELECT * FROM `P and IDs`")
Dim eStr As String
Dim answer As String
d = False
e = 0
Dim rec As Recordset
With rs
Do Until .EOF
If rs.Fields("FileName").Value = "3113-J-1030.PDF" Then
answer = MsgBox("Do you want to delete this record: ", vbQuestion
+ vbYesNo)
If answer = vbNo Then
Exit Sub
Else
answer = MsgBox("Are you shure you want to delete
this record: ", vbQuestion + vbYesNo)
If answer = vbNo Then
Exit Sub
Else
rs.Delete
End If
End If
End If
.MoveNext
e = e + 1
Loop
End With
rs.Close
dbDatabase.Close

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

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

This is my code:

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?
Click to expand...
Click to expand...
 
G

Graham Mayor

In the calling macro you could use

With Formname
.TextBox1.Value = Format(Date, "dd/MM/yyyy")
.Show
End With

Set the date format to your preference
--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

Hi Doug

I have another problem... hope you can help me...

In a Word Userform I have textbox1.

I need to populate this textbox with the currrent date when I open the
userform... so the user will not have to choose the current date from
a calsendar...

do you have an idea hoe to do that?

Thank You for everything

Iris

Doug Robbins - Word MVP said:
The following works for me:

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

Set dbDatabase = OpenDatabase("C:\access\db1.mdb")
Set rs = dbDatabase.OpenRecordset("SELECT * FROM `P and IDs`")
Dim eStr As String
Dim answer As String
d = False
e = 0
Dim rec As Recordset
With rs
Do Until .EOF
If rs.Fields("FileName").Value = "3113-J-1030.PDF" Then
answer = MsgBox("Do you want to delete this record: ",
vbQuestion + vbYesNo)
If answer = vbNo Then
Exit Sub
Else
answer = MsgBox("Are you shure you want to
delete this record: ", vbQuestion + vbYesNo)
If answer = vbNo Then
Exit Sub
Else
rs.Delete
End If
End If
End If
.MoveNext
e = e + 1
Loop
End With
rs.Close
dbDatabase.Close

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

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

This is my code:

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?
Click to expand...
Click to expand...
Click to expand...
 
I

iris

Hi Graham,

I wrote this code:

Private Sub UserForm_Initialize()
With UserForm8
.TextBox1.Value = Format(Date, "dd/mm/yyyy")
.Show
End With
End Sub

This code does insert the date to the textbox, but When I exit the form I
get an error:
object variable or with block varaiable not set

do you see what can be the problem here?

Graham Mayor said:
In the calling macro you could use

With Formname
.TextBox1.Value = Format(Date, "dd/MM/yyyy")
.Show
End With

Set the date format to your preference
--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

Hi Doug

I have another problem... hope you can help me...

In a Word Userform I have textbox1.

I need to populate this textbox with the currrent date when I open the
userform... so the user will not have to choose the current date from
a calsendar...

do you have an idea hoe to do that?

Thank You for everything

Iris

Doug Robbins - Word MVP said:
The following works for me:

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

Set dbDatabase = OpenDatabase("C:\access\db1.mdb")
Set rs = dbDatabase.OpenRecordset("SELECT * FROM `P and IDs`")
Dim eStr As String
Dim answer As String
d = False
e = 0
Dim rec As Recordset
With rs
Do Until .EOF
If rs.Fields("FileName").Value = "3113-J-1030.PDF" Then
answer = MsgBox("Do you want to delete this record: ",
vbQuestion + vbYesNo)
If answer = vbNo Then
Exit Sub
Else
answer = MsgBox("Are you shure you want to
delete this record: ", vbQuestion + vbYesNo)
If answer = vbNo Then
Exit Sub
Else
rs.Delete
End If
End If
End If
.MoveNext
e = e + 1
Loop
End With
rs.Close
dbDatabase.Close

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

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

This is my code:

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?
Click to expand...
Click to expand...
Click to expand...
 
I

iris

problem solved....

I wrote this code... without the "with" statement... and it works:

Private Sub UserForm_Initialize()
UserForm1.TextBox8.Value = Format(Date, "dd/mm/yyyy")
End Sub


Graham Mayor said:
In the calling macro you could use

With Formname
.TextBox1.Value = Format(Date, "dd/MM/yyyy")
.Show
End With

Set the date format to your preference
--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

Hi Doug

I have another problem... hope you can help me...

In a Word Userform I have textbox1.

I need to populate this textbox with the currrent date when I open the
userform... so the user will not have to choose the current date from
a calsendar...

do you have an idea hoe to do that?

Thank You for everything

Iris

Doug Robbins - Word MVP said:
The following works for me:

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

Set dbDatabase = OpenDatabase("C:\access\db1.mdb")
Set rs = dbDatabase.OpenRecordset("SELECT * FROM `P and IDs`")
Dim eStr As String
Dim answer As String
d = False
e = 0
Dim rec As Recordset
With rs
Do Until .EOF
If rs.Fields("FileName").Value = "3113-J-1030.PDF" Then
answer = MsgBox("Do you want to delete this record: ",
vbQuestion + vbYesNo)
If answer = vbNo Then
Exit Sub
Else
answer = MsgBox("Are you shure you want to
delete this record: ", vbQuestion + vbYesNo)
If answer = vbNo Then
Exit Sub
Else
rs.Delete
End If
End If
End If
.MoveNext
e = e + 1
Loop
End With
rs.Close
dbDatabase.Close

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

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

This is my code:

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?
Click to expand...
Click to expand...
Click to expand...
 
I

iris

problem solved.... Thank You!

Private Sub UserForm_Initialize()
UserForm8.TextBox8.Value = Format(Date, "dd/mm/yyyy")
End Sub

iris said:
Hi Doug

I have another problem... hope you can help me...

In a Word Userform I have textbox1.

I need to populate this textbox with the currrent date when I open the
userform... so the user will not have to choose the current date from a
calsendar...

do you have an idea hoe to do that?

Thank You for everything

Iris

Doug Robbins - Word MVP said:
The following works for me:

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

Set dbDatabase = OpenDatabase("C:\access\db1.mdb")
Set rs = dbDatabase.OpenRecordset("SELECT * FROM `P and IDs`")
Dim eStr As String
Dim answer As String
d = False
e = 0
Dim rec As Recordset
With rs
Do Until .EOF
If rs.Fields("FileName").Value = "3113-J-1030.PDF" Then
answer = MsgBox("Do you want to delete this record: ", vbQuestion
+ vbYesNo)
If answer = vbNo Then
Exit Sub
Else
answer = MsgBox("Are you shure you want to delete
this record: ", vbQuestion + vbYesNo)
If answer = vbNo Then
Exit Sub
Else
rs.Delete
End If
End If
End If
.MoveNext
e = e + 1
Loop
End With
rs.Close
dbDatabase.Close

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

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

This is my code:

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?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Deleting a recordset in Access database 13
VBA-Table Word 2019 - Move a selected text, from a cell to the adjacent cell to its right, by pressing the ENTER key 0
Making a macro only work on certain files 2
Building an array within another array ... 18
building an array for a listbox 2
cascading listboxes 4
cascading listboxes 0
cascading listboxs 1

Top