entering data from form fields into two tables

  • Thread starter Thread starter aptech
  • Start date Start date
A

aptech

i have some form fields that are in two tables and i want
to be able to get that data into both tables so that both
of them will reflect the current and latest data.
 
i have some form fields that are in two tables and i want
to be able to get that data into both tables so that both
of them will reflect the current and latest data.
Click to expand...

Ok, sounds reasonable. What problems are you having doing so?
 
well, i am only able to send the data to one table when
the user clicks on add button, i am trying to put the code
into the button_click event so that it also sends the data
from two textboxes to another table and updates those two
fields in that table.
Click to expand...

If you're trying to send data to two tables, storing it redundantly,
DON'T.

If you have two tables in a one to many relationship, consider basing
your mainform on the "one" table and a Subform on the "many".

If you really need to just write data to a second table (possible but
rather unusual), you can open a Recordset based on that table
(selecting which record you want to update - I have no idea how you
would determine that) and use the AddNew method to add a new record,
or the Update method to update an existing record.

If you would describe what you are trying to ACCOMPLISH, rather than
the details of how you want to accomplish it, someone may be able to
suggest a simple solution.
 
ok here is what i have at hand.
i have a master table and a activity(child table) table.
when a user updates the status and details fields in the
child table, i want to change only those two fields in the
master table also.

there is a one to many relationship as you said already.

and with adding it with recordset, i don't know how i will
determine which record i want to update. and as i only
want to update these two fields, i am so far unable to
find a solution.

the whole issue is to take the most current status as the
user enters it into the child table and insert it into the
master table into the relevant records.
 
well,
my boss is bent on doing it that way. i was unable to
convince him for one table only. he envisions two tables,
with one as master and the other as transaction table with
details. just like a bank system where master file has
account holder details and balance but everyday trans are
recorded in another table and only the balance is updated
in the master as the tran occurs.

actually we have to keep track of everyday status, so he
wants to preserve them in a different table, but when the
user enters the record in the child table, he wants the
status in master table fields to be replaced by whatever
the user entered.


one record could therefore have many status records in the
activity table, but will show last entered status(or most
recent) in the master table. these records(child table)
are to be kept as a proof of everyday status of the
problem.

master record will have only one status and that be
updated the moment user enters new status for that primary
key in child table.
 
well,
my boss is bent on doing it that way. i was unable to
convince him for one table only. he envisions two tables,
with one as master and the other as transaction table with
details.
Click to expand...

The "boss" should NEVER see a table datasheet. Ask him: do you want
this done using the principles of relational database design? or do
you want it done WRONG, in a way that requires extra programming,
ensures that there is a risk of having invalid Status and Details
displayed? Or do you want it done so that you see the current status
and the current details on the screen, with confidence that they are
in fact current?
just like a bank system where master file has
account holder details and balance but everyday trans are
recorded in another table and only the balance is updated
in the master as the tran occurs.
Click to expand...

Ummm... talk to your bank's IT folks. The balance is almost certainly
calculated on the fly, NOT stored.

But... <sigh>... if you REALLY REALLY have to do it this way put code
in the Subform's AfterInsert event:

Private Sub Form_AfterInsert()
Parent!txtStatus = Me!txtStatus
Parent!txtDetail = Me!txtDetail
End Sub
 
Back
Top