How to update a Yes/No fields when other Yes/No fields change

[Android]

Hello,

I have following fields on 1 form:

date ___ Diploma Achieved: [ ] (Yes/No type field)


date ___ Subject 1 Achieved: [ ] (Yes/No type field)
date ___ Subject 2 Achieved: [ ] (Yes/No type field)
date ___ Subject 3 Achieved: [ ] (Yes/No type field)
etc.

What I would like to do is:
When ever a subject achieved is clicked as True, I want a macro to run
which will:
-- Set Diploma Achieved as True if all Subjects are set to True
-- Set the Diploma Achieved date as the maximum of the other `dates,
if Diploma Achieved is True

I know this should be straightforward, but I am new to Access. I have
tried using SetValue without success and have not been able to find
examples similar to what I want to do.

Any hints or links for similar examples?

Regards..

 

[Ken Sheridan]

Firstly I'll explain how you can do it your way, then why you
shouldn't and finally how it should be done.

With your current set-up you'll find it a lot easier to do it with VBA
code than macro. You'd put exactly the same code in the AfterUpdate
event procedure of each of the Subject 1 Achieved, Subject 2 Achieved,
Subject 3 Achieved etc check box controls on the form. I'm guessing
that you are not too familiar with entering code in event procedures,
so here is how its done:

Select the control in form design view and open its properties sheet
if its not already open. Then select the AfterUpdate event property
in the properties sheet. Click on the 'build' button; that's the one
on the right with 3 dots. Select 'Code Builder' in the dialogue, and
click OK. The VBA window will open at the event procedure with the
first and last lines already in place. Enter the lines of code
between these two existing lines. To do this select the control in
form design view and open its properties sheet if its not already
open. Then select the relevant event property in the properties
sheet. Click on the 'build' button; that's the one on the right with
3 dots. Select 'Code Builder' in the dialogue, and click OK. The VBA
window will open at the event procedure with the first and last lines
already in place. Enter the lines of code between these two existing
lines.

The code for each event procedure would be as follows. For this
example I'm assuming that there are only 3 subjects involved, but
you'll be able to expand the code easily enough:

If Not(Me.[Subject 1 Achieved] =False_
Or Me.[Subject 2 Achieved] =False _
Or Me.[Subject 3 Achieved] =False) Then

Me.[Diploma Achieved] = True
Else
Me.[Diploma Achieved] = False
End If

However, the Diploma Achieved field in the underlying table is
redundant here as its value is computed from the values of the Subject
# Achieved fields. So you can do without the Diploma Achieved field
completely and put an unbound check box control on the form with a
ControlSource property of:

=[Subject 1 Achieved] And [Subject 2 Achieved] And [Subject 3
Achieved]

But that's not the end of the story as the table underlying your form
suffers from a fundamental design flaw. By having multiple fields for
the different subjects you are doing what's known as 'encoding data as
column headings'. A fundamental rule of the relational database model
is that data is stored as values at column positions in tables and in
no other way – its called the 'information principle'.

What you should have are separate tables for each 'entity type'. So
you'd have tables Students and Subjects to start with. The Students
table would have a StudentID primary key column (an autonumber is fine
for this) and columns such as FirstName, LastName, DateOfBirth etc.
Each column represents an 'attribute' of the students entity type.

The Subjects table would have columns SubjectID and Subject. Again an
autonumber is fine for the SubjectID column.

SubjectsAchieved is another entity type, but in this case its a
special kind of entity type as it is also a many-to-many relationship
type between Students and Subjects, i.e. each student can take more
than one subject and each subject is taken by more than one student.
A many-to-many relationship type is modelled by a third table, which
would have columns StudentID, SubjectID (not autonumbers in this case,
just straightforward long integer number data types), DateAchieved
(don't use Date as column name as it’s the name of a built in function
inn Access), SubjectAchieved (a Yes/No (Boolean) column).

The StudentID and SubjectID columns of the SubjectsAchieved table are
foreign key columns which reference the primary keys of the Students
and Subjects table, mapping a row in Students to a row in Subjects.
The DateAchieved and DiplomaAchieved columns represent other
attributes of this entity type.

For data entry you'd use a Students form with a SubjectsAchieved
subform embedded in it, the form and subform being linked on the
StudentID fields. The subform would be in continuous form or
datasheet view and would have control bound to the SubjectID,
DateAchieved and SubjectAchieved fields. The DateAchieved and
SubjectAchieved controls would be a text box and check box
respectively, the SubjectID control a combo box set up as follows:

ControlSource: SubjectID

RowSource: SELECT SubjectID, Subject, FROM Subjects ORDER BY
Subject;

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access
will automatically convert the last one. The important thing is that
the first dimension is zero to hide the first column and that the
second is at least as wide as the combo box.

To add a subject for a student is simple a case of inserting a new
record in the subform, selecting the subject from the combo box's list
in each case.

Again you don't need a Diploma Achieved field in any of the tables.
You can put an unbound combo box on the Students form with a
ControlSource property such as:

=(DCount("*", "SubjectsAchieved", "Not SubjectAchieved And StudentID =
" & [StudentID]) = 0) And (DCount("*", "SubjectsAchieved", "StudentID
= " & [StudentID]) > 0)

Ken Sheridan
Stafford, England

Hello,

I have following fields on 1 form:

date ___ Diploma Achieved: [ ] (Yes/No type field)

date ___ Subject 1 Achieved: [ ] (Yes/No type field)
date ___ Subject 2 Achieved: [ ] (Yes/No type field)
date ___ Subject 3 Achieved: [ ] (Yes/No type field)
etc.

What I would like to do is:
When ever a subject achieved is clicked as True, I want a macro to run
which will:
-- Set Diploma Achieved as True if all Subjects are set to True
-- Set the Diploma Achieved date as the maximum of the other `dates,
if Diploma Achieved is True

I know this should be straightforward, but I am new to Access. I have
tried using SetValue without success and have not been able to find
examples similar to what I want to do.

Any hints or links for similar examples?

Regards..

 

[Android]

Thank you for the detailed info.

For the time being I'm going to use the option of the one form (will
implement the 'information principle' later).

I used your code as described below, however the [Diploma Achieved]
field does not seem to be updating (I do want to use it for now even
though it is redundant).

Regards..

Firstly I'll explain how you can do it your way, then why you
shouldn't and finally how it should be done.

With your current set-up you'll find it a lot easier to do it with VBA
code than macro.  You'd put exactly the same code in the AfterUpdate
event procedure of each of the Subject 1 Achieved, Subject 2 Achieved,
Subject 3 Achieved etc check box controls on the form.  I'm guessing
that you are not too familiar with entering code in event procedures,
so here is how its done:

Select the control in form design view and open its properties sheet
if its not already open.  Then select the AfterUpdate event property
in the properties sheet.  Click on the 'build' button; that's the one
on the right with 3 dots.  Select 'Code Builder' in the dialogue, and
click OK.  The VBA window will open at the event procedure with the
first and last lines already in place.  Enter the lines of code
between these two existing lines.  To do this select the control in
form design view and open its properties sheet if its not already
open.  Then select the relevant event property in the properties
sheet.  Click on the 'build' button; that's the one on the right with
3 dots.  Select 'Code Builder' in the dialogue, and click OK.  The VBA
window will open at the event procedure with the first and last lines
already in place.  Enter the lines of code between these two existing
lines.

The code for each event procedure would be as follows.  For this
example I'm assuming that there are only 3 subjects involved, but
you'll be able to expand the code easily enough:

    If Not(Me.[Subject 1 Achieved] =False_
        Or Me.[Subject 2 Achieved] =False _
        Or Me.[Subject 3 Achieved] =False) Then

        Me.[Diploma Achieved] = True
   Else
        Me.[Diploma Achieved] = False
   End If

However, the Diploma Achieved field in the underlying table is
redundant here as its value is computed from the values of the Subject
# Achieved fields.  So you can do without the Diploma Achieved field
completely and put an unbound check box control on the form with a
ControlSource property of:

    =[Subject 1 Achieved] And [Subject 2 Achieved] And [Subject 3
Achieved]

But that's not the end of the story as the table underlying your form
suffers from a fundamental design flaw.  By having multiple fields for
the different subjects you are doing what's known as 'encoding data as
column headings'.  A fundamental rule of the relational database model
is that data is stored as values at column positions in tables and in
no other way – its called the 'information principle'.

What you should have are separate tables for each 'entity type'.  So
you'd have tables Students and Subjects to start with.  The Students
table would have a StudentID primary key column (an autonumber is fine
for this) and columns such as FirstName, LastName, DateOfBirth etc.
Each column represents an 'attribute' of the students entity type.

The Subjects table would have columns SubjectID and Subject.  Again an
autonumber is fine for the SubjectID column.

SubjectsAchieved is another entity type, but in this case its a
special kind of entity type as it is also a many-to-many relationship
type between Students and Subjects, i.e. each student can take more
than one subject and each subject is taken by more than one student.
A many-to-many relationship type is modelled by a third table, which
would have columns StudentID, SubjectID (not autonumbers in this case,
just straightforward long integer number data types), DateAchieved
(don't use Date as column name as it’s the name of a built in function
inn Access), SubjectAchieved (a Yes/No (Boolean) column).

The StudentID and SubjectID columns of the SubjectsAchieved table are
foreign key columns which reference the primary keys of the Students
and Subjects table, mapping a row in Students to a row in Subjects.
The DateAchieved and DiplomaAchieved columns represent other
attributes of this entity type.

For data entry you'd use a Students form with a SubjectsAchieved
subform embedded in it, the form and subform being linked on the
StudentID fields.  The subform would be in continuous form or
datasheet view and would have control bound to the SubjectID,
DateAchieved and SubjectAchieved fields.  The DateAchieved and
SubjectAchieved controls would be a text box and check box
respectively, the SubjectID control a combo box set up as follows:

ControlSource:    SubjectID

RowSource:     SELECT SubjectID, Subject, FROM Subjects ORDER BY
Subject;

BoundColum:   1
ColumnCount:  2
ColumnWidths:  0cm;8cm

If your units of measurement are imperial rather than metric Access
will automatically convert the last one.  The important thing is that
the first dimension is zero to hide the first column and that the
second is at least as wide as the combo box.

To add a subject for a student is simple a case of inserting a new
record in the subform, selecting the subject from the combo box's list
in each case.

Again you don't need a Diploma Achieved field in any of the tables.
You can put an unbound combo box on the Students form with a
ControlSource property such as:

=(DCount("*", "SubjectsAchieved", "Not SubjectAchieved And StudentID =
" & [StudentID]) = 0) And (DCount("*", "SubjectsAchieved", "StudentID
= " & [StudentID]) > 0)

Ken Sheridan
Stafford, England

Hello,

I have following fields on 1 form:

   date ___     Diploma Achieved: [  ] (Yes/No type field)

   date ___     Subject 1 Achieved: [  ] (Yes/No type field)
   date ___     Subject 2 Achieved: [  ] (Yes/No type field)
   date ___     Subject 3 Achieved: [  ] (Yes/No type field)
   etc.

What I would like to do is:
When ever a subject achieved is clicked as True, I want a macro to run
which will:
  -- Set Diploma Achieved as True if all Subjects are set to True
  -- Set the Diploma Achieved date as the maximum of the other `dates,
if Diploma Achieved is True

I know this should be straightforward, but I am new to Access. I have
tried using SetValue without success and have not been able to find
examples similar to what I want to do.

Any hints or links for similar examples?

Regards..- Hide quoted text -

- Show quoted text -

 

[Ken Sheridan]

Thank you for the detailed info.

For the time being I'm going to use the option of the one form (will
implement the  'information principle' later).

I used your code as described below, however the [Diploma Achieved]
field does not seem to be updating (I do want to use it for now even
though it is redundant).

Regards..

Firstly I'll explain how you can do it your way, then why you
shouldn't and finally how it should be done.

With your current set-up you'll find it a lot easier to do it with VBA
code than macro.  You'd put exactly the same code in the AfterUpdate
event procedure of each of the Subject 1 Achieved, Subject 2 Achieved,
Subject 3 Achieved etc check box controls on the form.  I'm guessing
that you are not too familiar with entering code in event procedures,
so here is how its done:

Select the control in form design view and open its properties sheet
if its not already open.  Then select the AfterUpdate event property
in the properties sheet.  Click on the 'build' button; that's the one
on the right with 3 dots.  Select 'Code Builder' in the dialogue, and
click OK.  The VBA window will open at the event procedure with the
first and last lines already in place.  Enter the lines of code
between these two existing lines.  To do this select the control in
form design view and open its properties sheet if its not already
open.  Then select the relevant event property in the properties
sheet.  Click on the 'build' button; that's the one on the right with
3 dots.  Select 'Code Builder' in the dialogue, and click OK.  The VBA
window will open at the event procedure with the first and last lines
already in place.  Enter the lines of code between these two existing
lines.

The code for each event procedure would be as follows.  For this
example I'm assuming that there are only 3 subjects involved, but
you'll be able to expand the code easily enough:

    If Not(Me.[Subject 1 Achieved] =False_
        Or Me.[Subject 2 Achieved] =False _
        Or Me.[Subject 3 Achieved] =False) Then

        Me.[Diploma Achieved] = True
   Else
        Me.[Diploma Achieved] = False
   End If

However, the Diploma Achieved field in the underlying table is
redundant here as its value is computed from the values of the Subject
# Achieved fields.  So you can do without the Diploma Achieved field
completely and put an unbound check box control on the form with a
ControlSource property of:

    =[Subject 1 Achieved] And [Subject 2 Achieved] And [Subject 3
Achieved]

But that's not the end of the story as the table underlying your form
suffers from a fundamental design flaw.  By having multiple fields for
the different subjects you are doing what's known as 'encoding data as
column headings'.  A fundamental rule of the relational database model
is that data is stored as values at column positions in tables and in
no other way – its called the 'information principle'.

What you should have are separate tables for each 'entity type'.  So
you'd have tables Students and Subjects to start with.  The Students
table would have a StudentID primary key column (an autonumber is fine
for this) and columns such as FirstName, LastName, DateOfBirth etc.
Each column represents an 'attribute' of the students entity type.

The Subjects table would have columns SubjectID and Subject.  Again an
autonumber is fine for the SubjectID column.

SubjectsAchieved is another entity type, but in this case its a
special kind of entity type as it is also a many-to-many relationship
type between Students and Subjects, i.e. each student can take more
than one subject and each subject is taken by more than one student.
A many-to-many relationship type is modelled by a third table, which
would have columns StudentID, SubjectID (not autonumbers in this case,
just straightforward long integer number data types), DateAchieved
(don't use Date as column name as it’s the name of a built in function
inn Access), SubjectAchieved (a Yes/No (Boolean) column).

The StudentID and SubjectID columns of the SubjectsAchieved table are
foreign key columns which reference the primary keys of the Students
and Subjects table, mapping a row in Students to a row in Subjects.
The DateAchieved and DiplomaAchieved columns represent other
attributes of this entity type.

For data entry you'd use a Students form with a SubjectsAchieved
subform embedded in it, the form and subform being linked on the
StudentID fields.  The subform would be in continuous form or
datasheet view and would have control bound to the SubjectID,
DateAchieved and SubjectAchieved fields.  The DateAchieved and
SubjectAchieved controls would be a text box and check box
respectively, the SubjectID control a combo box set up as follows:

ControlSource:    SubjectID

RowSource:     SELECT SubjectID, Subject, FROM Subjects ORDER BY
Subject;

BoundColum:   1
ColumnCount:  2
ColumnWidths:  0cm;8cm

If your units of measurement are imperial rather than metric Access
will automatically convert the last one.  The important thing is that
the first dimension is zero to hide the first column and that the
second is at least as wide as the combo box.

To add a subject for a student is simple a case of inserting a new
record in the subform, selecting the subject from the combo box's list
in each case.

Again you don't need a Diploma Achieved field in any of the tables.
You can put an unbound combo box on the Students form with a
ControlSource property such as:

=(DCount("*", "SubjectsAchieved", "Not SubjectAchieved And StudentID =
" & [StudentID]) = 0) And (DCount("*", "SubjectsAchieved", "StudentID
= " & [StudentID]) > 0)

Ken Sheridan
Stafford, England

Hello,
I have following fields on 1 form:
   date ___     Diploma Achieved: [  ] (Yes/No type field)
   date ___     Subject 1 Achieved: [  ] (Yes/No type field)
   date ___     Subject 2 Achieved: [  ] (Yes/No type field)
   date ___     Subject 3 Achieved: [  ] (Yes/No type field)
   etc.
What I would like to do is:
When ever a subject achieved is clicked as True, I want a macro to run
which will:
  -- Set Diploma Achieved as True if all Subjects are set to True
  -- Set the Diploma Achieved date as the maximum of the other `dates,
if Diploma Achieved is True
I know this should be straightforward, but I am new to Access. I have
tried using SetValue without success and have not been able to find
examples similar to what I want to do.
Any hints or links for similar examples?
Regards..- Hide quoted text -

- Show quoted text -

Me culpa; it should have been:

If Me.[Subject 1 Achieved] _
And Me.[Subject 2 Achieved] _
And Me.[Subject 3 Achieved] Then

Me.[Diploma Achieved] = True
Else
Me.[Diploma Achieved] = False
End If

Ken Sheridan
Stafford, England

 

[Android]

Thank you for the detailed info.

For the time being I'm going to use the option of the one form (will
implement the  'information principle' later).

I used your code as described below, however the [Diploma Achieved]
field does not seem to be updating (I do want to use it for now even
though it is redundant).

Regards..

Firstly I'll explain how you can do it your way, then why you
shouldn't and finally how it should be done.
With your current set-up you'll find it a lot easier to do it with VBA
code than macro.  You'd put exactly the same code in the AfterUpdate
event procedure of each of the Subject 1 Achieved, Subject 2 Achieved,
Subject 3 Achieved etc check box controls on the form.  I'm guessing
that you are not too familiar with entering code in event procedures,
so here is how its done:
Select the control in form design view and open its properties sheet
if its not already open.  Then select the AfterUpdate event property
in the properties sheet.  Click on the 'build' button; that's the one
on the right with 3 dots.  Select 'Code Builder' in the dialogue, and
click OK.  The VBA window will open at the event procedure with the
first and last lines already in place.  Enter the lines of code
between these two existing lines.  To do this select the control in
form design view and open its properties sheet if its not already
open.  Then select the relevant event property in the properties
sheet.  Click on the 'build' button; that's the one on the right with
3 dots.  Select 'Code Builder' in the dialogue, and click OK.  The VBA
window will open at the event procedure with the first and last lines
already in place.  Enter the lines of code between these two existing
lines.
The code for each event procedure would be as follows.  For this
example I'm assuming that there are only 3 subjects involved, but
you'll be able to expand the code easily enough:
    If Not(Me.[Subject 1 Achieved] =False_
        Or Me.[Subject 2 Achieved] =False _
        Or Me.[Subject 3 Achieved] =False) Then
        Me.[Diploma Achieved] = True
   Else
        Me.[Diploma Achieved] = False
   End If
However, the Diploma Achieved field in the underlying table is
redundant here as its value is computed from the values of the Subject
# Achieved fields.  So you can do without the Diploma Achieved field
completely and put an unbound check box control on the form with a
ControlSource property of:
    =[Subject 1 Achieved] And [Subject 2 Achieved] And [Subject3
Achieved]
But that's not the end of the story as the table underlying your form
suffers from a fundamental design flaw.  By having multiple fields for
the different subjects you are doing what's known as 'encoding data as
column headings'.  A fundamental rule of the relational database model
is that data is stored as values at column positions in tables and in
no other way – its called the 'information principle'.
What you should have are separate tables for each 'entity type'.  So
you'd have tables Students and Subjects to start with.  The Students
table would have a StudentID primary key column (an autonumber is fine
for this) and columns such as FirstName, LastName, DateOfBirth etc.
Each column represents an 'attribute' of the students entity type.
The Subjects table would have columns SubjectID and Subject.  Againan
autonumber is fine for the SubjectID column.
SubjectsAchieved is another entity type, but in this case its a
special kind of entity type as it is also a many-to-many relationship
type between Students and Subjects, i.e. each student can take more
than one subject and each subject is taken by more than one student.
A many-to-many relationship type is modelled by a third table, which
would have columns StudentID, SubjectID (not autonumbers in this case,
just straightforward long integer number data types), DateAchieved
(don't use Date as column name as it’s the name of a built in function
inn Access), SubjectAchieved (a Yes/No (Boolean) column).
The StudentID and SubjectID columns of the SubjectsAchieved table are
foreign key columns which reference the primary keys of the Students
and Subjects table, mapping a row in Students to a row in Subjects.
The DateAchieved and DiplomaAchieved columns represent other
attributes of this entity type.
For data entry you'd use a Students form with a SubjectsAchieved
subform embedded in it, the form and subform being linked on the
StudentID fields.  The subform would be in continuous form or
datasheet view and would have control bound to the SubjectID,
DateAchieved and SubjectAchieved fields.  The DateAchieved and
SubjectAchieved controls would be a text box and check box
respectively, the SubjectID control a combo box set up as follows:
ControlSource:    SubjectID
RowSource:     SELECT SubjectID, Subject, FROM Subjects ORDER BY
Subject;
BoundColum:   1
ColumnCount:  2
ColumnWidths:  0cm;8cm
If your units of measurement are imperial rather than metric Access
will automatically convert the last one.  The important thing is that
the first dimension is zero to hide the first column and that the
second is at least as wide as the combo box.
To add a subject for a student is simple a case of inserting a new
record in the subform, selecting the subject from the combo box's list
in each case.
Again you don't need a Diploma Achieved field in any of the tables.
You can put an unbound combo box on the Students form with a
ControlSource property such as:
=(DCount("*", "SubjectsAchieved", "Not SubjectAchieved And StudentID =
" & [StudentID]) = 0) And (DCount("*", "SubjectsAchieved", "StudentID
= " & [StudentID]) > 0)
Ken Sheridan
Stafford, England
Hello,
I have following fields on 1 form:
   date ___     Diploma Achieved: [  ] (Yes/No type field)
   date ___     Subject 1 Achieved: [  ] (Yes/No type field)
   date ___     Subject 2 Achieved: [  ] (Yes/No type field)
   date ___     Subject 3 Achieved: [  ] (Yes/No type field)
   etc.
What I would like to do is:
When ever a subject achieved is clicked as True, I want a macro to run
which will:
  -- Set Diploma Achieved as True if all Subjects are set to True
  -- Set the Diploma Achieved date as the maximum of the other `dates,
if Diploma Achieved is True
I know this should be straightforward, but I am new to Access. I have
tried using SetValue without success and have not been able to find
examples similar to what I want to do.
Any hints or links for similar examples?
Regards..- Hide quoted text -
- Show quoted text -

Me culpa; it should have been:

    If Me.[Subject 1 Achieved] _
        And Me.[Subject 2 Achieved] _
        And Me.[Subject 3 Achieved] Then

        Me.[Diploma Achieved] = True
   Else
        Me.[Diploma Achieved] = False
   End If

Ken Sheridan
Stafford, England

Sorry to be a pest, but this does not work either (although the logic
is a lot easier to follow). I even tried setting Me.[Diploma Achieved]
= True in both cases.

In my specific code that is:
Me.AllUnits = True
Else
Me.AllUnits = True
End If

I am wondering whether I should be using some other method for
changing value for AllUnits.


The exact code I have currently (to test with and without square
brackets) is:

Private Sub Unit1_Mathematics_AfterUpdate()
If Me.[Unit1_Mathematics] _
And Me.[Unit2_ComputerSkills] _
And Me.[Unit3_EngineeringDrawing] Then

Me.[AllUnits] = True
Else
Me.[AllUnits] = True
End If
End Sub

Private Sub Unit2_ComputerSkills_AfterUpdate()
If Me.Unit1_Mathematics _
And Me.Unit2_ComputerSkills _
And Me.Unit3_EngineeringDrawing Then

Me.AllUnits = True
Else
Me.AllUnits = True
End If

End Sub

 

[Larry Linson]

Interestingly, you appear to have introduced something entirely new, that
is, "AllUnits" in only the last post of this thread. We know only what you
tell us about your database, and if you don't tell us exactly what you have
and what you are trying to do with it, then the chances that we can be of
assistance are minimal.

Larry Linson
Microsoft Office Access MVP

Thank you for the detailed info.

For the time being I'm going to use the option of the one form (will
implement the 'information principle' later).

I used your code as described below, however the [Diploma Achieved]
field does not seem to be updating (I do want to use it for now even
though it is redundant).

Regards..

Firstly I'll explain how you can do it your way, then why you
shouldn't and finally how it should be done.
With your current set-up you'll find it a lot easier to do it with VBA
code than macro. You'd put exactly the same code in the AfterUpdate
event procedure of each of the Subject 1 Achieved, Subject 2 Achieved,
Subject 3 Achieved etc check box controls on the form. I'm guessing
that you are not too familiar with entering code in event procedures,
so here is how its done:
Select the control in form design view and open its properties sheet
if its not already open. Then select the AfterUpdate event property
in the properties sheet. Click on the 'build' button; that's the one
on the right with 3 dots. Select 'Code Builder' in the dialogue, and
click OK. The VBA window will open at the event procedure with the
first and last lines already in place. Enter the lines of code
between these two existing lines. To do this select the control in
form design view and open its properties sheet if its not already
open. Then select the relevant event property in the properties
sheet. Click on the 'build' button; that's the one on the right with
3 dots. Select 'Code Builder' in the dialogue, and click OK. The VBA
window will open at the event procedure with the first and last lines
already in place. Enter the lines of code between these two existing
lines.
The code for each event procedure would be as follows. For this
example I'm assuming that there are only 3 subjects involved, but
you'll be able to expand the code easily enough:
If Not(Me.[Subject 1 Achieved] =False_
Or Me.[Subject 2 Achieved] =False _
Or Me.[Subject 3 Achieved] =False) Then
Me.[Diploma Achieved] = True
Else
Me.[Diploma Achieved] = False
End If
However, the Diploma Achieved field in the underlying table is
redundant here as its value is computed from the values of the Subject
# Achieved fields. So you can do without the Diploma Achieved field
completely and put an unbound check box control on the form with a
ControlSource property of:
=[Subject 1 Achieved] And [Subject 2 Achieved] And [Subject 3
Achieved]
But that's not the end of the story as the table underlying your form
suffers from a fundamental design flaw. By having multiple fields for
the different subjects you are doing what's known as 'encoding data as
column headings'. A fundamental rule of the relational database model
is that data is stored as values at column positions in tables and in
no other way – its called the 'information principle'.
What you should have are separate tables for each 'entity type'. So
you'd have tables Students and Subjects to start with. The Students
table would have a StudentID primary key column (an autonumber is fine
for this) and columns such as FirstName, LastName, DateOfBirth etc.
Each column represents an 'attribute' of the students entity type.
The Subjects table would have columns SubjectID and Subject. Again an
autonumber is fine for the SubjectID column.
SubjectsAchieved is another entity type, but in this case its a
special kind of entity type as it is also a many-to-many relationship
type between Students and Subjects, i.e. each student can take more
than one subject and each subject is taken by more than one student.
A many-to-many relationship type is modelled by a third table, which
would have columns StudentID, SubjectID (not autonumbers in this case,
just straightforward long integer number data types), DateAchieved
(don't use Date as column name as it’s the name of a built in function
inn Access), SubjectAchieved (a Yes/No (Boolean) column).
The StudentID and SubjectID columns of the SubjectsAchieved table are
foreign key columns which reference the primary keys of the Students
and Subjects table, mapping a row in Students to a row in Subjects.
The DateAchieved and DiplomaAchieved columns represent other
attributes of this entity type.
For data entry you'd use a Students form with a SubjectsAchieved
subform embedded in it, the form and subform being linked on the
StudentID fields. The subform would be in continuous form or
datasheet view and would have control bound to the SubjectID,
DateAchieved and SubjectAchieved fields. The DateAchieved and
SubjectAchieved controls would be a text box and check box
respectively, the SubjectID control a combo box set up as follows:
ControlSource: SubjectID
RowSource: SELECT SubjectID, Subject, FROM Subjects ORDER BY
Subject;
BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm
If your units of measurement are imperial rather than metric Access
will automatically convert the last one. The important thing is that
the first dimension is zero to hide the first column and that the
second is at least as wide as the combo box.
To add a subject for a student is simple a case of inserting a new
record in the subform, selecting the subject from the combo box's list
in each case.
Again you don't need a Diploma Achieved field in any of the tables.
You can put an unbound combo box on the Students form with a
ControlSource property such as:
=(DCount("*", "SubjectsAchieved", "Not SubjectAchieved And StudentID =
" & [StudentID]) = 0) And (DCount("*", "SubjectsAchieved", "StudentID
= " & [StudentID]) > 0)
Ken Sheridan
Stafford, England
Hello,
I have following fields on 1 form:
date ___ Diploma Achieved: [ ] (Yes/No type field)
date ___ Subject 1 Achieved: [ ] (Yes/No type field)
date ___ Subject 2 Achieved: [ ] (Yes/No type field)
date ___ Subject 3 Achieved: [ ] (Yes/No type field)
etc.
What I would like to do is:
When ever a subject achieved is clicked as True, I want a macro to
run
which will:
-- Set Diploma Achieved as True if all Subjects are set to True
-- Set the Diploma Achieved date as the maximum of the other `dates,
if Diploma Achieved is True
I know this should be straightforward, but I am new to Access. I
have
tried using SetValue without success and have not been able to find
examples similar to what I want to do.
Any hints or links for similar examples?
Regards..- Hide quoted text -
- Show quoted text -

Me culpa; it should have been:

If Me.[Subject 1 Achieved] _
And Me.[Subject 2 Achieved] _
And Me.[Subject 3 Achieved] Then

Me.[Diploma Achieved] = True
Else
Me.[Diploma Achieved] = False
End If

Ken Sheridan
Stafford, England

Sorry to be a pest, but this does not work either (although the logic
is a lot easier to follow). I even tried setting Me.[Diploma Achieved]
= True in both cases.

In my specific code that is:
Me.AllUnits = True
Else
Me.AllUnits = True
End If

I am wondering whether I should be using some other method for
changing value for AllUnits.


The exact code I have currently (to test with and without square
brackets) is:

Private Sub Unit1_Mathematics_AfterUpdate()
If Me.[Unit1_Mathematics] _
And Me.[Unit2_ComputerSkills] _
And Me.[Unit3_EngineeringDrawing] Then

Me.[AllUnits] = True
Else
Me.[AllUnits] = True
End If
End Sub

Private Sub Unit2_ComputerSkills_AfterUpdate()
If Me.Unit1_Mathematics _
And Me.Unit2_ComputerSkills _
And Me.Unit3_EngineeringDrawing Then

Me.AllUnits = True
Else
Me.AllUnits = True
End If

End Sub

 

[Android]

Interestingly, you appear to have introduced something entirely new, that
is, "AllUnits" in only the last post of this thread.  We know only whatyou
tell us about your database, and if you don't tell us exactly what you have
and what you are trying to do with it, then the chances that we can be of
assistance are minimal.

 Larry Linson
 Microsoft Office Access MVP

Thank you for the detailed info.
For the time being I'm going to use the option of the one form (will
implement the 'information principle' later).
I used your code as described below, however the [Diploma Achieved]
field does not seem to be updating (I do want to use it for now even
though it is redundant).
Regards..
Firstly I'll explain how you can do it your way, then why you
shouldn't and finally how it should be done.
With your current set-up you'll find it a lot easier to do it with VBA
code than macro. You'd put exactly the same code in the AfterUpdate
event procedure of each of the Subject 1 Achieved, Subject 2 Achieved,
Subject 3 Achieved etc check box controls on the form. I'm guessing
that you are not too familiar with entering code in event procedures,
so here is how its done:
Select the control in form design view and open its properties sheet
if its not already open. Then select the AfterUpdate event property
in the properties sheet. Click on the 'build' button; that's the one
on the right with 3 dots. Select 'Code Builder' in the dialogue, and
click OK. The VBA window will open at the event procedure with the
first and last lines already in place. Enter the lines of code
between these two existing lines. To do this select the control in
form design view and open its properties sheet if its not already
open. Then select the relevant event property in the properties
sheet. Click on the 'build' button; that's the one on the right with
3 dots. Select 'Code Builder' in the dialogue, and click OK. The VBA
window will open at the event procedure with the first and last lines
already in place. Enter the lines of code between these two existing
lines.
The code for each event procedure would be as follows. For this
example I'm assuming that there are only 3 subjects involved, but
you'll be able to expand the code easily enough:
If Not(Me.[Subject 1 Achieved] =False_
Or Me.[Subject 2 Achieved] =False _
Or Me.[Subject 3 Achieved] =False) Then
Me.[Diploma Achieved] = True
Else
Me.[Diploma Achieved] = False
End If
However, the Diploma Achieved field in the underlying table is
redundant here as its value is computed from the values of the Subject
# Achieved fields. So you can do without the Diploma Achieved field
completely and put an unbound check box control on the form with a
ControlSource property of:
=[Subject 1 Achieved] And [Subject 2 Achieved] And [Subject 3
Achieved]
But that's not the end of the story as the table underlying your form
suffers from a fundamental design flaw. By having multiple fields for
the different subjects you are doing what's known as 'encoding dataas
column headings'. A fundamental rule of the relational database model
is that data is stored as values at column positions in tables and in
no other way – its called the 'information principle'.
What you should have are separate tables for each 'entity type'. So
you'd have tables Students and Subjects to start with. The Students
table would have a StudentID primary key column (an autonumber is fine
for this) and columns such as FirstName, LastName, DateOfBirth etc.
Each column represents an 'attribute' of the students entity type.
The Subjects table would have columns SubjectID and Subject. Again an
autonumber is fine for the SubjectID column.
SubjectsAchieved is another entity type, but in this case its a
special kind of entity type as it is also a many-to-many relationship
type between Students and Subjects, i.e. each student can take more
than one subject and each subject is taken by more than one student..
A many-to-many relationship type is modelled by a third table, which
would have columns StudentID, SubjectID (not autonumbers in this case,
just straightforward long integer number data types), DateAchieved
(don't use Date as column name as it’s the name of a built in function
inn Access), SubjectAchieved (a Yes/No (Boolean) column).
The StudentID and SubjectID columns of the SubjectsAchieved table are
foreign key columns which reference the primary keys of the Students
and Subjects table, mapping a row in Students to a row in Subjects.
The DateAchieved and DiplomaAchieved columns represent other
attributes of this entity type.
For data entry you'd use a Students form with a SubjectsAchieved
subform embedded in it, the form and subform being linked on the
StudentID fields. The subform would be in continuous form or
datasheet view and would have control bound to the SubjectID,
DateAchieved and SubjectAchieved fields. The DateAchieved and
SubjectAchieved controls would be a text box and check box
respectively, the SubjectID control a combo box set up as follows:
ControlSource: SubjectID
RowSource: SELECT SubjectID, Subject, FROM Subjects ORDER BY
Subject;
BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm
If your units of measurement are imperial rather than metric Access
will automatically convert the last one. The important thing is that
the first dimension is zero to hide the first column and that the
second is at least as wide as the combo box.
To add a subject for a student is simple a case of inserting a new
record in the subform, selecting the subject from the combo box's list
in each case.
Again you don't need a Diploma Achieved field in any of the tables.
You can put an unbound combo box on the Students form with a
ControlSource property such as:
=(DCount("*", "SubjectsAchieved", "Not SubjectAchieved And StudentID =
" & [StudentID]) = 0) And (DCount("*", "SubjectsAchieved", "StudentID
= " & [StudentID]) > 0)
Ken Sheridan
Stafford, England
Hello,
I have following fields on 1 form:
date ___ Diploma Achieved: [ ] (Yes/No type field)
date ___ Subject 1 Achieved: [ ] (Yes/No type field)
date ___ Subject 2 Achieved: [ ] (Yes/No type field)
date ___ Subject 3 Achieved: [ ] (Yes/No type field)
etc.
What I would like to do is:
When ever a subject achieved is clicked as True, I want a macro to
run
which will:
-- Set Diploma Achieved as True if all Subjects are set to True
-- Set the Diploma Achieved date as the maximum of the other `dates,
if Diploma Achieved is True
I know this should be straightforward, but I am new to Access. I
have
tried using SetValue without success and have not been able to find
examples similar to what I want to do.
Any hints or links for similar examples?
Regards..- Hide quoted text -
- Show quoted text -

Me culpa; it should have been:

If Me.[Subject 1 Achieved] _
And Me.[Subject 2 Achieved] _
And Me.[Subject 3 Achieved] Then

Me.[Diploma Achieved] = True
Else
Me.[Diploma Achieved] = False
End If

Ken Sheridan
Stafford, England

Sorry to be a pest, but this does not work either (although the logic
is a lot easier to follow). I even tried setting Me.[Diploma Achieved]
= True in both cases.

In my specific code that is:
        Me.AllUnits = True
    Else
        Me.AllUnits = True
    End If

I am wondering whether I should be using some other method for
changing value for AllUnits.

The exact code I have currently (to test with and without square
brackets) is:

Private Sub Unit1_Mathematics_AfterUpdate()
    If Me.[Unit1_Mathematics] _
        And Me.[Unit2_ComputerSkills] _
        And Me.[Unit3_EngineeringDrawing] Then

        Me.[AllUnits] = True
    Else
        Me.[AllUnits] = True
    End If
End Sub

Private Sub Unit2_ComputerSkills_AfterUpdate()
    If Me.Unit1_Mathematics _
        And Me.Unit2_ComputerSkills _
        And Me.Unit3_EngineeringDrawing Then

        Me.AllUnits = True
    Else
        Me.AllUnits = True
    End If

End Sub

My apology for any confusion, but it was in my original post. It was
Me.[Diploma Achieved]

In my latest post I simply replaced with the generic terms (for all
fields, not just this one) with the exact code. I guess I should have
used the exact field names from the beginning -- but please note I
have not introduced anything new.

It is still frustrating for me that the code does not seem to work
even though the logic makes complete sense. Any help would be
appreciated. I've been looking at other statements such as UPDATE, Iff
etc., without success so far.


Regards..

 

[Ken Sheridan]

Interestingly, you appear to have introduced something entirely new, that
is, "AllUnits" in only the last post of this thread.  We know only what you
tell us about your database, and if you don't tell us exactly what you have
and what you are trying to do with it, then the chances that we can be of
assistance are minimal.

 Larry Linson
 Microsoft Office Access MVP

"Android" <[email protected]> wrote in message

Thank you for the detailed info.
For the time being I'm going to use the option of the one form (will
implement the 'information principle' later).
I used your code as described below, however the [Diploma Achieved]
field does not seem to be updating (I do want to use it for now even
though it is redundant).
Regards..
Firstly I'll explain how you can do it your way, then why you
shouldn't and finally how it should be done.
With your current set-up you'll find it a lot easier to do it with VBA
code than macro. You'd put exactly the same code in the AfterUpdate
event procedure of each of the Subject 1 Achieved, Subject 2 Achieved,
Subject 3 Achieved etc check box controls on the form. I'm guessing
that you are not too familiar with entering code in event procedures,
so here is how its done:
Select the control in form design view and open its properties sheet
if its not already open. Then select the AfterUpdate event property
in the properties sheet. Click on the 'build' button; that's the one
on the right with 3 dots. Select 'Code Builder' in the dialogue, and
click OK. The VBA window will open at the event procedure with the
first and last lines already in place. Enter the lines of code
between these two existing lines. To do this select the control in
form design view and open its properties sheet if its not already
open. Then select the relevant event property in the properties
sheet. Click on the 'build' button; that's the one on the right with
3 dots. Select 'Code Builder' in the dialogue, and click OK. The VBA
window will open at the event procedure with the first and last lines
already in place. Enter the lines of code between these two existing
lines.
The code for each event procedure would be as follows. For this
example I'm assuming that there are only 3 subjects involved, but
you'll be able to expand the code easily enough:
If Not(Me.[Subject 1 Achieved] =False_
Or Me.[Subject 2 Achieved] =False _
Or Me.[Subject 3 Achieved] =False) Then
Me.[Diploma Achieved] = True
Else
Me.[Diploma Achieved] = False
End If
However, the Diploma Achieved field in the underlying table is
redundant here as its value is computed from the values of the Subject
# Achieved fields. So you can do without the Diploma Achieved field
completely and put an unbound check box control on the form with a
ControlSource property of:
=[Subject 1 Achieved] And [Subject 2 Achieved] And [Subject 3
Achieved]
But that's not the end of the story as the table underlying your form
suffers from a fundamental design flaw. By having multiple fieldsfor
the different subjects you are doing what's known as 'encoding data as
column headings'. A fundamental rule of the relational database model
is that data is stored as values at column positions in tables and in
no other way – its called the 'information principle'.
What you should have are separate tables for each 'entity type'. So
you'd have tables Students and Subjects to start with. The Students
table would have a StudentID primary key column (an autonumber isfine
for this) and columns such as FirstName, LastName, DateOfBirth etc.
Each column represents an 'attribute' of the students entity type..
The Subjects table would have columns SubjectID and Subject. Again an
autonumber is fine for the SubjectID column.
SubjectsAchieved is another entity type, but in this case its a
special kind of entity type as it is also a many-to-many relationship
type between Students and Subjects, i.e. each student can take more
than one subject and each subject is taken by more than one student.
A many-to-many relationship type is modelled by a third table, which
would have columns StudentID, SubjectID (not autonumbers in this case,
just straightforward long integer number data types), DateAchieved
(don't use Date as column name as it’s the name of a built in function
inn Access), SubjectAchieved (a Yes/No (Boolean) column).
The StudentID and SubjectID columns of the SubjectsAchieved tableare
foreign key columns which reference the primary keys of the Students
and Subjects table, mapping a row in Students to a row in Subjects.
The DateAchieved and DiplomaAchieved columns represent other
attributes of this entity type.
For data entry you'd use a Students form with a SubjectsAchieved
subform embedded in it, the form and subform being linked on the
StudentID fields. The subform would be in continuous form or
datasheet view and would have control bound to the SubjectID,
DateAchieved and SubjectAchieved fields. The DateAchieved and
SubjectAchieved controls would be a text box and check box
respectively, the SubjectID control a combo box set up as follows:
ControlSource: SubjectID
RowSource: SELECT SubjectID, Subject, FROM Subjects ORDER BY
Subject;
BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm
If your units of measurement are imperial rather than metric Access
will automatically convert the last one. The important thing is that
the first dimension is zero to hide the first column and that the
second is at least as wide as the combo box.
To add a subject for a student is simple a case of inserting a new
record in the subform, selecting the subject from the combo box'slist
in each case.
Again you don't need a Diploma Achieved field in any of the tables.
You can put an unbound combo box on the Students form with a
ControlSource property such as:
=(DCount("*", "SubjectsAchieved", "Not SubjectAchieved And StudentID =
" & [StudentID]) = 0) And (DCount("*", "SubjectsAchieved", "StudentID
= " & [StudentID]) > 0)
Ken Sheridan
Stafford, England
Hello,
I have following fields on 1 form:
date ___ Diploma Achieved: [ ] (Yes/No type field)
date ___ Subject 1 Achieved: [ ] (Yes/No type field)
date ___ Subject 2 Achieved: [ ] (Yes/No type field)
date ___ Subject 3 Achieved: [ ] (Yes/No type field)
etc.
What I would like to do is:
When ever a subject achieved is clicked as True, I want a macroto
run
which will:
-- Set Diploma Achieved as True if all Subjects are set to True
-- Set the Diploma Achieved date as the maximum of the other `dates,
if Diploma Achieved is True
I know this should be straightforward, but I am new to Access. I
have
tried using SetValue without success and have not been able to find
examples similar to what I want to do.
Any hints or links for similar examples?
Regards..- Hide quoted text -
- Show quoted text -
Me culpa; it should have been:
If Me.[Subject 1 Achieved] _
And Me.[Subject 2 Achieved] _
And Me.[Subject 3 Achieved] Then
Me.[Diploma Achieved] = True
Else
Me.[Diploma Achieved] = False
End If
Ken Sheridan
Stafford, England

Sorry to be a pest, but this does not work either (although the logic
is a lot easier to follow). I even tried setting Me.[Diploma Achieved]
= True in both cases.

In my specific code that is:
        Me.AllUnits = True
    Else
        Me.AllUnits = True
    End If

I am wondering whether I should be using some other method for
changing value for AllUnits.

The exact code I have currently (to test with and without square
brackets) is:

Private Sub Unit1_Mathematics_AfterUpdate()
    If Me.[Unit1_Mathematics] _
        And Me.[Unit2_ComputerSkills] _
        And Me.[Unit3_EngineeringDrawing] Then

        Me.[AllUnits] = True
    Else
        Me.[AllUnits] = True
    End If
End Sub

Private Sub Unit2_ComputerSkills_AfterUpdate()
    If Me.Unit1_Mathematics _
        And Me.Unit2_ComputerSkills _
        And Me.Unit3_EngineeringDrawing Then

        Me.AllUnits = True
    Else
        Me.AllUnits = True
    End If

End Sub

My apology for any confusion, but it was in my original post.  It was
Me.[Diploma Achieved]

In my latest post I simply replaced with the generic terms (for all
fields, not just this one) with the exact code.  I guess I should have
used the exact field names from the beginning -- but please note I
have not introduced anything new.

It is still frustrating for me that the code ...

read more »

The problem may be that the values of the individual unit controls are
not being updated manually in the form. The AfterUpdate event
procedure of a control only executes as a result of user action, which
was waht you asked for in the original post. Any previous records
won't show the correct value in the AllUnits control (and therefore in
the underlying column to which its bound); it will only have effect
when a user is entering data in the form.

You can, however bring all rows in the table up to date by means of a
simple update query:

UPDATE [YourTableNameGoesHere]
SET [AllUnits] = TRUE
WHERE [Unit1_Mathematics]
AND [Unit2_ComputerSkills]
AND [Unit3_EngineeringDrawing];

The code in each of the three check box's AfterUpdate procedures
should not set the AllUnits value to True in both cases. If a user
makes a mistake and unchecks one of the controls, having previously
checked all three, then you'd want AllUnits to revert to False, so:

Private Sub Unit1_Mathematics_AfterUpdate()
If Me.[Unit1_Mathematics] _
And Me.[Unit2_ComputerSkills] _
And Me.[Unit3_EngineeringDrawing] Then

Me.[AllUnits] = True
Else
Me.[AllUnits] = False
End If
End Sub

and similarly for computer skills and engineering drawing.

Ken Sheridan
Stafford, England

 

[Larry Linson]

My apology for any confusion, but it was in my original
post. It was Me.[Diploma Achieved]

As I said, all we know about your database is what you tell us and it was
not obvious to me that "All Units" was the same as "Diploma Achieved" from
what I read.

In my latest post I simply replaced with the generic terms
(for all fields, not just this one) with the exact code. I
guess I should have used the exact field names from the
beginning -- but please note I have not introduced anything new.

To change the names you are using to represent things in the middle of a
series of posts is confusing even if you tell us that's what you are doing.
As I did not read such a statement, it was confounding and that is why I
responded as I did. To me "All Units" was new; to you, because you have
your database in front of you, and know that you were using 'something
else', it may have been obvious.

It is still frustrating for me that the code does not
seem to work even though the logic makes complete
sense. Any help would be appreciated. I've been looking
at other statements such as UPDATE, Iff etc.,
without success so far.

Yep, it's always frustrating when code doesn't work and we haven't got it
figured out yet. The good news is that sooner or later, we almost always
either figure it out and get it to work, or we find another way to do it
that does work.

It is, in my (quite a few years of) experience, difficult enough to debug
code when it is all spread out in front of me; to assist someone debugging
remotely requires very clear, very simple, very straightforward description
and explanations for the simple reason that we cannot see everything spread
out in front of us. I often say, the questions that get the best answers
are those that are precise (in detail and complete) and concise (without
extra information). Describing a program, application, or database (or part
of it) that way is not always (maybe not ever) easy; and distilling the
situation down to essentials can often lead to answering my own question
before I even get around to posting.

Ken is one of the most capable Access developers who participates in this
newsgroup, and introducing another person into the mix might well be a
detriment instead of a help.

Larry Linson
Microsoft Office Access MVP