Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Increment a text field on a form (Access 2000)

    In a form, I need a way to increment a text field to the next number when I tab into that field. My scenario is this:

    I am using a form to fill in purchase orders by customer. I have a field called PurchaseOrderNo with the datatype of text...and the format of PO-0000. When I move to this field I want it to automatically see I am entering a new record and insert PO-1001 into the field.

    I found this code from a previous request, where HansV answered with the code below, but it didn't work for me...hmmm post 350694

    I found this code:

    Private Sub txtSomething_KeyDown(KeyCode As Integer, Shift As Integer)
    Select Case KeyCode
    Case vbKeyAdd
    txtSomething = Nz(txtSomething, 0) + 1
    KeyCode = 0
    Case vbKeySubtract
    txtSomething = Nz(txtSomething, 0) - 1
    KeyCode = 0
    End Select
    End Sub

    And it would seem it's what I need, however, I don't see how it works...I did what HansV said in the original answer to someone else with this code that he gave, but it isn't doing anything...

    I have also tried adding this:

    DMax("tblOrders","[PurchaseOrderNumber]")+1

    To the default line of the text field, but am sure DMAX would deal with dates...what could i use in place of DMAX?
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Increment a text field on a form (Access 2000)

    The code you mention has nothing to do with incrementing a number for a new record. The Lounger who asked for this code wanted to be able to increment or decrement a number (not a text value) by pressing the plus or minus key.

    You can do this in the Before Update event of the form:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim strMax As String
    Dim lngNewNum As Long
    If Me.NewRecord = True Then
    strMax = Nz(DMax("PurchaseOrderNo", "TableName"), "PO-0000")
    lngNewNum = CLng(Right(strMax, 4)) + 1
    Me.PurchaseOrderNo = "PO-" & Format(lngNewNum, "0000")
    End If
    End Sub

    Replace TableName with the name of your orders table.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Increment a text field on a form (Access 2000)

    Ok,

    I put this code into the FORMS Before update event, and it doesn't do nothing, any ideas?


    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim strMax As String
    Dim lngNewNum As Long
    If Me.NewRecord = True Then
    strMax = Nz(DMax("PurchaseOrderNumber", "tblOrders"), "PO-0000")
    lngNewNum = CLng(Right(strMax, 4)) + 1
    Me.PurchaseOrderNo = "PO-" & Format(lngNewNum, "0000")
    End If
    End Sub
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  4. #4
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Increment a text field on a form (Access 2000)

    Will DMax work on a field which is text and actually has text in it?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Increment a text field on a form (Access 2000)

    The code will run after you have entered data in a new record, and move to another record. If you would like it to be executed when you start entering data, put the code in the Before Insert event of the form. This can be dangerous, however: if two users start entering a new record at roughly the same time, they will get the same number. The chance of this is much smaller if you use the Before Update event.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Increment a text field on a form (Access 2000)

    Evidently not... hmmm
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Increment a text field on a form (Access 2000)

    Thank you Hans will try this right now.
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Increment a text field on a form (Access 2000)

    Yes, it will (I tested the code before posting it)

    For a text field, DMax will use alphanumerc sort order; in this case it will work out OK.
    It would be a problem if the values were PO-1, PO2, ..., PO-10, PO-11 etc., for then the alphanumeric order would be PO-1, PO-10, PO-11, ..., PO-2 etc.

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Increment a text field on a form (Access 2000)

    Your an absolute genious....Thank you HANSV
    I give you a sharp salute sir!!!
    <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

    As always, always grateful
    NMP
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  10. #10
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Increment a text field on a form (Access 2000)

    Sorry about the "evidently not" statement on DMAX....nothing personal

    Was just something I shot out there, and should have kept mouth shut.

    NMP
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Increment a text field on a form (Access 2000)

    It's OK, I wasn't offended.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •