Help me subtract value in table using textfield and button

[zyper]

Hello I'm a noob in vba and access so need help.
I have a table in wich I have stored numbers and i want to subtract them using vba.

example

Table1
ID number
1 20
2 40

textbox(ID):2 texbox(value):5 button(subtract)

So when i click button i want to subtract record with id 2 (40-5=35) and i want the new value to rewrite the previous one in the table.

result:
Table1
ID number
1 20
2 35
I want to know if it's posible to do this in vba.

 

[CJ_London]

In VBA you would write in the onclick event for your button

Private Sub myButton_Click()
 
    Currentdb.Execute("UPDATE Table1 SET [Number] = [Number]-" & texbox(Value) & " WHERE Table1.ID=" & [Textbox(ID)])
 
End Sub

 

[zyper]

CJ_London thank you very much that helped a loooot

 

[Boneless117]

Hi Guys,

Having a similar issues here.

This is my situation.

I have created a form that will add to a table when something is sold.

That is working all good and fine, but it would be good if after I click 'SAVE' it will find said item (by 'NAME') in another table then subtract the 'AMOUNT' by 1.

------------------------------------------------

Hardware: (Table)
Name | Ethernet
Amount | 46

SoldHardware: (Form)
Name | Combo box (Lookup by Hardware Table)
...
...
...

Save Button.

>

Lookup NAME in Hardware Table then subtract 1 from Amount

------------------------------------------------

Any ideas on how I can do this?
New to VB as well.

Thanks

 

[CJ_London]

You would adapt the same code as posted, changing names to suit your tables and fields.

Note, I hope Name is not a real name for your field - it is a reserved world and will cause problems.

Also, you should have a unique ID for your hardware table - otherwise what happens if you change the name?

 

[Boneless117]

Thanks for your reply,

Yeah its not name, its HardwareName Just did not want to confuse anyone.

The unique ID is the hardware name, as this is a record keeping database there will be no need to change the name.

I have had a look at your example, and I'm still not able to get it right.

The big difference that I have is that I'm not actually creating a text box that says "Enter Number to Subtract"

What I need is 'Once I click on Save it will remove 1 from the inventory'

This is what I have at the moment.

CurrentDb.Execute ("UPDATE NonSerialHardware SET Amount = Amount -" & 1 & " WHERE NonSerialHardware.HardwareName=" & [HardwareName])

Any help would be good

Thanks
Chris

 

[CJ_London]

Yeah its not name, its HardwareName Just did not want to confuse anyone.

Unfortunately it does confuse because if you had used Name, that could be a reason why it wasn't working, so advisors waste their time suggesting solutions that aren't relevant.

Anyway since NonSerialHardware is text the criteria needs use single quotation marks as below. There is also no need to separate out the 1. I'm assuming you have a field on your form called HardwareName.

Put this in the afterupdate event of your form

CurrentDb.Execute ("UPDATE NonSerialHardware 
SET Amount = Amount - 1 
WHERE NonSerialHardware ='" & me.HardwareName & "'")

 

[Boneless117]

This worked a treat!

Thanks for your help mate!

Next on my list is to expand my knowledge of VB

P.S sorry for the confusion :S

Thanks
Chris

 

[CJ_London]

pleased to help