code to update table

  • Thread starter Thread starter Frank
  • Start date Start date
F

Frank

I have two tables discount and stock with a discount
column and label column in each. After updating the
discounts in the discount table. I want to run code that
will check the label name in a stock table and update the
corresponding discount.

Any help in pointing me in the right direction would be
really appreciated.
 
You can open the stock table into a recordset, find the
matching label name and then edit the discount field. In
order to do this you need to go into the Tools drop menu
in the Visual Basic Editor, select "References" and check
off "Microsoft DAO 3.6 Object Library." Otherwise you
will get a reference error.
The code would look something like this:

sub update_stock (label_name as string, discount as
integer)

dim db as database
dim recStock as recordset

set db = currentdb
set recStock = db.openrecordset ("stock")
recStock.findfirst ("label = " & label_name") ' looks for
the passed label name
if recStock.nomatch = false then ' if it finds a matching
label name...
recStock.edit ' edits the discount
recStock("discount") = discount
recStock.update
end if
recStock.close
db.close
end sub
 
Frank,

Look into setting up a relationship between the two tables and enforcing
Cascade Updates. This will cause changes in one table to change values in
the second table automatically.

Another 'codeless' option is to set up a query that joins the 2 tables and
updating the resulting recordset. Again, a change in a single field should
propagate to both tables -- if the query is set up correctly.

These 2 options will work only in specific cases. To read more about these
options look at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;304473&Product=acc2002

If you are not averse to coding, you can set 2 recordsets and 'wrap'
simultaneous updates in a transaction. Something like this:

Private Sub Update2Tables ()

Dim ws as Workspace
Dim rst1 as Recordset
Dim rst2 as Recordset

Set ws = DBEngine.Workspaces(0)
Set rst1 = CurrentDB.OpenRecordset("Select * From Table1", dbOpenDynaset)
Set rst2 = CurrentDB.OpenRecordset("Select * From Table2", dbOpenDynaset)

On Error Goto errRollBack

ws.BeginTrans
rst1!Field1 = "NewValue"
rst2!Field1 = "NewValue"
ws.CommitTrans

MsgBox "Tables updated!"

Exit Sub

errRollBack:
ws.RollBack
MsgBox "Error updating tables!"

End Sub

Hope this helps.

-Assaf
 
I have two tables discount and stock with a discount
column and label column in each. After updating the
discounts in the discount table. I want to run code that
will check the label name in a stock table and update the
corresponding discount.
Click to expand...

Is it in fact necessary to store the discount in the stock table?
Ordinarily one would not store it redundantly, but would instead
create a Query joining the two tables and just look it up!

If you do want to store it - realizing that it a) is redundant, b)
wastes space, and c) risks there being one value for the discount in
the Discounts table and a different value in the Stock table - an
Update query will work. Create a Query joining the two tables on the
Lookup field, change it to an Update query, and put

=[Discounts].[Discount]

under the Discount field in the stock table. Run the query using the !
icon and it will overwrite all the existing discounts with the value
from the Discount table.
 
Thanks for the replies.... I will look at them in detail.
One issue I have is that in the stocks table the label
column will have many duplicates with a corresponding
discount. I created the labels table to select unique
label names and add the appropreate discount using a form.
I then wanted to replace/update the discount in the stock
table by running code from a command button on the form.
I appreciate your help and will feed back.
 
I seem to be going round in circles trying to get this to
work. Here is the prblem afresh.

1. tblstock has (in addition to others) three columns
discount,label,type there are many occurences of the
label+type combination across the 50000 records

2. tbldiscount has been created to give only unique
label+type occurrences and uses the same three columns.
which is used by a form to assign unique discounts to
unique labels+type combinations.

what I want it to do using tbldiscount as the source is
check the tblstock and amend the discount where the
labels+type combination are the same.


Heeelp ?
 
I seem to be going round in circles trying to get this to
work. Here is the prblem afresh.

1. tblstock has (in addition to others) three columns
discount,label,type there are many occurences of the
label+type combination across the 50000 records

2. tbldiscount has been created to give only unique
label+type occurrences and uses the same three columns.
which is used by a form to assign unique discounts to
unique labels+type combinations.

what I want it to do using tbldiscount as the source is
check the tblstock and amend the discount where the
labels+type combination are the same.
Click to expand...

I've asked this before; I'll ask it again.

WHY ARE YOU STORING DATA REDUNDANTLY?

It's bad design. It's making your job harder. It's not necessary. You
can create a Query joining tblStock to tblDiscounts, joining by Labels
and Type, and pick up the discount whenever you need it!

But... to answer your question...

Be sure that tblDiscount has a two-field primary key (a key icon on
Label and Type - ctrl-click the two fields and click the Key icon if
it doesn't have this).

Create a Query joining tblDiscount to tblStock; if Access doesn't
automatically join on both fields, manually drag tblDiscount.Labels to
tblStock.Labels and tblDiscount.Type to tblStock.Type.

Change the query to an Update query; on the UpdateTo line under
tblStock.Discount type

[tblDiscounts].[Discount]

Run the query.

You will now have two values of Discount for each value of Labels and
Type - one in each table; right now they'll be the same, but there is
no protection against having one of them changed.
 
Back
Top