Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 205
  • Last Modified:

Adding 1 to a text box

I have a text field that uses numbers, like 1234, I want to be able to add 1 to the last number and
place it in a table.

I start with; 1234
I add 1: 1235
I add another:1236 and so on.
then this would b e placed in a table called fun one. in a field called counter1
I cannot use auto number because it is used as the Id field.

1 Solution
Can you explain what the difference would be doing this instead of using an autonumber- it's not obvious from your post.
Rey Obrero (Capricorn1)Commented:
you can use

= Format(Nz(DMax("Format([TextField],""0000"")", "table1"), 0) + 1, "0000")
garycoon7Author Commented:
I would be starting at 11-0394, and where would this solution be placed?
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Rey Obrero (Capricorn1)Commented:
yuo will need a function to achieve that, place this codes in a regular module

Function getNextNumber() As String
Dim intMaxNumber, curVal, newVal
curVal = Nz(DMax("TextField", "Table1"),0)

If Len(curVal & "") > 0 Then
intMaxNumber = Val(Mid(curVal, 4))

intMaxNumber = Format(CStr(intMaxNumber + 1), "0000")
newVal = Left(curVal, 3) & intMaxNumber
newVal = "11-" & "0001"
End If
getNextNumber = newVal
End Function

Rey Obrero (Capricorn1)Commented:

to use the function, you will place something like this, when you add a record using a form

private sub form_current()
if me.newrecord then
end if

end sub
garycoon7Author Commented:
Thanks for the help, I will work with it to make it work.
>I cannot use auto number because it is used as the Id field.<

Please be aware that getting a new number this way can cause you problems is a multi user environment.
For instance: User 1 opens a new record and assigns the next number (1234+1) and then User 2 opens a new record and does the same, then user 1 saves the new record. User 2 will encounter an error because he has the same number...

garycoon7Author Commented:
Thanks JVWC, your right and in this case the administrator adds the records.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now