VBA to Add 1 to Highest Number

[QandAdam]

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

 

[welshgasman]

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.

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

 

[Akuini]

CompNo is just number or has letter on it?

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