Compile error: argument not optional (1 Viewer)

[jeds]

Using Access 2010. I need to run 2 queries with a command button, giving the user the option to cancel out.

Private Sub MoveToLife_Click()
On Error GoTo MoveToLife_Click_Err
DoCmd.RunCommand acCmdRefresh
DoCmd.OpenQuery "MoveToLifeQ", acViewNormal, acEdit
Beep
MsgBox "Are you sure you want to remove this member from the active member list? This cannot be reversed!", vbOKCancel, "Please Confirm"
If [COLOR=red]MsgBox[/COLOR] = vbOK Then
DoCmd.OpenQuery "RemoveFromMembers", acViewNormal, acEdit
ElseIf MsgBox = vbCancel Then
Exit Sub
End If
MoveToLife_Click_Exit:
    Exit Sub
 
MoveToLife_Click_Err:
    MsgBox Error$
    Resume MoveToLife_Click_Exit
End Sub

The debugger highlights MsgBox as above with the error:
"Compile error: argument not optional"

As a newbie I just figured if I put a message box in that had the option to cancel, it would do exactly that. So now I need to figure out how to tell it to actually cancel the action if user chooses so.

jeds

 

[spikepl]

1. Look up what "function"and "argument" is
2. And/or delete the offending line, and then re-type it SLOWLY, paying attention to what intellisense offers
3. Or, select the MsgBox with the cursor, press F1 - and read the helpfile about MsgBox

 

[jeds]

1. Look up what "function"and "argument" is
2. And/or delete the offending line, and then re-type it SLOWLY, paying attention to what intellisense offers
3. Or, select the MsgBox with the cursor, press F1 - and read the helpfile about MsgBox

Thanks spikepl; 1. The definiftions are frankly quite greek to me.
2. intellisense offered "vbOK" after the "=" , nothing more, but the editor did complain if I stopped there that it needed a "then" or "goto", hence the "then". That is all intellisense offers.
3.What I am getting from the help file is that the button arguments can be set with an integer value, however the value is already set thanks to intellisense. The return values for OK and cancel are 1 or 2. Yet setting
If MsgBox =vbOK to
If MsgBox = 1
or If MsgBox = 2
still returns the error.
and more greek: To specify more than the first named argument, you must use MsgBox in an expression. To omit some positional arguments, you must include the corresponding comma delimiter.

I also tried Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then
from the demo.

I have to ask as a non-coder: Why does access give us the option to build a message box that has a cancel button, yet to click on cancel it does not cancel the action?
Simply, why else would you want to be able to click cancel, but to actually cancel?

Hope this does not offend, it is very perplexing.

 

[jdraw]

Your quoted text has the info, but you have to read it carefully word by word.

To specify more than the first named argument, you must use MsgBox in an expression. To omit some positional arguments, you must include the corresponding comma delimiter.

Try this one
http://www.techonthenet.com/access/constants/msgbox_args.php
and if that doesn't work, try this
http://www.fontstuff.com/ebooks/free/fsmessageboxes.pdf

 

[mahenkj2]

Private Sub MoveToLife_Click()

On Error GoTo MoveToLife_Click_Err
DoCmd.RunCommand acCmdRefresh
DoCmd.OpenQuery "MoveToLifeQ", acViewNormal, acEdit
Beep
If MsgBox("Are you sure you want to remove this member from the active member list? This cannot be reversed!", vbOKCancel, "Please Confirm") = vbOK Then

DoCmd.OpenQuery "RemoveFromMembers", acViewNormal, acEdit

ElseIf MsgBox("Are you sure you want to remove this member from the active member list? This cannot be reversed!", vbOKCancel, "Please Confirm") = vbCancel Then
Exit Sub
End If
MoveToLife_Click_Exit:
Exit Sub

MoveToLife_Click_Err:
MsgBox Error$
Resume MoveToLife_Click_Exit
End Sub

Your code is giving error on Msgbox because you are not declaring it first. So, I have changed your code above. Please confirm if is as you want.

best regards.

 

[mahenkj2]

ElseIf MsgBox("Are you sure you want to remove this member from the active member list? This cannot be reversed!", vbOKCancel, "Please Confirm") = vbCancel Then

Also, you might not need to write condition for elseIF, instead you can just write

else
exit sub

Because, if OK is not selected, then there is only one choice i.e.cancel.

I hope I am correct.

best regards.

 

[jeds]

jdraw, great information in the links! I had started to come to realize I need to declare a variable for the response to the msgbox after spikepl's reply, your reply has made it a bit more clear.

mahenkj2 seems to accomplish the same thing in a method that is simpler to me than using Dim, now I have difficulty understanding how both methods accomplish the same result (declaring the variable). Or when his method might not work.

The fontstuff.com's pdf description of using intellisense clears up why I was not getting more ("you must include the corresponding comma delimiter").
Hats off again to spikpl, and to you for helping me complete the understanding. That document also presents the possiblility to include the member name in the msgbox, I have to work on it as I am not sure of the syntax but here is a start:

Dim mbr as name
Set mbr = Form.FirstName Form.LastName
If MsgBox("Are you sure you want to remove mbr from the active member list? This cannot be reversed!", vbOKCancel, "Please Confirm") = vbOK Then

...

Am I even close? Seems I am missing something.

Thanks again to you and all 3 of you. I am changing my sig in honor of all who reply to posts on this amazing forum!

 

[jeds]

mahenkj2 - Yes! it works! Thanks!

I did realize I needed to move it above the first query which appends the record to the new table, now it is working.

You all have motivated me to change my sig.

jeds

 

[jeds]

Also, you might not need to write condition for elseIF, instead you can just write

else
exit sub

Because, if OK is not selected, then there is only one choice i.e.cancel.

I hope I am correct.

best regards.

Absolutely correct! And cleaner code. Thanks.

 

[jdraw]

Just to add another option, and probably a little confusion
see http://www.functionx.com/vbaccess/topics/inputbox.htm

This is just meant to complete the picture, I'm really not suggesting you to alter existing code. Just making you aware of the InputBox function.

 

[jeds]

jdraw, you're right mostly confused, but appreciate it.

Stumped getting the value into the message box:

Private Sub Reason_Click()
On Error GoTo Reason_Click_Err
DoCmd.RunCommand acCmdRefresh
Beep
Member = ([FirstName]) ([LastName])
Dim Member As String
If MsgBox("Are you sure you want to remove & Member from the active member list? This cannot be reversed!" & vbCrLf & "Click Cancel to add a comment.", vbOKCancel, "Please Confirm") = vbOK
Then
DoCmd.OpenQuery "PastMemberQ", acViewNormal, acEdit
DoCmd.OpenQuery "RemoveFromMembers", acViewNormal, acEdit
Else
Exit Sub
End If
Reason_Click_Exit:
    Exit Sub
 
Reason_Click_Err:
    MsgBox Error$
    Resume Reason_Click_Exit
End Sub

Member = ([FirstName]) ([LastName]) : keep getting either "expected end of statement" or "expected end )"

I had started with

Sub Member()
Dim Member As String
Member = (Form.FirstName Form.LastName)

and the editor wanted an End Sub 1st, either I didn't nest the sub correctly, so I tried Private Sub.

Well yeah totally muddled now!

Also not sure if I have the msgbox message right, do I need quotes before and after & Member?

I ended up trying the above code (Member = ...) after seeing another post on nested subs where it was suggested using a function instead of a nested sub.

Sure I have it all wrong now!

jeds
edit: hold the phone!

Dim Member As String
Member = ([FirstName])
Beep
If MsgBox("Are you sure you want to remove & Member from the active member list? This cannot be reversed!" & vbCrLf & "Click Cancel to add a comment.", vbOKCancel, "Please Confirm") = vbOK Then

returns the literal "& Member" in the msgbox message, thanks to debugger.

 

[jdraw]

Dim Member As String
Member = ([FirstName])
Beep
If MsgBox("Are you sure you want to remove " & Member & " from the active member list? This cannot be reversed!" & vbCrLf & "Click Cancel to add a comment.", vbOKCancel, "Please Confirm") = vbOK Then

You are concatenating a string with a variable -- must use concatenation and enclose a string within double quotes

 

[jeds]

Thought I posted this earlier, but it is good now:

Dim Member As String
Member = [FirstName] & " " & [LastName]
Beep
If MsgBox("Are you sure you want to remove " & Member & " from the active member list? This cannot be reversed!" & vbCrLf & "Click Cancel to add a comment.", vbOKCancel, "Please Confirm") = vbOK Then

credit goes to jdraw and http://www.599cd.com/VideoPlayer/?F...m/videos/tips/access/string-concatenation.wmv

jeds