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

[potassium flower]

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?

[Rabbit]

That depends on what you want to do with that number. Are you trying to store it in a table?

[potassium flower]

'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.

[Rabbit]

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.

[potassium flower]

( 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?

[Rabbit]

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.

[potassium flower]

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-

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.

[potassium flower]

Does anyone else have any ideas? Help much appreciated!

[Rabbit]

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-

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.

[Rabbit]

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

[NeoPa]

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.

[potassium flower]

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.

[potassium flower]

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?

[Rabbit]

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.

[NeoPa]

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.

[potassium flower]

I don't understand, what other information should I provide in addition to post #12?

[NeoPa]

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.

[potassium flower]

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

=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 :(

[Rabbit]

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

=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.

[potassium flower]

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.

[Rabbit]

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.

[potassium flower]

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

[Rabbit]

Private Sub Number_Ordered_BeforeUpdate(Cancel As Integer)
   DoCmd.RunSQL "UPDATE Tbl_InStock SET InStockField = " & _
      DLookUp("InStockField", "Tbl_InStock", "Primary_Key = " & _
      Me.Foreign_Key) + DLookUp("AmountOrdered" & _
      , "Tbl_Orders", "Foreign_Key = " & Me.Foreign_Key) - Number_Ordered
End Sub

[NeoPa]

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...

Private Sub Number_Ordered_BeforeUpdate(Cancel As Integer)
  DoCmd.RunSQL "UPDATE Tbl_InStock " & _
               "SET InStockField = InStockField+" & _
               Me.Number_Ordered.OldValue - Me.Number_Ordered
End Sub

[potassium flower]

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...

Private Sub Number_Ordered_BeforeUpdate(Cancel As Integer)
  DoCmd.RunSQL "UPDATE Tbl_InStock " & _
               "SET InStockField = InStockField+" & _
               Me.Number_Ordered.OldValue - Me.Number_Ordered
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

Private Sub NumberofItems_BeforeUpdate(Cancel As Integer)
DoCmd.RunSQL "UPDATE tblItem " & _
               "SET NumberInStock = NumberInStock+" & _
               Me.NumberofItems.OldValue - Me.NumberofItems
 
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 :)

[NeoPa]

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.

Private Sub NumberofItems_BeforeUpdate(Cancel As Integer)
  Dim strSQL As String
 
  strSQL = "UPDATE tblItem " & _
           "SET NumberInStock = NumberInStock+" & _
           Me.NumberofItems.OldValue - Me.NumberofItems
  Debug.Print strSQL
  Call DoCmd.RunSQL(strSQL)
End Sub

[potassium flower]

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.

[NeoPa]

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.

[potassium flower]

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.

[NeoPa]

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.

[potassium flower]

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-

SELECT tblOrderDetail.OrderID,
       tblOrderDetail.ItemID,
       tblOrderDetail.Timing,
       tblOrderDetail.[Number of Items],
       tblItem.Cost,
       tblItem.Notes
FROM tblItem INNER JOIN tblOrderDetail
  ON tblItem.ItemID=tblOrderDetail.ItemID;

Mainform-

tblOrder

3.

NumberofItems

[NeoPa]

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 ;)

[NeoPa]

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-

SELECT tblOrderDetail.OrderID,
       tblOrderDetail.ItemID,
       tblOrderDetail.Timing,
       tblOrderDetail.[Number of Items],
       tblItem.Cost,
       tblItem.Notes
FROM tblItem INNER JOIN tblOrderDetail
  ON tblItem.ItemID=tblOrderDetail.ItemID;

Mainform-

tblOrder

3.

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.

[potassium flower]

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+'

[NeoPa]

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?

[potassium flower]

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-

Call DoCmd.RunSQL(strSQL)

Which is part of-

Private Sub NumberofItems_BeforeUpdate(Cancel As Integer)
  Dim strSQL As String
 
  strSQL = "UPDATE tblItem " & _
           "SET NumberInStock = NumberInStock+" & _
           Me.NumberofItems.OldValue - Me.NumberofItems
  Debug.Print strSQL
  Call DoCmd.RunSQL(strSQL)
End Sub

[NeoPa]

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).

[NeoPa]

...But try this version first :

Private Sub NumberofItems_BeforeUpdate(Cancel As Integer)
  Dim strSQL As String
 
  strSQL = "UPDATE tblItem " & _
           "SET NumberInStock = NumberInStock+" & _
           Nz(Me.NumberofItems.OldValue,0) - Nz(Me.NumberofItems,0)
  Debug.Print strSQL
  Call DoCmd.RunSQL(strSQL)
End Sub

[potassium flower]

"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??

[NeoPa]

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?

[NeoPa]

This code assumes you have a TextBox (or other) control on your form called ItemID.

Private Sub NumberofItems_BeforeUpdate(Cancel As Integer)
  Dim strSQL As String
 
  strSQL = "UPDATE tblItem " & _
           "SET NumberInStock = Nz(NumberInStock,0)+" & _
           Nz(Me.NumberofItems.OldValue,0) - Nz(Me.NumberofItems,0) & " " & _
           "WHERE ItemID=" & Me.ItemID
  Debug.Print strSQL
  Call DoCmd.RunSQL(strSQL)
End Sub

[potassium flower]

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.

[NeoPa]

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.

Private Sub NumberofItems_BeforeUpdate(Cancel As Integer)
  Dim strSQL As String
 
  strSQL = "UPDATE tblItem " & _
           "SET NumberInStock = Nz(NumberInStock,0)+" & _
           Nz(Me.NumberofItems.OldValue,0) - Nz(Me.NumberofItems,0) & " " & _
           "WHERE ItemID=" & Me.ItemID
  Debug.Print strSQL
  Call DoCmd.SetWarnings(False)
  Call DoCmd.RunSQL(strSQL)
  Call DoCmd.SetWarnings(True)
End Sub

[potassium flower]

Thanks that works fine. :) Sorry it took me a while to reply.

[NeoPa]

Not a problem.
Just pleased it all worked in the end :)