By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,635 Members | 911 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,635 IT Pros & Developers. It's quick & easy.

How to insert a record into a different table after a particular action?

P: 10
Hi there,

I have a form called Production Run and has a field to select a product. I would like to grab the recipe for a particular product from product recipe table, do some calculations on the fields and insert the most up to date recipe into another table called tblProductionRunUsage. I can then display the most up to date for each production run as a subform on the main production run form. The reason I am doing this to save the current product recipe for each production run as the product recipe changes often.

I have created an update query using the query wizard. This query will insert the most up to date product recipe for a particular production run into a table called tblProductionRunUsage.

I would like to run this query every time a production run record is added and a product is selected.

Does anyone know what is the code for this.

I have tried this:
DoCmd.OpenQuery "qryAppendToProductionRunUsage", acViewNormal, acReadOnly

It does not work. And when should I execute the query? After the user select the product? What if the user makes a mistake and chooses another product, then the insert operation will fail as it already exists in the tblProductionRunUsage.

Any help is greatly appreciated.
Dec 21 '07 #1
Share this Question
Share on Google+
3 Replies


P: 68
Hi there,

I have a form called Production Run and has a field to select a product. I would like to grab the recipe for a particular product from product recipe table, do some calculations on the fields and insert the most up to date recipe into another table called tblProductionRunUsage. I can then display the most up to date for each production run as a subform on the main production run form. The reason I am doing this to save the current product recipe for each production run as the product recipe changes often.

I have created an update query using the query wizard. This query will insert the most up to date product recipe for a particular production run into a table called tblProductionRunUsage.

I would like to run this query every time a production run record is added and a product is selected.

Does anyone know what is the code for this.

I have tried this:
DoCmd.OpenQuery "qryAppendToProductionRunUsage", acViewNormal, acReadOnly

It does not work. And when should I execute the query? After the user select the product? What if the user makes a mistake and chooses another product, then the insert operation will fail as it already exists in the tblProductionRunUsage.

Any help is greatly appreciated.
have you tried removing the acReadOnly off the end? Whether or not the query will fail depends on the table settings, i.e. whether it'll allow duplicates in the relevant fields. But in any case it sounds like you might want a facility where the user confirms their selection is ok, i.e. a confirm cmd button

HTH
Kevin
Dec 21 '07 #2

P: 10
thanks, I will try it tomorrow and will let you know.
Dec 22 '07 #3

P: 10
Hi there,

I understand that a append query will insert a record from one table to another. If I allow duplicate values for a particular field, it will allow more than one record for the particular field.

The new table should have a primary key called ProductionRunID and for each production run id it will have a recipe. There has to be way where I can change the product and the new insert record should overwrite the old record. Right now, I will not add the record because there is already a record with the same production run id. In other words, who do I use an update query to replace or overwrite the existing record.

Does anyone know how to do that?
Dec 28 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.