delete records

[Tom]

I`m traying to create a query that delete records by dates.
I created a form with 2 unbound text boxes to enter dates.
I created a delete query that the cratiria is under date. between textbox1
and textbox2. it didnt work that well.

Is there a better way to delete records between dates?
What is the right way to do it?


Thanks a lot,

Tom

 

[Allen Browne]

What you describe sounds reasonable.

I am not sure what "didn't work that well" actually means, but try this:

1. In query design view, choose Parameters from the Query menu.
Enter 2 rows into the dialog that match the expression in your critiera.
For example, if the form is named Form1, the 2 rows would be:
[Forms].[Form1].[textbox1] Date/Time
[Forms].[Form1].[textbox2] Date/Time

2. If the text boxes are unbound, open the form in design view, and set the
Format property of both text boxes to:
Short Date
or similar.

This should ensure that both the form and the query understand the dates
correctly, and so the query deletes the right records.

If it still fails, switch the query to SQL View (View menu), and post the
SQL statement as a folllowup to this thread.

 

[Tom]

Thanks for your response, your answer gave me the confidance that what I did
was right.
I deleted all the elements and rebuild it, it works great now.
thanks,
Tom

What you describe sounds reasonable.

I am not sure what "didn't work that well" actually means, but try this:

1. In query design view, choose Parameters from the Query menu.
Enter 2 rows into the dialog that match the expression in your critiera.
For example, if the form is named Form1, the 2 rows would be:
[Forms].[Form1].[textbox1] Date/Time
[Forms].[Form1].[textbox2] Date/Time

2. If the text boxes are unbound, open the form in design view, and set
the Format property of both text boxes to:
Short Date
or similar.

This should ensure that both the form and the query understand the dates
correctly, and so the query deletes the right records.

If it still fails, switch the query to SQL View (View menu), and post the
SQL statement as a folllowup to this thread.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I`m traying to create a query that delete records by dates.
I created a form with 2 unbound text boxes to enter dates.
I created a delete query that the cratiria is under date. between
textbox1 and textbox2. it didnt work that well.

Is there a better way to delete records between dates?
What is the right way to do it?


Thanks a lot,

Tom

 

[Tom]

How can I insert those parametersn into a massage box?
I want to create a Mgsbox that say:
Are sure you want to delete all records beteen textbox1 and textboxs2?

Thanks,

Tom

 

[Allen Browne]

Since the values are in the text boxes on the form, you could read them from
there.

 

[Tom]

Thank you, but whay is the right syntax for that?
How do I put Value of a text box in the sring of the massage?

Thanks again,

Tom

 

[Guest]

msgbox ("Are sure you want to delete all records beteen " &
Forms![Formname]![textbox1] &" and " & Forms![Formname]![textboxs2] & "?")

 

[Tom]

Thank you so much.
Few more issues, please
When I run my Delete query, there 2 conformation from access, one about
commit a change and second about how many rows were deleted.
I would like to eliminate them in the code( I know I can go to "options" and
remove the checkmarks). If I choose "No" in one of these masssages I get run
time error because of my code.
second, I would like to create my own massage that notify about how many
records were deleted

Thanks for the help,

Tom

msgbox ("Are sure you want to delete all records beteen " &
Forms![Formname]![textbox1] &" and " & Forms![Formname]![textboxs2] &
"?")

Thank you, but whay is the right syntax for that?
How do I put Value of a text box in the sring of the massage?

Thanks again,

Tom

 

[Guest]

You can in your code turn off the wrning messages and turn them back on when
complete.
DoCmd.SetWarnings False
.....
DoCmd.SetWarnings true
and just use a messagebox to show how many records were deleted.

Thank you so much.
Few more issues, please
When I run my Delete query, there 2 conformation from access, one about
commit a change and second about how many rows were deleted.
I would like to eliminate them in the code( I know I can go to "options" and
remove the checkmarks). If I choose "No" in one of these masssages I get run
time error because of my code.
second, I would like to create my own massage that notify about how many
records were deleted

Thanks for the help,

Tom

msgbox ("Are sure you want to delete all records beteen " &
Forms![Formname]![textbox1] &" and " & Forms![Formname]![textboxs2] &
"?")

Thank you, but whay is the right syntax for that?
How do I put Value of a text box in the sring of the massage?

Thanks again,

Tom

Since the values are in the text boxes on the form, you could read them
from there.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

How can I insert those parametersn into a massage box?
I want to create a Mgsbox that say:
Are sure you want to delete all records beteen textbox1 and textboxs2?

Thanks,

Tom

I`m traying to create a query that delete records by dates.
I created a form with 2 unbound text boxes to enter dates.
I created a delete query that the cratiria is under date. between
textbox1 and textbox2. it didnt work that well.

Is there a better way to delete records between dates?
What is the right way to do it?

 

[Tom]

Thanks for your response,
I tried to use docmd.setwarnings=false, usually it eliminate the massages
but not in this case.
can tou help me with the syntax for the msgbox that show how many were
deleted?

Thanks a lot,
Tom

You can in your code turn off the wrning messages and turn them back on
when
complete.
DoCmd.SetWarnings False
....
DoCmd.SetWarnings true
and just use a messagebox to show how many records were deleted.

Thank you so much.
Few more issues, please
When I run my Delete query, there 2 conformation from access, one about
commit a change and second about how many rows were deleted.
I would like to eliminate them in the code( I know I can go to "options"
and
remove the checkmarks). If I choose "No" in one of these masssages I get
run
time error because of my code.
second, I would like to create my own massage that notify about how many
records were deleted

Thanks for the help,

Tom

msgbox ("Are sure you want to delete all records beteen " &
Forms![Formname]![textbox1] &" and " & Forms![Formname]![textboxs2] &
"?")

:

Thank you, but whay is the right syntax for that?
How do I put Value of a text box in the sring of the massage?

Thanks again,

Tom

Since the values are in the text boxes on the form, you could read
them
from there.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

How can I insert those parametersn into a massage box?
I want to create a Mgsbox that say:
Are sure you want to delete all records beteen textbox1 and
textboxs2?

Thanks,

Tom

I`m traying to create a query that delete records by dates.
I created a form with 2 unbound text boxes to enter dates.
I created a delete query that the cratiria is under date. between
textbox1 and textbox2. it didnt work that well.

Is there a better way to delete records between dates?
What is the right way to do it?

 

[Guest]

There is no "=" sign in the expression. Setting the warnings to false will
not show any confirmation or error messages. To show how many records are
deleted, you will have to first know how many records meet the criteria.
Something like:

Dim sqlst As String
Dim con As Object
Dim rs As Object
Dim RecordDelete as integer

sqlst = "Select Count([Primary Key from your table] as CountToDelete " _
&"From YourTable " _
&" Where yourDate between #" & [text1] & "# and #" & [Text2] &"#"

Set con = Application.CurrentProject.Connection
Set rs = CreateObject("ADODB.recordset")

rs.Open sqlst, con, 1

If not rs.eof then
RecordDelete = rs![CountToDelete]
else
RecordDelete =0
end if

Runyour delete queary

msgbox("You deleted " & RecordDelete & " records")

 

[Allen Browne]

Tom, you really need to Execute the query statement instead of using RunSQL.
There is no confirmation message to annoy you, but you can still determine
if an error occurred. You can also determine the number of records deleted.

This example assumes you want to delete from Table1, where the field named
MyDate is between the values in the 2 text boxes on the same form. It builds
the delete query string, executes it, generates a trappable error if the
deletion does not complete successfully, and reports how many records were
deleted.

Dim db As DAO.Database
Dim strSql As String
Const strcJetDate = "\#mm\/dd\/yyyy\#"

If IsDate(Me.txtStartDate) And IsDate(Me.txtEndDate) Then
Set db = dbEngine(0)(0)
strSql = "DELETE FROM Table1 WHERE [MyDate] Between " & _
Format(Me.txtStartDate, strcJetDate) & " And " & _
Format(Me.txtEndDate, strcJetDate) & ";"
db.Execute strSql, dbFailOnError
MsgBox db.RecordsAffected & " record(s) deleted."
Else
MsgBox "Both dates required."
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for your response,
I tried to use docmd.setwarnings=false, usually it eliminate the massages
but not in this case.
can tou help me with the syntax for the msgbox that show how many were
deleted?

Thanks a lot,
Tom

You can in your code turn off the wrning messages and turn them back on
when
complete.
DoCmd.SetWarnings False
....
DoCmd.SetWarnings true
and just use a messagebox to show how many records were deleted.

Thank you so much.
Few more issues, please
When I run my Delete query, there 2 conformation from access, one about
commit a change and second about how many rows were deleted.
I would like to eliminate them in the code( I know I can go to "options"
and
remove the checkmarks). If I choose "No" in one of these masssages I get
run
time error because of my code.
second, I would like to create my own massage that notify about how many
records were deleted

Thanks for the help,

Tom

msgbox ("Are sure you want to delete all records beteen " &
Forms![Formname]![textbox1] &" and " & Forms![Formname]![textboxs2] &
"?")

:

Thank you, but whay is the right syntax for that?
How do I put Value of a text box in the sring of the massage?

Thanks again,

Tom

Since the values are in the text boxes on the form, you could read
them
from there.

How can I insert those parametersn into a massage box?
I want to create a Mgsbox that say:
Are sure you want to delete all records beteen textbox1 and
textboxs2?

Thanks,

Tom

I`m traying to create a query that delete records by dates.
I created a form with 2 unbound text boxes to enter dates.
I created a delete query that the cratiria is under date. between
textbox1 and textbox2. it didnt work that well.

Is there a better way to delete records between dates?
What is the right way to do it?

 

[Tom]

Thank you both.
Allen, it works great.

Tom, you really need to Execute the query statement instead of using
RunSQL. There is no confirmation message to annoy you, but you can still
determine if an error occurred. You can also determine the number of
records deleted.

This example assumes you want to delete from Table1, where the field named
MyDate is between the values in the 2 text boxes on the same form. It
builds the delete query string, executes it, generates a trappable error
if the deletion does not complete successfully, and reports how many
records were deleted.

Dim db As DAO.Database
Dim strSql As String
Const strcJetDate = "\#mm\/dd\/yyyy\#"

If IsDate(Me.txtStartDate) And IsDate(Me.txtEndDate) Then
Set db = dbEngine(0)(0)
strSql = "DELETE FROM Table1 WHERE [MyDate] Between " & _
Format(Me.txtStartDate, strcJetDate) & " And " & _
Format(Me.txtEndDate, strcJetDate) & ";"
db.Execute strSql, dbFailOnError
MsgBox db.RecordsAffected & " record(s) deleted."
Else
MsgBox "Both dates required."
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for your response,
I tried to use docmd.setwarnings=false, usually it eliminate the massages
but not in this case.
can tou help me with the syntax for the msgbox that show how many were
deleted?

Thanks a lot,
Tom

You can in your code turn off the wrning messages and turn them back on
when
complete.
DoCmd.SetWarnings False
....
DoCmd.SetWarnings true
and just use a messagebox to show how many records were deleted.


:

Thank you so much.
Few more issues, please
When I run my Delete query, there 2 conformation from access, one about
commit a change and second about how many rows were deleted.
I would like to eliminate them in the code( I know I can go to
"options" and
remove the checkmarks). If I choose "No" in one of these masssages I
get run
time error because of my code.
second, I would like to create my own massage that notify about how
many
records were deleted

Thanks for the help,

Tom

msgbox ("Are sure you want to delete all records beteen " &
Forms![Formname]![textbox1] &" and " & Forms![Formname]![textboxs2]
&
"?")

:

Thank you, but whay is the right syntax for that?
How do I put Value of a text box in the sring of the massage?

Thanks again,

Tom

Since the values are in the text boxes on the form, you could read
them
from there.

How can I insert those parametersn into a massage box?
I want to create a Mgsbox that say:
Are sure you want to delete all records beteen textbox1 and
textboxs2?

Thanks,

Tom

I`m traying to create a query that delete records by dates.
I created a form with 2 unbound text boxes to enter dates.
I created a delete query that the cratiria is under date.
between
textbox1 and textbox2. it didnt work that well.

Is there a better way to delete records between dates?
What is the right way to do it?