MANDATORY OR NONMANDATORY FIELDS?

[Field Options?]

I have to create an access database. I need to have an option for when the
field is a specific data element (e.g. male or female) that it is mandatory
or nonmandatory to fill out the next set of fields (e.g. shade out the next
set of fields if its male and make the fields mandatory to fill out if
female).

Can anyone help me??

 

[John W. Vinson]

I have to create an access database. I need to have an option for when the
field is a specific data element (e.g. male or female) that it is mandatory
or nonmandatory to fill out the next set of fields (e.g. shade out the next
set of fields if its male and make the fields mandatory to fill out if
female).

Can anyone help me??

This can be done with considerable difficulty using a Table Validation rule,
but I'd be inclined to create a Form to enter data into this table (something
you should do in any case) and put VBA code in the AfterUpdate event of the
Sex field to set the other controls' properties. You certainly cannot "shade
out" fields in a Table.

 

[Field Options?]

John,

How do you do this? I am not familar with VBA codes. I tried researching
online but I couldn't find anything helpful.

 

[John W. Vinson]

John,

How do you do this? I am not familar with VBA codes. I tried researching
online but I couldn't find anything helpful.

Well, I can't see your database and don't know specifics, but it would be
something like the following. View the Properties of the form. Assuming that
you have a combo box cboSex to select the sex, find the AfterUpdate property
on the Events tab; click the ... icon by it, and choose Code Builder. You'll
be given a VBA editor screen containing the Sub and End Sub lines. Edit them
to something like this (using your own control names of course):

Private Sub cboSex_AfterUpdate()
If Me!cboSex = "Male" Then
Me!txtThis.Enabled = False ' disable inappropriate controls
Me!txtThat.Enabled = False
<do any other appropriate changes for males>
Else
Me!txtThis.Enabled = True ' enable
Me!txtThat.Enabled = True
<ditto for females>
End If
End Sub

To make a field mandatory you need to check its value in the Form's
BeforeUpdate event:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If Me!cboSex = "Female" Then
If IsNull(Me!txtThis) Then
MsgBox "Please fill in <whatever this textbox is>", vbOKOnly
Cancel = True
Me!txtThis.SetFocus
End If
End If
<similar for the other controls>

 

[Field Options?]

John - how do you know these codes? Where can I learn these different type of
codes?

 

[John W. Vinson]

John - how do you know these codes?

I started programming (in FORTRAN II) in 1967 and have been learning ever
since.

Where can I learn these different type of codes?

Classes, books, Help files, user groups, practice, practice, practice...

Here's some references to get started.

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

 

[Field Options?]

John - is there just a module/lesson plan for this particular subject I need
help in? Thank you for this information. I didn't know the how complex access
was until this.

 

[John W. Vinson]

John - is there just a module/lesson plan for this particular subject I need
help in?

Well... neither you nor I know what you DON'T know of course, and I certainly
don't know what you feel that you need to know. VBA is a full-featured
programming language, with all the constructs of any other full language
(Fortran, PL/I, C, etc.); it is extended in having an object model for Access
database objects, but for the fundamentals of programming it's just another
language. If you've never learned to program a procedural language you'll have
that hill to climb of course!

John Viescas' book on VBA programming comes highly recommended (I don't happen
to have a copy), and there are others. It might be worth a trip to a *good*
large bookstore; pull six or eight relevant books off the shelf and see which
fit your learning style and information needs.