wileedingo
asked on
Problem deleting a record.
I have a form which is based on a query which in turn draws data from a number of related tables. When viewing this data I would like it to be possible to delete the related record from one of these tables by clicking a button, then have the form automatically requery to show one less record. This table is always at the Many end of any One-to-Many relationships.
What I have so far deletes the record, but then it sort of locks up. The message when I try to select a new record is that I "may be at the end of the recordset", although I can see there are more records. When I close and reopen the form, the deleted record is gone. Trying the Requery method, as shown below, nets the error message: Run-time error '3167': Record is deleted.
Here is my test button's code with Requery:
Private Sub DeleteStudent_Click()
Dim db As Database
Dim rst1 As Recordset
Set db = CurrentDb()
Set rst1 = db.OpenRecordset("RegData"
With rst1
.Index = "PrimaryKey"
.Seek "=", Me!record_no
If .NoMatch Then
MsgBox "No record to delete"
Else
.Delete
MsgBox "Student deleted from this class"
End If
End With
Me.Requery
End Sub
???
wileedingo
What I have so far deletes the record, but then it sort of locks up. The message when I try to select a new record is that I "may be at the end of the recordset", although I can see there are more records. When I close and reopen the form, the deleted record is gone. Trying the Requery method, as shown below, nets the error message: Run-time error '3167': Record is deleted.
Here is my test button's code with Requery:
Private Sub DeleteStudent_Click()
Dim db As Database
Dim rst1 As Recordset
Set db = CurrentDb()
Set rst1 = db.OpenRecordset("RegData"
With rst1
.Index = "PrimaryKey"
.Seek "=", Me!record_no
If .NoMatch Then
MsgBox "No record to delete"
Else
.Delete
MsgBox "Student deleted from this class"
End If
End With
Me.Requery
End Sub
???
wileedingo
Another (simpler) way might be to add a movenext after the .delete:
with rst1
.delete
.movenext
if .eof() and not .bof() then
.moveprevious
endif
end with
Then you should be on a valid undeleted record
Good luck
Macbeth
with rst1
.delete
.movenext
if .eof() and not .bof() then
.moveprevious
endif
end with
Then you should be on a valid undeleted record
Good luck
Macbeth
But, the problem seems to be related to the form and not to the recordset from the code?
I'm not sure. I think the form gets into trouble when referring to a deleted record - I'm not 100% sure how Access handles the requery, but I believe that it wants to return to the current record - which is deleted.
So all I have to do is to get to a valid record (either the next or the previous)
So all I have to do is to get to a valid record (either the next or the previous)
Yes, but your code still moves in the recordset and not in the form itself.
Well, lets here it from wileedingo.
Well, lets here it from wileedingo.
Trygve - you are right - didn't really get it the first time...
The concept still might work - when using
docmd.gotorecord ,acNext
and
docmd.gotorecord ,acPrevious
instead of .movenext and .moveprevious
I will test this approach when I have some spare time.
Good luck
Macbeth
docmd.gotorecord ,acNext
and
docmd.gotorecord ,acPrevious
instead of .movenext and .moveprevious
I will test this approach when I have some spare time.
Good luck
Macbeth
I though of that, ut then we still need to keep the ID of current post and move before deleting the record. We will also possibly need to check for the boundaries...
Since you will need to requery to get the recordset without the deleted record, then perhaps my suggestion is quite as elegant?
Since you will need to requery to get the recordset without the deleted record, then perhaps my suggestion is quite as elegant?
True...
ASKER
Hi.
Have tried Tryvge's solution but it stopped on Me.Filter. "Invalid use of property".
Attempted Macbeth's solution but it did the same as before - deleted the record and wouldn't let me select the next one. The problem here is the .movenext will apply to the recordset called up by the delete button's code, not to the set of records being displayed.
Using the docmd.gotorecord ,acNext version generated a different error message: Run Time Error 2489. The object '1' isn't open. * The objectname argument for the GoToRecord, RepaintObject, or SelectObject method names an object that is closed. Use one of the Open actions or methods to open the object so that you can carry out the desired action.
I played with Macbeth's solution a bit more as I had tried something like it already. Since the recordset called up by the button is not the same as that used by the form, I tried putting the movenext command first:
With rst1
.Index = "PrimaryKey"
.Seek "=", varID
If .NoMatch Then
MsgBox "No record to delete"
Else
DoCmd.GoToRecord , , acNext
If .EOF() And Not .BOF() Then
DoCmd.GoToRecord , , acPrevious
End If
.Delete
MsgBox "Student deleted from this class"
End If
End With
This had the effect of moving to another record and then deleting the desired one (e.g. record #3). You can then move around the remaining records, but if you try to move from record #4 to record #2, the form crashes when it encounters the deleted record #3.
The problem now is that the form also has code for the On Current event. I assume that the On Current code runs just before the next record is shown and then ends. I could live with a record that says "#Deleted" everywhere, but it seems that the form can't. The offending statement in the On Current code is:
Set rst1 = db.OpenRecordset("SELECT RegData.*, Schedule.Date FROM Schedule INNER JOIN RegData ON Schedule.SchedCode = RegData.SchedCode " & _
"WHERE (RegData.HSE_studentNo = " & Me!HSE_studentNo & ") AND RegData.CourseCode= " & Me!CourseCode)
This works fine when there is a record available, however, when this statement is attempted for a deleted record, the form is not able to get values for Me!HSE_studentNo or Me!CourseCode, and I am sent the misleading error message that there is an extra ')' in the statement. The whole thing would work in a wobbly fashion, I think, if I could instead say something like...
If Me!CourseCode = Deleted Then
DoCmd.GoToRecord , , acNext (with elaborations for boundaries)
Else
Set rst1 = ....
End If
....but '= Deleted' is not right.
The best solution would still be to somehow get the form to requery/update to completely remove a deleted record. Could I turn off repaint, close the form and reopen it at the appropriate record, then repaint? Would the code complete its run once the form has been temporarily closed? Would this cause the form to flicker or jump around in an annoying way?
wileedingo
wileedingo
Have tried Tryvge's solution but it stopped on Me.Filter. "Invalid use of property".
Attempted Macbeth's solution but it did the same as before - deleted the record and wouldn't let me select the next one. The problem here is the .movenext will apply to the recordset called up by the delete button's code, not to the set of records being displayed.
Using the docmd.gotorecord ,acNext version generated a different error message: Run Time Error 2489. The object '1' isn't open. * The objectname argument for the GoToRecord, RepaintObject, or SelectObject method names an object that is closed. Use one of the Open actions or methods to open the object so that you can carry out the desired action.
I played with Macbeth's solution a bit more as I had tried something like it already. Since the recordset called up by the button is not the same as that used by the form, I tried putting the movenext command first:
With rst1
.Index = "PrimaryKey"
.Seek "=", varID
If .NoMatch Then
MsgBox "No record to delete"
Else
DoCmd.GoToRecord , , acNext
If .EOF() And Not .BOF() Then
DoCmd.GoToRecord , , acPrevious
End If
.Delete
MsgBox "Student deleted from this class"
End If
End With
This had the effect of moving to another record and then deleting the desired one (e.g. record #3). You can then move around the remaining records, but if you try to move from record #4 to record #2, the form crashes when it encounters the deleted record #3.
The problem now is that the form also has code for the On Current event. I assume that the On Current code runs just before the next record is shown and then ends. I could live with a record that says "#Deleted" everywhere, but it seems that the form can't. The offending statement in the On Current code is:
Set rst1 = db.OpenRecordset("SELECT RegData.*, Schedule.Date FROM Schedule INNER JOIN RegData ON Schedule.SchedCode = RegData.SchedCode " & _
"WHERE (RegData.HSE_studentNo = " & Me!HSE_studentNo & ") AND RegData.CourseCode= " & Me!CourseCode)
This works fine when there is a record available, however, when this statement is attempted for a deleted record, the form is not able to get values for Me!HSE_studentNo or Me!CourseCode, and I am sent the misleading error message that there is an extra ')' in the statement. The whole thing would work in a wobbly fashion, I think, if I could instead say something like...
If Me!CourseCode = Deleted Then
DoCmd.GoToRecord , , acNext (with elaborations for boundaries)
Else
Set rst1 = ....
End If
....but '= Deleted' is not right.
The best solution would still be to somehow get the form to requery/update to completely remove a deleted record. Could I turn off repaint, close the form and reopen it at the appropriate record, then repaint? Would the code complete its run once the form has been temporarily closed? Would this cause the form to flicker or jump around in an annoying way?
wileedingo
wileedingo
The filter line should have read
Me.Filter = "1=2"
Sorry about that.
Me.Filter = "1=2"
Sorry about that.
ASKER
Trygve, you've done it again! If I had a tail, it would be wagging. Me!Happy. And you are right, your solution is most elegant.
Adding that one little = sign made it work. I'm not all that familiar with the use of filters, so I didn't catch the error. Tell me, what is the "1=2" part? Is that to create a null state for the empty set of records?
I will test your solution further, but I am ready to hand over the points if you would like to post an 'answer'...
thanks a million,
wileedingo
Adding that one little = sign made it work. I'm not all that familiar with the use of filters, so I didn't catch the error. Tell me, what is the "1=2" part? Is that to create a null state for the empty set of records?
I will test your solution further, but I am ready to hand over the points if you would like to post an 'answer'...
thanks a million,
wileedingo
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What about
DoCmd.RunCommand acCmdDeleteRecord
?
DoCmd.RunCommand acCmdDeleteRecord
?
ASKER
Thanks again, Trygve. Your filter explanation makes good sense.
Dedushka - I think your suggestion would result in other problems. It would try to delete the entire record shown by the form, but this is not what I want to do. This record comes from a query which draws bits and pieces of data from a number of tables. I needed to delete a single record related to the data displayed by the form from just one of the tables (if that makes sense).
wile e dingo
Dedushka - I think your suggestion would result in other problems. It would try to delete the entire record shown by the form, but this is not what I want to do. This record comes from a query which draws bits and pieces of data from a number of tables. I needed to delete a single record related to the data displayed by the form from just one of the tables (if that makes sense).
wile e dingo
Well, usually I just run SQL statement for deleting directly from the table, something like:
docmd.RunSQL "DELETE PersonID FROM TtlPerson WHERE (PersonID=" & me.PersonId & ");"
me.Requery
docmd.RunSQL "DELETE PersonID FROM TtlPerson WHERE (PersonID=" & me.PersonId & ");"
me.Requery
That is what I would do Dedushka. You will not be able to give your user your own, nicely formatted, message about the result of the deletion though.
Try CurrentDB.Execute which lets you use the RecordsAffected property to inform your user of otherwise take action depending on the amount of affected records.
From help file:
"
Sub RecordsUpdated()
Dim dbs As Database, qdf As QueryDef
Dim strSQL As String
' Return reference to current database.
Set dbs = CurrentDb
strSQL = "UPDATE Employees SET Title = " _
& "'Senior Sales Representative' " & "WHERE Title = 'Sales Representative';"
' Create new QueryDef object.
Set qdf = dbs.CreateQueryDef("Update
' Execute QueryDef object.
qdf.Execute
Debug.Print qdf.RecordsAffected
Set dbs = Nothing
End Sub
"
Try CurrentDB.Execute which lets you use the RecordsAffected property to inform your user of otherwise take action depending on the amount of affected records.
From help file:
"
Sub RecordsUpdated()
Dim dbs As Database, qdf As QueryDef
Dim strSQL As String
' Return reference to current database.
Set dbs = CurrentDb
strSQL = "UPDATE Employees SET Title = " _
& "'Senior Sales Representative' " & "WHERE Title = 'Sales Representative';"
' Create new QueryDef object.
Set qdf = dbs.CreateQueryDef("Update
' Execute QueryDef object.
qdf.Execute
Debug.Print qdf.RecordsAffected
Set dbs = Nothing
End Sub
"
Private Sub DeleteStudent_Click()
Dim db As Database
Dim rst1 As Recordset
Dim varID
Set db = CurrentDb()
Set rst1 = db.OpenRecordset("RegData"
varID = Me!record_no
' Set a filter that returns no records to make sure that we don't lock the record that is to be deleted and don't get problems from the requery
Me.Filter "1=2"
Me.FilterOn = True
With rst1
.Index = "PrimaryKey"
.Seek "=", varID
If .NoMatch Then
MsgBox "No record to delete"
Else
.Delete
MsgBox "Student deleted from this class"
End If
End With
' Removing the filter will requery, I think..
Me.FilterOn = False
' Remember to clean up recordset variables
rst1.Close
set rst1 = Nothing
End Sub