473,599 Members | 3,074 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

3 New Member
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 "TblRequest s" 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
9 2595
cactusdata
214 Recognized Expert New Member
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,566 Recognized Expert Moderator MVP
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 New Member
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 "cmdAddNumb er" (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
214 Recognized Expert New Member
You wrote Me.Request_ but if it now is Me.Request#, then change it accordingly.
Jun 26 '20 #5
NeoPa
32,566 Recognized Expert Moderator MVP
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
214 Recognized Expert New Member
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,566 Recognized Expert Moderator MVP
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 New Member
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,566 Recognized Expert Moderator MVP
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

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

Similar topics

13
2280
by: shank | last post by:
How do you return the highest value in a recordset of maybe 100 records? Is it necessary to run 2 recordsets? I was hoping it was as simple as Max(), but no luck. thanks
6
24327
by: Ada | last post by:
hello folks, is there a way to retrieve the highest value in the ArrayList? let say i have a value in the array: {1, 4, 15, 3, 7} it should return a value 15 as the result. i've looked at the Sort Method ( ) but doesn't seem like it can do the job or am i missing something?
7
3348
by: Jan | last post by:
Hi there, Is there a fast way to get the highest value from an array? I've got the array strStorage(intCounter) I tried something but it all and's to nothing If someone good helpme, TIA
3
8803
by: =?Utf-8?B?cm9kY2hhcg==?= | last post by:
hey all, i have an int array and was just wondering if there is a way to get the highest value in the array? for instance, int myValues = new int { 0, 1, 2 } highest value is 2. thanks,
1
3768
by: Coll | last post by:
Hi - I'm trying to figure out how to append a record to a table and then open a form and display that record. My thought was to use the autonum primary key field (recordnum) and display the highest value (which would be the new record), but I'm not sure if that can be done. Any thought on displaying the record with the highest value in the recordnum field or another approach would be appreciated. Thanks!
1
3570
by: CD Tom | last post by:
I'm thinking about updating from Access 2007 to Access 2016 but have run into a problem. I can't find any place to compile my accdb to an accde. I've installed the office 2016 and can bring up my database with out any problems, but where is the make accde? Thanks for any help.
2
2277
by: Silver993 | last post by:
I have created Access 2016 Database with custom Icon. I canít figure out what I am doing wrong. Here is the problem: When I tested the database in my developing Computer, it opens with my custom Icon and everything is great as expected. But when I move it to another computer that does not have Access 2016 installed and run my Database in Run-time (Accde), everything work OK. But the only problem is that my Application custom Icon got replaced...
1
2483
by: Murphy | last post by:
I am gettig ready to upgrade from Windows XP / Office 2007 to Windows 10 / Office Professional 2016. Are Access 2007 databases compatible with or convertible to Access 2016?
8
2649
by: MS Access Probz | last post by:
Hi all, I'm new to this forum. My Access skills are above basic, but I seem to be having trouble getting to the next level regardless of how much time I invest in it. I'm using Access 2016. Here's the current issue I'm working on. I have two forms. One called Frm_AddSku, the other called Frm_AddProdSubCat. I know that Access has a built in form property called List Items Edit Form, but I've added some VBA in an On Not In List Event,...
1
1433
by: HalloweenWeed | last post by:
Good day I am new to the forum and still learning MS Access. I have developed a code to allow 'live' 'searh-on-the-fly' queries 'as you type.' However, the problem lies in that when doing a query you lose the focus of the control in use, and when the SetFocus is set back to the control it selects all the text typed so far, so the next typed letter overwrites all the previous. I have developed a workaround that deselects the text and moves the...
0
7992
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
7904
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8398
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
8051
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
5850
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Duprť who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
3940
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2414
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1505
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1250
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.