Senin, 23 September 2013

[MS_AccessPros] Delete Records

 

The record source of my form's bounded to the result of a query named "qryResult",  which resulted from the criteria of a Search form.

 I am building a procedure for users to delete records.  I first tried:

1st ,  I just clicked on the record selector then press the Delete key on the keyboard, and let Access takes care the rest of it. This is good, until the last and the only record was deleted, all the controls on the detail section of the form become invisisible. Only the controls on the from header and footer remain on the form.

2nd , I tested my own  coding  methods  as shown here.  The trouble of this is,  when I use  the "Set rs = Me.RecordsetClone",  this  only removed the row(s) from the query -"qryResult" ,  not the table, the record(s)  in the table was never deleted. So in order for me to delete the record, I need to use the " Set rs = db.OpenRecordset("tblPled", dbOpenDynaset)".  One other  trouble  is, After the Delete command's executed or the record's deleted from the query, the deleted record remains on the screen and won't move to another record. If I need to delete the remaining records, I need to click the navigation to move to that record.

 

So, 1. What causes the all controls disappeared from the Detail section after a record was deleted?  2. After a record's deleted why the deleted record's still showing on the screen and does not move to another record or make other record current? Need a BookMark perphaps?

 

My post is kind of too long this time.

 

Phucon

 

Private Sub cmdDelete_Click()

On Error GoTo ErrorHandler

    Dim Response

    Response = MsgBox("Deleting Account " & Chr$(34) & Me.txtAcctID & Chr$(34) & ".   Proceed to delete?" & vbCrLf & "Press OK to confirm.", _    vbOKCancel + vbExclamation, "Deleting Record")

    If Response = vbCancel Then

        GoTo Exit_Delete

    Else

        Set db = CurrentDb

       'Set rs = Me.RecordsetClone

       Set rs = db.OpenRecordset("tblPled", dbOpenDynaset)

       TempVars.Add "DeletedAcct", Chr$(34) & Me.txtAcctID & Chr$(34)

        With rs

                .FindFirst "AcctID = " & Chr$(34) & Me.txtAcctID & Chr$(34)

                If Not .NoMatch Then

                    .Delete

                    MsgBox TempVars!DeletedAcct & "  deleted.", vbInformation, "Delete Record"

                   .MoveNext

                   If .EOF Then

                      .MovePrevious

                      If .BOF Then

                          .MoveNext

                      End If

                  End If

             End If

       End With

    End If

 

Exit_Delete:

     TempVars.Remove "DeleteAcct"

     Set rs = Nothing

     Set db = Nothing

    Exit Sub

ErrorHandler:

     If Err.Number = 3021 Then 'No current record. The last and the only record in the Recordset's been deleted.

         GoTo Exit_Delete

     Else

         MsgBox "Error Number: " & Err.Number & vbNewLine _

                 & "Error Describtion: " & Err.Description & " in procedure cmdDelete_Click."

         Resume Exit_Delete

         Resume

     End If

End Sub

 

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (1)
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar