Subform/Form question

[RandyH]

Hi all, Thanks in advance-and sorry for the length of this post!
I made a post on 1/16/04. John Vinson was kind enough to help me,
unfortunately I did a poor job of explaining my problem (sorry John). I'm
building a time
sheet database, using a form and subform (Operations and Operations Sub
Form). The main form shows the EmployeeID, LastName and FirstName (from the
Employees Table). I have an unbound combo box in the form header to make
selection of the employee easier (filtering the form(s), showing only that
employees time sheet data in the subform).
My subform is based off of a query that displays/allows data input of
the following: AccountNumber, AccountDescription, EmployeeID, LaborDate,
LaborHours, BeginingPayPeriod, EndingPayPeriod and PayDate. This as it is
works fine. The problem is that the employees need to record everything they
do during the day (each day their LaborHours entered should total to 8 hrs.
or however long they worked) Data entry is going to be a nightmare! I have
been experimenting with moving the PayPeriod and PayDate fields off of the
subform and add them to the form as a combo box to make data entry faster.
My reasoning for this is that the BeginingPayPeriod, EndingPayPeriod and
PayDate will remain the same for two weeks or so. This theory would also
limit the records visible in the subform to that particular pay period.
I have not been able to get this to work. My database structure is as
follows:
Tables:
Accounts - primary key is Record Number (auto-number field)
Time - primary key is AccountNumber (text field)
Employees - primary key is EmployeeID (number field)
Relationships - one to many from Accounts(AccountNumber) to
Time(AccountNumber)
Queries: - (of relevance)
Operations Query - used on my main form returns; EmployeeID, LastName
and FirstName
from Employees table.
Time Query - used on my subform returns; AccountNumber,
AccountDescription, EmployeeID, LaborDate, LaborHours, BeginingPayPeriod,
EndingPayPeriod and PayDate.

 

[John Vinson]

Hi all, Thanks in advance-and sorry for the length of this post!
I made a post on 1/16/04. John Vinson was kind enough to help me,
unfortunately I did a poor job of explaining my problem (sorry John).

No problem... let's see if a second round helps us both communicate
better!

I'm building a time
sheet database, using a form and subform (Operations and Operations Sub
Form). The main form shows the EmployeeID, LastName and FirstName (from the
Employees Table). I have an unbound combo box in the form header to make
selection of the employee easier (filtering the form(s), showing only that
employees time sheet data in the subform).

You realize that this isn't necessary? A Subform (with EmployeeID as
its Master/Child Link Field) does this filtering already. No combo box
and no code needed. You may want an unbound combo to *locate* a
particular employee's record though.

My subform is based off of a query that displays/allows data input of
the following: AccountNumber, AccountDescription, EmployeeID, LaborDate,
LaborHours, BeginingPayPeriod, EndingPayPeriod and PayDate. This as it is
works fine.

But you already HAVE the EmployeeID; why should it be entered on the
subform at all!? I'd say it shouldn't even be *visible* on the
subform.

The problem is that the employees need to record everything they
do during the day (each day their LaborHours entered should total to 8 hrs.
or however long they worked) Data entry is going to be a nightmare! I have
been experimenting with moving the PayPeriod and PayDate fields off of the
subform and add them to the form as a combo box to make data entry faster.

The Master Link Field and Child Link Field properties can consist of
up to TEN fields. You might want to have unbound textboxes or combos
for payperiod and paydate; if you set the Master Link Field to (say)

[EmployeeID];[cboPayPeriod];[txtPayDate]

using the names of the controls, you can set the Child Link Field to

[EmployeeID];[PayPeriod];[PayDate]

and they'll fill in automatically.

My reasoning for this is that the BeginingPayPeriod, EndingPayPeriod and
PayDate will remain the same for two weeks or so. This theory would also
limit the records visible in the subform to that particular pay period.
I have not been able to get this to work.

The Subform Wizard won't volunteer the information that you can link
on multiple fields, or that the master link field values can be the
names of controls rather than the names of fields - but it does work
nonetheless!

 

[RandyH]

Hi all, Thanks in advance-and sorry for the length of this post!
I made a post on 1/16/04. John Vinson was kind enough to help me,
unfortunately I did a poor job of explaining my problem (sorry John).

No problem... let's see if a second round helps us both communicate
better!

I'm building a time
sheet database, using a form and subform (Operations and Operations Sub
Form). The main form shows the EmployeeID, LastName and FirstName (from the
Employees Table). I have an unbound combo box in the form header to make
selection of the employee easier (filtering the form(s), showing only that
employees time sheet data in the subform).

You realize that this isn't necessary? A Subform (with EmployeeID as
its Master/Child Link Field) does this filtering already. No combo box
and no code needed. You may want an unbound combo to *locate* a
particular employee's record though.

My subform is based off of a query that displays/allows data input of
the following: AccountNumber, AccountDescription, EmployeeID, LaborDate,
LaborHours, BeginingPayPeriod, EndingPayPeriod and PayDate. This as it is
works fine.

But you already HAVE the EmployeeID; why should it be entered on the
subform at all!? I'd say it shouldn't even be *visible* on the
subform.

The problem is that the employees need to record everything they
do during the day (each day their LaborHours entered should total to 8 hrs.
or however long they worked) Data entry is going to be a nightmare! I have
been experimenting with moving the PayPeriod and PayDate fields off of the
subform and add them to the form as a combo box to make data entry

faster.

The Master Link Field and Child Link Field properties can consist of
up to TEN fields. You might want to have unbound textboxes or combos
for payperiod and paydate; if you set the Master Link Field to (say)

[EmployeeID];[cboPayPeriod];[txtPayDate]

using the names of the controls, you can set the Child Link Field to

[EmployeeID];[PayPeriod];[PayDate]

and they'll fill in automatically.

My reasoning for this is that the BeginingPayPeriod, EndingPayPeriod and
PayDate will remain the same for two weeks or so. This theory would also
limit the records visible in the subform to that particular pay period.
I have not been able to get this to work.

The Subform Wizard won't volunteer the information that you can link
on multiple fields, or that the master link field values can be the
names of controls rather than the names of fields - but it does work
nonetheless!

OK, cool; getting excited now! Things are appearing to work with one
exception. As I enter the date for the ending pay period (text box on main
form, PayPeriodEnd) my employee id combo box (cboEmployeeID) changes the id
to the current record number from the Time table (primary key from table
'Time', data type is AutoNumber). Does anything here give you a clue as to
why this might happen?

 

[John Vinson]

OK, cool; getting excited now! Things are appearing to work with one
exception. As I enter the date for the ending pay period (text box on main
form, PayPeriodEnd) my employee id combo box (cboEmployeeID) changes the id
to the current record number from the Time table (primary key from table
'Time', data type is AutoNumber). Does anything here give you a clue as to
why this might happen?

Not a trace of a clue. Do you have any VBA code on the form or on this
textbox?

 

[RandyH]

Not a trace of a clue. Do you have any VBA code on the form or on this
textbox?

No trace of a clue - Ouch! That makes two of us. I have not added any VBA
code to either the form or subform. My queries may be somewhat unorthodox; I
have added the PayPeriod and PayDate fields to my main forms query-should I
have? Also, I question my table relationships (only because I haven't used
two one-to-many relationships), could either of these be the culprit? I will
back up what I have and keep experimenting...

 

[John Vinson]

No trace of a clue - Ouch! That makes two of us. I have not added any VBA
code to either the form or subform. My queries may be somewhat unorthodox; I
have added the PayPeriod and PayDate fields to my main forms query-should I
have? Also, I question my table relationships (only because I haven't used
two one-to-many relationships), could either of these be the culprit? I will
back up what I have and keep experimenting...

Please post the SQL view of the Recordsources of both the mainform and
subform, and the Master/Child Link Field properties of the subform.
We'll get this figured out!

 

[RandyH]

Please post the SQL view of the Recordsources of both the mainform and
subform, and the Master/Child Link Field properties of the subform.
We'll get this figured out!

Thanks John for all of your help! I made the PayPeriod and PayDate text
fields unbound and removed the fields from my main forms query (although I
think the unbound fields were the real fix). I really appreciate your help,
and only wish I was proficient enough to help others!