Help with this form. Access 2007

[LiquidWrench]

I have a form called New Contact Details that has tabbed pages.
The first page is information that visitors provide us and the second tab is
information about a tracking program to make further outreach contacts.

This control source of this form is a query called QRY-7Step.
The 7Step tab is the page of concern.

It consists of basically a bunch of date fields. There are a total of seven
steps each constisting of three date fields a piece.

Each step is allowed a window of 4 days so the first 2 fields in each step
are calculated fields. The third field in each step allows for user input so
the actual day the step was performed can be recorded. At the bottom of the
form are two sections that allow users to perform actions depending whether
the steps are completed or not, they are appropiately called Track Completed
and Track Incomplete. What I am trying to accomplish is for the complete
action section to only show when all seven steps are completed i.e. when all
of the text boxes have information entered into them.

Here is the basic layout:

Track Start Date
Pastor Letter 1 Start Transportation Start Date
Pastor Letter 1 End Transportation End Date
Pastor Letter 1 Actual Transportation Actual

Welcome Card Start HBS Start Date
Welcome Card End HBS End Date
Welcome Card Actual HBS Actual

Welcome Call Start Pastor Letter 2 Start
Welcome Call End Pastor Letter 2 End
Welcome Call Actual Pastor Letter 2 Actual

DOZ Start
DOZ End
DOZ Actual

Track Expected End
Track Actual End


Track Complete Process Track Incomplete Process
ChkBox1 ChkBox2
Cmd Button 1 CmdButton 2
Cmd Button 3 CmdButton 4

I want the Track Complete Process Label, ChkBox1, Cmd Button1, CmdButton2 to
only show if all of the "Actual" text boxes have a valid date entered into
them.

I have experimented with simple code to do this but I am really at a loss.

If I can provide anymore information please let me know.

 

[Allen Browne]

You could use a text box with ControlSource like this:
=IIf(([Pastor Letter 1 Actual] Is Not Null)
AND ([Welcome Card Actual] Is Not Null)
AND (...),
"Complete", Null)

But what you have here is basically a spreadsheet in Access, not a
relational database. For a database, you would use 3 tables like this:

Person table (one record for each person), with fields:
==========
- PersonID AutoNumber Primary key
- Surname Text
- FirstName Text
- ProgramStart Date when the person started in the program.
....

Step table (one record for each step), with fields:
========
- StepID Number primary key
- StepType Text "Letter" or "Welcome", or ...
- DaysAfterStart Number how many days after the startdate this step
starts.
- DaysDuration Number how many days this step should last

PersonStep table (one record for each person for each step):
==============
- PersonID Number who this row is for
- StepNum Number which step of the program this is.
- CompleteDate Date when this step was actually completed.

With this approach, you can create a query using the 3 tables. For each
step, that start date is:
[ProgramStart] + [DaysAfterStart]
and the anticipated end date is:
[ProgramStart] + [DaysAfterStart] + [DaysDuration]
You can therefore compare StepActual date to this anticipated date.

The interface will be a main form bound to the Person table, and a subform
bound to the 3rd table. the subform will show a row for each step, so you
can see what's completed and what's not.

Using a relational design is really important. For another example, see:
Relationships between Tables (School Grades example)
at:
http://allenbrowne.com/casu-06.html

 

[LiquidWrench]

Ok I get what you are saying and I folllowed the link and read the content
and most of it makes sense. I sort of tried that approach in a previous
attempt and after reading your reply I still have a question primarily
concerning the "StepID" field in the Step Table. The question (at the risk
of sounding absolutely ignorant) is: Where does this value come from and does
it have to match the value in the autonumber field in the person table? If
it does will I be required to enter it every time a record is entered into
that table? I am sorry to sound ignorant but there are still things about
relationships that I am stil having trouble wrapping my head around.
--
LiquidWrench

"Of all the things I have lost in life, I miss my mind the most!!"

You could use a text box with ControlSource like this:
=IIf(([Pastor Letter 1 Actual] Is Not Null)
AND ([Welcome Card Actual] Is Not Null)
AND (...),
"Complete", Null)

But what you have here is basically a spreadsheet in Access, not a
relational database. For a database, you would use 3 tables like this:

Person table (one record for each person), with fields:
==========
- PersonID AutoNumber Primary key
- Surname Text
- FirstName Text
- ProgramStart Date when the person started in the program.
....

Step table (one record for each step), with fields:
========
- StepID Number primary key
- StepType Text "Letter" or "Welcome", or ...
- DaysAfterStart Number how many days after the startdate this step
starts.
- DaysDuration Number how many days this step should last

PersonStep table (one record for each person for each step):
==============
- PersonID Number who this row is for
- StepNum Number which step of the program this is.
- CompleteDate Date when this step was actually completed.

With this approach, you can create a query using the 3 tables. For each
step, that start date is:
[ProgramStart] + [DaysAfterStart]
and the anticipated end date is:
[ProgramStart] + [DaysAfterStart] + [DaysDuration]
You can therefore compare StepActual date to this anticipated date.

The interface will be a main form bound to the Person table, and a subform
bound to the 3rd table. the subform will show a row for each step, so you
can see what's completed and what's not.

Using a relational design is really important. For another example, see:
Relationships between Tables (School Grades example)
at:
http://allenbrowne.com/casu-06.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a form called New Contact Details that has tabbed pages.
The first page is information that visitors provide us and the second tab
is
information about a tracking program to make further outreach contacts.

This control source of this form is a query called QRY-7Step.
The 7Step tab is the page of concern.

It consists of basically a bunch of date fields. There are a total of
seven
steps each constisting of three date fields a piece.

Each step is allowed a window of 4 days so the first 2 fields in each step
are calculated fields. The third field in each step allows for user input
so
the actual day the step was performed can be recorded. At the bottom of
the
form are two sections that allow users to perform actions depending
whether
the steps are completed or not, they are appropiately called Track
Completed
and Track Incomplete. What I am trying to accomplish is for the complete
action section to only show when all seven steps are completed i.e. when
all
of the text boxes have information entered into them.

Here is the basic layout:

Track Start Date
Pastor Letter 1 Start Transportation Start
Date
Pastor Letter 1 End Transportation End Date
Pastor Letter 1 Actual Transportation Actual

Welcome Card Start HBS Start Date
Welcome Card End HBS End Date
Welcome Card Actual HBS Actual

Welcome Call Start Pastor Letter 2 Start
Welcome Call End Pastor Letter 2 End
Welcome Call Actual Pastor Letter 2 Actual

DOZ Start
DOZ End
DOZ Actual

Track Expected
End
Track Actual
End


Track Complete Process Track Incomplete
Process
ChkBox1 ChkBox2
Cmd Button 1 CmdButton 2
Cmd Button 3 CmdButton 4

I want the Track Complete Process Label, ChkBox1, Cmd Button1, CmdButton2
to
only show if all of the "Actual" text boxes have a valid date entered into
them.

I have experimented with simple code to do this but I am really at a loss.

 

[Allen Browne]

If I read your original post correctly, you have about 7 steps. You will
enter the 7 records in the Step table just once.

When you enter a new person, the PersonID autonumber is assigned
automatically. The 7 steps are then entered in the subform.

There are a couple of ways to do that. One would be to use a combo in the
subform so the user can enter the 7 records (for the 7 steps.) Another would
be to use the AfterInsert event procedure of the main *form* to execute an
Append query statement that automatically inserts the 7 steps for the new
person, and then Requery the subform show they show up automatically. This
requires some code. Post back if you're stuck with this.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Ok I get what you are saying and I folllowed the link and read the content
and most of it makes sense. I sort of tried that approach in a previous
attempt and after reading your reply I still have a question primarily
concerning the "StepID" field in the Step Table. The question (at the
risk
of sounding absolutely ignorant) is: Where does this value come from and
does
it have to match the value in the autonumber field in the person table?
If
it does will I be required to enter it every time a record is entered into
that table? I am sorry to sound ignorant but there are still things about
relationships that I am stil having trouble wrapping my head around.
--
LiquidWrench

"Of all the things I have lost in life, I miss my mind the most!!"

You could use a text box with ControlSource like this:
=IIf(([Pastor Letter 1 Actual] Is Not Null)
AND ([Welcome Card Actual] Is Not Null)
AND (...),
"Complete", Null)

But what you have here is basically a spreadsheet in Access, not a
relational database. For a database, you would use 3 tables like this:

Person table (one record for each person), with fields:
==========
- PersonID AutoNumber Primary key
- Surname Text
- FirstName Text
- ProgramStart Date when the person started in the program.
....

Step table (one record for each step), with fields:
========
- StepID Number primary key
- StepType Text "Letter" or "Welcome", or ...
- DaysAfterStart Number how many days after the startdate this
step
starts.
- DaysDuration Number how many days this step should last

PersonStep table (one record for each person for each step):
==============
- PersonID Number who this row is for
- StepNum Number which step of the program this is.
- CompleteDate Date when this step was actually completed.

With this approach, you can create a query using the 3 tables. For each
step, that start date is:
[ProgramStart] + [DaysAfterStart]
and the anticipated end date is:
[ProgramStart] + [DaysAfterStart] + [DaysDuration]
You can therefore compare StepActual date to this anticipated date.

The interface will be a main form bound to the Person table, and a
subform
bound to the 3rd table. the subform will show a row for each step, so you
can see what's completed and what's not.

Using a relational design is really important. For another example, see:
Relationships between Tables (School Grades example)
at:
http://allenbrowne.com/casu-06.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a form called New Contact Details that has tabbed pages.
The first page is information that visitors provide us and the second
tab
is
information about a tracking program to make further outreach contacts.

This control source of this form is a query called QRY-7Step.
The 7Step tab is the page of concern.

It consists of basically a bunch of date fields. There are a total of
seven
steps each constisting of three date fields a piece.

Each step is allowed a window of 4 days so the first 2 fields in each
step
are calculated fields. The third field in each step allows for user
input
so
the actual day the step was performed can be recorded. At the bottom
of
the
form are two sections that allow users to perform actions depending
whether
the steps are completed or not, they are appropiately called Track
Completed
and Track Incomplete. What I am trying to accomplish is for the
complete
action section to only show when all seven steps are completed i.e.
when
all
of the text boxes have information entered into them.

Here is the basic layout:

Track Start
Date
Pastor Letter 1 Start Transportation Start
Date
Pastor Letter 1 End Transportation End
Date
Pastor Letter 1 Actual Transportation Actual

Welcome Card Start HBS Start Date
Welcome Card End HBS End Date
Welcome Card Actual HBS Actual

Welcome Call Start Pastor Letter 2 Start
Welcome Call End Pastor Letter 2 End
Welcome Call Actual Pastor Letter 2 Actual

DOZ Start
DOZ End
DOZ Actual

Track
Expected
End
Track Actual
End


Track Complete Process Track Incomplete
Process
ChkBox1
ChkBox2
Cmd Button 1 CmdButton
2
Cmd Button 3 CmdButton
4

I want the Track Complete Process Label, ChkBox1, Cmd Button1,
CmdButton2
to
only show if all of the "Actual" text boxes have a valid date entered
into
them.

I have experimented with simple code to do this but I am really at a
loss.