Deleting & Re-querying From A Union Query

[Ella]

My main form, frmReview, contains a sub form, frmReviewSub, that displays in
a spreadsheet format from a union query.
To delete a record the user selects a record in the sub-form and then
presses a button to open a pop-up form to gives the user the option to press
a cancel button to take no action or to continue by pressing a delete button.
When the delete button is pressed the pop-up form closes, runs the delete
query and requerys the sub form in my main form, code below.
The problem is that the requery is frustrating the users by returning the
sub form back to the first record, which means they need to scroll back
through the records to find where they were at before the delete.
Is there a way to prevent this from happening and or to select the next
record after the deleted record?

I hope someone can help!

Regards
Ella

Private Sub cmdDeleteActionItem_Click()
On Error GoTo Err_cmdDeleteActionItem_Click
'frmDeleteItem
Dim stDocName As String

DoCmd.Close

stDocName = "QfrmDeleteItem"
DoCmd.OpenQuery stDocName, acNormal, acEdit

[Forms]![frmReview]![frmReviewSub].Requery

Exit_cmdDeleteActionItem_Click:
Exit Sub

Err_cmdDeleteActionItem_Click:
MsgBox Err.Description
Resume Exit_cmdDeleteActionItem_Click

End Sub

 

[Marshall Barton]

My main form, frmReview, contains a sub form, frmReviewSub, that displays in
a spreadsheet format from a union query.
To delete a record the user selects a record in the sub-form and then
presses a button to open a pop-up form to gives the user the option to press
a cancel button to take no action or to continue by pressing a delete button.
When the delete button is pressed the pop-up form closes, runs the delete
query and requerys the sub form in my main form, code below.
The problem is that the requery is frustrating the users by returning the
sub form back to the first record, which means they need to scroll back
through the records to find where they were at before the delete.
Is there a way to prevent this from happening and or to select the next
record after the deleted record?

Private Sub cmdDeleteActionItem_Click()
On Error GoTo Err_cmdDeleteActionItem_Click
'frmDeleteItem
Dim stDocName As String

DoCmd.Close

stDocName = "QfrmDeleteItem"
DoCmd.OpenQuery stDocName, acNormal, acEdit

[Forms]![frmReview]![frmReviewSub].Requery

Exit_cmdDeleteActionItem_Click:
Exit Sub

Err_cmdDeleteActionItem_Click:
MsgBox Err.Description
Resume Exit_cmdDeleteActionItem_Click

End Sub

You need to save the record's position in the subform's
recordset and (re)locate the record after the requery.

To assure that the delete has actually completed when the
requery runs, you should use the Execute method instead of
OpenQuery. Also, later versions of Access no longer accept
the abbreviated subform referencing syntax so you should
always use the Form property when referring to a subform
property or method.

The general idea of the code could be similar to this:

Private Sub cmdDeleteActionItem_Click()
On Error GoTo Err_cmdDeleteActionItem_Click
'frmDeleteItem
Dim db As Database
Dim lngRecordNum As Long

With Forms!frmReview!frmReviewSub.FORM
lngRecordNum = .CurrentRecord
Set db = CurrentDb()
db.Execute "QfrmDeleteItem", dbFailOnError
.Requery
With .Recordset
.MoveLast
If lngRecordNum <= .RecordCount Then
.MoveFirst
.Move lngRecordNum - 1
End If
End With
DoCmd.Close
End With

Exit_cmdDeleteActionItem_Click:
. . .
End Sub

 

[Ella]

Thanks for your reply Marsh.
I pasted in your code and got a message about On Error GoTo
Err_cmdDeleteActionItem_Click, so I copied and modified this from another
piece of code, Err_cmdDeleteActionItem_Click:
MsgBox Err.Description
Resume Exit_cmdDeleteActionItem_Click
So now when I press the delete button I get a pop-up, which states - To few
parameters, Expected 1. And when I click OK nothing happens.
I have included the code below.
With regards to your comments they are a bit complex as I am relatively new
to access I’m not sure what to do next.
Hope you will persist with me?

Ella


Private Sub cmdDeleteActionItem_Click()
On Error GoTo Err_cmdDeleteActionItem_Click
'frmDeleteItem
Dim db As Database
Dim lngRecordNum As Long

With Forms!frmReview!frmReviewSub.Form
lngRecordNum = .CurrentRecord
Set db = CurrentDb()
db.Execute "QfrmDeleteItem", dbFailOnError
.Requery
With .Recordset.MoveLast
If lngRecordNum <= .RecordCount Then
.MoveFirst
.Move lngRecordNum - 1
End If
End With
DoCmd.Close
End With

Exit_cmdDeleteActionItem_Click:
Exit Sub

Err_cmdDeleteActionItem_Click:
MsgBox Err.Description
Resume Exit_cmdDeleteActionItem_Click

End Sub

My main form, frmReview, contains a sub form, frmReviewSub, that displays in
a spreadsheet format from a union query.
To delete a record the user selects a record in the sub-form and then
presses a button to open a pop-up form to gives the user the option to press
a cancel button to take no action or to continue by pressing a delete button.
When the delete button is pressed the pop-up form closes, runs the delete
query and requerys the sub form in my main form, code below.
The problem is that the requery is frustrating the users by returning the
sub form back to the first record, which means they need to scroll back
through the records to find where they were at before the delete.
Is there a way to prevent this from happening and or to select the next
record after the deleted record?

Private Sub cmdDeleteActionItem_Click()
On Error GoTo Err_cmdDeleteActionItem_Click
'frmDeleteItem
Dim stDocName As String

DoCmd.Close

stDocName = "QfrmDeleteItem"
DoCmd.OpenQuery stDocName, acNormal, acEdit

[Forms]![frmReview]![frmReviewSub].Requery

Exit_cmdDeleteActionItem_Click:
Exit Sub

Err_cmdDeleteActionItem_Click:
MsgBox Err.Description
Resume Exit_cmdDeleteActionItem_Click

End Sub

You need to save the record's position in the subform's
recordset and (re)locate the record after the requery.

To assure that the delete has actually completed when the
requery runs, you should use the Execute method instead of
OpenQuery. Also, later versions of Access no longer accept
the abbreviated subform referencing syntax so you should
always use the Form property when referring to a subform
property or method.

The general idea of the code could be similar to this:

Private Sub cmdDeleteActionItem_Click()
On Error GoTo Err_cmdDeleteActionItem_Click
'frmDeleteItem
Dim db As Database
Dim lngRecordNum As Long

With Forms!frmReview!frmReviewSub.FORM
lngRecordNum = .CurrentRecord
Set db = CurrentDb()
db.Execute "QfrmDeleteItem", dbFailOnError
.Requery
With .Recordset
.MoveLast
If lngRecordNum <= .RecordCount Then
.MoveFirst
.Move lngRecordNum - 1
End If
End With
DoCmd.Close
End With

Exit_cmdDeleteActionItem_Click:
. . .
End Sub

 

[Marshall Barton]

So now when I press the delete button I get a pop-up, which states - To few
parameters, Expected 1. And when I click OK nothing happens.
I have included the code below.
With regards to your comments they are a bit complex as I am relatively new
to access I’m not sure what to do next.

Private Sub cmdDeleteActionItem_Click()
On Error GoTo Err_cmdDeleteActionItem_Click
'frmDeleteItem
Dim db As Database
Dim lngRecordNum As Long

With Forms!frmReview!frmReviewSub.Form
lngRecordNum = .CurrentRecord
Set db = CurrentDb()
db.Execute "QfrmDeleteItem", dbFailOnError
.Requery
With .Recordset.MoveLast
If lngRecordNum <= .RecordCount Then
.MoveFirst
.Move lngRecordNum - 1
End If
End With
DoCmd.Close
End With

Exit_cmdDeleteActionItem_Click: [snip]

Your line:
With .Recordset.MoveLast
is supposed to be two lines:
With .Recordset
.MoveLast

However, the error implies that there is something wrong in
your delete query. The reason is almost always because the
query contains a name that is not in the query's table or
because it refers to a control on the form. Unlike
OpenQuery, Execute does not automatically take care of
criteria like:
somefield = someform!somecontrol

Instead of executing a saved query, you can just use a
constructed SQL statement:

Dim strSQL As String
strSQL = "DELETE * FROM sometable " _
& "WHERE somefield=" & Me.somefield
db.Execute strSQL, dbFailOnError

If that's too confusing, I may be able to be more precise if
you'll post a Copy/Paste of the query's SQL view.

 

[Ella]

Marsh,
You’re right I am confused, very much so.
As you suggested I have posted the delete query SQL.

I do appreciate your assistance.

Ella

DELETE tbDetails.SADID, tbDetails.SADTypeID, tbDetails.DateEntered,
tbDetails.DateCompleted, tbDetails.DateToBeCompleted,
tbDetails.DateToBeCompletedReminder, tbDetails.UserAssigned,
tbDetails.UserEditing, tbDetails.UserEnteredBy, tbDetails.UserCompletedByID,
tbDetails.Initator, tbDetails.InitatorSource, tbDetails.Description,
tbDetails.Notes, tbDetails.Priority, tbDetails.ITNumber,
tbDetails.SelectedBy, tbDetails.Selected, tbDetails.Closed, tbDetails.File,
tbDetails.Actions
FROM tbDetails
WHERE (((tbDetails.SADID)=[Forms]![frmReview]![frmReviewSub]![SADID]));

So now when I press the delete button I get a pop-up, which states - To few
parameters, Expected 1. And when I click OK nothing happens.
I have included the code below.
With regards to your comments they are a bit complex as I am relatively new
to access I’m not sure what to do next.

Private Sub cmdDeleteActionItem_Click()
On Error GoTo Err_cmdDeleteActionItem_Click
'frmDeleteItem
Dim db As Database
Dim lngRecordNum As Long

With Forms!frmReview!frmReviewSub.Form
lngRecordNum = .CurrentRecord
Set db = CurrentDb()
db.Execute "QfrmDeleteItem", dbFailOnError
.Requery
With .Recordset.MoveLast
If lngRecordNum <= .RecordCount Then
.MoveFirst
.Move lngRecordNum - 1
End If
End With
DoCmd.Close
End With

Exit_cmdDeleteActionItem_Click: [snip]

Your line:
With .Recordset.MoveLast
is supposed to be two lines:
With .Recordset
.MoveLast

However, the error implies that there is something wrong in
your delete query. The reason is almost always because the
query contains a name that is not in the query's table or
because it refers to a control on the form. Unlike
OpenQuery, Execute does not automatically take care of
criteria like:
somefield = someform!somecontrol

Instead of executing a saved query, you can just use a
constructed SQL statement:

Dim strSQL As String
strSQL = "DELETE * FROM sometable " _
& "WHERE somefield=" & Me.somefield
db.Execute strSQL, dbFailOnError

If that's too confusing, I may be able to be more precise if
you'll post a Copy/Paste of the query's SQL view.

 

[Marshall Barton]

That's what I suspected. Try this:

Dim strSQL As String
strSQL = "DELETE * FROM tbDetails " _
& "WHERE SADID=" & Me.SADID
db.Execute strSQL, dbFailOnError

Don't forget to fix the With statement too.

If you are doing this with live data, be sure to test on a
copy of the table.
--
Marsh
MVP [MS Access]

Marsh,
You’re right I am confused, very much so.
As you suggested I have posted the delete query SQL.

DELETE tbDetails.SADID, tbDetails.SADTypeID, tbDetails.DateEntered,
tbDetails.DateCompleted, tbDetails.DateToBeCompleted,
tbDetails.DateToBeCompletedReminder, tbDetails.UserAssigned,
tbDetails.UserEditing, tbDetails.UserEnteredBy, tbDetails.UserCompletedByID,
tbDetails.Initator, tbDetails.InitatorSource, tbDetails.Description,
tbDetails.Notes, tbDetails.Priority, tbDetails.ITNumber,
tbDetails.SelectedBy, tbDetails.Selected, tbDetails.Closed, tbDetails.File,
tbDetails.Actions
FROM tbDetails
WHERE (((tbDetails.SADID)=[Forms]![frmReview]![frmReviewSub]![SADID]));

So now when I press the delete button I get a pop-up, which states - To few
parameters, Expected 1. And when I click OK nothing happens.
I have included the code below.
With regards to your comments they are a bit complex as I am relatively new
to access I’m not sure what to do next.

Private Sub cmdDeleteActionItem_Click()
On Error GoTo Err_cmdDeleteActionItem_Click
'frmDeleteItem
Dim db As Database
Dim lngRecordNum As Long

With Forms!frmReview!frmReviewSub.Form
lngRecordNum = .CurrentRecord
Set db = CurrentDb()
db.Execute "QfrmDeleteItem", dbFailOnError
.Requery
With .Recordset.MoveLast
If lngRecordNum <= .RecordCount Then
.MoveFirst
.Move lngRecordNum - 1
End If
End With
DoCmd.Close
End With

Exit_cmdDeleteActionItem_Click: [snip]

Your line:
With .Recordset.MoveLast
is supposed to be two lines:
With .Recordset
.MoveLast

However, the error implies that there is something wrong in
your delete query. The reason is almost always because the
query contains a name that is not in the query's table or
because it refers to a control on the form. Unlike
OpenQuery, Execute does not automatically take care of
criteria like:
somefield = someform!somecontrol

Instead of executing a saved query, you can just use a
constructed SQL statement:

Dim strSQL As String
strSQL = "DELETE * FROM sometable " _
& "WHERE somefield=" & Me.somefield
db.Execute strSQL, dbFailOnError

 

[Ella]

Where and how do I insert the code, does it go in the query or in the click
event of the form?
I changed the With statement so that it is two line, is that what you are
referring to?

That's what I suspected. Try this:

Dim strSQL As String
strSQL = "DELETE * FROM tbDetails " _
& "WHERE SADID=" & Me.SADID
db.Execute strSQL, dbFailOnError

Don't forget to fix the With statement too.

If you are doing this with live data, be sure to test on a
copy of the table.
--
Marsh
MVP [MS Access]

Marsh,
You’re right I am confused, very much so.
As you suggested I have posted the delete query SQL.

DELETE tbDetails.SADID, tbDetails.SADTypeID, tbDetails.DateEntered,
tbDetails.DateCompleted, tbDetails.DateToBeCompleted,
tbDetails.DateToBeCompletedReminder, tbDetails.UserAssigned,
tbDetails.UserEditing, tbDetails.UserEnteredBy, tbDetails.UserCompletedByID,
tbDetails.Initator, tbDetails.InitatorSource, tbDetails.Description,
tbDetails.Notes, tbDetails.Priority, tbDetails.ITNumber,
tbDetails.SelectedBy, tbDetails.Selected, tbDetails.Closed, tbDetails.File,
tbDetails.Actions
FROM tbDetails
WHERE (((tbDetails.SADID)=[Forms]![frmReview]![frmReviewSub]![SADID]));

Ella wrote:
So now when I press the delete button I get a pop-up, which states - To few
parameters, Expected 1. And when I click OK nothing happens.
I have included the code below.
With regards to your comments they are a bit complex as I am relatively new
to access I’m not sure what to do next.

Private Sub cmdDeleteActionItem_Click()
On Error GoTo Err_cmdDeleteActionItem_Click
'frmDeleteItem
Dim db As Database
Dim lngRecordNum As Long

With Forms!frmReview!frmReviewSub.Form
lngRecordNum = .CurrentRecord
Set db = CurrentDb()
db.Execute "QfrmDeleteItem", dbFailOnError
.Requery
With .Recordset.MoveLast
If lngRecordNum <= .RecordCount Then
.MoveFirst
.Move lngRecordNum - 1
End If
End With
DoCmd.Close
End With

Exit_cmdDeleteActionItem_Click:
[snip]




Your line:
With .Recordset.MoveLast
is supposed to be two lines:
With .Recordset
.MoveLast

However, the error implies that there is something wrong in
your delete query. The reason is almost always because the
query contains a name that is not in the query's table or
because it refers to a control on the form. Unlike
OpenQuery, Execute does not automatically take care of
criteria like:
somefield = someform!somecontrol

Instead of executing a saved query, you can just use a
constructed SQL statement:

Dim strSQL As String
strSQL = "DELETE * FROM sometable " _
& "WHERE somefield=" & Me.somefield
db.Execute strSQL, dbFailOnError

 

[Marshall Barton]

Where and how do I insert the code, does it go in the query or in the click
event of the form?
I changed the With statement so that it is two line, is that what you are
referring to?

Everything is now being done in the button's click event
procedure and you no longer need your query to do this.

Private Sub cmdDeleteActionItem_Click()
Dim db As Database
Dim lngRecordNum As Long
Dim strSQL As String
On Error GoTo Err_cmdDeleteActionItem_Click

With Forms!frmReview!frmReviewSub.Form
lngRecordNum = .CurrentRecord
Set db = CurrentDb()
strSQL = "DELETE * FROM tbDetails " _
& "WHERE SADID=" & Me.SADID
db.Execute strSQL, dbFailOnError
.Requery
With .Recordset
.MoveLast
If lngRecordNum <= .RecordCount Then
.MoveFirst
.Move lngRecordNum - 1
End If
End With
DoCmd.Close
End With

Exit_cmdDeleteActionItem_Click:
Exit Sub

Err_cmdDeleteActionItem_Click:
MsgBox Err.Description
Resume Exit_cmdDeleteActionItem_Click
End Sub

 

[Ella]

Thanks Marsh,
I copied the code into my form but it brings up a Compile error: “Method or
data member not foundâ€. When I click OK it highlights the Me.SADID part of
the following code.
strSQL = "DELETE * FROM tbDetails " _
& "WHERE SADID=" & Me.SADID

I just can’t figure out what it means.

Regards
Ella

 

[Marshall Barton]

That usually means that the SADID field is neither a field
in the form's record source table/query nor a control on the
form. Perhaps the field has a different name in the record
source?
--
Marsh
MVP [MS Access]

I copied the code into my form but it brings up a Compile error: “Method or
data member not found”. When I click OK it highlights the Me.SADID part of
the following code.
strSQL = "DELETE * FROM tbDetails " _
& "WHERE SADID=" & Me.SADID

[snip]

 

[Ella]

Success,
I replaced the Me.SADID below;
strSQL = "DELETE * FROM tbDetails " _
& "WHERE SADID=" & Me.SADID
and changed it to;
strSQL = "DELETE * FROM tbDetails " _
& "WHERE SADID = " & Forms!frmReview!frmReviewSub!SADID
It seems to work OK although after the deletion the next record is selected
and this record is displayed at the bottom of the sub form. This only happens
when there are more records to display than can be seen in the sub-form. The
records jump around a bit but at least the next selected record is still
highlighted at the bottom of the form.

Unless you have any suggestions that may prevent this jumping around I would
say you have succeeded in solving my issue.
I really appreciated your help.

Thanks very much and have a very merry Christmas.

Ella

That usually means that the SADID field is neither a field
in the form's record source table/query nor a control on the
form. Perhaps the field has a different name in the record
source?
--
Marsh
MVP [MS Access]

I copied the code into my form but it brings up a Compile error: “Method or
data member not foundâ€. When I click OK it highlights the Me.SADID part of
the following code.
strSQL = "DELETE * FROM tbDetails " _
& "WHERE SADID=" & Me.SADID

[snip]

 

[Marshall Barton]

Success,
I replaced the Me.SADID below;
strSQL = "DELETE * FROM tbDetails " _
& "WHERE SADID=" & Me.SADID
and changed it to;
strSQL = "DELETE * FROM tbDetails " _
& "WHERE SADID = " & Forms!frmReview!frmReviewSub!SADID
It seems to work OK although after the deletion the next record is selected
and this record is displayed at the bottom of the sub form. This only happens
when there are more records to display than can be seen in the sub-form. The
records jump around a bit but at least the next selected record is still
highlighted at the bottom of the form.

Unless you have any suggestions that may prevent this jumping around I would
say you have succeeded in solving my issue.
I really appreciated your help.

That's good news ;-)

Note that your abbreviated subform reference is no longer
allowed in later versions of Access. It should be:
Forms!frmReview!frmReviewSub.FORM!SADID

The only way I have heard of to keep the current record in
the same position on the screen is to manipulate the
vertical scroll bar. I have never used it, but if you feel
adventurous take a look at the GetSetSB sample db at
www.lebans.com