Re: FOrm and COmbo Box...

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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.


"NWO" <NWO@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:FAE2C61C-0976-4A79-AF00-F06781F46A9C@xxxxxxxxxxxxxxxx
> 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 :)
> -----
> "tina" wrote:
>
> > 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
> >
> >
> > "NWO" <NWO@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > news:F3A7791E-207E-4D63-B695-3ABC729CCCE4@xxxxxxxxxxxxxxxx
> > > 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" wrote:
> > >
> > > > 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
> > > >
> > > >
> > > > "NWO" <NWO@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > > > news:AF6DB7D3-2046-40D1-A7AF-BC89E0CC1C39@xxxxxxxxxxxxxxxx
> > > > > 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 :)
> > > > >
> > > > > -----
> > > > >
> > > > > "tina" wrote:
> > > > >
> > > > > > 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
> > > > > >
> > > > > >
> > > > > > "NWO" <NWO@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > > > > > news:FA7FE158-3261-4ADA-8102-5B5751835DA1@xxxxxxxxxxxxxxxx
> > > > > > > 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


.

Relevant Pages

  • Re: LDAP Performance (long)
    ... Cache the slapd's internal database lookups in slapd memory. ... The first is the new TAG:key lookup, ...
    (comp.mail.sendmail)
  • Re: Same database or another?
    ... that I have used the lookup wizard provided. ... if I choose to add tables to database that have absolutely no relation to ... >> work toward the capture of the feral animals. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Database design question
    ... and what he is talking about is a single code ... Pro SQL Server 2000 Database Design ... > dont know the details of the lookup data. ... > What do we then do with the more "unknown" user-defined lookup data. ...
    (microsoft.public.sqlserver.programming)
  • Re: CLOS and databases
    ... Updating any slot ought to add that object to a queue of objects to be ... the queue gets flushed to the database. ... Supporting this for the add/drop of a slot in one class is trivial. ...
    (comp.lang.lisp)
  • Re: Lookup Tables and Field Validation Rule Properties
    ... I am in the process of designing my first database and plan to use this ... employees data and track down employees personal info, ... The tables in my original design contained many lookup fields but after ... The other question I asked was, instead of setting the above validation in the table level, can't I implement it in the Form Level. ...
    (microsoft.public.access.gettingstarted)