If Then Statement...

  • Thread starter Thread starter PainInMyAccess via AccessMonster.com
  • Start date Start date
P

PainInMyAccess via AccessMonster.com

I am working on a form where I have a checkbox. If the checkbox is true then
I would like another text box to be required. I apologize but I only know
the basics of access. Please be as specific as possible.

Thanks in advance for your help!
 
You will need some VBA code in the Before Update event of your form. First I
will show you the code, then I will walk you through how to put it in the
correct place.
I will have to make up some names for the check box and text box. You will
have the change them to the actual names of the controls they represent.
The logic of this is before the form will allow the current record to be
updated, it checks to see if the Check Box is Checked. If it is, It checks
to see if anything is in the text box, it will present an error message to
the user, prevent the record from being updated, and position the cursor in
the required field so a value can be entered; Otherwise, it allows the update.

If Me.MyCheckBoxName = True Then 'It is checked
If IsNull(Me.MyTextBoxName) Then 'Nothing is entered
MsgBox("This Field Is Required")
Cancel = True 'Stop the Update
End If
End If

Now, to get it into place.

Open your form in design mode.
Right Click and select properties.
In the text box at the top of the dialog, select Form.
Select the Events tab.
Click in the Before Update event box.
Click on the small command button to the right with the 3 dots.
Select Code Builder.
Click OK.
The VBA editor will open with the cursor positioned in correct Sub. It will
look like this

Private Sub Form_BeforeUpdate(Cancel As Integer)
|
End Sub

Paste the code where the cursor it.
Be sure to change the names of the controls.
Save it.

Post back if you have more questions.
 
Hi I have a similar issue that you responded to. This is what I tried:

If Me.ShippingAddressPatient = True Then 'It is checked
If IsNull(Me.ShippingAddressPatient) Then 'Nothing is entered
Text43 ("This Field Is Required")
Cancel = True 'Stop the Update
End If
End If

It stated that I have a syntax error and highlighted Text43. Text 43 is the
text box that is required if the check box is true. Sorry, I no nothing
about vba. Thank you for being so patient and descriptive for us beginners!
 
I think you are getting a couple of things confused. I think what you really
want is:

If Me.ShippingAddressPatient = True Then 'It is checked
If IsNull(Me.Text43) Then 'Nothing is entered
MsgBox "This Field Is Required"
Cancel = True 'Stop the Update
End If
End If

Breaking it down line by line:

'Look to see if the control ShippingAddressPatient is checked
If Me.ShippingAddressPatient = True Then 'It is checked

'It is checkeced, so we look to see what value is in the control Text43
'If nothing has been entered, then it will evaluate to Null
'Use the IsNull function to determine whether anything has bee entered.
If IsNull(Me.Text43) Then 'Nothing is entered

'Text43 has nothing in it, so present a message box to the user to
tell them
'the field must have a value
MsgBox "This Field Is Required"

'Prevent the record from being updated in the table
Cancel = True 'Stop the Update
End If
End If
 
I have since modified the form and now my check boxes are toggle boxes in an
option group. I changed the first statement in the code to represent the
option group frame and I received an error. I then tried the name of the
toggle "yes" and nothing happened. The toggle for yes is Option59 and the
frame for the option group is Frame 56. Sorry I tried all all different
options. I would appreciate any help!
I think you are getting a couple of things confused. I think what you really
want is:

If Me.ShippingAddressPatient = True Then 'It is checked
If IsNull(Me.Text43) Then 'Nothing is entered
MsgBox "This Field Is Required"
Cancel = True 'Stop the Update
End If
End If

Breaking it down line by line:

'Look to see if the control ShippingAddressPatient is checked
If Me.ShippingAddressPatient = True Then 'It is checked

'It is checkeced, so we look to see what value is in the control Text43
'If nothing has been entered, then it will evaluate to Null
'Use the IsNull function to determine whether anything has bee entered.
If IsNull(Me.Text43) Then 'Nothing is entered

'Text43 has nothing in it, so present a message box to the user to
tell them
'the field must have a value
MsgBox "This Field Is Required"

'Prevent the record from being updated in the table
Cancel = True 'Stop the Update
End If
End If
Hi I have a similar issue that you responded to. This is what I tried:
Click to expand...
[quoted text clipped - 55 lines]
Click to expand...
 
It would be helpful if you post the code; however, it may be in the way you
are addressing your option group. Each button in an option group has an
Option Value property. When a button is pressed, it makes the value of the
option group whatever it's Option Value is. To find the value in an option
group, you just address it the same way you would a text box.

=Me.Frame1

So, if you have a button labeled Yes and and it has a value of -1 then the
above would return -1.


ladybug via AccessMonster.com said:
I have since modified the form and now my check boxes are toggle boxes in an
option group. I changed the first statement in the code to represent the
option group frame and I received an error. I then tried the name of the
toggle "yes" and nothing happened. The toggle for yes is Option59 and the
frame for the option group is Frame 56. Sorry I tried all all different
options. I would appreciate any help!
I think you are getting a couple of things confused. I think what you really
want is:

If Me.ShippingAddressPatient = True Then 'It is checked
If IsNull(Me.Text43) Then 'Nothing is entered
MsgBox "This Field Is Required"
Cancel = True 'Stop the Update
End If
End If

Breaking it down line by line:

'Look to see if the control ShippingAddressPatient is checked
If Me.ShippingAddressPatient = True Then 'It is checked

'It is checkeced, so we look to see what value is in the control Text43
'If nothing has been entered, then it will evaluate to Null
'Use the IsNull function to determine whether anything has bee entered.
If IsNull(Me.Text43) Then 'Nothing is entered

'Text43 has nothing in it, so present a message box to the user to
tell them
'the field must have a value
MsgBox "This Field Is Required"

'Prevent the record from being updated in the table
Cancel = True 'Stop the Update
End If
End If
Hi I have a similar issue that you responded to. This is what I tried:
Click to expand...
[quoted text clipped - 55 lines]
Thanks in advance for your help!
Click to expand...
Click to expand...
Click to expand...
 
I have:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Frame56 = True Then 'It is checked
If IsNull(Me.Text43) Then 'Nothing is entered
MsgBox "Reship Order Number is Required"
Cancel = True 'Stop the Update
End If
End If

The Button YES =1, button NO=2.
These two buttons are in Frame56.

I do not get an error with the code above, however it does not prevent the
form from being updated if YES is selected and the Text is not completed.


It would be helpful if you post the code; however, it may be in the way you
are addressing your option group. Each button in an option group has an
Option Value property. When a button is pressed, it makes the value of the
option group whatever it's Option Value is. To find the value in an option
group, you just address it the same way you would a text box.

=Me.Frame1

So, if you have a button labeled Yes and and it has a value of -1 then the
above would return -1.
I have since modified the form and now my check boxes are toggle boxes in an
option group. I changed the first statement in the code to represent the
Click to expand...
[quoted text clipped - 38 lines]
Click to expand...
 
Frame56 will never = True
The value of True is -1, but your Frame will only return 1 or 2.
There are two solutions.
Change the Option Values for your option buttons to:
-1 for True
0 for False
Or, change your code to compare to 1 or 2

ladybug via AccessMonster.com said:
I have:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Frame56 = True Then 'It is checked
If IsNull(Me.Text43) Then 'Nothing is entered
MsgBox "Reship Order Number is Required"
Cancel = True 'Stop the Update
End If
End If

The Button YES =1, button NO=2.
These two buttons are in Frame56.

I do not get an error with the code above, however it does not prevent the
form from being updated if YES is selected and the Text is not completed.


It would be helpful if you post the code; however, it may be in the way you
are addressing your option group. Each button in an option group has an
Option Value property. When a button is pressed, it makes the value of the
option group whatever it's Option Value is. To find the value in an option
group, you just address it the same way you would a text box.

=Me.Frame1

So, if you have a button labeled Yes and and it has a value of -1 then the
above would return -1.
I have since modified the form and now my check boxes are toggle boxes in an
option group. I changed the first statement in the code to represent the
Click to expand...
[quoted text clipped - 38 lines]
Thanks in advance for your help!
Click to expand...
Click to expand...
Click to expand...
 
Perfect. Thank you again!
Frame56 will never = True
The value of True is -1, but your Frame will only return 1 or 2.
There are two solutions.
Change the Option Values for your option buttons to:
-1 for True
0 for False
Or, change your code to compare to 1 or 2
I have:
Click to expand...
[quoted text clipped - 28 lines]
Click to expand...
 
Actually, any non-zero value is evaluated as True.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Klatuu said:
Frame56 will never = True
The value of True is -1, but your Frame will only return 1 or 2.
There are two solutions.
Change the Option Values for your option buttons to:
-1 for True
0 for False
Or, change your code to compare to 1 or 2

ladybug via AccessMonster.com said:
I have:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Frame56 = True Then 'It is checked
If IsNull(Me.Text43) Then 'Nothing is entered
MsgBox "Reship Order Number is Required"
Cancel = True 'Stop the Update
End If
End If

The Button YES =1, button NO=2.
These two buttons are in Frame56.

I do not get an error with the code above, however it does not prevent the
form from being updated if YES is selected and the Text is not completed.


It would be helpful if you post the code; however, it may be in the way you
are addressing your option group. Each button in an option group has an
Option Value property. When a button is pressed, it makes the value of the
option group whatever it's Option Value is. To find the value in an option
group, you just address it the same way you would a text box.

=Me.Frame1

So, if you have a button labeled Yes and and it has a value of -1 then the
above would return -1.

I have since modified the form and now my check boxes are toggle boxes in an
option group. I changed the first statement in the code to represent the
[quoted text clipped - 38 lines]

Thanks in advance for your help!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top