Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 20th, 2008, 07:01 PM
Registered User
 
Join Date: May 2007
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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!
 
Old January 21st, 2008, 04:41 PM
JOK JOK is offline
Registered User
 
Join Date: Oct 2007
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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!





Similar Threads
Thread Thread Starter Forum Replies Last Post
Updating Table gregalb SQL Server 2000 1 June 2nd, 2007 04:39 PM
Updating data table vaidyapragati ASP.NET 2.0 Basics 4 May 11th, 2007 11:52 PM
Data updating in Table & reflecting in report anukagni Access 5 May 31st, 2006 03:57 AM
How to Update one table with other table data? ramk_1978 SQL Language 2 May 26th, 2006 12:51 AM
Updating table Hudson40 Access VBA 6 March 23rd, 2005 12:07 PM





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