469,917 Members | 1,834 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,917 developers. It's quick & easy.

Button in Access 2016 to add +1 to last/highest value in a field

3 2Bits
Hi and thank you in advance everyone!
I want to add +1 to a value in a field by clicking on a button in a form.

My table called "Requests" has a field called "Request#". I want to add a button in the form called "TblRequests" so that when it is clicked, it will add +1 to the highest number in the field.

I am using this VBA code (please see below) that works very well but I have to double click on the field in order to add 1 to the greatest value.

Instead, I need to have a button that will do the same thing. Any help if so much appreciated.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Request__DblClick(Cancel As Integer)
  2. Me.Request_ = Nz(DMax("Val([Request#])", "Requests"), 0) + 1
  3. End Sub
Thanks
Jun 25 '20 #1

✓ answered by cactusdata

How about:

Expand|Select|Wrap|Line Numbers
  1. Private Sub AddOneButton_Click()
  2.  
  3.     Me.Request_ = Nz(DMax("Val([Request#])", "Requests"), 0) + 1
  4.  
  5. End Sub

9 2182
cactusdata
188 Expert 128KB
How about:

Expand|Select|Wrap|Line Numbers
  1. Private Sub AddOneButton_Click()
  2.  
  3.     Me.Request_ = Nz(DMax("Val([Request#])", "Requests"), 0) + 1
  4.  
  5. End Sub
Jun 26 '20 #2
NeoPa
32,231 Expert Mod 16PB
Hi Alocin.
CactusData's suggestion will work perfectly except for the name of the CommandButton. If it's important to name it as [TblRequests] then change the first line to :
Expand|Select|Wrap|Line Numbers
  1. Private Sub TblRequests_Click()
However, I'd advise you to use a different name as most people will read that and assume it refers to a Table object rather than a CommandButton. Usually CommandButtons are named starting with "cmd". This is not required but is generally helpful for when others are looking at your project.

It's interesting that you have the internal code logic done perfectly with just a little help needed on how to link it to a CommandButton click. Usually the internal code bit is where people struggle the most. Nice work :-)

NB. I've set the Best Answer for you as I can see it directly answers your question.
Jun 26 '20 #3
Alocin
3 2Bits
Thanks you guys, but not sure why nothing is happening after entering new code.
Here is what i have:
Table is "Requests"
Table field is "Request#"
Form Field I want the number to change is "Request#"
Button I want to click on is "cmdAddNumber" (changed name - thanks NeoPa)

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdAddNumber_Click()
  2.  
  3.     Me.Request_ = Nz(DMax("Val([Request#])", "Requests"), 0) + 1
  4.  
  5. End Sub

thank you for any help
Jun 26 '20 #4
cactusdata
188 Expert 128KB
You wrote Me.Request_ but if it now is Me.Request#, then change it accordingly.
Jun 26 '20 #5
NeoPa
32,231 Expert Mod 16PB
CactusData:
You wrote Me.Request_ but if it now is Me.Request#, then change it accordingly.
I believe the # character is proscribed (Not allowed.) in VBA names so that when it's used VBA transcribes it as an underscore (_).
@Alocin.
You may want to review that too. A hash (#) should be avoided in your table & field names where possible.

Before you do that though, check the properties pane when the new CommandButton (cmdAddNumber) is selected on your form. Is there an entry by On Click that says [Event Procedure]? If not then the code won't be triggered. To fix this go to the associated module, Cut all the code into the Clipboard, then Paste it all in again. This should set the property for you.
Jun 26 '20 #6
cactusdata
188 Expert 128KB
True. Try with:

Expand|Select|Wrap|Line Numbers
  1. Me.[Request#].Value = Nz(DMax("Val([Request#])", "Requests"), 0) + 1
Jun 26 '20 #7
NeoPa
32,231 Expert Mod 16PB
Actually Alocin, what you refer to as a form field is actually a Control rather than a Field. This would also benefit from being named accordingly. As a TextBox control (I suspect) it could be named something like [txtRequest], regardless of the name used in the table for the field.

So, assuming we were also to rename the Table's Field by removing the hash (#) char from the end, you'd end up with the following code :
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdAddNumber_Click()
  2.     Me.txtRequest = Nz(DMax("Val([Request])", "Requests"), 0) + 1
  3. End Sub
Jun 26 '20 #8
Alocin
3 2Bits
Omg you guys I am going to pull my hair! I have spent so much time on one little but obviously big issue to me.

Ok, the suggested code didn't make a difference. Nothing happens when I run it! Sorry, I am sure I am forgetting something.
So to be sure I still had an option, I tried putting in the old (above) double click code on the field "Request#" (as it was working before), now that one is not working either. I restarted Access and nothing changed. I even rebooted my machine, nothing changed. What have I done wrong because still neither is working now. So, I deleted the form and recreated the form using the wizard, as I did on my first form.

The table is still "Requests". The field in the table is still the same "Request#". The new form is still called the same "TblRequest". In form design mode, I select the control "Request#" (NeoPa, thanks for teaching me the proper terminology), I jump over to view the code. With the new form when in VBA I have a new clean object with no code. I paste the below code (see below full code), save the form. Then open the form and I get an error "Run-time error '3464': Data type mismatch in criteria expression". I tried also using code with "...Me.[Request#].Value = Nz(DMax("Val([Request#])", "Requests"), 0) + 1" with no difference in the outcome.


Expand|Select|Wrap|Line Numbers
  1. Private Sub Request__DblClick(Cancel As Integer)
  2.  
  3. Me.Request_ = Nz(DMax("Val([Request#])", "Requests"), 0) + 1
  4.  
  5. End Sub
Still please help!
Thank you.
Jun 26 '20 #9
NeoPa
32,231 Expert Mod 16PB
There is nothing obviously wrong with your code in the context you've described to us. I imagine something is not quite as portrayed but let's see if we can work out what.

Is the [Requests].[Request#] field numeric or is it text (string)?
Is there data in the table already?
If so, what is the largest value currently in the table?
Jun 26 '20 #10

Post your reply

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

Similar topics

6 posts views Thread by Ada | last post: by
3 posts views Thread by =?Utf-8?B?cm9kY2hhcg==?= | last post: by
1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.