Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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!
Reply With Quote
  #2 (permalink)  
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!

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 12:48 PM.


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