RE: [MS_AccessPros] Delete Records

Phucon ,

1st: apparently your form is set to not allow new records? If this is true then you won't see blanks to enter new records.

2nd: I would not use a recordset to delete individual records. To see changes made in the recordset you would need to requery or refresh the form.

Duane Hookom MVP
MS Access

________________________________
> To: MS_Access_Professionals@yahoogroups.com
> From: saigonf7q5@yahoo.com
> Date: Mon, 23 Sep 2013 13:35:23 -0700
> Subject: [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

------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://info.yahoo.com/legal/us/yahoo/utos/terms/