Deleting record from List box - only deletes FIRST record?

[Sharky II]

Hi guys

I've searched around and it's the same old story - however no matter what i (seem to) do, when i delete from the listbox, the selected record doesn't get deleted, but in fact the FIRST record in the table (actually it's a query) gets deleted? I've tried making a cmdButton and double clicking to delete, but can someone give me the 'definitive' code to deleting a SELECTED record from a listbox (and infact from the query too) please? I've used a few bits of code - some i might not have used properly... i've tried:

Private Sub List8_DblClick(Cancel As Integer)DoCmd.RunCommand acCmdSelectRecord 'select current record
DoCmd.RunCommand acCmdDeleteRecord 'delete selected record
Me.List8.Requery
End Sub

and also i've tried including stuff like

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[EnquiryID] = " & Me.EnquiryID
Me.Bookmark = rs.Bookmark

and some other stuff that i got off posts from wayne ryan and mile-o-phile... but like i say sometimes i'm not sure exactly how to adapt these... The situation is that the form is on linked to the customer form via a customerID, and it has it's own EnquiryID. So i wasn't sure which ID and Column etc i should use. I also tried a piece of code that wayne ryna gave but i don't wanna make this thread too long otherwise people won't read it

So if anyone could help, that'd be great, cheers

Edd

 

[Sharky II]

When i use the following code:

Private Sub List8_DblClick(Cancel As Integer)

    Dim db As DAO.Database, rs As DAO.Recordset
    Dim strSQL As String

    If MsgBox("Are you sure you wish to delete this record?", vbQuestion + vbYesNo, "Example") = vbYes Then
        strSQL = "SELECT * FROM qryListCustomerEnquiry WHERE EnquiryID = " & Me.List8.Column(0) & ";"
        Set db = CurrentDb
        Set rs = db.OpenRecordset(strSQL)
        With rs
            .Delete
            .Close
        End With

        db.Close

    End If

End Sub

I get a Runtime Error '3061' - Too few parameters. Expected one... teh debugger takes me to the Set rs = db.OpenRecordset(strSQL) line...

whaddaya think guys?

 

[bradcccs]

I would try structuring you SQL string as a Delete Query.

I know you are very close to achieving this, but I have attached a "mini demo" for others (that may or may not find it useful).

You will need to add handling for List Box selection being NULL.

You will also need another requery solution IF the form is bound to the same recordsource as the list box.

HTH

Brad.

 

[Sharky II]

hey brad

are you sure this works with queries? I'm using the following code:

Private Sub Command16_Click()
On Error GoTo Err_Command16_Click

Dim StrSQL As String
Dim EnquiriesDetailsID As Integer

If MsgBox("Record selected, '" & Me.List8.Column(5) & "' will be deleted. Continue?", vbYesNo, "Confirmation") = vbYes Then
EnquiriesDetailsID = Me.List8.Column(0)
StrSQL = "Delete * FROM qryListCustomerEnquiry WHERE qryListCustomerEnquiry.EnquiriesDetailsID= " & EnquiriesDetailsID & ";"
DoCmd.SetWarnings False
DoCmd.RunSQL StrSQL
DoCmd.SetWarnings True
Me.List8.Requery
End If

Exit_Command16_Click:
    Exit Sub

Err_Command16_Click:
    MsgBox Err.Description
    Resume Exit_Command16_Click
    
End Sub

and after saying 'do u want ot delete' etc it then says 'could not delete from specified tables'... assuming the code is correct (?)what could i be messing up on my forms or queries for this to not work?

cheers, i hope you can shed some light

Edd

 

[bradcccs]

Open your query up directly (not via the form) and see if you can delete a row manually.

(ie: select row and then delete)

If the recordset is not updateable, then you will be unable to delete records via sql or any other means.

I use the query/listbox to obtain the required ID and then delete from the table (eg: Delete * from Tbl_Enquiry where EnquiryID = x)

You must also remember that referential integrity will come into play. Eg: May not be able to delete customer if enquiries exist in tbl_enquiries etc.

Brad


Let me know if you query is updateable etc.

 

[Sharky II]

Yeah, i can delete rows from the query manually. It's odd... i'm trying to delete from the table (using your code) but it doesn't like that either - i'll keep trying

can you think of anything else it would be mate?

i hate these tiny thigns that slow you down!!!!!

cheers for your help

 

[Sharky II]

by jesus i think i've got it!

*smacks forehead*

humiliating mistake will be revealed upon closer investigation

 

[Sharky II]

well not that humiliating i just changed it to tables... for those who're interested the code was as followed:

Private Sub Command16_Click()
On Error GoTo Err_Command16_Click

Dim StrSQL As String
Dim EnquiriesDetailsID As Integer

If MsgBox("Record selected, '" & Me.List8.Column(5) & "' will be deleted. Continue?", vbYesNo, "Confirmation") = vbYes Then
EnqDetailsID = Me.List8.Column(0)
StrSQL = "Delete * FROM tblEnquiriesDetails WHERE tblEnquiriesDetails.EnquiriesDetailsID= " & EnqDetailsID & ";"

DoCmd.SetWarnings False
DoCmd.RunSQL StrSQL
DoCmd.SetWarnings True
Me.List8.Requery
End If

Exit_Command16_Click:
    Exit Sub

Err_Command16_Click:
    MsgBox Err.Description
    Resume Exit_Command16_Click
    
End Sub

Thanks a lot man. It affects teh tables directly so just better doube check that there's no important info being lost...

Can you elaborate on what you were saying earlier... about

"You will need to add handling for List Box selection being NULL.

You will also need another requery solution IF the form is bound to the same recordsource as the list box. "

Basically i don't know how to account for when the listbox is empty... and the form's record source is tblEnquiriesDetails whereas the lists record source is a query (qryListCustomerEnquiry) - does that count as 'the same' because some fields in the query/list are taken from tblEnquiriesDetails?

Cheers though for your help!

Edd

 

[bradcccs]

Handling NULL can be achieved by adding the following lines the start of your code.

If IsNull(Me.List8.Column(0)) Then
    MsgBox "Please make selection prior to attempting to delete record.", vbOKOnly, "Selection Required"
    Exit Sub
End If

You will know if you need another solution for requrerying if you are unable to continue to use your form after deleting the required record. - May get the "Someone else saved record . . . . . . Re-edit record" message (Can't remember exact wording).

Cheers

Brad.

 

[Sharky II]

Damn it, you're a genius i swear!

It all works PERFECTLY!

I am in your debt - many thanks mate, hopefully i can repay the favour one day

Cheers, thanks again

Edd