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!