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

Use a button to update a field in table based on single record in form?

P: 8
Hello,

I have a form on which user enters information about what item he bought. If he chooses ID for this item which is already in warehouse, system must automatically add the amount to the one which is already in warehouse. But the problem is that in my code the system adds all the values depending on ID match but not the current record. I mean, if the items called "Box" with ID "12" and amount of 5 was bought on tuesday and the same item is in the other table called WAREHOUSE the system add 5 boxes and that ok, but the next day the same item with the same id was bought and the system still uses the amount of tuesdays and this days when it adds values in warehouse table.

I do believe that the main question is " How to connect the exact record on curent view of the form to table not all the records that exist under same ID?"
Apr 19 '10 #1

✓ answered by TheSmileyCoder

What you could do is to add to the forms AfterUpdate a statement like:
Expand|Select|Wrap|Line Numbers
  1. dim strSQL as string
  2. strSQL="UPDATE tbl_WareHouse SET lngQuant=lngQuant+ " & me.tb_Amount & " WHERE ID_Item=" & me.tb_IDItem
  3. docmd.setwarnings(false)
  4. docmd.runsql strSQL
  5. docmd.setwarnings(true)
  6.  
Here I have assumed a couple of things:
The amount of an item is stored in lngQuant in the table tbl_WareHouse
Your form has a textbox with the amount called tb_Amount
The ID of the item is in a textbox called tb_IDITEM.

Note that this only works if the warehouse allready contains a record for that item (Otherwise there is nothing to update). You can of course make a check in code whether there is a record with that ID, and act accordingly.

Let me/Us know if you need further assistance.

Share this Question
Share on Google+
3 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
What you could do is to add to the forms AfterUpdate a statement like:
Expand|Select|Wrap|Line Numbers
  1. dim strSQL as string
  2. strSQL="UPDATE tbl_WareHouse SET lngQuant=lngQuant+ " & me.tb_Amount & " WHERE ID_Item=" & me.tb_IDItem
  3. docmd.setwarnings(false)
  4. docmd.runsql strSQL
  5. docmd.setwarnings(true)
  6.  
Here I have assumed a couple of things:
The amount of an item is stored in lngQuant in the table tbl_WareHouse
Your form has a textbox with the amount called tb_Amount
The ID of the item is in a textbox called tb_IDITEM.

Note that this only works if the warehouse allready contains a record for that item (Otherwise there is nothing to update). You can of course make a check in code whether there is a record with that ID, and act accordingly.

Let me/Us know if you need further assistance.
Apr 19 '10 #2

P: 8
Thank you very much. It seems actually that your code is working perfectly now.
Apr 19 '10 #3

TheSmileyCoder
Expert Mod 100+
P: 2,321
Thats good to hear. Thank you for remembering to reply.
Welcome to Bytes.
Apr 19 '10 #4

Post your reply

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