Update ProductLocation table using AfterUpdate of form

[Damon Heron]

I have a product form that fires an update query :

INSERT INTO ProductLocation ( ProductID, LocationID )
SELECT DISTINCT Products.ProductID, Products.LocationID
FROM Products INNER JOIN ProductLocation ON
Products.ProductID=ProductLocation.ProductID
WHERE (((Products.ProductID)=Forms!Products!ProductID) And
((Products.LocationID)=Forms!Products!cmbLocation));

I only want to do this when a new record is created -in other words the user
cannot change the product location on existing products without following
procedure. The combobox "cmbLocation" can only be changed on a new record.
My problem is the error msg for the query about "Access cannot update
.....due to the following violations...." This occurs when the user changes
anything on an existing form, so I can see my problem is the query running
each time any change is made, not just updating product location.

Is this clear? I am somewhat of a newbie. Thanks for your help!

Damon

 

[[MVP] S.Clark]

The table ProductLocation will allow you to store the information about a
product residing at multiple locations. What I don't see in the table is a
way to differentiate between which location the product currently resides.
So, can one product reside in multiple locations? Do you need the history
of what it's previous location was? If the answer to these questions are
both No, then you can simply add the LocationID to the Products table. But,
if the answers are Yes, then how do you differentiate between each location
or the current location? (i.e. The use of a date or a Yes/No field would
allow for this.)

On another topic, to do something when a record is created, you can trigger
code from the Before or After Insert property of the form.

I don't understand what you mean about "the user cannot change the product
location on existing products without following procedure." But, To make
something available during only the new record, you can add code to the On
Current method of the form:

If me.newrecord then
'do something
else
'do something else
end if

As you can see, most of your questions are really about the execution of
events on the form, and not so much to do with the actual query. You may
want to visit the Forms and Formscoding newsgroups also.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-