0

Once again Im stuck on the same database. This time Im creating an update query that removes 1 of each part in a table when a button is pressed. But when a part is already at 0 it goes to -1. How can I stop this from happening?

I used SQL to achieve this,

UPDATE [ASM-00065 WIP] 
SET [ASM-00065 WIP].[Current Stock] = [ASM-00065 WIP]![Current Stock]-1;
Improve this question

3 Answers 3

Reset to default
4

I might be understanding your question incorrectly, but do you want to update all rows in that table for that column, and subtract 1 from them? In that case you can use:

UPDATE [ASM-00065 WIP] 
SET [ASM-00065 WIP].[Current Stock] = [ASM-00065 WIP]![Current Stock]-1 
WHERE [ASM-00065 WIP].[Current Stock] > 0
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Can I create another part which enables me to take out two of one thing in that table but still take out 1 of everything else?
You can use syntax similar to the one provided by bluefeet: UPDATE [ASM-00065 WIP] SET [Current Stock] = IIF(Condition for subtracting 2 AND [Current Stock]>1, [Current Stock] - 2, IIF([Current Stock]>0, [Current Stock]-1, [Current Stock]));
Is there any way i can create the SQL code or even use VBA to inform the user that some of the records in the table = 0 when they press the button. I want to create it so they cannot remove any data from the table if one or more records = 0
The previous SQL code doesnt solve my problem, In my database I have 'Kits' and when a 'Kit' is removed one of each part that makes up the kit is also removed. For example, ASM-00065 consists of 6 parts. But one of these parts has two pieces. So i want to remove 2 of, say, MEC-00218 but still remove one of everything else. How can this be done?
Instead of informing the user that there are zero records, I would simply not display them to the user, if the record count is zero. For the second problem, it would be useful if you wrote a stored proc which takes the part numbers for the kit, and deletes the parts individually from the table.
|
1

Is MS-Access you can use IIF()

UPDATE [ASM-00065 WIP] 
SET [Current Stock] 
   = IIF([Current Stock]>0, [Current Stock]-1, [Current Stock]);
Improve this answer

4 Comments

Is there any way i can create the SQL code or even use VBA to inform the user that some of the records in the table = 0 when they press the button. I want to create it so they cannot remove any data from the table if one or more records = 0
@MichaelMcWilliams you would need to use VBA to notify them OnClick event that there are records that meet you condition. You could use a MsgBox to display the note.
Im going to have a stab at it here using VBA. If i have any problems ill post them on here
@MichaelMcWilliams if you have problems, then post a new question. good luck! :)
0

You can add a condition to filter the rows that you will update:

UPDATE [ASM-00065 WIP] SET [ASM-00065 WIP].[Current Stock] = [ASM-00065 WIP]![Current Stock]-1 WHERE [ASM-00065 WIP]![Current Stock] > 0;
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.