Delete the current record

[elyleeboy]

Well I have put off embarrasing myself by writing to this forum with my dopey question but here goes:

I have been trying to find a method of deleting the current record.

My attempt so far is as follows:

Dim MyDB As Database
Dim MyRS As Recordset

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("AllQuestions", dbOpenDynaset)


MyRS.Delete



This kind of works, but in my text/combo boxes I am left with: #Deleted.


Is there an alternative solution or can somebody tell me where I am going wrong

Thanks

 

[KenHigg]

Add:

me!mycombobox.requery


???
kh

 

[DBL]

if I don't want to give the user access to the Access delete record button I create a button which would run a delete query, with the criteria for the query set to the record that the user is currently on. This is the code on my button:

stdresponse = MsgBox("Are you sure you want to delete this record?" & vbCr & _
"Once it is deleted it cannot be restored. " & vbCr & vbCr & _
"You can only delete one record at a time", vbCritical + vbYesNo, "Delete Offspring")

If stdresponse = vbYes Then

DoCmd.SetWarnings False
DoCmd.OpenQuery "QryDeleteOffspring2"
Forms!frmBreedingProgramme!sfrmAnimalBreeding.Requery 'important bit
DoCmd.SetWarnings True

Else

Cancel = True
End If

My delete query would have the unique id field in the query grid with a reference to the form and record that we are currently on:

[Forms]![yourformname]![YourUniqueIDFieldName]

 

[elyleeboy]

Thanks Ken and DBL;

Just to go further with the SQL approach, as I said... I am very new to this;

what does the :
DoCmd.OpenQuery "QryDeleteOffspring2"
Forms!frmBreedingProgramme!sfrmAnimalBreeding.Requery 'important bit

... mean... could maybe expand this a little further

 

[DBL]

Sorry, I just copied it straight from my code.

DoCmd.OpenQuery "QryDeleteOffspring2"

I would create a delete query (called qryDeleteOffspring2 in my instance) and the DoCmd.OpenQuery would run the delete query

Forms!frmBreedingProgramme!sfrmAnimalBreeding.Requery 'important bit

This is the name of my form/subform and I'm just asking for the query that the subform is based on to refresh to take out the "Deleted" record.

 

[elyleeboy]

Thanks DBL

I slightly amended the code:

stdresponse = MsgBox("Are you sure you want to delete this record?" & vbCr & _
"Once it is deleted it cannot be restored. " & vbCr & vbCr & _
"You can only delete one record at a time", vbCritical + vbYesNo, "Delete Offspring")

If stdresponse = vbYes Then

DoCmd.SetWarnings False
lngrecordnum = Me.QuestionID.Value
DoCmd.RunSQL ("Delete * from AllQuestions where QuestionID =" & lngrecordnum & ";")
Forms!AllQuestions!Option1.Requery 'important bit
DoCmd.SetWarnings True

Else

Cancel = True
End If


Now I'm getting #Deleted in all my boxes (See attached)

Any ideas?