FOrm and COmbo Box...

[Guest]

I have a basic Combo Box/Employees form deal (with subforms, but the sub
forms work OK). When I select a user from my drop down Combo Box, the
correspdoniong Employees fields populate properly. Two issues bother me,
though. When I scroll down using my mouse, the fields change, but the value
in the Combo Box remains constant. Second, how do you open the form so that
both the Combo Box and the Employee fields are initially empty? I tried
doing some things with Me. Requery, but that didn't work. Any suggestions
for fixes for the above would be appreciated.

NWO

 

[tina]

i assume that the combo box on the form is unbound, and you're using it as a
"Find" or "Search" box, basically (from the user's point of view). if so,
then there's code in the combo box's AfterUpdate event procedure, to find a
specific record. personally, i usually like my "Find" boxes to be blank even
after a record is selected, so i add a line of code below the last line in
the AfterUpdate event procedure (but *above* the End Sub line), as

Me!ComboBoxName = Null

but if you want the combo box to reflect the current record in the form, you
can set code on the form's Current event, as

Me!ComboBoxName = Me!PrimaryKeyField

as for the "blank controls when the form opens" issue: i can think of only
two ways to do that. 1) run the following code in the form's OnLoad event
procedure, as

DoCmd.RunCommand acCmdRecordsGoToNew

so the form opens to a new (and therefore "blank") record, or 2) base the
form on a query and set criteria in the query's primary key field to

[Forms]![FormName]![ComboBoxName]

and make sure that the combo box is in the form's Header or Footer section,
not the Detail section. then run the following code in the combo box's
AfterUpdate event, as

Me.Requery

so the form will open with *no* records in it, and each time you select a
value from the combo box's droplist, the form's underlying query will be
requeried and will return *only one* record - the one that matches the
selection in the combo box.

hth

 

[Guest]

Thank you very much Tina.

You explain things in a very clear and easy to understadn manner.

Can you tackle this one:

I have a database that collects overtime data on a pay period basis. The
relationship is as folows:

Employee table (PK = Employee ID (auto number))
Pay Period table (PK = PPID (autonumber), with EmployeeID as a FK)
Overtime table (PK = OvertimeID (auto number), with PPID as the FK)

I set up a basic Form/SUb form screen to collect the data. User selects an
Employee record via a combo box, and if a Pay Period already exists for that
employee, the Pay Period number appears in the first sub form along with the
associated Overtime detail records in the second subform. User can either
add overtime details records records to the existing Pay Period or add a new
Pay Period via a drop down in the Pay Period Subform, and then proceed with
entering new OT detail records for the selcted Pay Period. This seems to
work OK, because it is the only way that I could figure out how to get the
data in the most efficient manner fomr the user. I just don't really like
the idea of the user having to create a new Pay Period record for every
employee who works overtime, although once the Pay Period record is created,
the user can then just add addtional ocvetime detail records. What I really
want to do is create a scheme as follows:

Note: SUpervisor enters all of his/her subordinate records.

Supervisors first selectes a PAy Period form a drop down list. Supervisor
then selects a Unit name form another drop down list and all of the employees
assinged to that unit appear in a subform. Supervisor then selects an
employee and either existing Overtime record(s) record appers for the Pay
Period selected or the supervisor has the ability to add new Overtime detail
records for the selected pay period. In other words, since this whole deal
is Pay Period driven, I only wish for the supervisor to enter the Pay Period
in once, and then enter OT detail records for a given employee assinged to a
given unit for that pay period without having to repeatedly having to enter
in the Pay Period. Any ideas on a desing approach to the above desired
outcome woudl be appreciated.

Again, thank you for the reply Tina.

NWO
--------------------------

i assume that the combo box on the form is unbound, and you're using it as a
"Find" or "Search" box, basically (from the user's point of view). if so,
then there's code in the combo box's AfterUpdate event procedure, to find a
specific record. personally, i usually like my "Find" boxes to be blank even
after a record is selected, so i add a line of code below the last line in
the AfterUpdate event procedure (but *above* the End Sub line), as

Me!ComboBoxName = Null

but if you want the combo box to reflect the current record in the form, you
can set code on the form's Current event, as

Me!ComboBoxName = Me!PrimaryKeyField

as for the "blank controls when the form opens" issue: i can think of only
two ways to do that. 1) run the following code in the form's OnLoad event
procedure, as

DoCmd.RunCommand acCmdRecordsGoToNew

so the form opens to a new (and therefore "blank") record, or 2) base the
form on a query and set criteria in the query's primary key field to

[Forms]![FormName]![ComboBoxName]

and make sure that the combo box is in the form's Header or Footer section,
not the Detail section. then run the following code in the combo box's
AfterUpdate event, as

Me.Requery

so the form will open with *no* records in it, and each time you select a
value from the combo box's droplist, the form's underlying query will be
requeried and will return *only one* record - the one that matches the
selection in the combo box.

hth

I have a basic Combo Box/Employees form deal (with subforms, but the sub
forms work OK). When I select a user from my drop down Combo Box, the
correspdoniong Employees fields populate properly. Two issues bother me,
though. When I scroll down using my mouse, the fields change, but the value
in the Combo Box remains constant. Second, how do you open the form so that
both the Combo Box and the Employee fields are initially empty? I tried
doing some things with Me. Requery, but that didn't work. Any suggestions
for fixes for the above would be appreciated.

NWO

 

[tina]

you're welcome

re your "new" question: from a data entry standpoint, you could probably
come up with a creative "outside the box" form setup to facilitate the data
entry the way you describe. in fact, even as i write this, a few ideas are
swimming around in my head.

but, i think i'd take a look at your table structure first.

I just don't really like
the idea of the user having to create a new Pay Period record for every
employee who works overtime, although once the Pay Period record is created,
the user can then just add addtional ocvetime detail records.

is the purpose of the PayPeriod table simply to "group" overtime records? if
so, that's not necessarily bad - i just need to understand the tables
structure better. can you post the fields in in the PayPeriod and Overtime
tables, please, as

PayPeriod
PPID (pk)
EmployeeID (fk)
NextFieldName
NextFieldName

and explain what each field is for, unless the fieldname makes it obvious.

hth

Thank you very much Tina.

You explain things in a very clear and easy to understadn manner.

Can you tackle this one:

I have a database that collects overtime data on a pay period basis. The
relationship is as folows:

Employee table (PK = Employee ID (auto number))
Pay Period table (PK = PPID (autonumber), with EmployeeID as a FK)
Overtime table (PK = OvertimeID (auto number), with PPID as the FK)

I set up a basic Form/SUb form screen to collect the data. User selects an
Employee record via a combo box, and if a Pay Period already exists for that
employee, the Pay Period number appears in the first sub form along with the
associated Overtime detail records in the second subform. User can either
add overtime details records records to the existing Pay Period or add a new
Pay Period via a drop down in the Pay Period Subform, and then proceed with
entering new OT detail records for the selcted Pay Period. This seems to
work OK, because it is the only way that I could figure out how to get the
data in the most efficient manner fomr the user. I just don't really like
the idea of the user having to create a new Pay Period record for every
employee who works overtime, although once the Pay Period record is created,
the user can then just add addtional ocvetime detail records. What I really
want to do is create a scheme as follows:

Note: SUpervisor enters all of his/her subordinate records.

Supervisors first selectes a PAy Period form a drop down list. Supervisor
then selects a Unit name form another drop down list and all of the employees
assinged to that unit appear in a subform. Supervisor then selects an
employee and either existing Overtime record(s) record appers for the Pay
Period selected or the supervisor has the ability to add new Overtime detail
records for the selected pay period. In other words, since this whole deal
is Pay Period driven, I only wish for the supervisor to enter the Pay Period
in once, and then enter OT detail records for a given employee assinged to a
given unit for that pay period without having to repeatedly having to enter
in the Pay Period. Any ideas on a desing approach to the above desired
outcome woudl be appreciated.

Again, thank you for the reply Tina.

NWO
--------------------------

i assume that the combo box on the form is unbound, and you're using it as a
"Find" or "Search" box, basically (from the user's point of view). if so,
then there's code in the combo box's AfterUpdate event procedure, to find a
specific record. personally, i usually like my "Find" boxes to be blank even
after a record is selected, so i add a line of code below the last line in
the AfterUpdate event procedure (but *above* the End Sub line), as

Me!ComboBoxName = Null

but if you want the combo box to reflect the current record in the form, you
can set code on the form's Current event, as

Me!ComboBoxName = Me!PrimaryKeyField

as for the "blank controls when the form opens" issue: i can think of only
two ways to do that. 1) run the following code in the form's OnLoad event
procedure, as

DoCmd.RunCommand acCmdRecordsGoToNew

so the form opens to a new (and therefore "blank") record, or 2) base the
form on a query and set criteria in the query's primary key field to

[Forms]![FormName]![ComboBoxName]

and make sure that the combo box is in the form's Header or Footer section,
not the Detail section. then run the following code in the combo box's
AfterUpdate event, as

Me.Requery

so the form will open with *no* records in it, and each time you select a
value from the combo box's droplist, the form's underlying query will be
requeried and will return *only one* record - the one that matches the
selection in the combo box.

hth

I have a basic Combo Box/Employees form deal (with subforms, but the sub
forms work OK). When I select a user from my drop down Combo Box, the
correspdoniong Employees fields populate properly. Two issues bother me,
though. When I scroll down using my mouse, the fields change, but the value
in the Combo Box remains constant. Second, how do you open the form

so
that

both the Combo Box and the Employee fields are initially empty? I tried
doing some things with Me. Requery, but that didn't work. Any suggestions
for fixes for the above would be appreciated.

NWO

 

[Guest]

Hello Tina (again...

Yes, the sole purpose for the current Pay Period scheme is to group the
Overtime detail records and tie to a specific Pay Period. With this being
the case, do you think it is not overburdonsome for the user to just click on
the appropaite Pay Period and then continue on with the data entry for each
user (there is a max of twenty user per supervisor and about 2 to 3 records
per employee, per pay period)? Because if this is a the case., then I'm
already set. At any rate, here are the tables with fields:

tblEmployee
EmployeeID (PK)
Name
Grade
PositionTitle
AssingedUnit

tblPayPeriod
PayPeriodID (PK)
EmployeeID (FK)
PayPeriod (i.e. 21-1,22-2,23-1...)

tblOvertimeDetailRecords
OvertimeID (PK)
PayPeriodID (FK)
G22LineNUmber (Drop down selection list)
NumApprovals
NumDenials
NumHours

Note that there is no direct realtionship between the tblEmployee and
tblOvertime. For each Pay Period record in the tblPayPeriod table, these is
a correpsnding variable number of OT detail records for that Pay Period and
the associated employee based on ther EmployeeID FK in the Pay Period table.
Relationship is a One to many from EmployeeID to Pay PeriodID, and a one to
many from PayPeriodID to OvertimeID.

Your input is always appreciated. I'm not the coding type, but am quickly
learning. I am more concerned with the design issue at this point, but if
you know of some code that will work, please advise. My guess, though, is
that if there is a problem with the above approach, the issue is design
related.

Thank you again Tina.

NWO

-----

you're welcome

re your "new" question: from a data entry standpoint, you could probably
come up with a creative "outside the box" form setup to facilitate the data
entry the way you describe. in fact, even as i write this, a few ideas are
swimming around in my head.

but, i think i'd take a look at your table structure first.

I just don't really like
the idea of the user having to create a new Pay Period record for every
employee who works overtime, although once the Pay Period record is created,
the user can then just add addtional ocvetime detail records.

is the purpose of the PayPeriod table simply to "group" overtime records? if
so, that's not necessarily bad - i just need to understand the tables
structure better. can you post the fields in in the PayPeriod and Overtime
tables, please, as

PayPeriod
PPID (pk)
EmployeeID (fk)
NextFieldName
NextFieldName

and explain what each field is for, unless the fieldname makes it obvious.

hth

Thank you very much Tina.

You explain things in a very clear and easy to understadn manner.

Can you tackle this one:

I have a database that collects overtime data on a pay period basis. The
relationship is as folows:

Employee table (PK = Employee ID (auto number))
Pay Period table (PK = PPID (autonumber), with EmployeeID as a FK)
Overtime table (PK = OvertimeID (auto number), with PPID as the FK)

I set up a basic Form/SUb form screen to collect the data. User selects an
Employee record via a combo box, and if a Pay Period already exists for that
employee, the Pay Period number appears in the first sub form along with the
associated Overtime detail records in the second subform. User can either
add overtime details records records to the existing Pay Period or add a new
Pay Period via a drop down in the Pay Period Subform, and then proceed with
entering new OT detail records for the selcted Pay Period. This seems to
work OK, because it is the only way that I could figure out how to get the
data in the most efficient manner fomr the user. I just don't really like
the idea of the user having to create a new Pay Period record for every
employee who works overtime, although once the Pay Period record is created,
the user can then just add addtional ocvetime detail records. What I really
want to do is create a scheme as follows:

Note: SUpervisor enters all of his/her subordinate records.

Supervisors first selectes a PAy Period form a drop down list. Supervisor
then selects a Unit name form another drop down list and all of the employees
assinged to that unit appear in a subform. Supervisor then selects an
employee and either existing Overtime record(s) record appers for the Pay
Period selected or the supervisor has the ability to add new Overtime detail
records for the selected pay period. In other words, since this whole deal
is Pay Period driven, I only wish for the supervisor to enter the Pay Period
in once, and then enter OT detail records for a given employee assinged to a
given unit for that pay period without having to repeatedly having to enter
in the Pay Period. Any ideas on a desing approach to the above desired
outcome woudl be appreciated.

Again, thank you for the reply Tina.

NWO
--------------------------

i assume that the combo box on the form is unbound, and you're using it as a
"Find" or "Search" box, basically (from the user's point of view). if so,
then there's code in the combo box's AfterUpdate event procedure, to find a
specific record. personally, i usually like my "Find" boxes to be blank even
after a record is selected, so i add a line of code below the last line in
the AfterUpdate event procedure (but *above* the End Sub line), as

Me!ComboBoxName = Null

but if you want the combo box to reflect the current record in the form, you
can set code on the form's Current event, as

Me!ComboBoxName = Me!PrimaryKeyField

as for the "blank controls when the form opens" issue: i can think of only
two ways to do that. 1) run the following code in the form's OnLoad event
procedure, as

DoCmd.RunCommand acCmdRecordsGoToNew

so the form opens to a new (and therefore "blank") record, or 2) base the
form on a query and set criteria in the query's primary key field to

[Forms]![FormName]![ComboBoxName]

and make sure that the combo box is in the form's Header or Footer section,
not the Detail section. then run the following code in the combo box's
AfterUpdate event, as

Me.Requery

so the form will open with *no* records in it, and each time you select a
value from the combo box's droplist, the form's underlying query will be
requeried and will return *only one* record - the one that matches the
selection in the combo box.

hth


I have a basic Combo Box/Employees form deal (with subforms, but the sub
forms work OK). When I select a user from my drop down Combo Box, the
correspdoniong Employees fields populate properly. Two issues bother me,
though. When I scroll down using my mouse, the fields change, but the
value
in the Combo Box remains constant. Second, how do you open the form so
that
both the Combo Box and the Employee fields are initially empty? I tried
doing some things with Me. Requery, but that didn't work. Any suggestions
for fixes for the above would be appreciated.

NWO

 

[tina]

well, i believe i would simplify the table structure (and hence the data
entry issues), as

no change to tblEmployees

tblPayPeriods
PayPeriodID
PayPeriod
note: this changes the table to be simply a list of all pay periods (you
could add the coming years pay periods at the beginning of each year, for
instance, so they're available all year long for data entry purposes). the
table is *not* linked to tblEmployees.
*also note*: you should NOT give a field in a table the *exact* same name
as the table's name. recommend you change one or the other, for example by
making the table name plural, as in the example above.

tblEmployeeOvertime
OvertimeID (pk)
EmployeeID (fk)
PayPeriodID (fk)
G22LineNUmber
NumApprovals
NumDenials
NumHours

note: if you have any Lookup fields in any of your tables, recommend you
get rid of them. see http://www.mvps.org/access/lookupfields.htm for reasons
why.

okay, now to address the data entry scenario you previously described as
what you "really want to do":

Supervisors first selectes a PAy Period form a drop down list. Supervisor
then selects a Unit name form another drop down list and all of the employees
assinged to that unit appear in a subform. Supervisor then selects an
employee and either existing Overtime record(s) record appers for the Pay
Period selected or the supervisor has the ability to add new Overtime detail
records for the selected pay period.

suggest the following:

create a form, bound to tblEmployees, i'll call it sfrmEmployees, and set
the form's DefaultView property to SingleForm.

create another form, bound to tblEmployeeOvertime, i'll call it
sfrmOvertime, and set the form's DefaultView to ContinuousForms or
Datasheet.

open sfrmEmployees and add a subform control, i'll call it ChildOvertime.
set its' properties as follows

SourceObject: sfrmOvertime
LinkChildFields: EmployeeID
LinkMasterFields: EmployeeID

when you move to each record in sfrmEmployees, the related records in
sfrmEmployeeOvertime will display in the subform ChildOvertime.

now create an unbound form, to serve as the "main" form. add two unbound
combo boxes, as

cboUnit (with RowSource based on a tblUnits - which would be a list of all
units that you're tracking employees for.)
cboPayPeriod (with RowSource based on tblPayPeriods.)

add a subform control, i'll call it ChildEmployees. set its' properties as
follows

SourceObject: sfrmEmployees
LinkChildFields: AssignedUnit
LinkMasterFields: cboUnit

now each time you select a unit from cboUnit in the main form, the subform
ChildEmployees will display that unit's employees.

go back to sfrmOvertime in the database window, and open it in Design view.
add the following code to the form's BeforeUpdate event procedure, as

Me!PayPeriodID = Me.Parent.Parent!cboPayPeriod

here's how it all works: the supervisor opens the main form, selects a pay
period from cboPayPeriod, and selects a unit from cboUnit. the subform
populates with all the employee records assigned to that unit. the
supervisor moves through the subform records to an employee he wants to add
overtime for, then he moves into the "sub" subform, and enters the
G22LineNumber and the NumHours, etc.
he does NOT need to add the OvertimeID because that should be generated
automatically as the primary key field.
he does NOT need to add the EmployeeID because that should be automatically
added via the link between the Employees subform and the Overtime "sub"
subform.
he does NOT need to add the PayPeriodID, because that will be automatically
added by the code in the "sub" subform's BeforeUpdate event.

whew, that's a loooong explanation. but if you set it up step by step, i
think you'll find it easy and pretty straightforward. and it provides the
quick, efficient data entry you wanted for your supervisor users.

hth

Hello Tina (again...

Yes, the sole purpose for the current Pay Period scheme is to group the
Overtime detail records and tie to a specific Pay Period. With this being
the case, do you think it is not overburdonsome for the user to just click on
the appropaite Pay Period and then continue on with the data entry for each
user (there is a max of twenty user per supervisor and about 2 to 3 records
per employee, per pay period)? Because if this is a the case., then I'm
already set. At any rate, here are the tables with fields:

tblEmployee
EmployeeID (PK)
Name
Grade
PositionTitle
AssingedUnit

tblPayPeriod
PayPeriodID (PK)
EmployeeID (FK)
PayPeriod (i.e. 21-1,22-2,23-1...)

tblOvertimeDetailRecords
OvertimeID (PK)
PayPeriodID (FK)
G22LineNUmber (Drop down selection list)
NumApprovals
NumDenials
NumHours

Note that there is no direct realtionship between the tblEmployee and
tblOvertime. For each Pay Period record in the tblPayPeriod table, these is
a correpsnding variable number of OT detail records for that Pay Period and
the associated employee based on ther EmployeeID FK in the Pay Period table.
Relationship is a One to many from EmployeeID to Pay PeriodID, and a one to
many from PayPeriodID to OvertimeID.

Your input is always appreciated. I'm not the coding type, but am quickly
learning. I am more concerned with the design issue at this point, but if
you know of some code that will work, please advise. My guess, though, is
that if there is a problem with the above approach, the issue is design
related.

Thank you again Tina.

NWO

-----

you're welcome

re your "new" question: from a data entry standpoint, you could probably
come up with a creative "outside the box" form setup to facilitate the data
entry the way you describe. in fact, even as i write this, a few ideas are
swimming around in my head.

but, i think i'd take a look at your table structure first.

I just don't really like
the idea of the user having to create a new Pay Period record for every
employee who works overtime, although once the Pay Period record is created,
the user can then just add addtional ocvetime detail records.

is the purpose of the PayPeriod table simply to "group" overtime records? if
so, that's not necessarily bad - i just need to understand the tables
structure better. can you post the fields in in the PayPeriod and Overtime
tables, please, as

PayPeriod
PPID (pk)
EmployeeID (fk)
NextFieldName
NextFieldName

and explain what each field is for, unless the fieldname makes it obvious.

hth

Thank you very much Tina.

You explain things in a very clear and easy to understadn manner.

Can you tackle this one:

I have a database that collects overtime data on a pay period basis. The
relationship is as folows:

Employee table (PK = Employee ID (auto number))
Pay Period table (PK = PPID (autonumber), with EmployeeID as a FK)
Overtime table (PK = OvertimeID (auto number), with PPID as the FK)

I set up a basic Form/SUb form screen to collect the data. User

selects
an

Employee record via a combo box, and if a Pay Period already exists

for
that

employee, the Pay Period number appears in the first sub form along

with
the

associated Overtime detail records in the second subform. User can either
add overtime details records records to the existing Pay Period or add

a
new

Pay Period via a drop down in the Pay Period Subform, and then proceed with
entering new OT detail records for the selcted Pay Period. This seems to
work OK, because it is the only way that I could figure out how to get the
data in the most efficient manner fomr the user. I just don't really like
the idea of the user having to create a new Pay Period record for every
employee who works overtime, although once the Pay Period record is created,
the user can then just add addtional ocvetime detail records. What I really
want to do is create a scheme as follows:

Note: SUpervisor enters all of his/her subordinate records.

Supervisors first selectes a PAy Period form a drop down list. Supervisor
then selects a Unit name form another drop down list and all of the employees
assinged to that unit appear in a subform. Supervisor then selects an
employee and either existing Overtime record(s) record appers for the Pay
Period selected or the supervisor has the ability to add new Overtime detail
records for the selected pay period. In other words, since this whole deal
is Pay Period driven, I only wish for the supervisor to enter the Pay Period
in once, and then enter OT detail records for a given employee

assinged to
a

given unit for that pay period without having to repeatedly having to enter
in the Pay Period. Any ideas on a desing approach to the above desired
outcome woudl be appreciated.

Again, thank you for the reply Tina.

NWO

it
as a

"Find" or "Search" box, basically (from the user's point of view).

if
so,

then there's code in the combo box's AfterUpdate event procedure, to find a
specific record. personally, i usually like my "Find" boxes to be

blank
even

after a record is selected, so i add a line of code below the last

line
in

the AfterUpdate event procedure (but *above* the End Sub line), as

Me!ComboBoxName = Null

but if you want the combo box to reflect the current record in the

form,
you

can set code on the form's Current event, as

Me!ComboBoxName = Me!PrimaryKeyField

as for the "blank controls when the form opens" issue: i can think

of
only

two ways to do that. 1) run the following code in the form's OnLoad event
procedure, as

DoCmd.RunCommand acCmdRecordsGoToNew

so the form opens to a new (and therefore "blank") record, or 2)

base
the

form on a query and set criteria in the query's primary key field to

[Forms]![FormName]![ComboBoxName]

and make sure that the combo box is in the form's Header or Footer section,
not the Detail section. then run the following code in the combo box's
AfterUpdate event, as

Me.Requery

so the form will open with *no* records in it, and each time you

select
a

value from the combo box's droplist, the form's underlying query will be
requeried and will return *only one* record - the one that matches the
selection in the combo box.

hth


I have a basic Combo Box/Employees form deal (with subforms, but

the
sub

forms work OK). When I select a user from my drop down Combo Box, the
correspdoniong Employees fields populate properly. Two issues

bother
me,

though. When I scroll down using my mouse, the fields change, but the
value
in the Combo Box remains constant. Second, how do you open the

form
so

that
both the Combo Box and the Employee fields are initially empty? I tried
doing some things with Me. Requery, but that didn't work. Any suggestions
for fixes for the above would be appreciated.

NWO

 

[Guest]

Darn girl, you're good! Let me have lunch and drink a cup (or two) of
coffee before I read your repsonse again so that I fully understand it and
try to implement your suggestions. I'll keep you informed of my progress and
outcome, that's if its OK with you.

Thank you again Tina.

My real name is Mark from Plano, TX
----------------------------------------

 

[Guest]

Tina, regarding the lookup table business, would you then recommend that the
values just be entered in the field even if there are several duplicate
values, or just do a better job at normalizing the table structure so that
these repeating fileds are located in another table (I suspect you're going
to go with the latter, which means I'll have to make significant chmnages to
my tables because most of the fileds are indeed based on lookup tables, but
that's OK because I want to do the right thing at the desing stage and not
pay for poor desing latter). Your coments are always welcome.

Mark
-------------

 

[tina]

Mark, my warning was about Lookup *fields* in tables, not about "lookup"
tables (i call them "supporting" tables, so as to not confuse them with
Lookup fields). databases often have numerous supporting tables. they're
typically "parent" tables, with a one-to-many link to one or more data
tables, that simply provide a list of valid values for use in the linked
data table(s) . usually, the values in a supporting table are not added
to/edited/deleted very often. and typically the kind of table that i call a
supporting table has only two fields, a Number field that serves as a
primary key, and a value field for whatever values are being listed. lookup
tables are useful in three ways: 1) they ensure that valid, correctly
spelled values are available to the user, in forms; 2) they allow for more
efficient data entry in forms - generally picking from a pre-defined combo
box droplist is easier and faster than typing a value manually and checking
that it's spelled correctly; 3) they provide standardized values, that are
meaningful to the company, which can be used to "slice 'n dice" the data for
statistical analysis.

the tblPayPeriods, that i suggested in my previous post, is an example of a
supporting table. some others i've used are: tblTitles (Mr., Mrs., Ms,
Rev., Dr., etc); tblPhoneTypes (Home, Office, Business, Cell, Fax, Pager,
etc); tblCategories (any list of categories that compartmentalize or "label"
data in a way that is meaningful to the company using the database);
tblStates (a list of the U.S. states with a field for the two-character USPS
abbreviation, and a field for the full name of the state/territory).

hth

 

[tina]

well, you're welcome
but maybe you'd better hold the praise until you've actually implemented the
solution <g>
yes, i would like to know if the solution works for you, so i'd be happy to
see you post back with the outcome. and of course i'll be watching this
thread for awhile anyway, in case you have a question or problem in setting
up the solution.

 

[Guest]

Thank you Tina.

I followed your example in your previous post and got things working all the
way to the Main form. The main form displays the Unit and Pay Period combo
boxes, and they have proper values in the drop down list, but nothing happens
when the selections are made. Also, when I open the form, I get a parameter
box asking for a Unit, and then a Pay Period, then the form appears,
selections are made via the combo boxes, but nothing appears in the sub form.
Any suggestions. I'm pretty sure that I folowed you procedures very
carefully. I also re-did the relationships relating the tblEmployeeOveetime
to tblEmployee with EmployeeID and relating the tblPayPeriod PayPeriodID to
tblEmployeeOveretime PayPeriodID.

Mark
-----

 

[Guest]

Almost there...

As it turns out, I made a couple of typos and the forms now work as designed
(thank you). There is still a problem, however. When you enter a record for
a particular pay period, exit the form, reopen the form, and select the same
unit and a different pay period, the Overtime detail record still shows the
data that was entered under a different pay period for the same employee. It
was my intent to only show records for a given pay period as entered - any
ideas how to fix this?

Thanks again Tina.

Mark
------------------------

 

[tina]

this is turning into a really long thread, Mark. rather than go thru another
extended Q&A, i'm willing to take a look at your database and troubleshoot
the solution, or create a "demo" db and send it to you, so you can see an
example of the setup. if you decide to pursue either option, post back with
an email address, disguised to foil the spammers, and tell me which option
you choose.

Thank you Tina.

I followed your example in your previous post and got things working all the
way to the Main form. The main form displays the Unit and Pay Period combo
boxes, and they have proper values in the drop down list, but nothing happens
when the selections are made. Also, when I open the form, I get a parameter
box asking for a Unit, and then a Pay Period, then the form appears,
selections are made via the combo boxes, but nothing appears in the sub form.
Any suggestions. I'm pretty sure that I folowed you procedures very
carefully. I also re-did the relationships relating the tblEmployeeOveetime
to tblEmployee with EmployeeID and relating the tblPayPeriod PayPeriodID to
tblEmployeeOveretime PayPeriodID.

Mark

the

 

[Guest]

Thank you Tina.

I think a Demo would be nice address is at end of this thread.

Thank you.

Mark

this is turning into a really long thread, Mark. rather than go thru another
extended Q&A, i'm willing to take a look at your database and troubleshoot
the solution, or create a "demo" db and send it to you, so you can see an
example of the setup. if you decide to pursue either option, post back with
an email address, disguised to foil the spammers, and tell me which option
you choose.

(e-mail address removed)

 

[tina]

i'll put together a small demo with the tables/fields we've been discussing,
Mark, and email it to you probably on Sat 11/19. (btw, i hope you don't get
spammed to death; it's generally not a good idea to post a "real" email
address in these newsgroups unless you disguise it to foil the spammers.)

 

[tina]

well, that's a little more involved. i'll see if i can write a solution into
the demo db, and let you know if it's included, when i send the demo db to
you.

 

[Guest]

Thank you Tina. I'm working today and I look forward to your e-mail.

How do you disguise an e-mail address?

Mark

 

[tina]

demo db on its' way.
basically, you break it up so that automated data miners (i think they're
called "spiders") dont' recognize it as an email address. example:

cRybEMOerVxE1aAtcLLComAcaPsItTdoAtnLetS

delete all the the capital letters and change the at and dot to symbols.

hth