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
LVL 1
wileedingoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TrygveCommented:
You are deleting current record. Try this, it just might work:

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

0
MacbethCommented:
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
0
TrygveCommented:
But, the problem seems to be related to the form and not to the recordset from the code?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

MacbethCommented:
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)
0
TrygveCommented:
Yes, but your code still moves in the recordset and not in the form itself.

Well, lets here it from wileedingo.
0
MacbethCommented:
Trygve - you are right - didn't really get it the first time...
0
MacbethCommented:
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
0
TrygveCommented:
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?
0
MacbethCommented:
True...
0
wileedingoAuthor Commented:
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
0
TrygveCommented:
The filter line should have read

Me.Filter = "1=2"

Sorry about that.
0
wileedingoAuthor Commented:
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
0
TrygveCommented:
Setting the forms filter to "1=2" is the same as setting its recordsource to

SELECT ... FROM ... WHERE 1=2

1=2 will of course never be true and no records are returned.

Me.FilterOn makes the filter active and your form has no records in its recordset. This means that it can't lock any records and any other form, code etc. will be able to use the records for any purpose.

Let me know if this was a very bad explanation. I have now spent 15.5 hours at work and it is time to go home :-)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DedushkaCommented:
What about
DoCmd.RunCommand acCmdDeleteRecord
?
0
wileedingoAuthor Commented:
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
0
DedushkaCommented:
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
0
TrygveCommented:
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("UpdateTitles", strSQL)
      ' Execute QueryDef object.
      qdf.Execute
      Debug.Print qdf.RecordsAffected
      Set dbs = Nothing

End Sub
"
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.