Subform/Master Form linking question

[Mason]

This is probably an incredibly newbie-ish question. I just haven't
had the cause to use many subforms before, so I'm pretty sure I just
don't understand it correctly (even after reading up on it).

I have a relationship set up that is used to track the hours an
employee has worked on each project during the week. I'm trying to
set up a Form to allow someone to choose their name and a Period
Ending date (basically, the tblTimeReport table), which when selected
will have the subform allow me to enter information into the Entry
table that pertains to that combination of name and date.

I'm having a nasty time trying to get it all working properly. With
some setups the subform won't allow me to enter data at all, while in
others it seems to ignore the name and date I've inputed. I have a
feeling that TimeReportID being the primary key of the tblTimeReport
table has something to do with it, but I'm not positive. I just can't
get the subform and main form to play nice.

Any help would be greatly appreciated. I've given a description of
the table below. Hopefully that's enough information for you to go
on.

* = Primary Key, + = Foreign Key

tblEmployee
EmployeeID, Autonumber*
LastName, Text
FirstName, Text
....

tblPeriodEnding
PeriodEnding, Date/Time*

tblTimeReport
TimeReportID, AutoNumber*
EmployeeID, Number+ (tblEmployee)
PeriodEnding, Date/Time+ (tblPeriodEnding)

tblEntry
EntryID, Autonumber*
TimeReportID, Number+ (tblTimeReport)
EmployeeID, Number+ (tblEmployee)
Project, Number+ (tblProject, not show here because it's not
pertinent)
MondayHours, Number
TuesdayHours, Number
WednesdayHours, Number
....
Regards,
MW

[Janross]

Hi, Mason:

Consider another approach. If all you're really doing is filling in your entry
table, then make the form bound to that table. At the top of the form, have
two unbound combo boxes, the first of which lists all employees, and the second
which lists all periods. Then, in the afterupdate event of the second combo
(or perhaps in code tied to a "find the record" button), find the appropriate
record in the Entry table that has the employee and period you've chosen. If
there's no existing record, it would go to a blank record, fill in the employee
and period you've chosen, and you're off. No subforms.

This may neglect something I didn't get in my first reading of your post, but
maybe it's food for thought.

Good luck.

Jan

I have a relationship set up that is used to track the hours an
employee has worked on each project during the week. I'm trying to
set up a Form to allow someone to choose their name and a Period
Ending date (basically, the tblTimeReport table), which when selected
will have the subform allow me to enter information into the Entry
table that pertains to that combination of name and date.

I'm having a nasty time trying to get it all working properly. With
some setups the subform won't allow me to enter data at all, while in
others it seems to ignore the name and date I've inputed. I have a
feeling that TimeReportID being the primary key of the tblTimeReport
table has something to do with it, but I'm not positive. I just can't
get the subform and main form to play nice.

Any help would be greatly appreciated. I've given a description of
the table below. Hopefully that's enough information for you to go
on.

* = Primary Key, + = Foreign Key

tblEmployee
EmployeeID, Autonumber*
LastName, Text
FirstName, Text
...

tblPeriodEnding
PeriodEnding, Date/Time*

tblTimeReport
TimeReportID, AutoNumber*
EmployeeID, Number+ (tblEmployee)
PeriodEnding, Date/Time+ (tblPeriodEnding)

tblEntry
EntryID, Autonumber*
TimeReportID, Number+ (tblTimeReport)
EmployeeID, Number+ (tblEmployee)
Project, Number+ (tblProject, not show here because it's not
pertinent)
MondayHours, Number
TuesdayHours, Number
WednesdayHours, Number

Jan Stempel
Stempel Consulting

[Mason]

Jan,

Thanks for the advice, I'm definitely going to take it. The question
this brings up for me (probably another newbie question) is, how do I
set up a datasheet-type view of the data while keeping it looking like
a form? For example:

Employee: Bob Period Ending: 10/1/2004

Project MondayHours TusedayHours WednesdayHours
Blah 5 3 2
Foo 3 5 6

So, there could easily be multiple lines. I'd want the ability to add
a new record at the bottom while still being able to see the other
data. A datasheet view is exactly what I'm looking for, except I hate
the fact that it adds an extra line below the one you're working on.
As a side note, is there any way to stop Access from adding another
line until you hit enter on the last field?

Hopefully my questions are clear.

Thanks again,

MW

ja*****@aol.com (Janross) wrote in message news:<20***************************@mb-m27.aol.com>...

Hi, Mason:

Consider another approach. If all you're really doing is filling in your entry
table, then make the form bound to that table. At the top of the form, have
two unbound combo boxes, the first of which lists all employees, and the second
which lists all periods. Then, in the afterupdate event of the second combo
(or perhaps in code tied to a "find the record" button), find the appropriate
record in the Entry table that has the employee and period you've chosen. If
there's no existing record, it would go to a blank record, fill in the employee
and period you've chosen, and you're off. No subforms.

This may neglect something I didn't get in my first reading of your post, but
maybe it's food for thought.

Good luck.

Jan

I have a relationship set up that is used to track the hours an
employee has worked on each project during the week. I'm trying to
set up a Form to allow someone to choose their name and a Period
Ending date (basically, the tblTimeReport table), which when selected
will have the subform allow me to enter information into the Entry
table that pertains to that combination of name and date.

I'm having a nasty time trying to get it all working properly. With
some setups the subform won't allow me to enter data at all, while in
others it seems to ignore the name and date I've inputed. I have a
feeling that TimeReportID being the primary key of the tblTimeReport
table has something to do with it, but I'm not positive. I just can't
get the subform and main form to play nice.

Any help would be greatly appreciated. I've given a description of
the table below. Hopefully that's enough information for you to go
on.

* = Primary Key, + = Foreign Key

tblEmployee
EmployeeID, Autonumber*
LastName, Text
FirstName, Text
...

tblPeriodEnding
PeriodEnding, Date/Time*

tblTimeReport
TimeReportID, AutoNumber*
EmployeeID, Number+ (tblEmployee)
PeriodEnding, Date/Time+ (tblPeriodEnding)

tblEntry
EntryID, Autonumber*
TimeReportID, Number+ (tblTimeReport)
EmployeeID, Number+ (tblEmployee)
Project, Number+ (tblProject, not show here because it's not
pertinent)
MondayHours, Number
TuesdayHours, Number
WednesdayHours, Number

Jan Stempel
Stempel Consulting

[Geoff]

> How do I

set up a datasheet-type view of the data while keeping it looking like a form? For example:

Employee: Bob Period Ending: 10/1/2004

Project MondayHours TusedayHours WednesdayHours
Blah 5 3 2
Foo 3 5 6
So, there could easily be multiple lines. I'd want the ability to add a new record at the bottom while still being able to see the other
data. A datasheet view is exactly what I'm looking for, except I hate the fact that it adds an extra line below the one you're working on.
As a side note, is there any way to stop Access from adding another
line until you hit enter on the last field?

If you're using Access 2000 or later, you could create a datasheet
subform and place it on a bound or unbound form. This may be flexible
enough to give you the solution you're after. I'm not sure you can
easily get rid of that last blank row.

Regards
Geoff

[Janross]

To get the equivalent of a datasheet view but with the flexibility and control
of a form, I usually set up continuous forms, with the field names in the form
header and text boxes (or combo boxes) for only the fields I need strung across
in the detail section. If necessary I sometimes put a second row below the
first, with something like a notes field.

You can't really get rid of that blank line at the bottom; it's the new record
ready to be added.

HTH

Jan

Thanks for the advice, I'm definitely going to take it. The question
this brings up for me (probably another newbie question) is, how do I
set up a datasheet-type view of the data while keeping it looking like
a form? For example:

Employee: Bob Period Ending: 10/1/2004

Project MondayHours TusedayHours WednesdayHours
Blah 5 3 2
Foo 3 5 6

So, there could easily be multiple lines. I'd want the ability to add
a new record at the bottom while still being able to see the other
data. A datasheet view is exactly what I'm looking for, except I hate
the fact that it adds an extra line below the one you're working on.
As a side note, is there any way to stop Access from adding another
line until you hit enter on the last field?

Hopefully my questions are clear.

Thanks again,

MW

ja*****@aol.com (Janross) wrote in message
news:<20***************************@mb-m27.aol.com>...

Hi, Mason:

Consider another approach. If all you're really doing is filling in your

entry

table, then make the form bound to that table. At the top of the form,

have

two unbound combo boxes, the first of which lists all employees, and the

second

which lists all periods. Then, in the afterupdate event of the second

combo

(or perhaps in code tied to a "find the record" button), find the

appropriate

record in the Entry table that has the employee and period you've chosen.

If

there's no existing record, it would go to a blank record, fill in the

employee

and period you've chosen, and you're off. No subforms.

This may neglect something I didn't get in my first reading of your post,

but

maybe it's food for thought.

Good luck.

Jan

>
>I have a relationship set up that is used to track the hours an
>employee has worked on each project during the week. I'm trying to
>set up a Form to allow someone to choose their name and a Period
>Ending date (basically, the tblTimeReport table), which when selected
>will have the subform allow me to enter information into the Entry
>table that pertains to that combination of name and date.
>
>I'm having a nasty time trying to get it all working properly. With
>some setups the subform won't allow me to enter data at all, while in
>others it seems to ignore the name and date I've inputed. I have a
>feeling that TimeReportID being the primary key of the tblTimeReport
>table has something to do with it, but I'm not positive. I just can't
>get the subform and main form to play nice.
>

Jan Stempel
Stempel Consulting