p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Access VBA (http://p2p.wrox.com/forumdisplay.php?f=80)
-   -   Updating one table with data from another table (http://p2p.wrox.com/showthread.php?t=65384)

dirtdog22 January 20th, 2008 07:01 PM

Updating one table with data from another table
 
I'm having a problem updating one table with data from another in Access 2003. My database uses a form to add, edit and delete records from a table called Products. There are two fields in this table, ProductID and ProductName. ProductID is the primary key and is autonumbered by Access.

The second table, Aliases, is made up of three fields, AliasID, ProductID and ProductAlias. AliasID is the primary key and is also autonumbered. ProductID is linked to the ProductID field in the Products table in a one to many relationship. The Alias table is used so that if a product has more than one name, the names will be recognized as one product by having a common ProductID. I have referential integrity set up between the tables with cascading updates and deletions.

When a new product is added by the form, the Products table is updated with the ProductName and an autogenerated ProductID. The ProductName is also added as a ProductAlias in the Alias table at the same time. The ProductID in the Aliases table is currently being updated manually after a new product is added. I would like to automate this using VBA, if possible.

My problem is that I can't figure out how to get the ProductID for the new ProductName copied into the Alias form for the new ProductAlias by query or ADO VBA. Any help is greatly appreciated!

JOK January 21st, 2008 04:41 PM

I can give you DAO code as follows:
Inside the button and/or code you use to say it's ok to add the new Product in form Products, call this procedure (this might replace code or punching wherever you are now initially adding a record to table Aliases):

Private Function fnAddAliasRecord ()
   Dim rs as DAO.recordset
   Dim ss as string

   ss = "Select * From Aliases"
   Set rs = CurrentDB.Openrecordset(ss, dbOpenDynaset)
   With rs
      .Addnew
      ' Here you are referring to textboxes on your Products form
      ' AliasID will automatically be assigned by Autonumber
      !ProductID = Me!txtProductID
      !ProductAlias = Me!txtProductName
      .Update
   End With
   Set rs = nothing
End Function

I hope this helps!



All times are GMT -4. The time now is 01:13 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.