Delete Row (1 Viewer)

[Taff]

Command Button Code Help

I am working in Access 2000 and currently I am trying to
delete a row from a table by using a command button. The
row to be deleted depends on an item selected in my list
box.

Command button is named:- cmdDeleteActivity
ListBox is named:- List3

This is the code I currently have on the OnClick Event of
the command button:-

Dim Title, Prompt, iReturn
Title = "Warning!"
Prompt = "Are you sure you want to Send this Learning
Activity" & vbCrLf & _
"to the Deleted Learning Activities Table?"
Buttons = vbYesNo + vbExclamation + vbDefaultButton2
iReturn = MsgBox(Prompt, Buttons, Title)
Select Case iReturn

Case vbYes
DoCmd.RunMacro "WarningOff"
DoCmd.RunSQL "Delete * from [Learning activity dataset]
where [learning activity dataset].[learn_id]='" & Me!
List3.Column(0) & "'" & " and [Learning activity dataset].
[provi_id] = '" & Me!List3.Column(1) & "'" & " and
[Learning activity dataset].[lprog_id] = '" & Me!
List3.Column(2) & "'" & " and [Learning activity dataset].
[lacti_id] = " & Me!List3.Column(3) & ""
Me.List3.Requery
DoCmd.RunMacro "Warningon"

Case Else
DoCmd.Beep
End Select

The problem I am having is I would only like to delete the
record if column(11) in my list box is null, but am unsure
of how to add this into the current code.


Any Help would be greatly appreciated.

Thanks in Advance All


Anthony

 

[Mile-O]

Do you not have a primary key on the RecordSource in the listbox?

 

[Taff]

Yes 4.

Learn_id
Provi_id
Lprog_id
Lacti_id

 

[jon_sg]

After Case vbYes

Add

If isnull(me!list3.column(11)) Then
'Case vbYes Code

Else

MsgBox "Cannot Delete as column(11) is not null", vbOkOnly
End If

Regards

Jon

 

[Mile-O]

4 primary keys or one composite key?

Anyway, for the meantime - I've tidied up the code and added a condition:

Private Sub cmdDeleteActivity_Click()

    On Error GoTo Err_cmdDeleteActivity_Click
    
    ' constant declaration(s)
    Const Title1 = "Warning!"
    Const Title2 = "Error!"
    Const Buttons1 = vbYesNo + vbQuestion
    Const Buttons2 = vbOKOnly + vbExclamation
    Const Prompt1 = "Are you sure you want to Send this Learning Activity " & vbCrLf & _
        "to the Deleted Learning Activities Table?"
    Const Prompt2 = "You are unable to delete this record."
    
    ' variable declaration(s)
    Dim strSQL As String
    
    ' build delete query
    strSQL = _
        "DELETE * FROM [Learning Activity Dataset] WHERE " & _
            "[Learn_ID] = """ & Me.List3.Column(0) & """ AND " & _
            "[Provi_ID] = """ & Me.List3.Column(1) & """ AND " & _
            "[LProg_ID] = """ & Me.List3.Column(2) & """ AND " & _
            "[Lacti_ID] = """ & Me.List3.Column(3) & """;"
            
    ' check if column has Null value
    If IsNull(List3.Column(11)) Then ' True
        ' confirm deletion
        If MsgBox(Prompt1, Buttons1, Title1) = vbYes Then
            With DoCmd
                .SetWarnings False ' disable warnings
                .RunSQL strSQL ' run delete query
                .SetWarnings True ' enable warnings
            End With
            Me.List3.Requery ' requery the listbox
        Else
            DoCmd.Beep ' make an annoying sound
        End If
    Else
        ' inform use that they can't delete the selected record
        MsgBox Prompt2, Buttons2, Title2
    End If
    
Exit_cmdDeleteActivity_Click:
    strSQL = vbNullString
    Exit Sub
    
Err_cmdDeleteActivity_Click:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_cmdDeleteActivity_Click

End Sub

 

[Mile-O]

me!list3.column(11))

Dot is better than Bang...

 

[jon_sg]

Dot is better than Bang...

Old habits die hard.....

 

[Taff]

Hi jon,

Thanks for replying. Tried that and it asks me to confirm that i would like to delete the Row, but when i click yes it does nothing.

Any Ideas.

Maybe it would need to be added into the SQL somewhere?

Regards

Ant

 

[jon_sg]

Set the warnings back on and see what it tells you

 

[jon_sg]

The relationships probably don't allow you to make cascading deletes

Mile is the real expert I am just a hack, you could also try his code but takes longer to type

 

[Taff]

Hi All,

I now get the message:-

Error

Cannot Delete this Record

?

Ant

 

[Mile-O]

you could also try his code but takes longer to type

But quicker to just copy and past over what you already have. It should, hopefully, work.

 

[Taff]

Copied and Pasted the code but no luck unfortunately.

What did you mean by composite key?

 

[Mile-O]

A primary key in one table that is composed from more than one field.

 

[jon_sg]

If the Pk's are from seperate tables and the relationships are set a simple solution, though probably not the best, may be to right click on the joins in the relationship window
and in the join properties select enforce referential integrity and make sure that allow cascading deletes is selected.
do this for each of the tables containing your four primary keys and then it should work

 

[Taff]

Okay got ya.

I have one Primary Key made up of the Above four fields.

However it is a foreign key in this table.

 

[Taff]

Selecting Cascading Deletes has unfortunately again not worked.

Stumped!

Ant.

 

[jon_sg]

Maybe try posting your db As a .zip file somone will be able to point you in the right direction.

Sorry I couldn't help mate

 

[Taff]

Am a bit unsure where to post my database.

Where would you recommend?

Thanks

Anthony

 

[Mile-O]

This thread might be a good place to try.

When you post a reply, there's an option to Upload an attachment.