Required field based on 2 conditions

[SylvieB]

I have a form that has a Close process as follow, when the user select “Wonâ€
or “Lost†from the combo box Reason, AwardAmount and AwardTo are required
fields. They both need to be filled out by the user. If other options other
than “Won†or “Lost†are selected from the Reason combo box, the two fields
are not required. I’m having a hard time to write the code; please help.
Here the code so far:
If Me.Closed = true then
ifNz(Reason.won) = 0 then
msgBox “this is a required fieldâ€, vbInformation, “missing valueâ€
cancel = true
ifNz(Reason.lost) = 0 then
msgBox “this is a required fieldâ€, vbinformation, “missing
valueâ€
cancel = true
ifNz(AwardTo, 0)= 0 then
MsgBox “You must select a name from the listâ€, vbInformation, “Missing valueâ€
Cancel = True
elseIf Nz(AwardAmount, 0)=0 then
MsgBox “you must enter an award amountâ€, “vbinformation, “Missing valueâ€
Cancel = true
Endif
Endif

Thank you

 

[Dirk Goldgar]

I have a form that has a Close process as follow, when the user select
“Wonâ€
or “Lost†from the combo box Reason, AwardAmount and AwardTo are required
fields. They both need to be filled out by the user. If other options
other
than “Won†or “Lost†are selected from the Reason combo box, the two
fields
are not required. I’m having a hard time to write the code; please help.
Here the code so far:
If Me.Closed = true then
ifNz(Reason.won) = 0 then
msgBox “this is a required fieldâ€, vbInformation, “missing valueâ€
cancel = true
ifNz(Reason.lost) = 0 then
msgBox “this is a required fieldâ€, vbinformation, “missing
valueâ€
cancel = true
ifNz(AwardTo, 0)= 0 then
MsgBox “You must select a name from the listâ€, vbInformation, “Missing
valueâ€
Cancel = True
elseIf Nz(AwardAmount, 0)=0 then
MsgBox “you must enter an award amountâ€, “vbinformation, “Missing valueâ€
Cancel = true
Endif
Endif

There's a lot there that looks wrong, but there are things that aren't clear
about how you have this set up.

When does the code get executed? It looks to me as though it should be in
the form's BeforeUpdate event. Is that where it is? For now, I'll assume
it is.

Is "Closed" the name of a boolean (Yes/No) field in the form's recordsource?
Is it the name of a checkbox control on the form, possibly bound to such a
field? For now, I'll assume both are correct.

Is the combo box "Reason" set up such that its values are text items such as
"Won" and "Lost"? I'll assume so. Also, I think your code implies that the
Reason combo may not be left blank. I'm assuming that, too.

If all my assumptions are correct, your code should *probably* be something
like the following:

'----- start of revised code -----
Dim strMessage As String

If Me.Closed = True Then

Select Case Me.Reason

Case "Won", "Lost"

If IsNull(Me.AwardTo Then
Me.AwardTo.SetFocus
strMessage = vbCr & "You must select a name from the
list.â€
End If

If Nz(Me.AwardAmount, 0) = 0 Then
If Len(strMessage) = 0 Then Me.AwardAmount.SetFocus
strMessage = strMessage & vbCr & _
“You must enter an award amount.â€,
End If

Case Else
If IsNull(Me.Reason) Then
strMessage = vbCr & "You must select a reason."
Me.Reason.SetFocus
End If

End Select

End If

' If we have any message to display, that means
' some required information is missing.
If Len(strMessage) > 0 Then

Cancel = True

MsgBox _
“Required information is missing:" & vbCr & strMessage, _
vbInformation, _
“Missing valueâ€

End If
'----- end of revised code -----

 

[Sylvie Boulekfouf]

Hi Dirk
Thank you so much for your answer. Yes, your assumptions are correct, the code gets executed in BeforeUpdate event; Closed is a check box control; Reason value are text and can not be blank. I'm confused with your lines of code. I'm new to VBA so please bear with me. Why are you using the Case statement and not If, Then, Else.
the combo box Reason has 4 values (won, lost, not pursued, cancelled). the scenario is as follow: when a user select a reason (required field), only if "won" or "lost" is selected, he needs to fill out two text boxes: the award amount and a company name also on the same form. Any other option selected from Reason won't required the user to fill out the two text boxed mentioned above. To close the form, the user uses the Closed box control and check mark it. If no Reason is selected, the form wont' close and a message will pop up asking to select a reason. I hope it makes more sense.
I used your codes and when I'm getting a compile error at
Me.AwardTo.SetFocus. Any ideas?
Again thank you for any help you can provide. I really appreciate it.
Sylvie




Dirk Goldgar wrote:

There is a lot there that looks wrong, but there are things that are not
08-Feb-10

There is a lot there that looks wrong, but there are things that are not clea
about how you have this set up

When does the code get executed? It looks to me as though it should be i
the form's BeforeUpdate event. Is that where it is? For now, I will assum
it is

Is "Closed" the name of a boolean (Yes/No) field in the form's recordsource
Is it the name of a checkbox control on the form, possibly bound to such
field? For now, I will assume both are correct

Is the combo box "Reason" set up such that its values are text items such a
"Won" and "Lost"? I will assume so. Also, I think your code implies that th
Reason combo may not be left blank. I am assuming that, too

If all my assumptions are correct, your code should *probably* be somethin
like the following

'----- start of revised code ----
Dim strMessage As Strin

If Me.Closed = True The

Select Case Me.Reaso

Case "Won", "Lost

If IsNull(Me.AwardTo The
Me.AwardTo.SetFocu
strMessage = vbCr & "You must select a name from th
list.??
End I

If Nz(Me.AwardAmount, 0) = 0 The
If Len(strMessage) = 0 Then Me.AwardAmount.SetFocu
strMessage = strMessage & vbCr &
???You must enter an award amount.???
End I

Case Els
If IsNull(Me.Reason) The
strMessage = vbCr & "You must select a reason.
Me.Reason.SetFocu
End I

End Selec

End I

' If we have any message to display, that mean
' some required information is missing
If Len(strMessage) > 0 The

Cancel = Tru

MsgBox
???Required information is missing:" & vbCr & strMessage,
vbInformation,
???Missing value??

End I
'----- end of revised code ----

-
Dirk Goldgar, MS Access MV
Access tips: www.datagnostics.com/tips.htm

(please reply to the newsgroup)

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
How to display a Gravatar Image with 100 Percent Client Script Code
http://www.eggheadcafe.com/tutorial...c-b0877c10ecb4/how-to-display-a-gravatar.aspx

 

[Dirk Goldgar]

in message

Yes, your assumptions are correct, the code gets executed in BeforeUpdate
event; Closed is a check box control; Reason value are text and can not be
blank. I'm confused with your lines of code. I'm new to VBA so please bear
with me. Why are you using the Case statement and not If, Then, Else.

The same logic could be implemented using "If ... Then ... Else" or "If ...
Then ... ElseIf". I used Select Case because it seemd more concise to me,
given that we are dealing with cases -- possible values of the Reason
control.

the combo box Reason has 4 values (won, lost, not pursued, cancelled). the
scenario is as follow: when a user select a reason (required field), only
if "won" or "lost" is selected, he needs to fill out two text boxes: the
award amount and a company name also on the same form. Any other option
selected from Reason won't required the user to fill out the two text
boxed mentioned above. To close the form, the user uses the Closed box
control and check mark it. If no Reason is selected, the form wont' close
and a message will pop up asking to select a reason. I hope it makes more
sense.

That sounds like the logic my code is intended to implement.

I used your codes and when I'm getting a compile error at
Me.AwardTo.SetFocus. Any ideas?

What is the error message? If AwardTo is a control on the form and is
capable of receiving the focus, I don't see why you would get a compile
error there.

Are you sure the error was on that line and not on the next one? I see that
in my post, the next line of code was wrapped by the newsreader, so that
what should have been on one line got broken into two. If you didn't fix
that break, it would give a compile error. So where the line in my original
post came across like this:

strMessage = vbCr & "You must select a name from the
list.”

.... you should change it so that it's like this:

strMessage = vbCr & "You must select a name from the list.”

(but with the indentation adjusted for consistency).

 

[SylvieB]

Yes, I'm sure. Here is the error i'm getting:
compile Error
Method or data member not found

and yes, AwardTo is a control on the form

 

[SylvieB]

ok. My mistake i mistyped the control name. I fixed it. Now when i run the
code, it says: "run-time error '2108': you must save the field before you
execute teh GoToControl action, the GoToControl method, or the SetFocus
method".
Sorry to be a pain. Really i appreciate your patience.

 

[Dirk Goldgar]

ok. My mistake i mistyped the control name. I fixed it. Now when i run the
code, it says: "run-time error '2108': you must save the field before you
execute teh GoToControl action, the GoToControl method, or the SetFocus
method".

That sounds like you don't have this code in the BeforeUpdate event of the
form, but maybe in the BeforeUpdate event of a *control*. Is that the case?
You shouldn't really put multi-field validation code in the BeforeUpdate
event of a control. Validation that involves the values of mutliple
controls belongs in the BeforeUpdate event of the form itself.

If that is not the problem, please post the complete event procedure.

 

[SylvieB]

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMessage As String

If Me.ClosedOpp = True Then
Select Case Me.OppReason
Case "Won", "Lost"

If IsNull(Me.AwardTo) Then
Me.AwardTo.SetFocus
strMessage = vbCr & "You must select a name from the list"
End If

If Nz(Me.AwardAmount, 0) = 0 Then
If Len(strMessage) = 0 Then Me.AwardAmount.SetFocus
strMessage = vbCr & "You must enter an award amount"
End If

Case Else
If IsNull(Me.OppReason) Then
strMessage = vbCr & "You must select a reason."
Me.OppReason.SetFocus
End If

End Select

End If

If Len(strMessage) > 0 Then

Cancel = True

MsgBox "enter notes" & vbCr & strMessage, vbInformation, "missing
value"

End If
End Sub

 

[Dirk Goldgar]

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMessage As String

If Me.ClosedOpp = True Then
Select Case Me.OppReason
Case "Won", "Lost"

If IsNull(Me.AwardTo) Then
Me.AwardTo.SetFocus
strMessage = vbCr & "You must select a name from the list"
End If

If Nz(Me.AwardAmount, 0) = 0 Then
If Len(strMessage) = 0 Then Me.AwardAmount.SetFocus
strMessage = vbCr & "You must enter an award amount"
End If

Case Else
If IsNull(Me.OppReason) Then
strMessage = vbCr & "You must select a reason."
Me.OppReason.SetFocus
End If

End Select

End If

If Len(strMessage) > 0 Then

Cancel = True

MsgBox "enter notes" & vbCr & strMessage, vbInformation, "missing
value"

End If
End Sub

Hmm, I may be missing something but I don't see anything wrong there. Are
you sure the error is occurring in this procedure? When the error occurs,
does the message have a Debug button? If so, click it and see what
statement is highlighted as the cause of the error.

 

[Sylvie]

Hmm, I may be missing something but I don't see anything wrong there.  Are
you sure the error is occurring in this procedure?  When the error occurs,
does the message have a Debug button?  If so, click it and see what
statement is highlighted as the cause of the error.

--
Dirk Goldgar, MS Access MVP
Access tips:www.datagnostics.com/tips.html

(please reply to the newsgroup)- Hide quoted text -

- Show quoted text -

Hey Dirk

It's working fine now. I don't know what changed since yesterday but
THANK YOU very much for all your help and patience. One last thing if
you don't mind, when i click OK to the pop up message telling me that
i need to add for example an award amount, the message does not go
away. I click a second time and it closes the form. I'm sure that's
something simple. How can i make the message to go away without
closing the form when clicking OK? again Thank you

 

[Dirk Goldgar]

It's working fine now. I don't know what changed since yesterday but THANK
YOU very much for all your help and patience. One last thing if you don't
mind, when i click OK to the pop up message telling me that i need to add
for example an award amount, the message does not go away. I click a
second time and it closes the form. I'm sure that's something simple. How
can i make the message to go away without closing the form when clicking
OK?

Hmm, that doesn't sound like "working fine" to me. Although you posted your
code in an earlier message, would you mind copying and pasting it once
again, as it is now, into a new reply message? I don't see how the code
I've seen could give the behavior you report.

Is there any code in other form events that might account for the second
message?