Adding 1 to a text box

Posted on 2011-05-10
Last Modified: 2012-05-11
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.

Question by:garycoon7
    LVL 77

    Expert Comment

    Can you explain what the difference would be doing this instead of using an autonumber- it's not obvious from your post.
    LVL 119

    Expert Comment

    by:Rey Obrero
    you can use

    = Format(Nz(DMax("Format([TextField],""0000"")", "table1"), 0) + 1, "0000")

    Author Comment

    I would be starting at 11-0394, and where would this solution be placed?
    LVL 119

    Expert Comment

    by:Rey Obrero
    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

    LVL 119

    Accepted Solution


    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

    Author Closing Comment

    Thanks for the help, I will work with it to make it work.
    LVL 6

    Expert Comment

    >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...


    Author Comment

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

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Suggested Solutions

    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now