if then statement

  • Thread starter Thread starter Alex Martinez
  • Start date Start date
A

Alex Martinez

Hi,

I need some help here. I have the following field:
MD - combobox
FE - Yes/No field
FV - Yes/No Field

FP - combobox
CO - combobox
Rev - combobox
Ck - combobox
Dis - combobox

If the user populates MD or FE or FV field and also populates the FP or CO
or CK or Rev, or Dis field I want an error message to appear (see below),
but it seems that I am not 100% sucessful. For example the user can
populate the cboFP field and populate the cboMD field selecting whats in
the combobox for example "A1" then the dialog box appear, but if the use
select "A2" in the cboMD and the cboFP or any other field (CO, CK, Rev, or
Dis) I don't get the dialog box. What am I doing wront. Thank you in
advance.

Private Sub Form_BeforeUpdate(Cancel As Integer)
ElseIf Me.[cboMD]) = "A1" or Me.[cboMD]) = "A2" Or Me.[FV] = -1 Or Me.[FE]
= -1 And Not IsNull(Me.[cboFP]) Or Not IsNull(Me.[cboCO]) Or Not
IsNull(Me.[cboRev]) Or Not IsNull(Me.[cboCk]) Or Not IsNull(Me.[cboDis])
Then
Cancel = True
MsgBox "Is this a PA or MA?. Try Again."
End If
End Sub
 
if i understand correctly, you have two "groups" of controls. groupA is made
up of controls MD, FE, and FV. groupB is made up of controls FP, CO, Rev,
Ck, and Dis. the two groups are mutually exclusive - if a value is entered
in a groupA control, then no value should be entered in any groupB
control...and vice versa.

if the above is correct, suggest you validate the data entry in those
controls as it occurs, instead of waiting for the form's BeforeUpdate event.
i would simply run code on each control's AfterUpdate event; if something is
entered in a groupA control, then any data in any groupB control would be
erased and a msgbox opened to notify the user; and the same action on groupA
controls when data is entered in a groupB control.

to set up the above, do the following:

set the Tag property of the MD, FE, and FV controls to A. you can do this
quickly by selecting all three controls at once, and typing an A in the Tag
property in the Properties box (see the Other tab). set the Tag property of
the FP, CO, Rev, Ck, and Dis controls to B. do *not* use single or double
quotes on the Tag line, just type the capital letter by itself.

add the following procedure to the form's module, as

Private Sub isCheckGroups(ByVal strGroup As String)

Dim ctrl As Control, blnNotice As Boolean
blnNotice = False

If Not IsNull(Screen.ActiveControl) Then
For Each ctrl In Me.Controls
If ctrl.Tag = strGroup Then
If Not IsNull(ctrl) Then
ctrl = Null
blnNotice = True
End If
End If
Next ctrl
End If

If blnNotice Then
Dim strFields As String
If strGroup = "A" Then
strFields = "MD, FE, and FV"
Else
strFields = "FP, CO, Rev, Ck, and Dis"
End If
MsgBox "The data has been erased from fields " & strFields & " in
this record."
End If

End Sub

in the AfterUpdate event procedure of the MD, FE, and FV controls, add the
following line of code, as

isCheckGroups "B"

in the AfterUpdate event procedure of the FP, CO, Rev, Ck, and Dis controls,
add the following line of code, as

isCheckGroups "A"

hth


Alex Martinez said:
Hi,

I need some help here. I have the following field:
MD - combobox
FE - Yes/No field
FV - Yes/No Field

FP - combobox
CO - combobox
Rev - combobox
Ck - combobox
Dis - combobox

If the user populates MD or FE or FV field and also populates the FP or CO
or CK or Rev, or Dis field I want an error message to appear (see below),
but it seems that I am not 100% sucessful. For example the user can
populate the cboFP field and populate the cboMD field selecting whats in
the combobox for example "A1" then the dialog box appear, but if the use
select "A2" in the cboMD and the cboFP or any other field (CO, CK, Rev, or
Dis) I don't get the dialog box. What am I doing wront. Thank you in
advance.

Private Sub Form_BeforeUpdate(Cancel As Integer)
ElseIf Me.[cboMD]) = "A1" or Me.[cboMD]) = "A2" Or Me.[FV] = -1 Or Me.[FE]
= -1 And Not IsNull(Me.[cboFP]) Or Not IsNull(Me.[cboCO]) Or Not
IsNull(Me.[cboRev]) Or Not IsNull(Me.[cboCk]) Or Not IsNull(Me.[cboDis])
Then
Cancel = True
MsgBox "Is this a PA or MA?. Try Again."
End If
End Sub
Click to expand...
 
Back
Top