CODEWITHSUNDEEP
ms-accessif-statementvba
Paul Machin
Paul Machin

Reputation: 39

VBA If statement confusion

The below code worked fine until I added the code for the vbokcancel bit... code below:

If Me.results.ListIndex = -1 Or Me.userfolder.ListIndex = -1 Or
Me.choice.ListIndex = -1 Then           

MsgBox "Please choose something to remove"

Else

If MsgBox("Are you sure you want to delete this user from this folder?", vbOKCancel) = vbOK Then

    If Me.choice.Value = "Folder" Then

        username = Me.results.Column(0)
        prfolder = Me.results.Column(3)

        strSQL = "DELETE tblRelationship.*, tblPra.praNo, tblFolder.folder FROM tblPra INNER JOIN (tblFolder INNER JOIN tblRelationship ON tblFolder.folderID = tblRelationship.folderID) ON tblPra.praID = tblRelationship.praID WHERE (((tblPra.praNo)='" & username & "') AND ((tblFolder.folder)='" & prfolder & "'));"
        CurrentDb.Execute strSQL
        Me.results.Requery

    ElseIf Me.choice.Value = "Username" Then

        prfolder = Me.results.Column(0)

        strSQL = "DELETE tblRelationship.*, tblFolder.folder FROM tblPra INNER JOIN (tblFolder INNER JOIN tblRelationship ON tblFolder.folderID = tblRelationship.folderID) ON tblPra.praID = tblRelationship.praID WHERE (((tblFolder.folder)='" & prfolder & "'));"
        CurrentDb.Execute strSQL
        Me.results.Requery

    End If
  End If
End If

It will activate the If MsgBox("Are you sure you want to delete this user from this folder?", vbOKCancel) = vbOK section but if the "OK" button is clicked it skips straight to the End If and does not run the code within the IF statement. Now I thought what I did was perfectly "Legal" in coding, apparently not... any ideas what I've done to screw up?

If you press "Cancel" it does what I already want it to do, which is close the message box and the user is back at the same screen.. so there's no need for an Else statement because it already does what I want it to do.

Upvotes: 0

Views: 275

Answers (1)

Johnny Bones
Johnny Bones

Reputation: 8414

This is too long for a comment and it's not really an answer, but it should guide you in the right direction.

Try setting the value of the messagebox equal to a variable. Something like this:

If Me.results.ListIndex = -1 Or Me.userfolder.ListIndex = -1 Or
Me.choice.ListIndex = -1 Then           

MsgBox "Please choose something to remove"

Else

    X = MsgBox("Are you sure you want to delete this user from this folder?", vbOKCancel)

    If X = vbOK Then  'put a breakpoint here

        If Me.choice.Value = "Folder" Then

            username = Me.results.Column(0)
            prfolder = Me.results.Column(3)

            strSQL = "DELETE tblRelationship.*, tblPra.praNo, tblFolder.folder FROM tblPra INNER JOIN (tblFolder INNER JOIN tblRelationship ON tblFolder.folderID = tblRelationship.folderID) ON tblPra.praID = tblRelationship.praID WHERE (((tblPra.praNo)='" & username & "') AND ((tblFolder.folder)='" & prfolder & "'));"
            CurrentDb.Execute strSQL
            Me.results.Requery

        ElseIf Me.choice.Value = "Username" Then

            prfolder = Me.results.Column(0)

            strSQL = "DELETE tblRelationship.*, tblFolder.folder FROM tblPra INNER JOIN (tblFolder INNER JOIN tblRelationship ON tblFolder.folderID = tblRelationship.folderID) ON tblPra.praID = tblRelationship.praID WHERE (((tblFolder.folder)='" & prfolder & "'));"
            CurrentDb.Execute strSQL
            Me.results.Requery

        End If
    End If

End If

Then, check X's value and see what it thinks X is equal to. That should give you an idea of why the If/Then code block isn't working.

Upvotes: 1

Related Questions