Advice needed for Updating subform datasheet

  • Thread starter Thread starter p
  • Start date Start date
P

p

I have a subform datasheet with many rows.. When a user enters a new row,
column one of six, I need to query another database to automatically
populate the remaining subform datasheet columns. I have the select from
the other database working but am looking for best method/location to update
the other columns in datasheet?

I tried adding code to the subform textbox "After Update" to update table.
Found this event only is triggered for first row AND found the data has not
been written to database yet, so the SQL update fails since there is no data
yet.

I think I want to hook the subform datasheet "After Insert" and update the
database with new data after data has been commited. BUT, HOW DO I know
which row is being inserted?

Is there a way to reference other subform datasheet controls, so I can
populate them, before record is commited? How do I determine what is being
inserted?

Thanks in advance.
P
 
I have a subform datasheet with many rows.. When a user enters a new row,
column one of six, I need to query another database to automatically
populate the remaining subform datasheet columns.
Click to expand...

ummm...

WHY?

Storing data redundantly, copying it from one table into another
table, is almost never either necessary or desirable. What are these
two tables? How are they related? What's the mainform, and how is its
recordsource related to these two tables?

Could the Subform perhaps be based on a Query, linking to the table
from which you wish to view the other five fields?
 
John,

The other database is a large commercial Acct/Inventory app. The SN tables
are large and we can't modify the tables or SPs since it voids support and
user licenses are very high. We track some internal biz processes,
including returns, that fall between our "normal" applications so we dont
have to customize our critical systems and add licenses for all users in
company, for simple tasks (process RMAs and milestone tracking etc). Plus
our users want a simple interface rather than trying to use the "All in one
Acct/Inventory etc package". We extract information as needed that fall
between other database apps, add our specific extra information about the
returns. We want to automate data entry if it exists already. We know there
is some duplicate data....

I will check on making a pass through query using the SN to set the row.
The query for the other subform datasheet columns has to join 5 tables which
should be a sproc but we still have a mdb (using linked ODBC to sql2000) not
adp..

P
 
John,

The downside of the pass through query is that it takes a few seconds to
retrieve the other columns from one SN now. But if I use a passthrough
query, it will get slower with more SNs I add to the subform. If we query
one SN at a time as SNs are entered and saved in a local database, this will
only be one delay. The ugly passthrough query will have to join 6 tables in
two databases everytime you view the form?

P
 
I found the one of the 5 tables did not have a covering index has 1.7
million rows. I will add an index which should help significantly for the
pass through query.

P
 
Back
Top