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")
.Index = "PrimaryKey"
.Seek "=", Me!record_no
If .NoMatch Then
MsgBox "No record to delete"
MsgBox "Student deleted from this class"