Listbox delete problem

[faz88]

Hi, On my form i have several listboxes. When i want to delete a record which has no related records, it will delete. But if there is a related record in the database the row will not delete. How do i go about showing an error message to say the selected row cannot be deleted?

thanks

 

[ezfriend]

Do do that, you will need to use some sort of select statement to check if a record exists in another table. For example.

Private Sub DeleteRecord()

    'assuming that you know the record ID for this particular record that you want to delete.
    If RecordExistInAnotherTable(lRecordID)=False Then

            CurrentDB.execute "DELETE FROM tblA WHERE Record_ID = " & lRecordID

    End If


End Sub

Private Function RecordExistInAnotherTable(byval lRecordID as Long) as Boolean

    Dim rs As Recordset

    Set rs = currentdb.OpenRecordset("Select COUNT(*) FROM tblAnotherTable WHERE Record_ID = " & lRecordID

    If rs(0)>0 then
        RecordExistInAnotherTable = True
    Else
        RecordExistInAnotherTable = False

    End If

    rs.Close
    Set rs = Nothing

End Function

 

[RuralGuy]

Just catch error # 3200 in your error handler code and display whatever message you feel appropriate.

 

[faz88]

Just catch error # 3200 in your error handler code and display whatever message you feel appropriate.

I tried doing the error handler thing but it still doesnt show me any message. Not even an automated access message.

This is the code i used:

On Error GoTo ErrorHandler

ExitErrorHandler:
Exit Sub
ErrorHandler:
MsgBox "Error No.:" & Err.Number
Resume ExitErrorHandler

 

[RuralGuy]

The error handler needs to be in the Procedure where you are deleting the record. How are you deleting the record?

 

[faz88]

The error handler needs to be in the Procedure where you are deleting the record. How are you deleting the record?

The records are deleted by using a command button
This is the code where i put the error handler in

Private Sub Delete_Click()

On Error GoTo ErrorHandler

Dim strsql As String

If MsgBox("Are you sure you want to delete this?", vbQuestion + vbYesNo) = vbYes Then


strsql = "DELETE * FROM treatment WHERE TreatmentID = " & txttreatmentID.Value & " "
CurrentDb.Execute strsql

End If


ExitErrorHandler:
Exit Sub
ErrorHandler:
MsgBox "Error No.:" & Err.Number
Resume ExitErrorHandler

 

[RuralGuy]

Try:
CurrentDb.Execute strsql, dbFailOnError

 

[faz88]

Try:
CurrentDb.Execute strsql, dbFailOnError

That worked, Thank you!!

 

[RuralGuy]

That worked, Thank you!!

You are welcome.