Adding new records using subform?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ptlm65 - 20 Dec 2005 17:49 GMT

ok I know I have been pleading for help on this but I almost have i
complete. I just need help finishing it

I have two tables
tblNames
Name
HoursGiven

tblHours
DateWorked
HoursWorked
Name (foreign key linked to tlbNames)

I have a select query that sums HoursWorked and a query that subtract
the sumofHoursWorked from HoursGiven, leaving a new fiel
HoursRemaining

I have a form with a combo box that I can choose a name, whic
backfills three textboxes:
txtName
txtHoursGiven
txtHoursRemaining

I have two textboxes in a subform
txtDateWorked
txtHoursworked

What would the code be that when a new date is entered int
txtDateWorked and new hours entered into txtHoursWorked, the new dat
is added to the DateWorked and HoursWorked fields of tblHours?
Also, how would I link that new data to the chosen name in the mai
form

--
ptlm65Posted from - http://www.officehelp.i

Reply to this Message

John Vinson - 23 Dec 2005 19:34 GMT

>ok I know I have been pleading for help on this but I almost have it
>complete. I just need help finishing it

Well... I'm sorry, but you have some of what I consider pretty major
(and some minor) problems in your table design.

>I have two tables
>tblNames
>Name
>HoursGiven

Name is a reserved word, and as such a bad choice of a fieldname. Even
more so, a person's name is a BAD choice of Primary Key. Primary Keys
should meet three criteria: they should be unique, stable, and (less
importantly) short. Names fail on all three counts: you might have two
people named Jane Smith; or Janet Hopkins might get married and change
her name to Janet Willard. You're MUCH better off having a unique,
stable, short Autonumber PersonID.

>tblHours
>DateWorked
>HoursWorked
>Name (foreign key linked to tlbNames)

Here you'ld use a Long Integer personID as the foreign key.

>I have a select query that sums HoursWorked and a query that subtracts
>the sumofHoursWorked from HoursGiven, leaving a new field
>HoursRemaining

This should simply be calculated as needed. It's not necessary or
appropriate to store this in any table.

>I have a form with a combo box that I can choose a name, which
>backfills three textboxes:
>txtName
>txtHoursGiven
>txtHoursRemaining

What Table is this form based upon? Why "backfill" rather than simply
displaying the data that's already in the table?

>I have two textboxes in a subform
>txtDateWorked
>txtHoursworked

Again... what's the Recordsource of the subform?

>What would the code be that when a new date is entered into
>txtDateWorked and new hours entered into txtHoursWorked, the new data
>is added to the DateWorked and HoursWorked fields of tblHours?
>Also, how would I link that new data to the chosen name in the main
>form?

Simply base the Subform on the Hours table; use the PersonID (or the
Name field, in your current table structure) as the master/child link
field.

I think you're doing TOO MUCH - you don't need (I don't think) to
"backfill" anything. If your mainform is based on tblNames, the
subform on tblHours, with the appropriate master/child link, then you
would simply enter a new record into the subform and it will be
written directly to the table, with no code or fancy tricks needed!

John W. Vinson[MVP]

Reply to this Message