VBA to Add 1 to Highest Number

QandAdam

New Member
Joined
Jan 12, 2019
Messages
30
Very complicated one here so I won't explain why I need this..

I want to create a button that when clicked a pop-up appears saying "Enter URN". When the user enters this info, the record with that URN is updated. It will update by adding a number to the 'CompNo' field which will be Maximum number currently in that field for all rows +1.

e.g. There's a record with a CompNo of 1002 (Which is the highest number currently). When the user enters the URN, the record that links to that URN is updated so the CompNo is changed from Null to 1003.

The easiest way of explaining is this:
Select [URN]
Update [CompNo] to Max([CompNo])+1
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
I'd probably use DMax. Here I update the date for record with ID = 1 with the max date plus 1 day in the same table.


Code:
UPDATE Transactions SET Transactions.TransactionDate = DMax("TransactionDate","Transactions") +1
WHERE (((Transactions.ID)=1));

Very complicated one here so I won't explain why I need this..

I want to create a button that when clicked a pop-up appears saying "Enter URN". When the user enters this info, the record with that URN is updated. It will update by adding a number to the 'CompNo' field which will be Maximum number currently in that field for all rows +1.

e.g. There's a record with a CompNo of 1002 (Which is the highest number currently). When the user enters the URN, the record that links to that URN is updated so the CompNo is changed from Null to 1003.

The easiest way of explaining is this:
Select [URN]
Update [CompNo] to Max([CompNo])+1
 
Last edited:
Upvote 0
CompNo is just number or has letter on it?

edit: sorry, just realized it's an access forum, which I know nothing about.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top