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

[Alocin]

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.

Private Sub Request__DblClick(Cancel As Integer)
Me.Request_ = Nz(DMax("Val([Request#])", "Requests"), 0) + 1
End Sub

Thanks

[cactusdata]

How about:

Private Sub AddOneButton_Click()
 
    Me.Request_ = Nz(DMax("Val([Request#])", "Requests"), 0) + 1
 
End Sub

[NeoPa]

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 :

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.

[Alocin]

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)

Private Sub cmdAddNumber_Click()
 
    Me.Request_ = Nz(DMax("Val([Request#])", "Requests"), 0) + 1
 
End Sub

thank you for any help

[cactusdata]

You wrote Me.Request_ but if it now is Me.Request#, then change it accordingly.

[NeoPa]

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.

[cactusdata]

True. Try with:

Me.[Request#].Value = Nz(DMax("Val([Request#])", "Requests"), 0) + 1

[NeoPa]

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 :

Private Sub cmdAddNumber_Click()
    Me.txtRequest = Nz(DMax("Val([Request])", "Requests"), 0) + 1
End Sub

[Alocin]

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.

Private Sub Request__DblClick(Cancel As Integer)
 
Me.Request_ = Nz(DMax("Val([Request#])", "Requests"), 0) + 1
 
End Sub

Still please help!
Thank you.

[NeoPa]

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?