Message box and if statements

P

Phil

Dear All

it has been a long time since I have posted here but I am now totally stuck

I have a Combo box on a form that has a value of 1-3, if the value is 3 I
want a message box to ask a question, if the answer is yes I want it to set
[ACST] to -1 (it is a checkbox) [EventName] to "National Advanced
Communication Course"
I have tried to come up with the code and it works to bring up the message
box only for number 3 but it does not change the control value, I have only
added one control at the moment just to see if I was on the right track

I always have trouble when it comes to more than 1 if statement

Here is what i came up with:

Private Sub EventTypeID_AfterUpdate()


If Me.EventTypeID.Value = 3 Then
If MsgBox("Is this a National Advanced Communication Skills Training
Programme?", vbYesNo, "Advanced Communication Skills") = vbayes Then
Me.EventName = "ACST", Me.ACST = -1
Else
Else
End If



End Sub

Hope someone can help

Thanks

Phil
 
B

BruceM

You need an End If for each If. Had you compiled the code (Debug >>
Compile) that would have been caught. You don't need Else unless there is
something to do if the logical test (the If statement) is false, although
some people add the comment:
' Do nothing
Also, use separate lines for the setting the field values.

If Me.EventTypeID = 3 Then
If MsgBox("Is this a National Advanced Communication Skills Training
Programme?", vbYesNo, "Advanced Communication Skills") = vbYes Then
Me.EventName = "ACST"
Me.ACST = -1
Else ' Do nothing
End If
Else ' Do nothing
End If

or:

If Me.EventTypeID = 3 Then
If MsgBox("Is this a National Advanced Communication Skills Training
Programme?", vbYesNo, "Advanced Communication Skills") = vbYes Then
Me.EventName = "ACST"
Me.ACST = -1
End If
End If

Note that Value is the default property, so in most cases you don't need to
specify it (although there is no harm to doing so). However, if you are
going to use it is probably clearest to read if you use it in all cases.

Remember, Compile is your friend. Another thing is to check the top of the
form's code module. You should see the line Option Explicit on the line
after Option Compare Database. If it is not there you should add it. To
assure it is added in future code modules, in the VBA editor click Tools >>
Options. Click the Editor tab, and check Require Variable Declaration.
This may be accomplished somewhat differently in Access 2007, but I expect
the general idea is the same.
 
P

Phil

Thank you it worked and thank you for taking the time to explain why that is
worth more

Thanks

PHil

BruceM said:
You need an End If for each If. Had you compiled the code (Debug >>
Compile) that would have been caught. You don't need Else unless there is
something to do if the logical test (the If statement) is false, although
some people add the comment:
' Do nothing
Also, use separate lines for the setting the field values.

If Me.EventTypeID = 3 Then
If MsgBox("Is this a National Advanced Communication Skills Training
Programme?", vbYesNo, "Advanced Communication Skills") = vbYes Then
Me.EventName = "ACST"
Me.ACST = -1
Else ' Do nothing
End If
Else ' Do nothing
End If

or:

If Me.EventTypeID = 3 Then
If MsgBox("Is this a National Advanced Communication Skills Training
Programme?", vbYesNo, "Advanced Communication Skills") = vbYes Then
Me.EventName = "ACST"
Me.ACST = -1
End If
End If

Note that Value is the default property, so in most cases you don't need to
specify it (although there is no harm to doing so). However, if you are
going to use it is probably clearest to read if you use it in all cases.

Remember, Compile is your friend. Another thing is to check the top of the
form's code module. You should see the line Option Explicit on the line
after Option Compare Database. If it is not there you should add it. To
assure it is added in future code modules, in the VBA editor click Tools >>
Options. Click the Editor tab, and check Require Variable Declaration.
This may be accomplished somewhat differently in Access 2007, but I expect
the general idea is the same.

Phil said:
Dear All

it has been a long time since I have posted here but I am now totally
stuck

I have a Combo box on a form that has a value of 1-3, if the value is 3 I
want a message box to ask a question, if the answer is yes I want it to
set
[ACST] to -1 (it is a checkbox) [EventName] to "National Advanced
Communication Course"
I have tried to come up with the code and it works to bring up the message
box only for number 3 but it does not change the control value, I have
only
added one control at the moment just to see if I was on the right track

I always have trouble when it comes to more than 1 if statement

Here is what i came up with:

Private Sub EventTypeID_AfterUpdate()


If Me.EventTypeID.Value = 3 Then
If MsgBox("Is this a National Advanced Communication Skills Training
Programme?", vbYesNo, "Advanced Communication Skills") = vbayes Then
Me.EventName = "ACST", Me.ACST = -1
Else
Else
End If



End Sub

Hope someone can help

Thanks

Phil
Click to expand...
Click to expand...
 
Top