My code- she ain't pretty!

S

Stephanie

Hi. I'm hoping for help streamlining code and getting msg
boxes to activate correctly and timely.

I have 2 options that are yes/no each. Yes, I've been
yelled at but believe me it works better this way. Option1
(Member) and Option2 (READAffiliate).
When a user enters data on the form, I require them to
choose either Option1 or Option2 (I have this on the
clicks: Me!Option1 = Not Me!Option2).

I want the 1) Option1/Option2, and 2)MemberDateName, 3)
MemberStatusName, 4) MemberTypeName and 5)
StateOrProvinceName all required whether or not its
Option1 or Option2.

I can't get MemberStatusName and MemberTypeName to
trigger. And after the msg boxes for "Member or READ
Team" or "Date" pops up, the "State" msg box triggers
immediately. Argh!
Since I have Me.Undo I can't seem to debug and walk
through (I'd appreciate advice on this too). Thanks and
here goes:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim iAns As Integer
If (Me!Option1) = 0 And (Me!Option2) = 0 Then
iAns = MsgBox("Please select either 'ITA Member'
or 'R.E.A.D. Team' to continue, or click Cancel to erase
this record", vbOKCancel)
If iAns = vbOK Then
Cancel = True
Me!Option1.SetFocus
Else
Cancel = True
Me.Undo ' erase the entire form
End If
End If
If IsNull(Me!MemberDateName) And (Not IsNull(Option1) Or
Not IsNull(Option2)) Then
iAns = MsgBox("Please enter an 'Associated Since' Date
to continue, or click Cancel to erase this record",
vbOKCancel)
If iAns = vbOK Then
Cancel = True
Me!MemberDateName.SetFocus
Else
Cancel = True
Me.Undo ' erase the entire form
End If
End If
If Not IsNull(Option1) Or Not IsNull(Option2) Then
If IsNull(Me!MemberStatusName) Or IsNull(Me!
MemberTypeName) Then
iAns = MsgBox("Please enter Member 'Status' and 'Type'
to continue, or click Cancel to erase this record",
vbOKCancel)
If iAns = vbOK Then
Cancel = True
Me!MemberStatusName.SetFocus
Else
Cancel = True
Me.Undo ' erase the entire form
End If
End If
End If
If Not IsNull(Option1) Or Not IsNull(Option2) Then
If IsNull(Me!StateOrProvinceName) Then
iAns = MsgBox("Please enter the 2 letter 'State' code
to continue, or click Cancel to erase this record",
vbOKCancel)
' Date to continue, or click Cancel to erase this
record", vbOKCancel)
If iAns = vbOK Then
Cancel = True
Me!StateOrProvinceName.SetFocus
Else
Cancel = True
Me.Undo ' erase the entire form
End If
End If
End If
End Sub
 
M

MacDermott

Well, there's plenty that could be streamlined, both about your code and
about your approach, (IOW - no, I don't believe you that it's better that
way...)

But let me start by pointing out that your code could be paraphrased like
this:
If Condition1 is true then
Ask Question A
If A is OK
Do This
If A is Cancel
Do That
End Of Condition

So what's the code going to do at the end of this code?
It'll go merrily on and execute whatever's next in the code.
If you want it to stop when Condition1 is true ,after your Question A but
before you look at the next question, you'll need some code before the End
If of your first condition.
Exit Sub
should do the trick.

That won't fix all of your problems, but it should get you started.
Once you get that working, post back for help on some of your other issues.

HTH
- Turtle
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top