Delete Row

[Taff]

Hi All,

Attached is a copy of my database.

If anyone can give me help its greatly appreciated.

Thanks

Anthony

 

[Mile-O]

On the delete button, change your code to this:

Private Sub DeleteActivity_Click()

    On Error GoTo Err_DeleteActivity_Click

    Dim strSQL As String
    
    Const Prompt = "Are you sure you want to Send this Learning Activity " & _
        "to the Deleted Learning Activities Table?"
    Const Title = "Warning!"
    Const Buttons = vbYesNo + vbQuestion

    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) & ";"

    If Me.List3.ItemsSelected.Count > 0 Then
        If MsgBox(Prompt, Buttons, Title) = vbYes Then
            DoCmd.SetWarnings False
            DoCmd.RunSQL strSQL
            DoCmd.SetWarnings True
            Me.List3.Requery
        End If
    End If
    
Exit_DeleteActivity_Click:
    strSQL = vbNullString
    Exit Sub
    
Err_DeleteActivity_Click:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_DeleteActivity_Click
End Sub

 

[Taff]

Thanks Mile-O-Phile it works fine.

However if i add before that code:-

If isnull ([List3].column(11)) then

if wont work even though Column(11) is empty.

Any Ideas?

Thanks Again

Ant

 

[Mile-O]

if wont work even though Column(11) is empty.

It's not empty; it's a Null string:

This is the code you want:

If Me.List3.Column(11) = vbNullString Then

Also, I noticed a bit missing from my code above:

Resume exit_deleteactivity

should be:

Resume Exit_DeleteActivity_Click

 

[Taff]

Hi Mile-O-Phile,

It now works great.

I added a bit to the code you gave, not sure if its the way i should have but it works anyway.

It now Looks like this:-

Private Sub DeleteActivity_Click()

    On Error GoTo Err_DeleteActivity_Click

If Me.List3.Column(11) = vbNullString Then

    Dim strSQL As String
    
    Const Prompt = "Are you sure you want to Delete this Learning Activity?"
    Const Title = "Warning!"
    Const Buttons = vbYesNo + vbQuestion

    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) & ";"

    If Me.List3.ItemsSelected.Count > 0 Then
        If MsgBox(Prompt, Buttons, Title) = vbYes Then
            DoCmd.SetWarnings False
            DoCmd.RunSQL strSQL
            DoCmd.SetWarnings True
            Me.List3.Requery
        End If
    End If
Else

    Dim Strsql2 As String
    
    Const Prompt2 = "Are you sure you want to send this Learning Activity to the Delete Learning Activities Table?"
    Const Title2 = "Warning!"
    Const Buttons2 = vbYesNo + vbQuestion
    
    Strsql2 = "INSERT INTO [Deleted Activities] SELECT * 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) & ";"

    If Me.List3.ItemsSelected.Count > 0 Then
        If MsgBox(Prompt2, Buttons2, Title2) = vbYes Then
            DoCmd.SetWarnings False
            DoCmd.RunSQL Strsql2
            DoCmd.SetWarnings True
            Me.List3.Requery
        End If
    End If

Exit_DeleteActivity_Click:
    strSQL = vbNullString
    Exit Sub
    
Err_DeleteActivity_Click:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_DeleteActivity_Click
End If
End Sub

Thanks Again

Anthony

 

[Mile-O]

That can be shortened...check back later.

 

[Mile-O]

Here you go:

Private Sub DeleteActivity_Click()

    On Error GoTo Err_DeleteActivity_Click

    Dim strSQL As String
    Dim strCriteria As String
    Dim strPrompt As String
    
    Const Title = "Warning!"
    Const Buttons = vbYesNo + vbQuestion
    
    If IsNull(Me.List3) Then Exit Sub
    
    If Me.List3.Column(11) = vbNullString Then
        strSQL = "DELETE * FROM [Learning activity dataset] "
        strPrompt = "Are you sure you want to Delete this Learning Activity?"
    Else
        strSQL = "INSERT INTO [Deleted Activities] " & _
            "SELECT * FROM [Learning Activity Dataset] "
        strPrompt = "Are you sure you want to send this Learning Activity to the Delete Learning Activities Table?"
    End If

    strCriteria = _
        "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) & ";"
    
    If Me.List3.ItemsSelected.Count > 0 Then
        If MsgBox(strPrompt, Buttons, Title) = vbYes Then
            DoCmd.SetWarnings False
            DoCmd.RunSQL strSQL & strCriteria
            DoCmd.SetWarnings True
            Me.List3.Requery
        End If
    End If

Exit_DeleteActivity_Click:
    strSQL = vbNullString
    strCriteria = vbNullString
    strPrompt = vbNullString
    Exit Sub
    
Err_DeleteActivity_Click:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_DeleteActivity_Click

End Sub

NOTE: Try and keep consistencies - you had Me! and Me. in different places (Me. is always better, where possible)

 

[Taff]

Thanks Again Mile-O-Phile.

Whats the difference between Me! and Me. ?

 

[Mile-O]

Whats the difference between Me! and Me. ?

Me! or Me. ?

[FAQ] Referring to objects in Access: ! and . and (" ") notation

 

[Taff]

I See.

Thanks

 

[Mile-O]

No probs!

 

[Taff]

One more thing,

what is the Err_ for, is it error trapping?

 

[Mile-O]

Yes, the Err_ is for error trapping.

For more info, I suppose here would be a good place to start.

 

[Taff]

Hi All,

With the below code, the part that inserts my row into a new table, i need to be able to remove the row it has inserted from the current table as otherwise i will have two rows the same.

Not sure if you can cut and paste, or would i need to delete it after it has been inserted into the new table?

Thanks for any help.


Anthony

 

[Mile-O]

Does this help?

Private Sub DeleteActivity_Click()

    On Error GoTo Err_DeleteActivity_Click

    Const strSQLInsert = "INSERT INTO [Deleted Activities] " & _
            "SELECT * FROM [Learning Activity Dataset] "
    Const strSQLDelete = "DELETE * FROM [Learning activity dataset] "
    Dim strCriteria As String
    Dim strPrompt As String
    
    Const Title = "Warning!"
    Const Buttons = vbYesNo + vbQuestion
    
    If IsNull(Me.List3) Then Exit Sub
    
    strCriteria = _
        "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) & ";"
    
    If Me.List3.Column(11) = vbNullString Then
        strPrompt = "Are you sure you want to Delete this Learning Activity?"
    Else
        strPrompt = "Are you sure you want to send this Learning Activity to the Delete Learning Activities Table?"
    End If

    If Me.List3.ItemsSelected.Count > 0 Then
        If MsgBox(strPrompt, Buttons, Title) = vbYes Then
            With DoCmd
                If Me.List3.Column(11) = vbNullString Then
                    .SetWarnings False
                    .RunSQL strSQLDelete & strCriteria
                    .SetWarnings True
                Else
                    .SetWarnings False
                    .RunSQL strSQLInsert & strCriteria
                    .RunSQL strSQLDelete & strCriteria
                    .SetWarnings True
                End If
            End With
            Me.List3.Requery
        End If
    End If

Exit_DeleteActivity_Click:
    strCriteria = vbNullString
    strPrompt = vbNullString
    Exit Sub
    
Err_DeleteActivity_Click:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_DeleteActivity_Click

End Sub

 

[Taff]

Thanks Again Mile-O-Phile.

Works Fine!!!