help with tables

[LTOSH]

(Using Access 2000)
I have a 3 tables.
Client Table...ClientID(pk), first name, last name, address etc.
Exercises Table...ExerciseID(pk), exercise name, description etc
Workout Table...WorkoutID(pk), ClientID, ExerciseID, workout date, Exercise
Name, sets, reps, weight, description.

I am wanting to create a form where i can choose a client (combo box), type
in the workout date, then create a workout. I want to save this workout
specific to the client name. Where do i begin?

Thanks!

 

[Allen Browne]

Create a form bound to the Client table, where you can enter the client's
information.

Create a form (to become a subform), in Continuous Form view, with a combo
box for the ExerciseID, a text box for the date, etc. Cut the labels from
the controls, and put them into the Form Header section. Then arrange the
boxes side-by-side, so the whole detail section is only about 0.2" tall. Set
the form's Default View to Continuous form. Save.

Now add this as a subform on your continuous form. The LinkMasterFields and
LinkChildFields properties will be set to ClientID. Consequently, the
subform shows the records relevant to the client in the main form, and you
can add as many rows as you want for that client.

So you can jump to a particular client's record, use the combo wizard to add
another unbound box to the top of your main form for selecting the client.
Alternatively, use the code in this link:
http://allenbrowne.com/ser-03.html

 

[tina]

a note on your tables design: recommend you remove the Exercise Name field
from the Workout table. that value is already stored in the Exercises table,
which is linked to the Workout table by the ExerciseID foreign key field, so
storing it again violates normalization rules. and if the Description field
in the Workout table is the same as the Description field in the Exercises
table, then remove it from the Workout table too, for the same reason. and,
btw, recommend you remove any spaces from your fieldnames - Exercise name
should be ExerciseName; for more information, see
http://home.att.net/~california.db/tips.html#aTip5.

once you've made the correction(s) above, suggest the following for your
data entry forms: create a form bound to the Client table, SingleForm view,
i'll call it frmClients. create another form bound to the Workout table,
Datasheet view, i'll call it sfrmWorkouts. in sfrmWorkouts, add a combobox
control, and set the ControlSource to ExerciseID; set the RowSource to a SQL
statement, as

SELECT ExerciseID, ExerciseName FROM Exercises;

set the following combobox properties, as

ColumnCount: 2
ColumnWidths: 0"; 1"
BoundColumn: 1
LimitToList: Yes

when you look at the combobox droplist in Form view (Datasheet view, in this
case), you'll see only the exercise name, but when you pick an exercise from
the list, the ExerciseID will be saved in the field in the Workout table, as
it should be.

add sfrmWorkouts to the frmClients, as a subform. set the subform's
LinkChildFields property to ClientID (which refers to the foreign key field
in the Workout table), and set the LinkMasterFields property to ClientID
(which refers to the primary key field in the Client table).

so now you can open the mainform (frmClients), and add a new client record
or go to an existing client record, and then - in the subform - choose an
exercise from the combobox droplist and enter the date, sets, reps, and
weight; enter as many exercise records as you want for each client.

hth

 

[LTOSH]

thanks allen and tina for guidance on my tables...i will get to work on them
probably tomorrow. i may be bck for further help.
thanks again!!

 

[tina]

you're welcome

thanks allen and tina for guidance on my tables...i will get to work on them
probably tomorrow. i may be bck for further help.
thanks again!!

 

[LTOSH]

Tina, I could use a little more help.

I have made the changes as you suggested and looks great so far but I am not
sure where to put my workout date. In my subform I have done as you
suggested below. However, if I put my workout date in this subform and make
it a continous form it repeats the workout date too. I want to create a
Workout for a specific client and a specific date. then add multiple
exercises for that date. Hope this makes sense.

I have a command button on a "switchboard" that opens my frmClient. When I
Click this button I want to open a new form, how do i tell it to do that?

I then want to be able to EDIT that particular workout later if needed by
creating a command button called "edit workout". when i click this command
button i want it to ask me for which client(list box), then whick workout
date (list box).

Thanks so much for all help!
LTOSH

 

[tina]

well, you could split the Workout table into a Workout table with a child
WorkoutExercises table - but i can't see much sense in that if the Workout
table's only common field will be the workout date field. you'd still have
the same number of fields in the child table, just replacing the date field
with a foreign key field linking back to the Workout table. it just adds an
unnecessary layer to your tables/relationships.

instead, suggest you keep the tables structure as is, and add an unbound
textbox control to frmClients, i'll call it txtWorkoutDate. enter the date
you want to see/add workout records for. add the txtWorkoutDate to the
subform control's LinkMasterFields property, and add the workout date field
name (from the workouts table) to the LinkChildFields property. by changing
the date in the textbox, you'll change the records returned by the subform.
also, you can add a Default value to the date control in the subform, as

=[Forms]![frmClients]![txtWorkoutDate]

so when you *add* a new record to the subform, it will automatically have
the date entered in the txtWorkDate control in the mainform.

the above will work for the form/subform setup i outlined in my previous
post. i'm not sure what you mean by

I have a command button on a "switchboard" that opens my frmClient. When I
Click this button I want to open a new form, how do i tell it to do that?

the command button opens frmClient. what "new form" do you want to open?
instead of frmClient? in addition to frmClient? please explain.

I then want to be able to EDIT that particular workout later if needed by
creating a command button called "edit workout". when i click this command
button i want it to ask me for which client(list box), then whick workout
date (list box).

well, in the form/subform setup i proposed, you CAN edit any existing
workout record anytime you want, as well as adding a workout record anytime
you want. if instead you want a form simply to add/edit client records, and
a separate form to add/edit workout records for specific clients/dates, you
can do that pretty much as you proposed - with an unbound listbox with
RowSource based on the clients table, and an unbound listbox of dates with
RowSource based on a SELECT DISTINCT query of the date field of the workout
table, and filtering the workouts form's RecordSource with the values chosen
in those two controls. though having a listbox of dates might be somewhat
trickier from the standpoint of entering new records in the form with a date
that was not yet in the date listbox - you'd want to requery the listbox at
some point, and some issues might arise; i'd have to work through it with a
test setup to really discover the ins and outs of making it work.

hth

 

[LTOSH]

when i say add "new form", i mean when i click on the button it opens a blank
frmClient...ready for new information....right now when i click my command
button and it opens the frmClient it has the information that was previously
entered.

i have been playing around some waiting for a response on last post and
wondered about adding on the frmClient a tab control (labeled Day 1, Day 2
etc). then add the sfrmWorkouts to each Day. Ex. Click Day 1 tab, then
select exercises etc for that day...Click Day 2 select exercises for that
day. i have set up 2 days but on Day 2 it shows the exercise information
from Day 1. Is this a possiblility to make this set up easier?

I'm just learning here...what are your suggestions?
Thanks!

well, you could split the Workout table into a Workout table with a child
WorkoutExercises table - but i can't see much sense in that if the Workout
table's only common field will be the workout date field. you'd still have
the same number of fields in the child table, just replacing the date field
with a foreign key field linking back to the Workout table. it just adds an
unnecessary layer to your tables/relationships.

instead, suggest you keep the tables structure as is, and add an unbound
textbox control to frmClients, i'll call it txtWorkoutDate. enter the date
you want to see/add workout records for. add the txtWorkoutDate to the
subform control's LinkMasterFields property, and add the workout date field
name (from the workouts table) to the LinkChildFields property. by changing
the date in the textbox, you'll change the records returned by the subform.
also, you can add a Default value to the date control in the subform, as

=[Forms]![frmClients]![txtWorkoutDate]

so when you *add* a new record to the subform, it will automatically have
the date entered in the txtWorkDate control in the mainform.

the above will work for the form/subform setup i outlined in my previous
post. i'm not sure what you mean by

I have a command button on a "switchboard" that opens my frmClient. When I
Click this button I want to open a new form, how do i tell it to do that?

the command button opens frmClient. what "new form" do you want to open?
instead of frmClient? in addition to frmClient? please explain.

I then want to be able to EDIT that particular workout later if needed by
creating a command button called "edit workout". when i click this command
button i want it to ask me for which client(list box), then whick workout
date (list box).

well, in the form/subform setup i proposed, you CAN edit any existing
workout record anytime you want, as well as adding a workout record anytime
you want. if instead you want a form simply to add/edit client records, and
a separate form to add/edit workout records for specific clients/dates, you
can do that pretty much as you proposed - with an unbound listbox with
RowSource based on the clients table, and an unbound listbox of dates with
RowSource based on a SELECT DISTINCT query of the date field of the workout
table, and filtering the workouts form's RecordSource with the values chosen
in those two controls. though having a listbox of dates might be somewhat
trickier from the standpoint of entering new records in the form with a date
that was not yet in the date listbox - you'd want to requery the listbox at
some point, and some issues might arise; i'd have to work through it with a
test setup to really discover the ins and outs of making it work.

hth

Tina, I could use a little more help.

I have made the changes as you suggested and looks great so far but I am not
sure where to put my workout date. In my subform I have done as you
suggested below. However, if I put my workout date in this subform and make
it a continous form it repeats the workout date too. I want to create a
Workout for a specific client and a specific date. then add multiple
exercises for that date. Hope this makes sense.

I have a command button on a "switchboard" that opens my frmClient. When I
Click this button I want to open a new form, how do i tell it to do that?

I then want to be able to EDIT that particular workout later if needed by
creating a command button called "edit workout". when i click this command
button i want it to ask me for which client(list box), then whick workout
date (list box).

Thanks so much for all help!
LTOSH

 

[tina]

comments inline.

when i say add "new form", i mean when i click on the button it opens a blank
frmClient...ready for new information....right now when i click my command
button and it opens the frmClient it has the information that was previously
entered.

you can add code to frmClients Load event procedure, as

DoCmd.RunCommand acCmdRecordsGoToNew

i have been playing around some waiting for a response on last post and
wondered about adding on the frmClient a tab control (labeled Day 1, Day 2
etc). then add the sfrmWorkouts to each Day. Ex. Click Day 1 tab, then
select exercises etc for that day...Click Day 2 select exercises for that
day. i have set up 2 days but on Day 2 it shows the exercise information
from Day 1. Is this a possiblility to make this set up easier?

well, it's becoming unclear just what you want the workouts table for. from
the table fields you originally posted, including a date field, it seemed
clear that you wanted to a) enter records assigning specific exercises to be
done on specific dates (2/16/09, 2/17/09, 2/18/09, etc), OR b) enter records
to track what specific exercises were performed on specific dates. either
purpose would be served by the same table structure.

but now you're talking about Day 1, Day 2, etc. that sounds more like you
want to set up a workout program for each client, with a set of exercise
assignments for a set of rotating days, such as

Day 1
exercise a, ...
exercise b, ...
exercise c, ...

Day 2
exercise d, ...
exercise e, ...
exercise f, ...

Day 3
exercise b, ...
exercise d, ...
exercise f, ...
exercise g, ...

then the client would perhaps take a day off, and begin the rotation again
with the exercises assigned to Day 1.

in the above scenario, you wouldn't have a date field in the workouts table
at all. you'd have a "Day" field, to assign specific exercises to specific
*days* in the program rotation. so please clarify, just what is the intended
purpose of the database, and specifically of the workout table?

I'm just learning here...what are your suggestions?
Thanks!

well, you could split the Workout table into a Workout table with a child
WorkoutExercises table - but i can't see much sense in that if the Workout
table's only common field will be the workout date field. you'd still have
the same number of fields in the child table, just replacing the date field
with a foreign key field linking back to the Workout table. it just adds an
unnecessary layer to your tables/relationships.

instead, suggest you keep the tables structure as is, and add an unbound
textbox control to frmClients, i'll call it txtWorkoutDate. enter the date
you want to see/add workout records for. add the txtWorkoutDate to the
subform control's LinkMasterFields property, and add the workout date field
name (from the workouts table) to the LinkChildFields property. by changing
the date in the textbox, you'll change the records returned by the subform.
also, you can add a Default value to the date control in the subform, as

=[Forms]![frmClients]![txtWorkoutDate]

so when you *add* a new record to the subform, it will automatically have
the date entered in the txtWorkDate control in the mainform.

the above will work for the form/subform setup i outlined in my previous
post. i'm not sure what you mean by

I have a command button on a "switchboard" that opens my frmClient.

When
I

Click this button I want to open a new form, how do i tell it to do

that?

the command button opens frmClient. what "new form" do you want to open?
instead of frmClient? in addition to frmClient? please explain.

I then want to be able to EDIT that particular workout later if needed by
creating a command button called "edit workout". when i click this command
button i want it to ask me for which client(list box), then whick workout
date (list box).

well, in the form/subform setup i proposed, you CAN edit any existing
workout record anytime you want, as well as adding a workout record anytime
you want. if instead you want a form simply to add/edit client records, and
a separate form to add/edit workout records for specific clients/dates, you
can do that pretty much as you proposed - with an unbound listbox with
RowSource based on the clients table, and an unbound listbox of dates with
RowSource based on a SELECT DISTINCT query of the date field of the workout
table, and filtering the workouts form's RecordSource with the values chosen
in those two controls. though having a listbox of dates might be somewhat
trickier from the standpoint of entering new records in the form with a date
that was not yet in the date listbox - you'd want to requery the listbox at
some point, and some issues might arise; i'd have to work through it with a
test setup to really discover the ins and outs of making it work.

hth

Tina, I could use a little more help.

I have made the changes as you suggested and looks great so far but I

am
not

sure where to put my workout date. In my subform I have done as you
suggested below. However, if I put my workout date in this subform

and
make

it a continous form it repeats the workout date too. I want to create a
Workout for a specific client and a specific date. then add multiple
exercises for that date. Hope this makes sense.

I have a command button on a "switchboard" that opens my frmClient.

When
I

Click this button I want to open a new form, how do i tell it to do that?

I then want to be able to EDIT that particular workout later if needed by
creating a command button called "edit workout". when i click this command
button i want it to ask me for which client(list box), then whick workout
date (list box).

Thanks so much for all help!
LTOSH

:

you're welcome


thanks allen and tina for guidance on my tables...i will get to

work
on

them
probably tomorrow. i may be bck for further help.
thanks again!!

:

a note on your tables design: recommend you remove the Exercise Name
field
from the Workout table. that value is already stored in the Exercises
table,
which is linked to the Workout table by the ExerciseID foreign key
field, so
storing it again violates normalization rules. and if the Description
field
in the Workout table is the same as the Description field in the
Exercises
table, then remove it from the Workout table too, for the same reason.
and,
btw, recommend you remove any spaces from your fieldnames - Exercise
name
should be ExerciseName; for more information, see
http://home.att.net/~california.db/tips.html#aTip5.

once you've made the correction(s) above, suggest the following

for
your

data entry forms: create a form bound to the Client table, SingleForm
view,
i'll call it frmClients. create another form bound to the

Workout
table,

Datasheet view, i'll call it sfrmWorkouts. in sfrmWorkouts, add a
combobox
control, and set the ControlSource to ExerciseID; set the

RowSource
to a

SQL
statement, as

SELECT ExerciseID, ExerciseName FROM Exercises;

set the following combobox properties, as

ColumnCount: 2
ColumnWidths: 0"; 1"
BoundColumn: 1
LimitToList: Yes

when you look at the combobox droplist in Form view (Datasheet

view,
in

this
case), you'll see only the exercise name, but when you pick an exercise
from
the list, the ExerciseID will be saved in the field in the Workout
table, as
it should be.

add sfrmWorkouts to the frmClients, as a subform. set the subform's
LinkChildFields property to ClientID (which refers to the

foreign
key

field
in the Workout table), and set the LinkMasterFields property to ClientID
(which refers to the primary key field in the Client table).

so now you can open the mainform (frmClients), and add a new client
record
or go to an existing client record, and then - in the subform - choose
an
exercise from the combobox droplist and enter the date, sets,

reps,
and

weight; enter as many exercise records as you want for each client.

hth


(Using Access 2000)
I have a 3 tables.
Client Table...ClientID(pk), first name, last name, address etc.
Exercises Table...ExerciseID(pk), exercise name, description etc
Workout Table...WorkoutID(pk), ClientID, ExerciseID, workout date,
Exercise
Name, sets, reps, weight, description.

I am wanting to create a form where i can choose a client

(combo
box),

type
in the workout date, then create a workout. I want to save this
workout
specific to the client name. Where do i begin?

Thanks!

 

[LTOSH]

i apologize for being unclear.

your second scenio is what i am thinking. i just haven't been able to
explain it properly. Yes i want to set up a workout program with a set of
exercises specific to day 1, day 2 etc as you have described but specific for
a client...

comments inline.

when i say add "new form", i mean when i click on the button it opens a blank
frmClient...ready for new information....right now when i click my command
button and it opens the frmClient it has the information that was previously
entered.

you can add code to frmClients Load event procedure, as

DoCmd.RunCommand acCmdRecordsGoToNew

i have been playing around some waiting for a response on last post and
wondered about adding on the frmClient a tab control (labeled Day 1, Day 2
etc). then add the sfrmWorkouts to each Day. Ex. Click Day 1 tab, then
select exercises etc for that day...Click Day 2 select exercises for that
day. i have set up 2 days but on Day 2 it shows the exercise information
from Day 1. Is this a possiblility to make this set up easier?

well, it's becoming unclear just what you want the workouts table for. from
the table fields you originally posted, including a date field, it seemed
clear that you wanted to a) enter records assigning specific exercises to be
done on specific dates (2/16/09, 2/17/09, 2/18/09, etc), OR b) enter records
to track what specific exercises were performed on specific dates. either
purpose would be served by the same table structure.

but now you're talking about Day 1, Day 2, etc. that sounds more like you
want to set up a workout program for each client, with a set of exercise
assignments for a set of rotating days, such as

Day 1
exercise a, ...
exercise b, ...
exercise c, ...

Day 2
exercise d, ...
exercise e, ...
exercise f, ...

Day 3
exercise b, ...
exercise d, ...
exercise f, ...
exercise g, ...

then the client would perhaps take a day off, and begin the rotation again
with the exercises assigned to Day 1.

in the above scenario, you wouldn't have a date field in the workouts table
at all. you'd have a "Day" field, to assign specific exercises to specific
*days* in the program rotation. so please clarify, just what is the intended
purpose of the database, and specifically of the workout table?

I'm just learning here...what are your suggestions?
Thanks!

well, you could split the Workout table into a Workout table with a child
WorkoutExercises table - but i can't see much sense in that if the Workout
table's only common field will be the workout date field. you'd still have
the same number of fields in the child table, just replacing the date field
with a foreign key field linking back to the Workout table. it just adds an
unnecessary layer to your tables/relationships.

instead, suggest you keep the tables structure as is, and add an unbound
textbox control to frmClients, i'll call it txtWorkoutDate. enter the date
you want to see/add workout records for. add the txtWorkoutDate to the
subform control's LinkMasterFields property, and add the workout date field
name (from the workouts table) to the LinkChildFields property. by changing
the date in the textbox, you'll change the records returned by the subform.
also, you can add a Default value to the date control in the subform, as

=[Forms]![frmClients]![txtWorkoutDate]

so when you *add* a new record to the subform, it will automatically have
the date entered in the txtWorkDate control in the mainform.

the above will work for the form/subform setup i outlined in my previous
post. i'm not sure what you mean by

I have a command button on a "switchboard" that opens my frmClient. When
I
Click this button I want to open a new form, how do i tell it to do that?

the command button opens frmClient. what "new form" do you want to open?
instead of frmClient? in addition to frmClient? please explain.

I then want to be able to EDIT that particular workout later if needed by
creating a command button called "edit workout". when i click this
command
button i want it to ask me for which client(list box), then whick workout
date (list box).

well, in the form/subform setup i proposed, you CAN edit any existing
workout record anytime you want, as well as adding a workout record anytime
you want. if instead you want a form simply to add/edit client records, and
a separate form to add/edit workout records for specific clients/dates, you
can do that pretty much as you proposed - with an unbound listbox with
RowSource based on the clients table, and an unbound listbox of dates with
RowSource based on a SELECT DISTINCT query of the date field of the workout
table, and filtering the workouts form's RecordSource with the values chosen
in those two controls. though having a listbox of dates might be somewhat
trickier from the standpoint of entering new records in the form with a date
that was not yet in the date listbox - you'd want to requery the listbox at
some point, and some issues might arise; i'd have to work through it with a
test setup to really discover the ins and outs of making it work.

hth


Tina, I could use a little more help.

I have made the changes as you suggested and looks great so far but I am
not
sure where to put my workout date. In my subform I have done as you
suggested below. However, if I put my workout date in this subform and
make
it a continous form it repeats the workout date too. I want to create a
Workout for a specific client and a specific date. then add multiple
exercises for that date. Hope this makes sense.

I have a command button on a "switchboard" that opens my frmClient. When
I
Click this button I want to open a new form, how do i tell it to do that?

I then want to be able to EDIT that particular workout later if needed by
creating a command button called "edit workout". when i click this
command
button i want it to ask me for which client(list box), then whick workout
date (list box).

Thanks so much for all help!
LTOSH

:

you're welcome


thanks allen and tina for guidance on my tables...i will get to work
on
them
probably tomorrow. i may be bck for further help.
thanks again!!

:

a note on your tables design: recommend you remove the Exercise
Name
field
from the Workout table. that value is already stored in the
Exercises
table,
which is linked to the Workout table by the ExerciseID foreign key
field, so
storing it again violates normalization rules. and if the
Description
field
in the Workout table is the same as the Description field in the
Exercises
table, then remove it from the Workout table too, for the same
reason.
and,
btw, recommend you remove any spaces from your fieldnames - Exercise
name
should be ExerciseName; for more information, see
http://home.att.net/~california.db/tips.html#aTip5.

once you've made the correction(s) above, suggest the following for
your
data entry forms: create a form bound to the Client table,
SingleForm
view,
i'll call it frmClients. create another form bound to the Workout
table,
Datasheet view, i'll call it sfrmWorkouts. in sfrmWorkouts, add a
combobox
control, and set the ControlSource to ExerciseID; set the RowSource
to a
SQL
statement, as

SELECT ExerciseID, ExerciseName FROM Exercises;

set the following combobox properties, as

ColumnCount: 2
ColumnWidths: 0"; 1"
BoundColumn: 1
LimitToList: Yes

when you look at the combobox droplist in Form view (Datasheet view,
in
this
case), you'll see only the exercise name, but when you pick an
exercise
from
the list, the ExerciseID will be saved in the field in the Workout
table, as
it should be.

add sfrmWorkouts to the frmClients, as a subform. set the subform's
LinkChildFields property to ClientID (which refers to the foreign
key
field
in the Workout table), and set the LinkMasterFields property to
ClientID
(which refers to the primary key field in the Client table).

so now you can open the mainform (frmClients), and add a new client
record
or go to an existing client record, and then - in the subform -
choose
an
exercise from the combobox droplist and enter the date, sets, reps,
and
weight; enter as many exercise records as you want for each client.

hth


(Using Access 2000)
I have a 3 tables.
Client Table...ClientID(pk), first name, last name, address etc.
Exercises Table...ExerciseID(pk), exercise name, description etc
Workout Table...WorkoutID(pk), ClientID, ExerciseID, workout date,
Exercise
Name, sets, reps, weight, description.

I am wanting to create a form where i can choose a client (combo
box),
type
in the workout date, then create a workout. I want to save this
workout
specific to the client name. Where do i begin?

Thanks!

 

[tina]

then i'd go back to the workout table, get rid of the date field, and add a
field to store rotation day. and make sure you don't call the field "Day",
as that is a Reserved word in Access. i'd probably just call it RotationDay.
other than that, i still don't see a reason to change the overall
tables/relationships structure. just remember that after you change the
field in the workout table, you'll need to change queries and forms that
include that field.

other than that, working with rotation days rather than specific calendar
dates will actually simplify matters. in the listbox proposal you posted
earlier in the thread, for example, your RotationDays listbox would be a
finite list - simply the maximum number of days you want to include in any
single rotation, such as

1
2
3
4
5
6

some clients might be on a 3-day rotation, some on 4-day, some on 6-day,
whatever is appropriate.

as for your last idea of using a tabbed control, that could easily work
pretty much the same as the above mentioned listbox. since the pages index
is zero-based, i'd probably set the subform's LinkMasterFields property to
the name of a hidden textbox control (i'll call it txtDay) with
ControlSource as

=[TabCtl0] + 1

replacing TabCtl0 with the correct name of the tab control on your form, of
course. so if the first page of the tab control was Rotation Day 1, the page
index (and therefore the value of the tab control) would be 0. 0 + 1 = 1. so
clicking on the first tab would show the exercises assigned to Day 1. and
again, you could set the DefaultValue of the RotationDay control in the
subform to

=[Forms]![frmClients]![txtDay]

so new records entered would be assigned to the day of the currently
selected tab. so, yes, you could have an unbound form, with a listbox
showing all your clients, with a single subform for the workouts table, set
"under" a tab control so it will "show through" on every tab. and a hidden
textbox control to convert the page index value of the tab control to the
rotation day. then set the LinkMasterFields property of the subform to the
listbox control (as previously discussed) and the hidden textbox control.
set the LinkChildFields property to the name of the foreign key field in the
subform and the rotation day field in the subform.

choosing a client in the listbox will limit the subform records to that
client, and choosing a page (tab) of the tabcontrol will further limit the
subform records to a particular rotation day for that client.

hth

i apologize for being unclear.

your second scenio is what i am thinking. i just haven't been able to
explain it properly. Yes i want to set up a workout program with a set of
exercises specific to day 1, day 2 etc as you have described but specific for
a client...

comments inline.

when i say add "new form", i mean when i click on the button it opens

a
blank

frmClient...ready for new information....right now when i click my command
button and it opens the frmClient it has the information that was previously
entered.

you can add code to frmClients Load event procedure, as

DoCmd.RunCommand acCmdRecordsGoToNew

i have been playing around some waiting for a response on last post and
wondered about adding on the frmClient a tab control (labeled Day 1, Day 2
etc). then add the sfrmWorkouts to each Day. Ex. Click Day 1 tab, then
select exercises etc for that day...Click Day 2 select exercises for that
day. i have set up 2 days but on Day 2 it shows the exercise information
from Day 1. Is this a possiblility to make this set up easier?

well, it's becoming unclear just what you want the workouts table for. from
the table fields you originally posted, including a date field, it seemed
clear that you wanted to a) enter records assigning specific exercises to be
done on specific dates (2/16/09, 2/17/09, 2/18/09, etc), OR b) enter records
to track what specific exercises were performed on specific dates. either
purpose would be served by the same table structure.

but now you're talking about Day 1, Day 2, etc. that sounds more like you
want to set up a workout program for each client, with a set of exercise
assignments for a set of rotating days, such as

Day 1
exercise a, ...
exercise b, ...
exercise c, ...

Day 2
exercise d, ...
exercise e, ...
exercise f, ...

Day 3
exercise b, ...
exercise d, ...
exercise f, ...
exercise g, ...

then the client would perhaps take a day off, and begin the rotation again
with the exercises assigned to Day 1.

in the above scenario, you wouldn't have a date field in the workouts table
at all. you'd have a "Day" field, to assign specific exercises to specific
*days* in the program rotation. so please clarify, just what is the intended
purpose of the database, and specifically of the workout table?

I'm just learning here...what are your suggestions?
Thanks!

:

well, you could split the Workout table into a Workout table with a child
WorkoutExercises table - but i can't see much sense in that if the Workout
table's only common field will be the workout date field. you'd

still
have

the same number of fields in the child table, just replacing the

date
field

with a foreign key field linking back to the Workout table. it just

adds
an

unnecessary layer to your tables/relationships.

instead, suggest you keep the tables structure as is, and add an unbound
textbox control to frmClients, i'll call it txtWorkoutDate. enter

the
date

you want to see/add workout records for. add the txtWorkoutDate to the
subform control's LinkMasterFields property, and add the workout

date
field

name (from the workouts table) to the LinkChildFields property. by changing
the date in the textbox, you'll change the records returned by the subform.
also, you can add a Default value to the date control in the subform, as

=[Forms]![frmClients]![txtWorkoutDate]

so when you *add* a new record to the subform, it will automatically have
the date entered in the txtWorkDate control in the mainform.

the above will work for the form/subform setup i outlined in my previous
post. i'm not sure what you mean by

I have a command button on a "switchboard" that opens my

frmClient.
When

I
Click this button I want to open a new form, how do i tell it to

do
that?

the command button opens frmClient. what "new form" do you want to open?
instead of frmClient? in addition to frmClient? please explain.

I then want to be able to EDIT that particular workout later if

needed
by

creating a command button called "edit workout". when i click this
command
button i want it to ask me for which client(list box), then whick workout
date (list box).

well, in the form/subform setup i proposed, you CAN edit any existing
workout record anytime you want, as well as adding a workout record anytime
you want. if instead you want a form simply to add/edit client

records,
and

a separate form to add/edit workout records for specific

clients/dates,
you

can do that pretty much as you proposed - with an unbound listbox with
RowSource based on the clients table, and an unbound listbox of

dates
with

RowSource based on a SELECT DISTINCT query of the date field of the workout
table, and filtering the workouts form's RecordSource with the

values
chosen

in those two controls. though having a listbox of dates might be somewhat
trickier from the standpoint of entering new records in the form

with a
date

that was not yet in the date listbox - you'd want to requery the

listbox
at

some point, and some issues might arise; i'd have to work through it with a
test setup to really discover the ins and outs of making it work.

hth


Tina, I could use a little more help.

I have made the changes as you suggested and looks great so far

but I
am

not
sure where to put my workout date. In my subform I have done as you
suggested below. However, if I put my workout date in this

subform
and

make
it a continous form it repeats the workout date too. I want to

create
a

Workout for a specific client and a specific date. then add multiple
exercises for that date. Hope this makes sense.

I have a command button on a "switchboard" that opens my

frmClient.
When

I
Click this button I want to open a new form, how do i tell it to

do
that?

I then want to be able to EDIT that particular workout later if

needed
by

creating a command button called "edit workout". when i click this
command
button i want it to ask me for which client(list box), then whick workout
date (list box).

Thanks so much for all help!
LTOSH

:

you're welcome


thanks allen and tina for guidance on my tables...i will get

to
work

on
them
probably tomorrow. i may be bck for further help.
thanks again!!

:

a note on your tables design: recommend you remove the Exercise
Name
field
from the Workout table. that value is already stored in the
Exercises
table,
which is linked to the Workout table by the ExerciseID

foreign
key

field, so
storing it again violates normalization rules. and if the
Description
field
in the Workout table is the same as the Description field in the
Exercises
table, then remove it from the Workout table too, for the same
reason.
and,
btw, recommend you remove any spaces from your fieldnames - Exercise
name
should be ExerciseName; for more information, see
http://home.att.net/~california.db/tips.html#aTip5.

once you've made the correction(s) above, suggest the

following
for

your
data entry forms: create a form bound to the Client table,
SingleForm
view,
i'll call it frmClients. create another form bound to the Workout
table,
Datasheet view, i'll call it sfrmWorkouts. in sfrmWorkouts,

add
a

combobox
control, and set the ControlSource to ExerciseID; set the RowSource
to a
SQL
statement, as

SELECT ExerciseID, ExerciseName FROM Exercises;

set the following combobox properties, as

ColumnCount: 2
ColumnWidths: 0"; 1"
BoundColumn: 1
LimitToList: Yes

when you look at the combobox droplist in Form view

(Datasheet
view,

in
this
case), you'll see only the exercise name, but when you pick an
exercise
from
the list, the ExerciseID will be saved in the field in the Workout
table, as
it should be.

add sfrmWorkouts to the frmClients, as a subform. set the subform's
LinkChildFields property to ClientID (which refers to the foreign
key
field
in the Workout table), and set the LinkMasterFields property to
ClientID
(which refers to the primary key field in the Client table).

so now you can open the mainform (frmClients), and add a new client
record
or go to an existing client record, and then - in the subform -
choose
an
exercise from the combobox droplist and enter the date,

sets,
reps,

and
weight; enter as many exercise records as you want for each client.

hth


(Using Access 2000)
I have a 3 tables.
Client Table...ClientID(pk), first name, last name,

address
etc.

Exercises Table...ExerciseID(pk), exercise name,

description
etc

Workout Table...WorkoutID(pk), ClientID, ExerciseID,

workout
date,

Exercise
Name, sets, reps, weight, description.

I am wanting to create a form where i can choose a client (combo
box),
type
in the workout date, then create a workout. I want to

save
this

workout
specific to the client name. Where do i begin?

Thanks!

 

[LTOSH]

Thank you so much for your help. however i am having trouble following the
set up of all this. Before I go on, which of the two suggestions would be
the easiest for me?? you see what i have suggested below and you have
responded, again thank you! If you would suggest which would be easiest and
walk me through it I would be grateful. I was not able to understand and
follow the directions for the "tabbed control" so if that is what you think i
should go with then please help me with that.
Thanks for all your help!

then i'd go back to the workout table, get rid of the date field, and add a
field to store rotation day. and make sure you don't call the field "Day",
as that is a Reserved word in Access. i'd probably just call it RotationDay.
other than that, i still don't see a reason to change the overall
tables/relationships structure. just remember that after you change the
field in the workout table, you'll need to change queries and forms that
include that field.

other than that, working with rotation days rather than specific calendar
dates will actually simplify matters. in the listbox proposal you posted
earlier in the thread, for example, your RotationDays listbox would be a
finite list - simply the maximum number of days you want to include in any
single rotation, such as

1
2
3
4
5
6

some clients might be on a 3-day rotation, some on 4-day, some on 6-day,
whatever is appropriate.

as for your last idea of using a tabbed control, that could easily work
pretty much the same as the above mentioned listbox. since the pages index
is zero-based, i'd probably set the subform's LinkMasterFields property to
the name of a hidden textbox control (i'll call it txtDay) with
ControlSource as

=[TabCtl0] + 1

replacing TabCtl0 with the correct name of the tab control on your form, of
course. so if the first page of the tab control was Rotation Day 1, the page
index (and therefore the value of the tab control) would be 0. 0 + 1 = 1. so
clicking on the first tab would show the exercises assigned to Day 1. and
again, you could set the DefaultValue of the RotationDay control in the
subform to

=[Forms]![frmClients]![txtDay]

so new records entered would be assigned to the day of the currently
selected tab. so, yes, you could have an unbound form, with a listbox
showing all your clients, with a single subform for the workouts table, set
"under" a tab control so it will "show through" on every tab. and a hidden
textbox control to convert the page index value of the tab control to the
rotation day. then set the LinkMasterFields property of the subform to the
listbox control (as previously discussed) and the hidden textbox control.
set the LinkChildFields property to the name of the foreign key field in the
subform and the rotation day field in the subform.

choosing a client in the listbox will limit the subform records to that
client, and choosing a page (tab) of the tabcontrol will further limit the
subform records to a particular rotation day for that client.

hth

i apologize for being unclear.

your second scenio is what i am thinking. i just haven't been able to
explain it properly. Yes i want to set up a workout program with a set of
exercises specific to day 1, day 2 etc as you have described but specific for
a client...

comments inline.

when i say add "new form", i mean when i click on the button it opens a
blank
frmClient...ready for new information....right now when i click my command
button and it opens the frmClient it has the information that was
previously
entered.

you can add code to frmClients Load event procedure, as

DoCmd.RunCommand acCmdRecordsGoToNew


i have been playing around some waiting for a response on last post and
wondered about adding on the frmClient a tab control (labeled Day 1, Day 2
etc). then add the sfrmWorkouts to each Day. Ex. Click Day 1 tab, then
select exercises etc for that day...Click Day 2 select exercises for that
day. i have set up 2 days but on Day 2 it shows the exercise information
from Day 1. Is this a possiblility to make this set up easier?

well, it's becoming unclear just what you want the workouts table for. from
the table fields you originally posted, including a date field, it seemed
clear that you wanted to a) enter records assigning specific exercises to be
done on specific dates (2/16/09, 2/17/09, 2/18/09, etc), OR b) enter records
to track what specific exercises were performed on specific dates. either
purpose would be served by the same table structure.

but now you're talking about Day 1, Day 2, etc. that sounds more like you
want to set up a workout program for each client, with a set of exercise
assignments for a set of rotating days, such as

Day 1
exercise a, ...
exercise b, ...
exercise c, ...

Day 2
exercise d, ...
exercise e, ...
exercise f, ...

Day 3
exercise b, ...
exercise d, ...
exercise f, ...
exercise g, ...

then the client would perhaps take a day off, and begin the rotation again
with the exercises assigned to Day 1.

in the above scenario, you wouldn't have a date field in the workouts table
at all. you'd have a "Day" field, to assign specific exercises to specific
*days* in the program rotation. so please clarify, just what is the intended
purpose of the database, and specifically of the workout table?


I'm just learning here...what are your suggestions?
Thanks!

:

well, you could split the Workout table into a Workout table with a
child
WorkoutExercises table - but i can't see much sense in that if the
Workout
table's only common field will be the workout date field. you'd still
have
the same number of fields in the child table, just replacing the date
field
with a foreign key field linking back to the Workout table. it just adds
an
unnecessary layer to your tables/relationships.

instead, suggest you keep the tables structure as is, and add an unbound
textbox control to frmClients, i'll call it txtWorkoutDate. enter the
date
you want to see/add workout records for. add the txtWorkoutDate to the
subform control's LinkMasterFields property, and add the workout date
field
name (from the workouts table) to the LinkChildFields property. by
changing
the date in the textbox, you'll change the records returned by the
subform.
also, you can add a Default value to the date control in the subform, as

=[Forms]![frmClients]![txtWorkoutDate]

so when you *add* a new record to the subform, it will automatically
have
the date entered in the txtWorkDate control in the mainform.

the above will work for the form/subform setup i outlined in my previous
post. i'm not sure what you mean by

I have a command button on a "switchboard" that opens my frmClient.
When
I
Click this button I want to open a new form, how do i tell it to do
that?

the command button opens frmClient. what "new form" do you want to open?
instead of frmClient? in addition to frmClient? please explain.

I then want to be able to EDIT that particular workout later if needed
by
creating a command button called "edit workout". when i click this
command
button i want it to ask me for which client(list box), then whick
workout
date (list box).

well, in the form/subform setup i proposed, you CAN edit any existing
workout record anytime you want, as well as adding a workout record
anytime
you want. if instead you want a form simply to add/edit client records,
and
a separate form to add/edit workout records for specific clients/dates,
you
can do that pretty much as you proposed - with an unbound listbox with
RowSource based on the clients table, and an unbound listbox of dates
with
RowSource based on a SELECT DISTINCT query of the date field of the
workout
table, and filtering the workouts form's RecordSource with the values
chosen
in those two controls. though having a listbox of dates might be
somewhat
trickier from the standpoint of entering new records in the form with a
date
that was not yet in the date listbox - you'd want to requery the listbox
at
some point, and some issues might arise; i'd have to work through it
with a
test setup to really discover the ins and outs of making it work.

hth


Tina, I could use a little more help.

I have made the changes as you suggested and looks great so far but I
am
not
sure where to put my workout date. In my subform I have done as you
suggested below. However, if I put my workout date in this subform
and
make
it a continous form it repeats the workout date too. I want to create
a
Workout for a specific client and a specific date. then add multiple
exercises for that date. Hope this makes sense.

I have a command button on a "switchboard" that opens my frmClient.
When
I
Click this button I want to open a new form, how do i tell it to do
that?

I then want to be able to EDIT that particular workout later if needed
by
creating a command button called "edit workout". when i click this
command
button i want it to ask me for which client(list box), then whick
workout
date (list box).

Thanks so much for all help!
LTOSH

:

you're welcome


news:[email protected]...

 

[tina]

well, the setup is not difficult once you understand how tab controls work,
subforms work, etc - but getting there can be hard. i think i'd have to
write a bit of a book to walk you through either scenario step-by-step. if
you can wait until the weekend, and if you're using any version of Access
between A97 and A2003, i'll use the tables/fields we've discussed in this
thread to build a "quick and dirty" demo of the listbox/subform/tabcontrol
setup, and load it to my website where you can download it and study it. if
you'd like me to do this, post back and tell me what version of Access
you're using.

hth

Thank you so much for your help. however i am having trouble following the
set up of all this. Before I go on, which of the two suggestions would be
the easiest for me?? you see what i have suggested below and you have
responded, again thank you! If you would suggest which would be easiest and
walk me through it I would be grateful. I was not able to understand and
follow the directions for the "tabbed control" so if that is what you think i
should go with then please help me with that.
Thanks for all your help!

then i'd go back to the workout table, get rid of the date field, and add a
field to store rotation day. and make sure you don't call the field "Day",
as that is a Reserved word in Access. i'd probably just call it RotationDay.
other than that, i still don't see a reason to change the overall
tables/relationships structure. just remember that after you change the
field in the workout table, you'll need to change queries and forms that
include that field.

other than that, working with rotation days rather than specific calendar
dates will actually simplify matters. in the listbox proposal you posted
earlier in the thread, for example, your RotationDays listbox would be a
finite list - simply the maximum number of days you want to include in any
single rotation, such as

1
2
3
4
5
6

some clients might be on a 3-day rotation, some on 4-day, some on 6-day,
whatever is appropriate.

as for your last idea of using a tabbed control, that could easily work
pretty much the same as the above mentioned listbox. since the pages index
is zero-based, i'd probably set the subform's LinkMasterFields property to
the name of a hidden textbox control (i'll call it txtDay) with
ControlSource as

=[TabCtl0] + 1

replacing TabCtl0 with the correct name of the tab control on your form, of
course. so if the first page of the tab control was Rotation Day 1, the page
index (and therefore the value of the tab control) would be 0. 0 + 1 = 1. so
clicking on the first tab would show the exercises assigned to Day 1. and
again, you could set the DefaultValue of the RotationDay control in the
subform to

=[Forms]![frmClients]![txtDay]

so new records entered would be assigned to the day of the currently
selected tab. so, yes, you could have an unbound form, with a listbox
showing all your clients, with a single subform for the workouts table, set
"under" a tab control so it will "show through" on every tab. and a hidden
textbox control to convert the page index value of the tab control to the
rotation day. then set the LinkMasterFields property of the subform to the
listbox control (as previously discussed) and the hidden textbox control.
set the LinkChildFields property to the name of the foreign key field in the
subform and the rotation day field in the subform.

choosing a client in the listbox will limit the subform records to that
client, and choosing a page (tab) of the tabcontrol will further limit the
subform records to a particular rotation day for that client.

hth

i apologize for being unclear.

your second scenio is what i am thinking. i just haven't been able to
explain it properly. Yes i want to set up a workout program with a set of
exercises specific to day 1, day 2 etc as you have described but

specific
for

a client...


:

comments inline.

when i say add "new form", i mean when i click on the button it

opens
a

blank
frmClient...ready for new information....right now when i click my command
button and it opens the frmClient it has the information that was
previously
entered.

you can add code to frmClients Load event procedure, as

DoCmd.RunCommand acCmdRecordsGoToNew


i have been playing around some waiting for a response on last

post
and

wondered about adding on the frmClient a tab control (labeled Day

1,
Day 2

etc). then add the sfrmWorkouts to each Day. Ex. Click Day 1 tab, then
select exercises etc for that day...Click Day 2 select exercises

for
that

day. i have set up 2 days but on Day 2 it shows the exercise information
from Day 1. Is this a possiblility to make this set up easier?

well, it's becoming unclear just what you want the workouts table

for.
from

the table fields you originally posted, including a date field, it seemed
clear that you wanted to a) enter records assigning specific

exercises
to be

done on specific dates (2/16/09, 2/17/09, 2/18/09, etc), OR b) enter records
to track what specific exercises were performed on specific dates. either
purpose would be served by the same table structure.

but now you're talking about Day 1, Day 2, etc. that sounds more

like
you

want to set up a workout program for each client, with a set of exercise
assignments for a set of rotating days, such as

Day 1
exercise a, ...
exercise b, ...
exercise c, ...

Day 2
exercise d, ...
exercise e, ...
exercise f, ...

Day 3
exercise b, ...
exercise d, ...
exercise f, ...
exercise g, ...

then the client would perhaps take a day off, and begin the rotation again
with the exercises assigned to Day 1.

in the above scenario, you wouldn't have a date field in the

workouts
table

at all. you'd have a "Day" field, to assign specific exercises to specific
*days* in the program rotation. so please clarify, just what is the intended
purpose of the database, and specifically of the workout table?


I'm just learning here...what are your suggestions?
Thanks!

:

well, you could split the Workout table into a Workout table with a
child
WorkoutExercises table - but i can't see much sense in that if the
Workout
table's only common field will be the workout date field. you'd still
have
the same number of fields in the child table, just replacing the date
field
with a foreign key field linking back to the Workout table. it

just
adds

an
unnecessary layer to your tables/relationships.

instead, suggest you keep the tables structure as is, and add an unbound
textbox control to frmClients, i'll call it txtWorkoutDate.

enter
the

date
you want to see/add workout records for. add the txtWorkoutDate

to
the

subform control's LinkMasterFields property, and add the workout date
field
name (from the workouts table) to the LinkChildFields property. by
changing
the date in the textbox, you'll change the records returned by the
subform.
also, you can add a Default value to the date control in the subform, as

=[Forms]![frmClients]![txtWorkoutDate]

so when you *add* a new record to the subform, it will automatically
have
the date entered in the txtWorkDate control in the mainform.

the above will work for the form/subform setup i outlined in my previous
post. i'm not sure what you mean by

I have a command button on a "switchboard" that opens my frmClient.
When
I
Click this button I want to open a new form, how do i tell it

to
do

that?

the command button opens frmClient. what "new form" do you want

to
open?

instead of frmClient? in addition to frmClient? please explain.

I then want to be able to EDIT that particular workout later

if
needed

by
creating a command button called "edit workout". when i click this
command
button i want it to ask me for which client(list box), then whick
workout
date (list box).

well, in the form/subform setup i proposed, you CAN edit any existing
workout record anytime you want, as well as adding a workout record
anytime
you want. if instead you want a form simply to add/edit client records,
and
a separate form to add/edit workout records for specific clients/dates,
you
can do that pretty much as you proposed - with an unbound

listbox
with

RowSource based on the clients table, and an unbound listbox of dates
with
RowSource based on a SELECT DISTINCT query of the date field of the
workout
table, and filtering the workouts form's RecordSource with the values
chosen
in those two controls. though having a listbox of dates might be
somewhat
trickier from the standpoint of entering new records in the form with a
date
that was not yet in the date listbox - you'd want to requery the listbox
at
some point, and some issues might arise; i'd have to work through it
with a
test setup to really discover the ins and outs of making it work.

hth


Tina, I could use a little more help.

I have made the changes as you suggested and looks great so

far
but I

am
not
sure where to put my workout date. In my subform I have done

as
you

suggested below. However, if I put my workout date in this subform
and
make
it a continous form it repeats the workout date too. I want

to
create

a
Workout for a specific client and a specific date. then add multiple
exercises for that date. Hope this makes sense.

I have a command button on a "switchboard" that opens my frmClient.
When
I
Click this button I want to open a new form, how do i tell it

to
do

that?

I then want to be able to EDIT that particular workout later

if
needed

by
creating a command button called "edit workout". when i click this
command
button i want it to ask me for which client(list box), then whick
workout
date (list box).

Thanks so much for all help!
LTOSH

:

you're welcome


news:[email protected]...

 

[LTOSH]

ok here is where i am and i hope to have response from you on this.

I have created a form (frmClients2) with a combo box for the client name.
I have placed my "sfrmWorkouts2" on this form "under" a tab control so it
will "show through" on all tabs. I have 8 tabs labeled Day 1 - Day 6,
Cardio, Abs.

Here is where i need your help....on your last instructins below from the
part about the "txtDay" down to the paragraph ending with the
"LinkChildFields" instructions.

right now when i add exercises it shows for all the tabs. I also want to
make sure that this is specific for client that is chosen.

Thanks for your help...hope to hear from you soon.
LTOSH

then i'd go back to the workout table, get rid of the date field, and add a
field to store rotation day. and make sure you don't call the field "Day",
as that is a Reserved word in Access. i'd probably just call it RotationDay.
other than that, i still don't see a reason to change the overall
tables/relationships structure. just remember that after you change the
field in the workout table, you'll need to change queries and forms that
include that field.

other than that, working with rotation days rather than specific calendar
dates will actually simplify matters. in the listbox proposal you posted
earlier in the thread, for example, your RotationDays listbox would be a
finite list - simply the maximum number of days you want to include in any
single rotation, such as

1
2
3
4
5
6

some clients might be on a 3-day rotation, some on 4-day, some on 6-day,
whatever is appropriate.

as for your last idea of using a tabbed control, that could easily work
pretty much the same as the above mentioned listbox. since the pages index
is zero-based, i'd probably set the subform's LinkMasterFields property to
the name of a hidden textbox control (i'll call it txtDay) with
ControlSource as

=[TabCtl0] + 1

replacing TabCtl0 with the correct name of the tab control on your form, of
course. so if the first page of the tab control was Rotation Day 1, the page
index (and therefore the value of the tab control) would be 0. 0 + 1 = 1. so
clicking on the first tab would show the exercises assigned to Day 1. and
again, you could set the DefaultValue of the RotationDay control in the
subform to

=[Forms]![frmClients]![txtDay]

so new records entered would be assigned to the day of the currently
selected tab. so, yes, you could have an unbound form, with a listbox
showing all your clients, with a single subform for the workouts table, set
"under" a tab control so it will "show through" on every tab. and a hidden
textbox control to convert the page index value of the tab control to the
rotation day. then set the LinkMasterFields property of the subform to the
listbox control (as previously discussed) and the hidden textbox control.
set the LinkChildFields property to the name of the foreign key field in the
subform and the rotation day field in the subform.

choosing a client in the listbox will limit the subform records to that
client, and choosing a page (tab) of the tabcontrol will further limit the
subform records to a particular rotation day for that client.

hth

i apologize for being unclear.

your second scenio is what i am thinking. i just haven't been able to
explain it properly. Yes i want to set up a workout program with a set of
exercises specific to day 1, day 2 etc as you have described but specific for
a client...

comments inline.

when i say add "new form", i mean when i click on the button it opens a
blank
frmClient...ready for new information....right now when i click my command
button and it opens the frmClient it has the information that was
previously
entered.

you can add code to frmClients Load event procedure, as

DoCmd.RunCommand acCmdRecordsGoToNew


i have been playing around some waiting for a response on last post and
wondered about adding on the frmClient a tab control (labeled Day 1, Day 2
etc). then add the sfrmWorkouts to each Day. Ex. Click Day 1 tab, then
select exercises etc for that day...Click Day 2 select exercises for that
day. i have set up 2 days but on Day 2 it shows the exercise information
from Day 1. Is this a possiblility to make this set up easier?

well, it's becoming unclear just what you want the workouts table for. from
the table fields you originally posted, including a date field, it seemed
clear that you wanted to a) enter records assigning specific exercises to be
done on specific dates (2/16/09, 2/17/09, 2/18/09, etc), OR b) enter records
to track what specific exercises were performed on specific dates. either
purpose would be served by the same table structure.

but now you're talking about Day 1, Day 2, etc. that sounds more like you
want to set up a workout program for each client, with a set of exercise
assignments for a set of rotating days, such as

Day 1
exercise a, ...
exercise b, ...
exercise c, ...

Day 2
exercise d, ...
exercise e, ...
exercise f, ...

Day 3
exercise b, ...
exercise d, ...
exercise f, ...
exercise g, ...

then the client would perhaps take a day off, and begin the rotation again
with the exercises assigned to Day 1.

in the above scenario, you wouldn't have a date field in the workouts table
at all. you'd have a "Day" field, to assign specific exercises to specific
*days* in the program rotation. so please clarify, just what is the intended
purpose of the database, and specifically of the workout table?


I'm just learning here...what are your suggestions?
Thanks!

:

well, you could split the Workout table into a Workout table with a
child
WorkoutExercises table - but i can't see much sense in that if the
Workout
table's only common field will be the workout date field. you'd still
have
the same number of fields in the child table, just replacing the date
field
with a foreign key field linking back to the Workout table. it just adds
an
unnecessary layer to your tables/relationships.

instead, suggest you keep the tables structure as is, and add an unbound
textbox control to frmClients, i'll call it txtWorkoutDate. enter the
date
you want to see/add workout records for. add the txtWorkoutDate to the
subform control's LinkMasterFields property, and add the workout date
field
name (from the workouts table) to the LinkChildFields property. by
changing
the date in the textbox, you'll change the records returned by the
subform.
also, you can add a Default value to the date control in the subform, as

=[Forms]![frmClients]![txtWorkoutDate]

so when you *add* a new record to the subform, it will automatically
have
the date entered in the txtWorkDate control in the mainform.

the above will work for the form/subform setup i outlined in my previous
post. i'm not sure what you mean by

I have a command button on a "switchboard" that opens my frmClient.
When
I
Click this button I want to open a new form, how do i tell it to do
that?

the command button opens frmClient. what "new form" do you want to open?
instead of frmClient? in addition to frmClient? please explain.

I then want to be able to EDIT that particular workout later if needed
by
creating a command button called "edit workout". when i click this
command
button i want it to ask me for which client(list box), then whick
workout
date (list box).

well, in the form/subform setup i proposed, you CAN edit any existing
workout record anytime you want, as well as adding a workout record
anytime
you want. if instead you want a form simply to add/edit client records,
and
a separate form to add/edit workout records for specific clients/dates,
you
can do that pretty much as you proposed - with an unbound listbox with
RowSource based on the clients table, and an unbound listbox of dates
with
RowSource based on a SELECT DISTINCT query of the date field of the
workout
table, and filtering the workouts form's RecordSource with the values
chosen
in those two controls. though having a listbox of dates might be
somewhat
trickier from the standpoint of entering new records in the form with a
date
that was not yet in the date listbox - you'd want to requery the listbox
at
some point, and some issues might arise; i'd have to work through it
with a
test setup to really discover the ins and outs of making it work.

hth


Tina, I could use a little more help.

I have made the changes as you suggested and looks great so far but I
am
not
sure where to put my workout date. In my subform I have done as you
suggested below. However, if I put my workout date in this subform
and
make
it a continous form it repeats the workout date too. I want to create
a
Workout for a specific client and a specific date. then add multiple
exercises for that date. Hope this makes sense.

I have a command button on a "switchboard" that opens my frmClient.
When
I
Click this button I want to open a new form, how do i tell it to do
that?

I then want to be able to EDIT that particular workout later if needed
by
creating a command button called "edit workout". when i click this
command
button i want it to ask me for which client(list box), then whick
workout
date (list box).

Thanks so much for all help!
LTOSH

:

you're welcome


news:[email protected]...

 

[LTOSH]

haha as you were typing me i was posting what i have done so far.
if after you read what i just posted and the help at this point is too
lengthy then yes i guess i can wait till weekend and you could set the "quick
and dirty" demo up as you called it hahaha. i am currently running access
2000.

so i need to study up on subform/tabcontrol setup stuff then.
just hard for me to wait (hehehe OCD). I feel that once this is set up then
the rest will be easier, just feel like this part of it is a roadblock for
me.
Thanks for your help and any other suggestions you might have.

well, the setup is not difficult once you understand how tab controls work,
subforms work, etc - but getting there can be hard. i think i'd have to
write a bit of a book to walk you through either scenario step-by-step. if
you can wait until the weekend, and if you're using any version of Access
between A97 and A2003, i'll use the tables/fields we've discussed in this
thread to build a "quick and dirty" demo of the listbox/subform/tabcontrol
setup, and load it to my website where you can download it and study it. if
you'd like me to do this, post back and tell me what version of Access
you're using.

hth

Thank you so much for your help. however i am having trouble following the
set up of all this. Before I go on, which of the two suggestions would be
the easiest for me?? you see what i have suggested below and you have
responded, again thank you! If you would suggest which would be easiest and
walk me through it I would be grateful. I was not able to understand and
follow the directions for the "tabbed control" so if that is what you think i
should go with then please help me with that.
Thanks for all your help!

then i'd go back to the workout table, get rid of the date field, and add a
field to store rotation day. and make sure you don't call the field "Day",
as that is a Reserved word in Access. i'd probably just call it RotationDay.
other than that, i still don't see a reason to change the overall
tables/relationships structure. just remember that after you change the
field in the workout table, you'll need to change queries and forms that
include that field.

other than that, working with rotation days rather than specific calendar
dates will actually simplify matters. in the listbox proposal you posted
earlier in the thread, for example, your RotationDays listbox would be a
finite list - simply the maximum number of days you want to include in any
single rotation, such as

1
2
3
4
5
6

some clients might be on a 3-day rotation, some on 4-day, some on 6-day,
whatever is appropriate.

as for your last idea of using a tabbed control, that could easily work
pretty much the same as the above mentioned listbox. since the pages index
is zero-based, i'd probably set the subform's LinkMasterFields property to
the name of a hidden textbox control (i'll call it txtDay) with
ControlSource as

=[TabCtl0] + 1

replacing TabCtl0 with the correct name of the tab control on your form, of
course. so if the first page of the tab control was Rotation Day 1, the page
index (and therefore the value of the tab control) would be 0. 0 + 1 = 1. so
clicking on the first tab would show the exercises assigned to Day 1. and
again, you could set the DefaultValue of the RotationDay control in the
subform to

=[Forms]![frmClients]![txtDay]

so new records entered would be assigned to the day of the currently
selected tab. so, yes, you could have an unbound form, with a listbox
showing all your clients, with a single subform for the workouts table, set
"under" a tab control so it will "show through" on every tab. and a hidden
textbox control to convert the page index value of the tab control to the
rotation day. then set the LinkMasterFields property of the subform to the
listbox control (as previously discussed) and the hidden textbox control.
set the LinkChildFields property to the name of the foreign key field in the
subform and the rotation day field in the subform.

choosing a client in the listbox will limit the subform records to that
client, and choosing a page (tab) of the tabcontrol will further limit the
subform records to a particular rotation day for that client.

hth


i apologize for being unclear.

your second scenio is what i am thinking. i just haven't been able to
explain it properly. Yes i want to set up a workout program with a set of
exercises specific to day 1, day 2 etc as you have described but specific
for
a client...


:

comments inline.

when i say add "new form", i mean when i click on the button it opens
a
blank
frmClient...ready for new information....right now when i click my
command
button and it opens the frmClient it has the information that was
previously
entered.

you can add code to frmClients Load event procedure, as

DoCmd.RunCommand acCmdRecordsGoToNew


i have been playing around some waiting for a response on last post
and
wondered about adding on the frmClient a tab control (labeled Day 1,
Day 2
etc). then add the sfrmWorkouts to each Day. Ex. Click Day 1 tab,
then
select exercises etc for that day...Click Day 2 select exercises for
that
day. i have set up 2 days but on Day 2 it shows the exercise
information
from Day 1. Is this a possiblility to make this set up easier?

well, it's becoming unclear just what you want the workouts table for.
from
the table fields you originally posted, including a date field, it
seemed
clear that you wanted to a) enter records assigning specific exercises
to be
done on specific dates (2/16/09, 2/17/09, 2/18/09, etc), OR b) enter
records
to track what specific exercises were performed on specific dates.
either
purpose would be served by the same table structure.

but now you're talking about Day 1, Day 2, etc. that sounds more like
you
want to set up a workout program for each client, with a set of exercise
assignments for a set of rotating days, such as

Day 1
exercise a, ...
exercise b, ...
exercise c, ...

Day 2
exercise d, ...
exercise e, ...
exercise f, ...

Day 3
exercise b, ...
exercise d, ...
exercise f, ...
exercise g, ...

then the client would perhaps take a day off, and begin the rotation
again
with the exercises assigned to Day 1.

in the above scenario, you wouldn't have a date field in the workouts
table
at all. you'd have a "Day" field, to assign specific exercises to
specific
*days* in the program rotation. so please clarify, just what is the
intended
purpose of the database, and specifically of the workout table?


I'm just learning here...what are your suggestions?
Thanks!

:

well, you could split the Workout table into a Workout table with a
child
WorkoutExercises table - but i can't see much sense in that if the
Workout
table's only common field will be the workout date field. you'd
still
have
the same number of fields in the child table, just replacing the
date
field
with a foreign key field linking back to the Workout table. it just
adds
an
unnecessary layer to your tables/relationships.

instead, suggest you keep the tables structure as is, and add an
unbound
textbox control to frmClients, i'll call it txtWorkoutDate. enter
the
date
you want to see/add workout records for. add the txtWorkoutDate to
the
subform control's LinkMasterFields property, and add the workout
date
field
name (from the workouts table) to the LinkChildFields property. by
changing
the date in the textbox, you'll change the records returned by the
subform.
also, you can add a Default value to the date control in the
subform, as

=[Forms]![frmClients]![txtWorkoutDate]

so when you *add* a new record to the subform, it will automatically
have
the date entered in the txtWorkDate control in the mainform.

the above will work for the form/subform setup i outlined in my
previous
post. i'm not sure what you mean by

I have a command button on a "switchboard" that opens my
frmClient.
When
I
Click this button I want to open a new form, how do i tell it to
do
that?

the command button opens frmClient. what "new form" do you want to
open?
instead of frmClient? in addition to frmClient? please explain.

I then want to be able to EDIT that particular workout later if
needed
by
creating a command button called "edit workout". when i click
this
command
button i want it to ask me for which client(list box), then whick
workout
date (list box).

well, in the form/subform setup i proposed, you CAN edit any
existing
workout record anytime you want, as well as adding a workout record
anytime
you want. if instead you want a form simply to add/edit client
records,
and
a separate form to add/edit workout records for specific
clients/dates,
you
can do that pretty much as you proposed - with an unbound listbox
with
RowSource based on the clients table, and an unbound listbox of

 

[tina]

okay. keep an eye on this thread; i'll post back again when the demo is
available, should be by Sunday night.

haha as you were typing me i was posting what i have done so far.
if after you read what i just posted and the help at this point is too
lengthy then yes i guess i can wait till weekend and you could set the "quick
and dirty" demo up as you called it hahaha. i am currently running access
2000.

so i need to study up on subform/tabcontrol setup stuff then.
just hard for me to wait (hehehe OCD). I feel that once this is set up then
the rest will be easier, just feel like this part of it is a roadblock for
me.
Thanks for your help and any other suggestions you might have.

well, the setup is not difficult once you understand how tab controls work,
subforms work, etc - but getting there can be hard. i think i'd have to
write a bit of a book to walk you through either scenario step-by-step. if
you can wait until the weekend, and if you're using any version of Access
between A97 and A2003, i'll use the tables/fields we've discussed in this
thread to build a "quick and dirty" demo of the listbox/subform/tabcontrol
setup, and load it to my website where you can download it and study it. if
you'd like me to do this, post back and tell me what version of Access
you're using.

hth

Thank you so much for your help. however i am having trouble following the
set up of all this. Before I go on, which of the two suggestions would be
the easiest for me?? you see what i have suggested below and you have
responded, again thank you! If you would suggest which would be

easiest
and

walk me through it I would be grateful. I was not able to understand and
follow the directions for the "tabbed control" so if that is what you think i
should go with then please help me with that.
Thanks for all your help!

:

then i'd go back to the workout table, get rid of the date field,

and
add a

field to store rotation day. and make sure you don't call the field "Day",
as that is a Reserved word in Access. i'd probably just call it RotationDay.
other than that, i still don't see a reason to change the overall
tables/relationships structure. just remember that after you change the
field in the workout table, you'll need to change queries and forms that
include that field.

other than that, working with rotation days rather than specific calendar
dates will actually simplify matters. in the listbox proposal you posted
earlier in the thread, for example, your RotationDays listbox would be a
finite list - simply the maximum number of days you want to include

in
any

single rotation, such as

1
2
3
4
5
6

some clients might be on a 3-day rotation, some on 4-day, some on 6-day,
whatever is appropriate.

as for your last idea of using a tabbed control, that could easily work
pretty much the same as the above mentioned listbox. since the pages index
is zero-based, i'd probably set the subform's LinkMasterFields

property
to

the name of a hidden textbox control (i'll call it txtDay) with
ControlSource as

=[TabCtl0] + 1

replacing TabCtl0 with the correct name of the tab control on your

form,
of

course. so if the first page of the tab control was Rotation Day 1,

the
page

index (and therefore the value of the tab control) would be 0. 0 + 1

=
1. so

clicking on the first tab would show the exercises assigned to Day

1.
and

again, you could set the DefaultValue of the RotationDay control in the
subform to

=[Forms]![frmClients]![txtDay]

so new records entered would be assigned to the day of the currently
selected tab. so, yes, you could have an unbound form, with a listbox
showing all your clients, with a single subform for the workouts

table,
set

"under" a tab control so it will "show through" on every tab. and a hidden
textbox control to convert the page index value of the tab control

to
the

rotation day. then set the LinkMasterFields property of the subform

to
the

listbox control (as previously discussed) and the hidden textbox control.
set the LinkChildFields property to the name of the foreign key

field in
the

subform and the rotation day field in the subform.

choosing a client in the listbox will limit the subform records to that
client, and choosing a page (tab) of the tabcontrol will further

limit
the

subform records to a particular rotation day for that client.

hth


i apologize for being unclear.

your second scenio is what i am thinking. i just haven't been able to
explain it properly. Yes i want to set up a workout program with

a
set of

exercises specific to day 1, day 2 etc as you have described but specific
for
a client...


:

comments inline.

when i say add "new form", i mean when i click on the button

it
opens

a
blank
frmClient...ready for new information....right now when i click my
command
button and it opens the frmClient it has the information that was
previously
entered.

you can add code to frmClients Load event procedure, as

DoCmd.RunCommand acCmdRecordsGoToNew


i have been playing around some waiting for a response on last post
and
wondered about adding on the frmClient a tab control (labeled

Day
1,

Day 2
etc). then add the sfrmWorkouts to each Day. Ex. Click Day 1 tab,
then
select exercises etc for that day...Click Day 2 select

exercises
for

that
day. i have set up 2 days but on Day 2 it shows the exercise
information
from Day 1. Is this a possiblility to make this set up easier?

well, it's becoming unclear just what you want the workouts

table
for.

from
the table fields you originally posted, including a date field, it
seemed
clear that you wanted to a) enter records assigning specific exercises
to be
done on specific dates (2/16/09, 2/17/09, 2/18/09, etc), OR b) enter
records
to track what specific exercises were performed on specific dates.
either
purpose would be served by the same table structure.

but now you're talking about Day 1, Day 2, etc. that sounds more like
you
want to set up a workout program for each client, with a set of exercise
assignments for a set of rotating days, such as

Day 1
exercise a, ...
exercise b, ...
exercise c, ...

Day 2
exercise d, ...
exercise e, ...
exercise f, ...

Day 3
exercise b, ...
exercise d, ...
exercise f, ...
exercise g, ...

then the client would perhaps take a day off, and begin the rotation
again
with the exercises assigned to Day 1.

in the above scenario, you wouldn't have a date field in the workouts
table
at all. you'd have a "Day" field, to assign specific exercises to
specific
*days* in the program rotation. so please clarify, just what is the
intended
purpose of the database, and specifically of the workout table?


I'm just learning here...what are your suggestions?
Thanks!

:

well, you could split the Workout table into a Workout table with a
child
WorkoutExercises table - but i can't see much sense in that

if
the

Workout
table's only common field will be the workout date field. you'd
still
have
the same number of fields in the child table, just replacing the
date
field
with a foreign key field linking back to the Workout table.

it
just

adds
an
unnecessary layer to your tables/relationships.

instead, suggest you keep the tables structure as is, and add an
unbound
textbox control to frmClients, i'll call it txtWorkoutDate. enter
the
date
you want to see/add workout records for. add the

txtWorkoutDate
to

the
subform control's LinkMasterFields property, and add the workout
date
field
name (from the workouts table) to the LinkChildFields

property.
by

changing
the date in the textbox, you'll change the records returned

by
the

subform.
also, you can add a Default value to the date control in the
subform, as

=[Forms]![frmClients]![txtWorkoutDate]

so when you *add* a new record to the subform, it will automatically
have
the date entered in the txtWorkDate control in the mainform.

the above will work for the form/subform setup i outlined in my
previous
post. i'm not sure what you mean by

I have a command button on a "switchboard" that opens my
frmClient.
When
I
Click this button I want to open a new form, how do i tell

it
to

do
that?

the command button opens frmClient. what "new form" do you

want
to

open?
instead of frmClient? in addition to frmClient? please explain.

I then want to be able to EDIT that particular workout

later
if

needed
by
creating a command button called "edit workout". when i click
this
command
button i want it to ask me for which client(list box),

then
whick

workout
date (list box).

well, in the form/subform setup i proposed, you CAN edit any
existing
workout record anytime you want, as well as adding a workout record
anytime
you want. if instead you want a form simply to add/edit client
records,
and
a separate form to add/edit workout records for specific
clients/dates,
you
can do that pretty much as you proposed - with an unbound listbox
with
RowSource based on the clients table, and an unbound listbox

of

 

[LTOSH]

was curious to see if you were going to have the demo ready soon.
i'm really anxious to get this going.
again i really appreciate your help!

okay. keep an eye on this thread; i'll post back again when the demo is
available, should be by Sunday night.

haha as you were typing me i was posting what i have done so far.
if after you read what i just posted and the help at this point is too
lengthy then yes i guess i can wait till weekend and you could set the "quick
and dirty" demo up as you called it hahaha. i am currently running access
2000.

so i need to study up on subform/tabcontrol setup stuff then.
just hard for me to wait (hehehe OCD). I feel that once this is set up then
the rest will be easier, just feel like this part of it is a roadblock for
me.
Thanks for your help and any other suggestions you might have.

well, the setup is not difficult once you understand how tab controls work,
subforms work, etc - but getting there can be hard. i think i'd have to
write a bit of a book to walk you through either scenario step-by-step. if
you can wait until the weekend, and if you're using any version of Access
between A97 and A2003, i'll use the tables/fields we've discussed in this
thread to build a "quick and dirty" demo of the listbox/subform/tabcontrol
setup, and load it to my website where you can download it and study it. if
you'd like me to do this, post back and tell me what version of Access
you're using.

hth


Thank you so much for your help. however i am having trouble following the
set up of all this. Before I go on, which of the two suggestions would be
the easiest for me?? you see what i have suggested below and you have
responded, again thank you! If you would suggest which would be easiest
and
walk me through it I would be grateful. I was not able to understand and
follow the directions for the "tabbed control" so if that is what you
think i
should go with then please help me with that.
Thanks for all your help!

:

then i'd go back to the workout table, get rid of the date field, and
add a
field to store rotation day. and make sure you don't call the field
"Day",
as that is a Reserved word in Access. i'd probably just call it
RotationDay.
other than that, i still don't see a reason to change the overall
tables/relationships structure. just remember that after you change the
field in the workout table, you'll need to change queries and forms that
include that field.

other than that, working with rotation days rather than specific
calendar
dates will actually simplify matters. in the listbox proposal you posted
earlier in the thread, for example, your RotationDays listbox would be a
finite list - simply the maximum number of days you want to include in
any
single rotation, such as

1
2
3
4
5
6

some clients might be on a 3-day rotation, some on 4-day, some on 6-day,
whatever is appropriate.

as for your last idea of using a tabbed control, that could easily work
pretty much the same as the above mentioned listbox. since the pages
index
is zero-based, i'd probably set the subform's LinkMasterFields property
to
the name of a hidden textbox control (i'll call it txtDay) with
ControlSource as

=[TabCtl0] + 1

replacing TabCtl0 with the correct name of the tab control on your form,
of
course. so if the first page of the tab control was Rotation Day 1, the
page
index (and therefore the value of the tab control) would be 0. 0 + 1 =
1. so
clicking on the first tab would show the exercises assigned to Day 1.
and
again, you could set the DefaultValue of the RotationDay control in the
subform to

=[Forms]![frmClients]![txtDay]

so new records entered would be assigned to the day of the currently
selected tab. so, yes, you could have an unbound form, with a listbox
showing all your clients, with a single subform for the workouts table,
set
"under" a tab control so it will "show through" on every tab. and a
hidden
textbox control to convert the page index value of the tab control to
the
rotation day. then set the LinkMasterFields property of the subform to
the
listbox control (as previously discussed) and the hidden textbox
control.
set the LinkChildFields property to the name of the foreign key field in
the
subform and the rotation day field in the subform.

choosing a client in the listbox will limit the subform records to that
client, and choosing a page (tab) of the tabcontrol will further limit
the
subform records to a particular rotation day for that client.

hth


i apologize for being unclear.

your second scenio is what i am thinking. i just haven't been able to
explain it properly. Yes i want to set up a workout program with a
set of
exercises specific to day 1, day 2 etc as you have described but
specific
for
a client...


:

comments inline.

when i say add "new form", i mean when i click on the button it
opens
a
blank
frmClient...ready for new information....right now when i click my
command
button and it opens the frmClient it has the information that was
previously
entered.

you can add code to frmClients Load event procedure, as

DoCmd.RunCommand acCmdRecordsGoToNew


i have been playing around some waiting for a response on last
post
and
wondered about adding on the frmClient a tab control (labeled Day
1,
Day 2
etc). then add the sfrmWorkouts to each Day. Ex. Click Day 1 tab,
then
select exercises etc for that day...Click Day 2 select exercises
for
that
day. i have set up 2 days but on Day 2 it shows the exercise
information
from Day 1. Is this a possiblility to make this set up easier?

well, it's becoming unclear just what you want the workouts table
for.
from
the table fields you originally posted, including a date field, it
seemed
clear that you wanted to a) enter records assigning specific
exercises
to be
done on specific dates (2/16/09, 2/17/09, 2/18/09, etc), OR b) enter
records
to track what specific exercises were performed on specific dates.
either
purpose would be served by the same table structure.

but now you're talking about Day 1, Day 2, etc. that sounds more
like
you
want to set up a workout program for each client, with a set of
exercise
assignments for a set of rotating days, such as

Day 1
exercise a, ...
exercise b, ...
exercise c, ...

Day 2
exercise d, ...
exercise e, ...
exercise f, ...

Day 3
exercise b, ...
exercise d, ...
exercise f, ...
exercise g, ...

then the client would perhaps take a day off, and begin the rotation
again
with the exercises assigned to Day 1.

in the above scenario, you wouldn't have a date field in the
workouts
table
at all. you'd have a "Day" field, to assign specific exercises to
specific
*days* in the program rotation. so please clarify, just what is the
intended
purpose of the database, and specifically of the workout table?


I'm just learning here...what are your suggestions?
Thanks!

:

well, you could split the Workout table into a Workout table
with a
child
WorkoutExercises table - but i can't see much sense in that if
the
Workout
table's only common field will be the workout date field. you'd
still
have
the same number of fields in the child table, just replacing the
date
field
with a foreign key field linking back to the Workout table. it
just
adds
an
unnecessary layer to your tables/relationships.

 

[tina]

okay, i've just uploaded the file. it's an A2000 file, named demo.bak.
****make sure you change the file extension from .bak to .mdb BEFORE you try
to open the file.****
to download the file, go to
http://home.att.net/~california.db/instructions.html and click on the "demo"
link near the bottom of the page.

the db opens to a working form. browse it as a user, then close it and open
frmDeveloperComments, and read the notes. study as much of the setup as you
wish, then open frmALTDeveloperComments, read the notes, and then open
frmALTMenu, and continue from there.

hth

okay. keep an eye on this thread; i'll post back again when the demo is
available, should be by Sunday night.

haha as you were typing me i was posting what i have done so far.
if after you read what i just posted and the help at this point is too
lengthy then yes i guess i can wait till weekend and you could set the "quick
and dirty" demo up as you called it hahaha. i am currently running access
2000.

so i need to study up on subform/tabcontrol setup stuff then.
just hard for me to wait (hehehe OCD). I feel that once this is set up then
the rest will be easier, just feel like this part of it is a roadblock for
me.
Thanks for your help and any other suggestions you might have.

step-by-step.

if

it.

if

following

the

understand

and

change

the

forms

that

would
be a include

in

any
single rotation, such as

1
2
3
4
5
6

some clients might be on a 3-day rotation, some on 4-day, some on 6-day,
whatever is appropriate.

as for your last idea of using a tabbed control, that could easily work
pretty much the same as the above mentioned listbox. since the pages
index
is zero-based, i'd probably set the subform's LinkMasterFields property
to
the name of a hidden textbox control (i'll call it txtDay) with
ControlSource as

=[TabCtl0] + 1

replacing TabCtl0 with the correct name of the tab control on your form,
of
course. so if the first page of the tab control was Rotation Day

1,

the

1

=

in

the

subform to

=[Forms]![frmClients]![txtDay]

so new records entered would be assigned to the day of the currently
selected tab. so, yes, you could have an unbound form, with a listbox
showing all your clients, with a single subform for the workouts table,
set
"under" a tab control so it will "show through" on every tab. and a
hidden
textbox control to convert the page index value of the tab control to
the
rotation day. then set the LinkMasterFields property of the

subform

to

with

a

that

was

(labeled

Day

1

tab,

field,

it

is

the

that

if

replacing

the

table.

it

returned

by

the
subform.
also, you can add a Default value to the date control in the
subform, as

=[Forms]![frmClients]![txtWorkoutDate]

so when you *add* a new record to the subform, it will
automatically
have
the date entered in the txtWorkDate control in the mainform.

the above will work for the form/subform setup i outlined

in

my

tell

it

listbox
of

 

[LTOSH]

oh wow thanks so much. i got it downloaded and the extension changed. Now
gonna study it. I look forward to learning what you have done. You are
awesome! Thanks for the instruction and your time. I'm sure I will be back
soon.
Thanks!!!

okay, i've just uploaded the file. it's an A2000 file, named demo.bak.
****make sure you change the file extension from .bak to .mdb BEFORE you try
to open the file.****
to download the file, go to
http://home.att.net/~california.db/instructions.html and click on the "demo"
link near the bottom of the page.

the db opens to a working form. browse it as a user, then close it and open
frmDeveloperComments, and read the notes. study as much of the setup as you
wish, then open frmALTDeveloperComments, read the notes, and then open
frmALTMenu, and continue from there.

hth

okay. keep an eye on this thread; i'll post back again when the demo is
available, should be by Sunday night.

haha as you were typing me i was posting what i have done so far.
if after you read what i just posted and the help at this point is too
lengthy then yes i guess i can wait till weekend and you could set the "quick
and dirty" demo up as you called it hahaha. i am currently running access
2000.

so i need to study up on subform/tabcontrol setup stuff then.
just hard for me to wait (hehehe OCD). I feel that once this is set up then
the rest will be easier, just feel like this part of it is a roadblock for
me.
Thanks for your help and any other suggestions you might have.

:

well, the setup is not difficult once you understand how tab controls work,
subforms work, etc - but getting there can be hard. i think i'd have to
write a bit of a book to walk you through either scenario

step-by-step.

if
you can wait until the weekend, and if you're using any version of Access
between A97 and A2003, i'll use the tables/fields we've discussed in this
thread to build a "quick and dirty" demo of the listbox/subform/tabcontrol
setup, and load it to my website where you can download it and study

it.

if
you'd like me to do this, post back and tell me what version of Access
you're using.

hth


Thank you so much for your help. however i am having trouble

following

the
set up of all this. Before I go on, which of the two suggestions would be
the easiest for me?? you see what i have suggested below and you have
responded, again thank you! If you would suggest which would be easiest
and
walk me through it I would be grateful. I was not able to

understand

and
follow the directions for the "tabbed control" so if that is what you
think i
should go with then please help me with that.
Thanks for all your help!

:

then i'd go back to the workout table, get rid of the date field, and
add a
field to store rotation day. and make sure you don't call the field
"Day",
as that is a Reserved word in Access. i'd probably just call it
RotationDay.
other than that, i still don't see a reason to change the overall
tables/relationships structure. just remember that after you

change

the
field in the workout table, you'll need to change queries and

forms

that
include that field.

other than that, working with rotation days rather than specific
calendar
dates will actually simplify matters. in the listbox proposal you posted
earlier in the thread, for example, your RotationDays listbox

would
be a

finite list - simply the maximum number of days you want to

include

in
any
single rotation, such as

1
2
3
4
5
6

some clients might be on a 3-day rotation, some on 4-day, some on 6-day,
whatever is appropriate.

as for your last idea of using a tabbed control, that could easily work
pretty much the same as the above mentioned listbox. since the pages
index
is zero-based, i'd probably set the subform's LinkMasterFields property
to
the name of a hidden textbox control (i'll call it txtDay) with
ControlSource as

=[TabCtl0] + 1

replacing TabCtl0 with the correct name of the tab control on your form,
of
course. so if the first page of the tab control was Rotation Day

1,

the
page
index (and therefore the value of the tab control) would be 0. 0 +

1

=
1. so
clicking on the first tab would show the exercises assigned to Day 1.
and
again, you could set the DefaultValue of the RotationDay control

in

the
subform to

=[Forms]![frmClients]![txtDay]

so new records entered would be assigned to the day of the currently
selected tab. so, yes, you could have an unbound form, with a listbox
showing all your clients, with a single subform for the workouts table,
set
"under" a tab control so it will "show through" on every tab. and a
hidden
textbox control to convert the page index value of the tab control to
the
rotation day. then set the LinkMasterFields property of the

subform

to
the
listbox control (as previously discussed) and the hidden textbox
control.
set the LinkChildFields property to the name of the foreign key field in
the
subform and the rotation day field in the subform.

choosing a client in the listbox will limit the subform records to that
client, and choosing a page (tab) of the tabcontrol will further limit
the
subform records to a particular rotation day for that client.

hth


i apologize for being unclear.

your second scenio is what i am thinking. i just haven't been able to
explain it properly. Yes i want to set up a workout program

with

a
set of
exercises specific to day 1, day 2 etc as you have described but
specific
for
a client...


:

comments inline.

when i say add "new form", i mean when i click on the button it
opens
a
blank
frmClient...ready for new information....right now when i click my
command
button and it opens the frmClient it has the information

that

was
previously
entered.

you can add code to frmClients Load event procedure, as

DoCmd.RunCommand acCmdRecordsGoToNew


i have been playing around some waiting for a response on last
post
and
wondered about adding on the frmClient a tab control

(labeled

Day
1,
Day 2
etc). then add the sfrmWorkouts to each Day. Ex. Click Day

1

tab,
then
select exercises etc for that day...Click Day 2 select exercises
for
that
day. i have set up 2 days but on Day 2 it shows the exercise
information
from Day 1. Is this a possiblility to make this set up easier?

well, it's becoming unclear just what you want the workouts table
for.
from
the table fields you originally posted, including a date

field,

it
seemed
clear that you wanted to a) enter records assigning specific
exercises
to be
done on specific dates (2/16/09, 2/17/09, 2/18/09, etc), OR b) enter
records
to track what specific exercises were performed on specific dates.
either
purpose would be served by the same table structure.

but now you're talking about Day 1, Day 2, etc. that sounds more
like
you
want to set up a workout program for each client, with a set of
exercise
assignments for a set of rotating days, such as

Day 1
exercise a, ...
exercise b, ...
exercise c, ...

Day 2
exercise d, ...
exercise e, ...
exercise f, ...

Day 3
exercise b, ...
exercise d, ...

 

[tina]

you're welcome

oh wow thanks so much. i got it downloaded and the extension changed. Now
gonna study it. I look forward to learning what you have done. You are
awesome! Thanks for the instruction and your time. I'm sure I will be back
soon.
Thanks!!!

okay, i've just uploaded the file. it's an A2000 file, named demo.bak.
****make sure you change the file extension from .bak to .mdb BEFORE you try
to open the file.****
to download the file, go to
http://home.att.net/~california.db/instructions.html and click on the "demo"
link near the bottom of the page.

the db opens to a working form. browse it as a user, then close it and open
frmDeveloperComments, and read the notes. study as much of the setup as you
wish, then open frmALTDeveloperComments, read the notes, and then open
frmALTMenu, and continue from there.

hth

okay. keep an eye on this thread; i'll post back again when the demo is
available, should be by Sunday night.


haha as you were typing me i was posting what i have done so far.
if after you read what i just posted and the help at this point is too
lengthy then yes i guess i can wait till weekend and you could set the
"quick
and dirty" demo up as you called it hahaha. i am currently running access
2000.

so i need to study up on subform/tabcontrol setup stuff then.
just hard for me to wait (hehehe OCD). I feel that once this is set up
then
the rest will be easier, just feel like this part of it is a

roadblock
for

me.
Thanks for your help and any other suggestions you might have.

:

well, the setup is not difficult once you understand how tab controls
work,
subforms work, etc - but getting there can be hard. i think i'd

have
to

write a bit of a book to walk you through either scenario step-by-step.
if
you can wait until the weekend, and if you're using any version of
Access
between A97 and A2003, i'll use the tables/fields we've discussed in
this
thread to build a "quick and dirty" demo of the
listbox/subform/tabcontrol
setup, and load it to my website where you can download it and

study
it.

if
you'd like me to do this, post back and tell me what version of Access
you're using.

hth


Thank you so much for your help. however i am having trouble following
the
set up of all this. Before I go on, which of the two suggestions
would be
the easiest for me?? you see what i have suggested below and you have
responded, again thank you! If you would suggest which would be
easiest
and
walk me through it I would be grateful. I was not able to understand
and
follow the directions for the "tabbed control" so if that is

what
you

think i
should go with then please help me with that.
Thanks for all your help!

:

then i'd go back to the workout table, get rid of the date field,
and
add a
field to store rotation day. and make sure you don't call the

field

"Day",
as that is a Reserved word in Access. i'd probably just call it
RotationDay.
other than that, i still don't see a reason to change the overall
tables/relationships structure. just remember that after you change
the
field in the workout table, you'll need to change queries and forms
that
include that field.

other than that, working with rotation days rather than specific
calendar
dates will actually simplify matters. in the listbox proposal you
posted
earlier in the thread, for example, your RotationDays listbox would
be a
finite list - simply the maximum number of days you want to include
in
any
single rotation, such as

1
2
3
4
5
6

some clients might be on a 3-day rotation, some on 4-day, some on
6-day,
whatever is appropriate.

as for your last idea of using a tabbed control, that could easily
work
pretty much the same as the above mentioned listbox. since the pages
index
is zero-based, i'd probably set the subform's LinkMasterFields
property
to
the name of a hidden textbox control (i'll call it txtDay) with
ControlSource as

=[TabCtl0] + 1

replacing TabCtl0 with the correct name of the tab control on your
form,
of
course. so if the first page of the tab control was Rotation

Day
1,

the
page
index (and therefore the value of the tab control) would be 0.

0 +
1

=
1. so
clicking on the first tab would show the exercises assigned to Day
1.
and
again, you could set the DefaultValue of the RotationDay

control
in

the
subform to

=[Forms]![frmClients]![txtDay]

so new records entered would be assigned to the day of the currently
selected tab. so, yes, you could have an unbound form, with a
listbox
showing all your clients, with a single subform for the workouts
table,
set
"under" a tab control so it will "show through" on every tab.

and
a

hidden
textbox control to convert the page index value of the tab control
to
the
rotation day. then set the LinkMasterFields property of the subform
to
the
listbox control (as previously discussed) and the hidden textbox
control.
set the LinkChildFields property to the name of the foreign key
field in
the
subform and the rotation day field in the subform.

choosing a client in the listbox will limit the subform records to
that
client, and choosing a page (tab) of the tabcontrol will further
limit
the
subform records to a particular rotation day for that client.

hth


i apologize for being unclear.

your second scenio is what i am thinking. i just haven't been
able to
explain it properly. Yes i want to set up a workout program with
a
set of
exercises specific to day 1, day 2 etc as you have described but
specific
for
a client...


:

comments inline.

when i say add "new form", i mean when i click on the button
it
opens
a
blank
frmClient...ready for new information....right now when i
click my
command
button and it opens the frmClient it has the information that
was
previously
entered.

you can add code to frmClients Load event procedure, as

DoCmd.RunCommand acCmdRecordsGoToNew


i have been playing around some waiting for a response

on
last

post
and
wondered about adding on the frmClient a tab control (labeled
Day
1,
Day 2
etc). then add the sfrmWorkouts to each Day. Ex. Click

Day
1

tab,
then
select exercises etc for that day...Click Day 2 select
exercises
for
that
day. i have set up 2 days but on Day 2 it shows the exercise
information
from Day 1. Is this a possiblility to make this set up
easier?

well, it's becoming unclear just what you want the workouts
table
for.
from
the table fields you originally posted, including a date field,
it
seemed
clear that you wanted to a) enter records assigning specific
exercises
to be
done on specific dates (2/16/09, 2/17/09, 2/18/09, etc), OR b)
enter
records
to track what specific exercises were performed on specific
dates.
either
purpose would be served by the same table structure.

but now you're talking about Day 1, Day 2, etc. that

sounds
more

like
you
want to set up a workout program for each client, with a

set
of

exercise
assignments for a set of rotating days, such as

Day 1
exercise a, ...
exercise b, ...
exercise c, ...

Day 2
exercise d, ...
exercise e, ...
exercise f, ...

Day 3
exercise b, ...
exercise d, ...