Run delete query instead of report...

A

Ann

I am using MS Access 2002. I have a response table, tblSurveyMonkeyAppend.
I have a listbox that gives me the names of courses, [txtCourseTitle] from
this table. Sometimes a course can have one response or many responses. I
use a listbox of course titles to choose one or more courses. Then I run a
report for just those courses. Here is the code:

Private Sub cmdBarChart_Click()

Dim frm As Form, ctl As ListBox, var As Variant
Dim strCriteria As String, temp As String

Set frm = Forms!frmSurveyMonkeyParameter
Set ctl = frm!lstCourses

'If no selection, display warning and exit
If ctl.ItemsSelected.Count = 0 Then
MsgBox "Please select a course."
Exit Sub
'builds SQL WHERE clause
'using each of the selected projects
Else
For Each var In ctl.ItemsSelected
temp = "[txtCourseTitle] = " & Chr(39) & _
ctl.ItemData(var) & Chr(39) & " Or "
strCriteria = strCriteria & temp
Next var
End If

'deletes the final Or from the WHERE clause
strCriteria = Left$(strCriteria, Len(strCriteria) - 4)

'outputs report
On Error GoTo ErrorOpen
DoCmd.OpenReport "rptBarCharts", acViewPreview, , strCriteria

ExitOpen:
Exit Sub

ErrorOpen:
If Err = 2501 Then
Resume ExitOpen
Else
MsgBox Err.Description
Resume ExitOpen
End If

Set ctl = Nothing
Set frm = Nothing

End Sub

What I want to know is can this code be altered to run a delete query with
the txtCourseTitle chosen instead of a report. I've tried to change the
DoCmd.OpenReport "rptBarCharts", acViewPreview, , strCriteria line to open
the delete query but I get an error that it can't be found. I am not a
programmer so I really don't know if this would even work if it could find
the query. These responses need to be deleted at the end of each quarter and
brought down again and they are cumulative. Thanks in advance for any help
you can offer.
 
A

Argyronet

Hi,

If I all understood what you want to do, you select one or more items in a
listbox, then you want to show (see) the rows (record) which have to be
delete.
And instead of a report, the the concerned records..
If so, you have to solutions :
The first one is to create dynamically the query with the appropriate
parameters and then, open the query in normal mode; Then you can click on the
exclamation mark button to execute the query showed as a datasheet. This is
the better short way...

To create the query, you have to check before if it exists before and then
if so, modifying the SQL property and if not, create it with the SQL script
for deletion.

Example :
Private Sub OpenQBE(ByVal TableName As String)
Const VOID_QUERY As String = "qry_DeleteMyRecords"
Dim oQDF As DAO.QueryDef
Dim F As Integer
Dim SQLQuery As String
On Error Resume Next
Set oQDF = CurrentDb.QueryDefs(VOID_QUERY)
If Err = 0 Then
DoCmd.DeleteObject acQuery, VOID_QUERY
End If
SQLQuery = "DELETE * FROM " & TableName
Set oQDF = CurrentDb.CreateQueryDef(VOID_QUERY, SQLQuery)
Set oQDF = Nothing
DoCmd.OpenQuery VOID_QUERY, acViewNormal
DoCmd.Maximize
End Sub

The 2nd solution consists to build a form with a Recordset source depending
of your selection from the ListBox on the AfterUpdate or Click event.
subForm.RecordSource = "SELECT * FROM MyTable WHERE txtCourseTitle IN (" &
strCriteria &");

It could be designed into the same form and displayed in a subform. Then you
click on a command button labelled "Delete those records" where the event
procedure is simply :

If MsgBox("Delete above records ?", vbQuestion,"Confirm")=vbYes Then
CurrentDB.Excecute "DELETE * FROM MyTable WHERE txtCourseTitle IN (" &
strCriteria &");
End If

Regards,
--
Argy
Goto : http://argyronet.developpez.com/
Livres :
Créez des programmes avec Microsoft Access 2007 (ISBN 2742982442)
VBA pour Office 2007 (ISBN 2742983910)



Ann said:
I am using MS Access 2002. I have a response table, tblSurveyMonkeyAppend.
I have a listbox that gives me the names of courses, [txtCourseTitle] from
this table. Sometimes a course can have one response or many responses. I
use a listbox of course titles to choose one or more courses. Then I run a
report for just those courses. Here is the code:

Private Sub cmdBarChart_Click()

Dim frm As Form, ctl As ListBox, var As Variant
Dim strCriteria As String, temp As String

Set frm = Forms!frmSurveyMonkeyParameter
Set ctl = frm!lstCourses

'If no selection, display warning and exit
If ctl.ItemsSelected.Count = 0 Then
MsgBox "Please select a course."
Exit Sub
'builds SQL WHERE clause
'using each of the selected projects
Else
For Each var In ctl.ItemsSelected
temp = "[txtCourseTitle] = " & Chr(39) & _
ctl.ItemData(var) & Chr(39) & " Or "
strCriteria = strCriteria & temp
Next var
End If

'deletes the final Or from the WHERE clause
strCriteria = Left$(strCriteria, Len(strCriteria) - 4)

'outputs report
On Error GoTo ErrorOpen
DoCmd.OpenReport "rptBarCharts", acViewPreview, , strCriteria

ExitOpen:
Exit Sub

ErrorOpen:
If Err = 2501 Then
Resume ExitOpen
Else
MsgBox Err.Description
Resume ExitOpen
End If

Set ctl = Nothing
Set frm = Nothing

End Sub

What I want to know is can this code be altered to run a delete query with
the txtCourseTitle chosen instead of a report. I've tried to change the
DoCmd.OpenReport "rptBarCharts", acViewPreview, , strCriteria line to open
the delete query but I get an error that it can't be found. I am not a
programmer so I really don't know if this would even work if it could find
the query. These responses need to be deleted at the end of each quarter and
brought down again and they are cumulative. Thanks in advance for any help
you can offer.
Click to expand...
 
A

Ann

Thanks for the help. I'm going to try the second option first. I'll let you
know how it goes.

Argyronet said:
Hi,

If I all understood what you want to do, you select one or more items in a
listbox, then you want to show (see) the rows (record) which have to be
delete.
And instead of a report, the the concerned records..
If so, you have to solutions :
The first one is to create dynamically the query with the appropriate
parameters and then, open the query in normal mode; Then you can click on the
exclamation mark button to execute the query showed as a datasheet. This is
the better short way...

To create the query, you have to check before if it exists before and then
if so, modifying the SQL property and if not, create it with the SQL script
for deletion.

Example :
Private Sub OpenQBE(ByVal TableName As String)
Const VOID_QUERY As String = "qry_DeleteMyRecords"
Dim oQDF As DAO.QueryDef
Dim F As Integer
Dim SQLQuery As String
On Error Resume Next
Set oQDF = CurrentDb.QueryDefs(VOID_QUERY)
If Err = 0 Then
DoCmd.DeleteObject acQuery, VOID_QUERY
End If
SQLQuery = "DELETE * FROM " & TableName
Set oQDF = CurrentDb.CreateQueryDef(VOID_QUERY, SQLQuery)
Set oQDF = Nothing
DoCmd.OpenQuery VOID_QUERY, acViewNormal
DoCmd.Maximize
End Sub

The 2nd solution consists to build a form with a Recordset source depending
of your selection from the ListBox on the AfterUpdate or Click event.
subForm.RecordSource = "SELECT * FROM MyTable WHERE txtCourseTitle IN (" &
strCriteria &");

It could be designed into the same form and displayed in a subform. Then you
click on a command button labelled "Delete those records" where the event
procedure is simply :

If MsgBox("Delete above records ?", vbQuestion,"Confirm")=vbYes Then
CurrentDB.Excecute "DELETE * FROM MyTable WHERE txtCourseTitle IN (" &
strCriteria &");
End If

Regards,
--
Argy
Goto : http://argyronet.developpez.com/
Livres :
Créez des programmes avec Microsoft Access 2007 (ISBN 2742982442)
VBA pour Office 2007 (ISBN 2742983910)



Ann said:
I am using MS Access 2002. I have a response table, tblSurveyMonkeyAppend.
I have a listbox that gives me the names of courses, [txtCourseTitle] from
this table. Sometimes a course can have one response or many responses. I
use a listbox of course titles to choose one or more courses. Then I run a
report for just those courses. Here is the code:

Private Sub cmdBarChart_Click()

Dim frm As Form, ctl As ListBox, var As Variant
Dim strCriteria As String, temp As String

Set frm = Forms!frmSurveyMonkeyParameter
Set ctl = frm!lstCourses

'If no selection, display warning and exit
If ctl.ItemsSelected.Count = 0 Then
MsgBox "Please select a course."
Exit Sub
'builds SQL WHERE clause
'using each of the selected projects
Else
For Each var In ctl.ItemsSelected
temp = "[txtCourseTitle] = " & Chr(39) & _
ctl.ItemData(var) & Chr(39) & " Or "
strCriteria = strCriteria & temp
Next var
End If

'deletes the final Or from the WHERE clause
strCriteria = Left$(strCriteria, Len(strCriteria) - 4)

'outputs report
On Error GoTo ErrorOpen
DoCmd.OpenReport "rptBarCharts", acViewPreview, , strCriteria

ExitOpen:
Exit Sub

ErrorOpen:
If Err = 2501 Then
Resume ExitOpen
Else
MsgBox Err.Description
Resume ExitOpen
End If

Set ctl = Nothing
Set frm = Nothing

End Sub

What I want to know is can this code be altered to run a delete query with
the txtCourseTitle chosen instead of a report. I've tried to change the
DoCmd.OpenReport "rptBarCharts", acViewPreview, , strCriteria line to open
the delete query but I get an error that it can't be found. I am not a
programmer so I really don't know if this would even work if it could find
the query. These responses need to be deleted at the end of each quarter and
brought down again and they are cumulative. Thanks in advance for any help
you can offer.
Click to expand...
Click to expand...
 
A

Ann

Hi,

I am having problems. I received an error, Method or data member not found
on the .RecordSource part of the code for the subform.

subfrmSurveyMonkeyDeleteResponses.RecordSource = "SELECT * FROM
tblSurveyMonkeyAppend WHERE txtCourseTitle IN (" & strCriteria & ");"


Argyronet said:
Hi,

If I all understood what you want to do, you select one or more items in a
listbox, then you want to show (see) the rows (record) which have to be
delete.
And instead of a report, the the concerned records..
If so, you have to solutions :
The first one is to create dynamically the query with the appropriate
parameters and then, open the query in normal mode; Then you can click on the
exclamation mark button to execute the query showed as a datasheet. This is
the better short way...

To create the query, you have to check before if it exists before and then
if so, modifying the SQL property and if not, create it with the SQL script
for deletion.

Example :
Private Sub OpenQBE(ByVal TableName As String)
Const VOID_QUERY As String = "qry_DeleteMyRecords"
Dim oQDF As DAO.QueryDef
Dim F As Integer
Dim SQLQuery As String
On Error Resume Next
Set oQDF = CurrentDb.QueryDefs(VOID_QUERY)
If Err = 0 Then
DoCmd.DeleteObject acQuery, VOID_QUERY
End If
SQLQuery = "DELETE * FROM " & TableName
Set oQDF = CurrentDb.CreateQueryDef(VOID_QUERY, SQLQuery)
Set oQDF = Nothing
DoCmd.OpenQuery VOID_QUERY, acViewNormal
DoCmd.Maximize
End Sub

The 2nd solution consists to build a form with a Recordset source depending
of your selection from the ListBox on the AfterUpdate or Click event.
subForm.RecordSource = "SELECT * FROM MyTable WHERE txtCourseTitle IN (" &
strCriteria &");

It could be designed into the same form and displayed in a subform. Then you
click on a command button labelled "Delete those records" where the event
procedure is simply :

If MsgBox("Delete above records ?", vbQuestion,"Confirm")=vbYes Then
CurrentDB.Excecute "DELETE * FROM MyTable WHERE txtCourseTitle IN (" &
strCriteria &");
End If

Regards,
--
Argy
Goto : http://argyronet.developpez.com/
Livres :
Créez des programmes avec Microsoft Access 2007 (ISBN 2742982442)
VBA pour Office 2007 (ISBN 2742983910)



Ann said:
I am using MS Access 2002. I have a response table, tblSurveyMonkeyAppend.
I have a listbox that gives me the names of courses, [txtCourseTitle] from
this table. Sometimes a course can have one response or many responses. I
use a listbox of course titles to choose one or more courses. Then I run a
report for just those courses. Here is the code:

Private Sub cmdBarChart_Click()

Dim frm As Form, ctl As ListBox, var As Variant
Dim strCriteria As String, temp As String

Set frm = Forms!frmSurveyMonkeyParameter
Set ctl = frm!lstCourses

'If no selection, display warning and exit
If ctl.ItemsSelected.Count = 0 Then
MsgBox "Please select a course."
Exit Sub
'builds SQL WHERE clause
'using each of the selected projects
Else
For Each var In ctl.ItemsSelected
temp = "[txtCourseTitle] = " & Chr(39) & _
ctl.ItemData(var) & Chr(39) & " Or "
strCriteria = strCriteria & temp
Next var
End If

'deletes the final Or from the WHERE clause
strCriteria = Left$(strCriteria, Len(strCriteria) - 4)

'outputs report
On Error GoTo ErrorOpen
DoCmd.OpenReport "rptBarCharts", acViewPreview, , strCriteria

ExitOpen:
Exit Sub

ErrorOpen:
If Err = 2501 Then
Resume ExitOpen
Else
MsgBox Err.Description
Resume ExitOpen
End If

Set ctl = Nothing
Set frm = Nothing

End Sub

What I want to know is can this code be altered to run a delete query with
the txtCourseTitle chosen instead of a report. I've tried to change the
DoCmd.OpenReport "rptBarCharts", acViewPreview, , strCriteria line to open
the delete query but I get an error that it can't be found. I am not a
programmer so I really don't know if this would even work if it could find
the query. These responses need to be deleted at the end of each quarter and
brought down again and they are cumulative. Thanks in advance for any help
you can offer.
Click to expand...
Click to expand...
 
J

John Spencer

Looks to me like your SQL string is incorrect. Also you are not assigning to
the form in the subform control.

Dim strSQL as String 'Add this so you can check the SQL string for validity
'Before you attempt to use it.

strSQL = "SELECT * FROM tblSurveyMonkeyAppend WHERE " & strCriteria
'Add the following two code lines to check the accuracy of the SQL string
'Once you are satisfied you have a valid SQL string then continue on.
Debug.Print strSQL 'Temporary DEBUG line
Stop 'Temporary DEBUG line
Me.SubFormControlName.Form.ControlSource = StrSQL

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Hi,

I am having problems. I received an error, Method or data member not found
on the .RecordSource part of the code for the subform.

subfrmSurveyMonkeyDeleteResponses.RecordSource = "SELECT * FROM
tblSurveyMonkeyAppend WHERE txtCourseTitle IN (" & strCriteria & ");"


Argyronet said:
Hi,

If I all understood what you want to do, you select one or more items in a
listbox, then you want to show (see) the rows (record) which have to be
delete.
And instead of a report, the the concerned records..
If so, you have to solutions :
The first one is to create dynamically the query with the appropriate
parameters and then, open the query in normal mode; Then you can click on the
exclamation mark button to execute the query showed as a datasheet. This is
the better short way...

To create the query, you have to check before if it exists before and then
if so, modifying the SQL property and if not, create it with the SQL script
for deletion.

Example :
Private Sub OpenQBE(ByVal TableName As String)
Const VOID_QUERY As String = "qry_DeleteMyRecords"
Dim oQDF As DAO.QueryDef
Dim F As Integer
Dim SQLQuery As String
On Error Resume Next
Set oQDF = CurrentDb.QueryDefs(VOID_QUERY)
If Err = 0 Then
DoCmd.DeleteObject acQuery, VOID_QUERY
End If
SQLQuery = "DELETE * FROM " & TableName
Set oQDF = CurrentDb.CreateQueryDef(VOID_QUERY, SQLQuery)
Set oQDF = Nothing
DoCmd.OpenQuery VOID_QUERY, acViewNormal
DoCmd.Maximize
End Sub

The 2nd solution consists to build a form with a Recordset source depending
of your selection from the ListBox on the AfterUpdate or Click event.
subForm.RecordSource = "SELECT * FROM MyTable WHERE txtCourseTitle IN (" &
strCriteria &");

It could be designed into the same form and displayed in a subform. Then you
click on a command button labelled "Delete those records" where the event
procedure is simply :

If MsgBox("Delete above records ?", vbQuestion,"Confirm")=vbYes Then
CurrentDB.Excecute "DELETE * FROM MyTable WHERE txtCourseTitle IN (" &
strCriteria &");
End If

Regards,
--
Argy
Goto : http://argyronet.developpez.com/
Livres :
Créez des programmes avec Microsoft Access 2007 (ISBN 2742982442)
VBA pour Office 2007 (ISBN 2742983910)



Ann said:
I am using MS Access 2002. I have a response table, tblSurveyMonkeyAppend.
I have a listbox that gives me the names of courses, [txtCourseTitle] from
this table. Sometimes a course can have one response or many responses. I
use a listbox of course titles to choose one or more courses. Then I run a
report for just those courses. Here is the code:

Private Sub cmdBarChart_Click()

Dim frm As Form, ctl As ListBox, var As Variant
Dim strCriteria As String, temp As String

Set frm = Forms!frmSurveyMonkeyParameter
Set ctl = frm!lstCourses

'If no selection, display warning and exit
If ctl.ItemsSelected.Count = 0 Then
MsgBox "Please select a course."
Exit Sub
'builds SQL WHERE clause
'using each of the selected projects
Else
For Each var In ctl.ItemsSelected
temp = "[txtCourseTitle] = " & Chr(39) & _
ctl.ItemData(var) & Chr(39) & " Or "
strCriteria = strCriteria & temp
Next var
End If

'deletes the final Or from the WHERE clause
strCriteria = Left$(strCriteria, Len(strCriteria) - 4)

'outputs report
On Error GoTo ErrorOpen
DoCmd.OpenReport "rptBarCharts", acViewPreview, , strCriteria

ExitOpen:
Exit Sub

ErrorOpen:
If Err = 2501 Then
Resume ExitOpen
Else
MsgBox Err.Description
Resume ExitOpen
End If

Set ctl = Nothing
Set frm = Nothing

End Sub

What I want to know is can this code be altered to run a delete query with
the txtCourseTitle chosen instead of a report. I've tried to change the
DoCmd.OpenReport "rptBarCharts", acViewPreview, , strCriteria line to open
the delete query but I get an error that it can't be found. I am not a
programmer so I really don't know if this would even work if it could find
the query. These responses need to be deleted at the end of each quarter and
brought down again and they are cumulative. Thanks in advance for any help
you can offer.
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

Multiple Criteria in Listbox 3
Multiple Criteria in Code 13
Blank Report Message 2
Adding code to an existing COMMAND BUTTON 4
List Box Selection to Run report 3
More Problems with List Box 2
labels report not linking to identifier 1
Run-Timer Error: "2302" on report generation 1

Top