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+ (tblPeriodEndin g)

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]

Re: Subform/Master Form linking question

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
[color=blue]
>
>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
>...
>
>tblPeriodEndin g
>PeriodEnding , Date/Time*
>
>tblTimeRepor t
>TimeReportID , AutoNumber*
>EmployeeID, Number+ (tblEmployee)
>PeriodEnding , Date/Time+ (tblPeriodEndin g)
>
>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[/color]

Jan Stempel
Stempel Consulting

[Mason]

Re: Subform/Master Form linking question

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

janross@aol.com (Janross) wrote in message news:<200409290 82205.17374.000 01204@mb-m27.aol.com>...[color=blue]
> 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
>[color=green]
> >
> >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
> >...
> >
> >tblPeriodEndin g
> >PeriodEnding , Date/Time*
> >
> >tblTimeRepor t
> >TimeReportID , AutoNumber*
> >EmployeeID, Number+ (tblEmployee)
> >PeriodEnding , Date/Time+ (tblPeriodEndin g)
> >
> >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[/color]
>
> Jan Stempel
> Stempel Consulting[/color]

[Geoff]

Re: Subform/Master Form linking question

> How do I[color=blue]
> set up a datasheet-type view of the data while keeping it looking[/color]
like[color=blue]
> 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[/color]
add[color=blue]
> 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[/color]
hate[color=blue]
> 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?[/color]

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]

Re: Subform/Master Form linking question

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
[color=blue]
>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
>
>janross@aol.co m (Janross) wrote in message
>news:<20040929 082205.17374.00 001204@mb-m27.aol.com>...[color=green]
>> Hi, Mason:
>>
>> Consider another approach. If all you're really doing is filling in your[/color]
>entry[color=green]
>> table, then make the form bound to that table. At the top of the form,[/color]
>have[color=green]
>> two unbound combo boxes, the first of which lists all employees, and the[/color]
>second[color=green]
>> which lists all periods. Then, in the afterupdate event of the second[/color]
>combo[color=green]
>> (or perhaps in code tied to a "find the record" button), find the[/color]
>appropriate[color=green]
>> record in the Entry table that has the employee and period you've chosen.[/color]
>If[color=green]
>> there's no existing record, it would go to a blank record, fill in the[/color]
>employee[color=green]
>> 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,[/color]
>but[color=green]
>> maybe it's food for thought.
>>
>> Good luck.
>>
>> Jan
>>[color=darkred]
>> >
>> >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.
>> >[/color][/color][/color]



Jan Stempel
Stempel Consulting