Subform/Form question

  • Thread starter Thread starter RandyH
  • Start date Start date
R

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.
 
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).
Click to expand...

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).
Click to expand...

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.
Click to expand...

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.
Click to expand...

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.
Click to expand...

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!
 
John Vinson said:
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).
Click to expand...

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).
Click to expand...

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.
Click to expand...

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
Click to expand...
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.
Click to expand...

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!
Click to expand...

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?
 
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?
Click to expand...


Not a trace of a clue. Do you have any VBA code on the form or on this
textbox?
 
John Vinson said:
Not a trace of a clue. Do you have any VBA code on the form or on this
textbox?
Click to expand...

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...
 
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...
Click to expand...

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!
 
John Vinson said:
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!
Click to expand...

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!
 
Back
Top