I want to update a row if it exists and Insert if the row don't ex

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am Using Left outer join to join the tables. The inner table may or may
not have a record for that key. I want to give user option to update the
info on inner table. How can I check to decide upfront if I need an Update
or insert routine.
Thanks a lot.
 
Z-Man said:
I am Using Left outer join to join the tables. The inner table may
or may not have a record for that key. I want to give user option to
update the info on inner table. How can I check to decide upfront if
I need an Update or insert routine.
Thanks a lot.
Click to expand...

You could go about this several different ways, depending on exactly
what you want to achieve. Are you intending to operate on at most one
matching record from the "inner" table? Do you want to insert a record
if it doesn't exist, and update it if it does exist, without user
confirmation? Or do you want to find out if it exists, then ask for
confirmation of the update if it exists? Can you identify by key a
specific record to be updated, in the case that at least one "inner"
record exists?

You could ...

A. Use DLookup first to find out if the record exists. Then, if it
doesn't, add it; if it does, ask for confirmation and then update it.

B. Open a recordset on the query. Determine by inspection of the field
values in the recordset whether the record exists or not. If it
doesn't, just update the appropriate fields in the recordset; if it
does , ask for confirmation and then update the recordset.

C. If the record to be added would have a unique key, then you could use
the DAO Execute method to insert the record, determine if the insert
failed (db.RecordsAffected = 0) and then decide whether to run an update
query instead.

I suppose other approaches are also possible, but you haven't given
quite enough information about what you're trying to do.
 
Thank a lot Dirk,
You gave me some nice ideas to work with. Here is what I am working with. I
am creating application for construction of telecom sites. I am keeping all
he information on a site, Address, Driving direction, Contact person etc. I
have a driver table with site demograpics that is always present. If there
is no contact information I won't have a row in contact table.
On a form the person visting the site can update any information. If there
is no contact infomation and (no row in the table) The update will fail. I
wanted update routine to check if the site exits (site# is the Key) else I
need to insert a site row.

On second thought can I create two buttons an update button & an Insert
Button with two different routines, and make only one button visiable at a
time.
 
Z-Man said:
Thank a lot Dirk,
You gave me some nice ideas to work with. Here is what I am working
with. I am creating application for construction of telecom sites. I
am keeping all he information on a site, Address, Driving direction,
Contact person etc. I have a driver table with site demograpics
that is always present. If there is no contact information I won't
have a row in contact table.
On a form the person visting the site can update any information. If
there is no contact infomation and (no row in the table) The update
will fail. I wanted update routine to check if the site exits (site#
is the Key) else I need to insert a site row.

On second thought can I create two buttons an update button & an
Insert Button with two different routines, and make only one button
visiable at a time.
Click to expand...

If you're doing this on a form, one easy and simple way is to use a
subform for the contact information, linked by the SiteID (or whatever
is field by which the tables are related). If no contact information
has been entered yet, the subform will naturally be positioned at the
"new" record. As subform would also, naturally, allow you to enter
multiple contacts for the same site, which might be a good thing --
however, if you want to restrict it to only having one contact per site,
you can put code in a couple of subform events to set AllowAdditions to
False if there's already a record for the current site.
 
Back
Top