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

deducting a number in a field on a form from a table

P: 45
I am creating a restaurant meal ordering system. I am not very good with VB code, but am getting the hang of it slowly. I want to be able to enter the number of meals requested as part of an order onto frmOrderDetails ( the subform of frmOrder ) and that number to be deducted from the number of that meal in stock in the tblMeal, and am not sure how to go about it?
Feb 21 '07 #1
Share this Question
Share on Google+
44 Replies


Rabbit
Expert Mod 10K+
P: 12,315
That depends on what you want to do with that number. Are you trying to store it in a table?
Feb 21 '07 #2

P: 45
'Number of Items' is stored in 'tblOrderDetails' 'Number of (Items) in Stock' is stored in 'tblItems'. Using 'frmOrderDetails' I want to input the 'Number of Items' and that number to be subtracted from 'Number of (Items) in Stock' .

I'm using the word Items instead of Meals, because it also includes drinks.

I dont know whether I've made it clear but this is for a particular meal- not a total of all meals in the order.
Feb 21 '07 #3

Rabbit
Expert Mod 10K+
P: 12,315
I'm assuming you have an Item ID field and that the tables are linked on that field? You can then run an update query.
Feb 21 '07 #4

P: 45
( tblOrder
OrderID (key)
TableID (foreign key)
GuestsArrived
GuestsLeft
Order Notes )

tblOrderDetail
OrderID (key)
ItemID (key)
Number of Items
Item Notes

tblItem
ItemID (key)
Name
ItemTypeID (foreign key)
NumberInStock
Re-OrderLevel
Cost
Notes
InUse


Yes ItemID is the Key. I don't think I want to update though surely- I thought it would be a sum of some kind?
Feb 21 '07 #5

Rabbit
Expert Mod 10K+
P: 12,315
Well, you could do an update where the Item ID matches and set Amount in Stock = Amount in Stock - Amount Ordered. Problem is you have to be absolutely sure you only run this once per order, multiple runs would screw up the numbers.

Another option would be to use use the sum of the items from the Order Details and match to see if it is equal to or exceeds the number in stock.

Perhaps someone else will have a better solution for you.
Feb 21 '07 #6

P: 45
Can anyone else help?

I have tried creating a button in the subform that would appear after every meal but it isn't showing up on the form. So instead I tried attaching the code below onto a button on the main form-

Expand|Select|Wrap|Line Numbers
  1. tblItem!NumberInStock=tblItem!NumberInStock- me![tblOrderDetail Subform]![Number of Items]
But an error message- 'Can't find the macro...' appears

Can anyone else help? I think I'm going about this the wrong way.
Feb 22 '07 #7

P: 45
Does anyone else have any ideas? Help much appreciated!
Feb 22 '07 #8

Rabbit
Expert Mod 10K+
P: 12,315
Can anyone else help?

I have tried creating a button in the subform that would appear after every meal but it isn't showing up on the form. So instead I tried attaching the code below onto a button on the main form-

Expand|Select|Wrap|Line Numbers
  1. tblItem!NumberInStock=tblItem!NumberInStock- me![tblOrderDetail Subform]![Number of Items]
But an error message- 'Can't find the macro...' appears

Can anyone else help? I think I'm going about this the wrong way.
You won't be able to do it with that code no matter how you change the code unless you use a DoCmd.RunSQL Update. And this runs into the same problem if they accidentally run the code multiple times, i.e. by pressing the button one too many times.
Feb 22 '07 #9

Rabbit
Expert Mod 10K+
P: 12,315
Does anyone else have any ideas? Help much appreciated!
Please do not bump the thread until 24 hours has passed and you have yet to receive an answer. The experts on this forum are volunteers and are using their spare time to help people so it is understandable if you do not get your answers right away. Please have patience. If after a day you still have not received an answer, then feel free to bump it at that time.

MODERATOR
Feb 22 '07 #10

NeoPa
Expert Mod 15k+
P: 31,186
I am creating a restaurant meal ordering system. I am not very good with VB code, but am getting the hang of it slowly. I want to be able to enter the number of meals requested as part of an order onto frmOrderDetails ( the subform of frmOrder ) and that number to be deducted from the number of that meal in stock in the tblMeal, and am not sure how to go about it?
Your question implies that you are looking for an update process but later comments suggest you would prefer to work it out on the fly.
The latter is probably a more reliable way to do it but I can't see exactly what it is. Are you wanting to know how to show something on a form? Is it a stock available check perhaps?
These questions affect how you go about doing this so this is required information.
I'm away for a couple of days now, but if you can explain what you need a little more clearly I'll get back to this after the weekend. Of course, with the clearer question, someone else may be able to help in the mean-time.
Good luck.
Feb 23 '07 #11

P: 45
To attach the event to a button woulld be best, if the process happens automatically then I would encounter problems if the order changed after being made. The idea is that when an order is made 2 steak and chips and 1 risotto are entered on the order form (made up of frmOrder and frmOrderDetails Subform, the data entry is on the subform). 2 then needs to be deducted from the number of steak and chips in stock, and 1 from the number of risottos, the number of each item in stock is held in the Item table (tblItem).
I hope this exaple better illustrates what I have been trying to say.
Feb 24 '07 #12

P: 45
Your question implies that you are looking for an update process but later comments suggest you would prefer to work it out on the fly.
The latter is probably a more reliable way to do it but I can't see exactly what it is. Are you wanting to know how to show something on a form? Is it a stock available check perhaps?
These questions affect how you go about doing this so this is required information.
I'm away for a couple of days now, but if you can explain what you need a little more clearly I'll get back to this after the weekend. Of course, with the clearer question, someone else may be able to help in the mean-time.
Good luck.
No one has come back to me in in the past week. If I'm asking the wrong kind of question could someone tell me?
Mar 6 '07 #13

Rabbit
Expert Mod 10K+
P: 12,315
To attach the event to a button woulld be best, if the process happens automatically then I would encounter problems if the order changed after being made. The idea is that when an order is made 2 steak and chips and 1 risotto are entered on the order form (made up of frmOrder and frmOrderDetails Subform, the data entry is on the subform). 2 then needs to be deducted from the number of steak and chips in stock, and 1 from the number of risottos, the number of each item in stock is held in the Item table (tblItem).
I hope this exaple better illustrates what I have been trying to say.
If you attached the event to a button you run the same risk as if the process happened automatically. They could make an order, click the button, realize it's wrong, change the number, and click the button again.

I'm thinking it can be automatic with a Before Update that uses a DLookUp to grab the old value and an After Update that will subtract the old value and then add the new value.
Mar 6 '07 #14

NeoPa
Expert Mod 15k+
P: 31,186
No one has come back to me in in the past week. If I'm asking the wrong kind of question could someone tell me?
Certainly. And btw, you needn't wait a week if you feel your thread still requires attention and hasn't received any for over 24 hours. That would be considered a reasonably time to bump it.

I hadn't replied further as I felt I'd made it clear in my last post (#11) what information would be a minimum before I got further frustrated trying to communicate. Without a clear and related answer to my questions I cannot proceed. Hence I had not contributed further. If, at any time, you are confused by my questions then please just tell me. Otherwise I will simply assume that you have no further interest in my responses.
Mar 6 '07 #15

P: 45
I don't understand, what other information should I provide in addition to post #12?
Mar 6 '07 #16

NeoPa
Expert Mod 15k+
P: 31,186
I don't understand, what other information should I provide in addition to post #12?
I'll try to explain this as clearly as I can. Remember, I don't want to go on a hunt through the thread just to understand the problem, every time I come back to it. The information should be easily available - not a treasure hunt. It is after all, my time I'm trying to make available to you and not vice-versa.
Your question implies that you are looking for an update process but later comments suggest you would prefer to work it out on the fly.
A) Which of these is it?
The latter is probably a more reliable way to do it but I can't see exactly what it is. Are you wanting to know how to show something on a form? Is it a stock available check perhaps?
B) Explain this in terms relative to this question. Please do not start an explanation from scratch and expect me to cross-reference your answer with a view to extracting this information.
These questions affect how you go about doing this so this is required information.
This just illustrates that I was not simply ignoring you. I made it very plain what information was required. It was (and is) your choice what you do with this. I certainly won't feel affronted (or offended) if you feel this is either too complicated or too much trouble for the potential benefit.
Mar 7 '07 #17

P: 45
If you attached the event to a button you run the same risk as if the process happened automatically. They could make an order, click the button, realize it's wrong, change the number, and click the button again.

I'm thinking it can be automatic with a Before Update that uses a DLookUp to grab the old value and an After Update that will subtract the old value and then add the new value.
A. Taking Rabbit's point into consideration I would like to use an After Update on the 'Number of Items' field in the 'OrderDetails Subform', of the 'Order' form

B. Nothing extra needs to be shown on the 'Order' form or the 'OrderDetails Subform'. With the use of code, the number of a particular item orderd (e.g. 2 Risottos) will be entered in the 'OrderDetails Subform', in a field called 'Number of Items', and then that number will be deducted from the number in stock (held in a table called tblItem, column name 'NumberInStock').

So far I have tried
Expand|Select|Wrap|Line Numbers
  1. =tblItem!NumberInStock=(tblItem!NumberInStock-[Number of Items]) 
in the After Update property of the 'Number of Items' field on the 'OrderDetails Subform' But an error appears saying "The Object doesn't contain the Automation object tblItem!NumberInStock" I think this is becasue it is not part of the table, however it should be linked by the 'ItemID' which is part of both 'tblItem' and 'tblOrderDetail', and I don't know how to write this.

Sorry for messing you around :(
Mar 7 '07 #18

Rabbit
Expert Mod 10K+
P: 12,315
A. Taking Rabbit's point into consideration I would like to use an After Update on the 'Number of Items' field in the 'OrderDetails Subform', of the 'Order' form

B. Nothing extra needs to be shown on the 'Order' form or the 'OrderDetails Subform'. With the use of code, the number of a particular item orderd (e.g. 2 Risottos) will be entered in the 'OrderDetails Subform', in a field called 'Number of Items', and then that number will be deducted from the number in stock (held in a table called tblItem, column name 'NumberInStock').

So far I have tried
Expand|Select|Wrap|Line Numbers
  1. =tblItem!NumberInStock=(tblItem!NumberInStock-[Number of Items]) 
in the After Update property of the 'Number of Items' field on the 'OrderDetails Subform' But an error appears saying "The Object doesn't contain the Automation object tblItem!NumberInStock" I think this is becasue it is not part of the table, however it should be linked by the 'ItemID' which is part of both 'tblItem' and 'tblOrderDetail', and I don't know how to write this.

Sorry for messing you around :(
1) You can't reference a field of a record from a table using that.

2) Assuming that NumberInStock is in a table that is not bound to the form, you're going to have to use code to update the table.

3) I did not say put it in the After Update event. The code you have now will give incorrect results because it will only ever subtract. So if they change the field a lot, then it's going to subtract that many times.

4) I said you need to put it in the Before Update event because you need to retrieve the old value from the table, add it to the stock, and then subtract the new value.
Mar 7 '07 #19

P: 45
1) You can't reference a field of a record from a table using that.

2) Assuming that NumberInStock is in a table that is not bound to the form, you're going to have to use code to update the table.

3) I did not say put it in the After Update event. The code you have now will give incorrect results because it will only ever subtract. So if they change the field a lot, then it's going to subtract that many times.

4) I said you need to put it in the Before Update event because you need to retrieve the old value from the table, add it to the stock, and then subtract the new value.
I'm not sure I understand. The 'NumberOfItems' (frmOrderDetails Subform) where the number is being entered, and what the event will be triggered by, is not the new total for the 'NumberInStock' (tblItems), it is the number of meals that have been used up, so the NumberInStock needs to be the same as before less the number that have been used. Sorry if I've got this confused.
Mar 7 '07 #20

Rabbit
Expert Mod 10K+
P: 12,315
I'm not sure I understand. The 'NumberOfItems' (frmOrderDetails Subform) where the number is being entered, and what the event will be triggered by, is not the new total for the 'NumberInStock' (tblItems), it is the number of meals that have been used up, so the NumberInStock needs to be the same as before less the number that have been used. Sorry if I've got this confused.
Yes, I understand this, that's why I laid it out the way I did.
If they want 10 Meal X and you have 25 in stock, then you want to change the number in stock to 25-10=15 in stock.

However, if you use your code, which won't work by the way, but if you used the basic idea of it, if someone were to change it from 10 to 8 then what would happen is 15-8=7 in stock.

What you want to do is, get the old value, add it to what's in stock, then subtract the new value. Basically:

I have 25 Meal X in stock.
There's an order and I enter in 10.
Meal X in stock is now set to 25-10 = 15
But wait, I made a mistake, it was supposed to only be 8.
So I change the textbox to 8. So what I want to do is get the old value, 10, add that to what's in stock, 15, and then subtract the new value, 8.
15+10-8=17.

To get the old value, you need vba code in the before update event to grab that old value before the new value is saved to the table.
Mar 7 '07 #21

P: 45
To get the old value, you need vba code in the before update event to grab that old value before the new value is saved to the table.
Can someone suggest how I could do that? Thanks
Mar 7 '07 #22

Rabbit
Expert Mod 10K+
P: 12,315
Expand|Select|Wrap|Line Numbers
  1. Private Sub Number_Ordered_BeforeUpdate(Cancel As Integer)
  2.    DoCmd.RunSQL "UPDATE Tbl_InStock SET InStockField = " & _
  3.       DLookUp("InStockField", "Tbl_InStock", "Primary_Key = " & _
  4.       Me.Foreign_Key) + DLookUp("AmountOrdered" & _
  5.       , "Tbl_Orders", "Foreign_Key = " & Me.Foreign_Key) - Number_Ordered
  6. End Sub
Mar 7 '07 #23

NeoPa
Expert Mod 15k+
P: 31,186
On an Access form (as well as a number of Office objects) there is also the .OldValue property. I've had a stab at a replacement for this procedure but you may want to check that it hangs together properly...
Expand|Select|Wrap|Line Numbers
  1. Private Sub Number_Ordered_BeforeUpdate(Cancel As Integer)
  2.   DoCmd.RunSQL "UPDATE Tbl_InStock " & _
  3.                "SET InStockField = InStockField+" & _
  4.                Me.Number_Ordered.OldValue - Me.Number_Ordered
  5. End Sub
Mar 7 '07 #24

P: 45
On an Access form (as well as a number of Office objects) there is also the .OldValue property. I've had a stab at a replacement for this procedure but you may want to check that it hangs together properly...
Expand|Select|Wrap|Line Numbers
  1. Private Sub Number_Ordered_BeforeUpdate(Cancel As Integer)
  2.   DoCmd.RunSQL "UPDATE Tbl_InStock " & _
  3.                "SET InStockField = InStockField+" & _
  4.                Me.Number_Ordered.OldValue - Me.Number_Ordered
  5. End Sub
Thanks. I tried the code you gave exactly and nothing happened- no error message, no deduction in stock shown in tblItem.

I tried changing the code to
Expand|Select|Wrap|Line Numbers
  1. Private Sub NumberofItems_BeforeUpdate(Cancel As Integer)
  2. DoCmd.RunSQL "UPDATE tblItem " & _
  3.                "SET NumberInStock = NumberInStock+" & _
  4.                Me.NumberofItems.OldValue - Me.NumberofItems
  5.  
  6. End Sub
Where 'NumberofItems' is the field on the data entry form that needs to to subtracted from the number in stock. 'tblItem' is where the 'NumberInStock' is held. This was only guess work and I probably have the meaning of your code wrong. This comes up with 'NAME?', predictably :)
Mar 8 '07 #25

NeoPa
Expert Mod 15k+
P: 31,186
No! It seems you made a pretty good stab at it (I have to say that, as I would have used exactly that code if I'd known the names of all the items).
I suppose the next step is to say where the 'NAME?' appeared (BTW Are you sure it was not '#NAME?')?
Then try this version of the code and post what shows in the immediate pane (Ctrl-G) with an explanation of what you changed the value from and to.
Expand|Select|Wrap|Line Numbers
  1. Private Sub NumberofItems_BeforeUpdate(Cancel As Integer)
  2.   Dim strSQL As String
  3.  
  4.   strSQL = "UPDATE tblItem " & _
  5.            "SET NumberInStock = NumberInStock+" & _
  6.            Me.NumberofItems.OldValue - Me.NumberofItems
  7.   Debug.Print strSQL
  8.   Call DoCmd.RunSQL(strSQL)
  9. End Sub
Mar 9 '07 #26

P: 45
Yes it was #NAME? sorry. It appears in the NumberOfItems field on the OrderDetails Subform- where the data would be entered.

Regarding
with an explanation of what you changed the value from and to
I've tried the new code and #NAME? still apears in the same place. This means I can't enter data, it that what you mean?

CtrlG produced the window with code in, no message appears, if you press Run then a window appears asking for Maco Name does come up though.
Mar 9 '07 #27

NeoPa
Expert Mod 15k+
P: 31,186
  1. Does it come up with #NAME? before you try to make any changes?
  2. What is the Record Source of your form (If query then please include the SQL)?
  3. What is the Control Source of your [NumberofItems] TextBox?
You should understand that this is unlikely to be connected with the BeforeUpdate code provided as this won't get to run until after a change is made on the form to change the data.

PS. When answering the questions please include the number of the question as a reference.
Mar 9 '07 #28

P: 45
1. Yes it does, it's there all the time.

2. the forms are structured as follows- tblOrder (OrderID, TableID, GuestsArrived, GuestsLeft, DateOfOrder, TimeOfOrder, Notes)
tblOrderDetails Subform (OrderID, ItemID, Timing, NumberOfItems)
tblOrder is the main form and tblOrderDetails Subform is the subform.
Both forms come from tables of the same name- tblOrder and tblOrderDetails

3. The control source of 'NumberOfItems' is tblOrderDetail

I hope I have the meanings of Record Source and Control Source right.
Mar 9 '07 #29

NeoPa
Expert Mod 15k+
P: 31,186
That's a very clear reply :)
From that I can tell that you didn't quite understand what I meant by Record Source and Control Source. This is a good thing, as I now know what to be clearer on.
The Record Source is a property of a form. The Control Source is a property of a TextBox.


Record Source (2)
  1. Open the form in Design View.
  2. Select the form itself.
  3. Make sure the properties pane is showing (Alt-Enter).
  4. Click against the Record Source property.
  5. Copy (Ctrl-C) this value.
  6. Paste (Ctrl-V) it into a post and tag it as an answer to question 2.
You will probably need to do this for the main form as well, although I suspect the subform is the one we're interested in here.


Control Source (3)
  1. Open the form (tblOrderDetails) in Design View.
  2. Select the NumberOfItems TextBox.
  3. Make sure the properties pane is showing (Alt-Enter).
  4. Click against the Control Source property.
  5. Copy (Ctrl-C) this value.
  6. Paste (Ctrl-V) it into a post and tag it as an answer to question 3.
Mar 9 '07 #30

P: 45
2. What is the Record Source of your form (If query then please include the SQL)?
3. What is the Control Source of your [NumberofItems] TextBox?
2. Subform-
Expand|Select|Wrap|Line Numbers
  1. SELECT tblOrderDetail.OrderID,
  2.        tblOrderDetail.ItemID,
  3.        tblOrderDetail.Timing,
  4.        tblOrderDetail.[Number of Items],
  5.        tblItem.Cost,
  6.        tblItem.Notes
  7. FROM tblItem INNER JOIN tblOrderDetail
  8.   ON tblItem.ItemID=tblOrderDetail.ItemID;
Mainform-
Expand|Select|Wrap|Line Numbers
  1. tblOrder
3.
Expand|Select|Wrap|Line Numbers
  1. NumberofItems
Mar 10 '07 #31

NeoPa
Expert Mod 15k+
P: 31,186
Seems like a fine answer.
I'll revisit this tomorrow when my brain's working again.
Then we'll see if I can't actually help some ;)
Mar 11 '07 #32

NeoPa
Expert Mod 15k+
P: 31,186
  1. Does it come up with #NAME? before you try to make any changes?
  2. What is the Record Source of your form (If query then please include the SQL)?
  3. What is the Control Source of your [NumberofItems] TextBox?
2. Subform-
Expand|Select|Wrap|Line Numbers
  1. SELECT tblOrderDetail.OrderID,
  2.        tblOrderDetail.ItemID,
  3.        tblOrderDetail.Timing,
  4.        tblOrderDetail.[Number of Items],
  5.        tblItem.Cost,
  6.        tblItem.Notes
  7. FROM tblItem INNER JOIN tblOrderDetail
  8.   ON tblItem.ItemID=tblOrderDetail.ItemID;
Mainform-
Expand|Select|Wrap|Line Numbers
  1. tblOrder
3.
Expand|Select|Wrap|Line Numbers
  1. NumberofItems
If you look at the SQL in your answer to Q2 you'll see that the name of the field is actually tblOrderDetail.[Number of Items]. This means that, to use this field, you must have [Number of Items] as the Control Source.
Mar 11 '07 #33

P: 45
If you look at the SQL in your answer to Q2 you'll see that the name of the field is actually tblOrderDetail.[Number of Items]. This means that, to use this field, you must have [Number of Items] as the Control Source.
Thanks I hadn't realised that. I've now altered it so that there are no spaces ('NumberInStock'). I tested this and when you enter a number in the 'NumberOfItems' in tblOrderDetails Subform then click somewhere else a message comes up saying 'Runtime Error '3075' Syntax error missing operator in query expression NumberInStock+'
Mar 12 '07 #34

NeoPa
Expert Mod 15k+
P: 31,186
There are two possible problems here :
  1. For some reason when I told you the problem with [Number of Items], you chose to change the [NumberInStock] reference instead.
  2. You've misposted what you've actually done, fixed it correctly and come across another error.
I need to know these things explicitly and accurately.
  1. Does the #Name? problem still exist?
  2. If not, which line of the VBA code is highlighted in Yellow when the error message appears?
Mar 12 '07 #35

P: 45
1/2
Sorry. Point 2. is correct it should have read "I've now altered it so that there are no spaces ('NumberOfItems')."

A/B
No the #NAME? problem doesn't exist anymore, instead when you enter a number in the 'NumberOfItems' in tblOrderDetails Subform then click somewhere else a message comes up saying 'Runtime Error '3075' Syntax error missing operator in query expression NumberInStock+' and this code is highlighted-

Expand|Select|Wrap|Line Numbers
  1. Call DoCmd.RunSQL(strSQL)
Which is part of-
Expand|Select|Wrap|Line Numbers
  1. Private Sub NumberofItems_BeforeUpdate(Cancel As Integer)
  2.   Dim strSQL As String
  3.  
  4.   strSQL = "UPDATE tblItem " & _
  5.            "SET NumberInStock = NumberInStock+" & _
  6.            Me.NumberofItems.OldValue - Me.NumberofItems
  7.   Debug.Print strSQL
  8.   Call DoCmd.RunSQL(strSQL)
  9. End Sub
Mar 12 '07 #36

NeoPa
Expert Mod 15k+
P: 31,186
Right, I'm up with you again.
Can you post the results of the previous line for me. This will be in the Immediate Pane (Ctrl-G) of the VBA Window (Alt-F11 from Access).
Mar 12 '07 #37

NeoPa
Expert Mod 15k+
P: 31,186
...But try this version first :
Expand|Select|Wrap|Line Numbers
  1. Private Sub NumberofItems_BeforeUpdate(Cancel As Integer)
  2.   Dim strSQL As String
  3.  
  4.   strSQL = "UPDATE tblItem " & _
  5.            "SET NumberInStock = NumberInStock+" & _
  6.            Nz(Me.NumberofItems.OldValue,0) - Nz(Me.NumberofItems,0)
  7.   Debug.Print strSQL
  8.   Call DoCmd.RunSQL(strSQL)
  9. End Sub
Mar 12 '07 #38

P: 45
"You are about to update 36 rows. Once you click Yes, you can't use the undo command to reverse the changes. Are you sure you want to update these records?"

Click Yes- "Microsoft Access can't update all the records in the update query
Microsoft Access didn't update 0 fields(s) due to a type conversion failure, 0 record(s) due to key violations, 0 record(s) due to lock violations, and 57 record(s) due to validation rule violations" Do you want to continue running this type of action query anyway? To ignore the errors and run the query click yes. For an explanation of the causes of the violations click help."

So I removed the only validation rule I could find- which was on the 'NumberInStock' column of the tblItems table. This removed the second error message. I then tested it and the NumberInStock went down accordingly, but so did the NumberInStock for every other item- if i entered '1' in the NumberOfItems field then theNumberInStock field for all the items decreased by 1. This worries me because that's a problem with my relationship structure somehow??
Mar 12 '07 #39

NeoPa
Expert Mod 15k+
P: 31,186
  1. What was the validation rule you found and removed?
  2. I blame the original poster who I stole the code from. It doesn't include the WHERE clause :(
I'll have to look that up and provide a replacement.
Just to be clear - the intention was to update one record only.
Last point, would you like the warning message to disappear too?
Mar 12 '07 #40

NeoPa
Expert Mod 15k+
P: 31,186
This code assumes you have a TextBox (or other) control on your form called ItemID.
Expand|Select|Wrap|Line Numbers
  1. Private Sub NumberofItems_BeforeUpdate(Cancel As Integer)
  2.   Dim strSQL As String
  3.  
  4.   strSQL = "UPDATE tblItem " & _
  5.            "SET NumberInStock = Nz(NumberInStock,0)+" & _
  6.            Nz(Me.NumberofItems.OldValue,0) - Nz(Me.NumberofItems,0) & " " & _
  7.            "WHERE ItemID=" & Me.ItemID
  8.   Debug.Print strSQL
  9.   Call DoCmd.RunSQL(strSQL)
  10. End Sub
Mar 12 '07 #41

P: 45
1. NumberInStock>=0, guess I should put it back when the code works properly.
2. Thanks, that would solve the problem of the decrement effecting every item?

Yes that was the intention.
Removing the warning message would be best thanks.
Mar 12 '07 #42

NeoPa
Expert Mod 15k+
P: 31,186
Sorry, forgot about the warning messages.
This code should also get around the Validation Rule. There were probably some empty fields in the table which, as Nulls, would have caused it to fail.
Expand|Select|Wrap|Line Numbers
  1. Private Sub NumberofItems_BeforeUpdate(Cancel As Integer)
  2.   Dim strSQL As String
  3.  
  4.   strSQL = "UPDATE tblItem " & _
  5.            "SET NumberInStock = Nz(NumberInStock,0)+" & _
  6.            Nz(Me.NumberofItems.OldValue,0) - Nz(Me.NumberofItems,0) & " " & _
  7.            "WHERE ItemID=" & Me.ItemID
  8.   Debug.Print strSQL
  9.   Call DoCmd.SetWarnings(False)
  10.   Call DoCmd.RunSQL(strSQL)
  11.   Call DoCmd.SetWarnings(True)
  12. End Sub
Mar 12 '07 #43

P: 45
Thanks that works fine. :) Sorry it took me a while to reply.
Mar 19 '07 #44

NeoPa
Expert Mod 15k+
P: 31,186
Not a problem.
Just pleased it all worked in the end :)
Mar 19 '07 #45

Post your reply

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