Query fun again!

[Sam Summers]

Hi

I have just found a major loop hole in my application which is:-

My database has five types of equipment and various items of each or any type may be set as being at a certain location.
I have a move all button that moves all the items from their current location to a newly selected one.
What i have just discovered is that when the user selects the new location and clicks on the button which runs this query:

INSERT INTO AccessOnHire ( HireDate, EquipmentID, LocationID )
SELECT Equipment.HireDate, Equipment.EquipmentID, Equipment.LocationID
FROM Equipment
WHERE (((Equipment.EquipmentID)=[Forms]![ViewbyLocation]![EquipmentID]) AND ((Equipment.LocationID)=[Forms]![ViewbyLocation]![LocationID]));

All the items of all the categories are moved instead of just the ones currently displayed in the continuous form 'ViewbyLocation'.

This is critical to the functionality of the database.

This query is also run at the same time but only updates the appropriate record that the record selector is currently pointing to as opposed to all the items in the database:

UPDATE Equipment SET Equipment.HireDate = [Forms]![Main]![RefDate]
WHERE (((Equipment.EquipmentID)=[Forms]![ViewbyLocation]![EquipmentID]) AND ((Equipment.LocationID)=[Forms]![ViewbyLocation]![LocationID]));

Thank you in advance

 

[dfitz]

The problem is that you are using a continuous form that displays all records. A better way of doing this would be to select the records that you want to move, say by using a checkbox and replace the WHERE statement with WHERE mycheckbox=True.
df

 

[Sam Summers]

Each form displays the required records fine but the query is moving all the items in the database as opposed to just the items displayed on the individual forms for each category.
Check boxes would be impractical as there may be anything up to 1000 records that would need to be 'checked'.

Thanks anyway

 

[dfitz]

Another idea!
How do you select the records to appear in the form? If this is done using a query, can you use the query as the datasource for the INSERT query so that you don't need to refer to the form?
df

 

[Rich]

Have you tried Running the Insert on it's own?