INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Code to increase a field value by 1

Code to increase a field value by 1

(OP)
Hi,

I've been trying to write some code to increase a field value by 1 each time a command button on my form is clicked.

Basically my company uses "Job Numbers" for every order they get, if the job is based in woking the number will be WWO-0001 and if it's based at Gatwick then the number will be GWO-0001.  I want the command button to look up the last job number used and increment it by 1, unfortunately im not sure how to go about this. At the moment im using this code with a combo box.:

 Private Sub JobLocation_AfterUpdate()

    If JobLocation = "Gatwick" Then
        
        Me!JobNo = "GWO-"
                  
        Else
                  
        Me!JobNo = "WWO-"
    
    End If

End Sub

The problem with this is that the user has to enter the actual number part themselves.  I created a command button which displays a list of the Job numbers from the site selected, however my manager is still not satisfied. Can anyone help me out?

Cheers,

Marc.

RE: Code to increase a field value by 1

(OP)
ah.... sorry i did look must have missed it :s

Thanks

RE: Code to increase a field value by 1

No problem. There are a lot of FAQs, after a while you remember what is out there.

RE: Code to increase a field value by 1

marcfl . . .

If your having trouble, try the following. It sets the DefaultValue for JobNo to the next increment for Working or Gatwick. The values will not increment from the default unless you actually save a record. This allows the user to back out of the record without using the new value. This is all under the assumption you want to increment for new records.

CODE

   Dim CBx As ComboBox, Prefix As String, Cri As String
   Dim NextNum As Long, DQ As String
   
   Set CBx = Me!JobLocation
   DQ = """"
   Prefix = Switch(CBx = "Gatwick", "GWO-", _
                   CBx = "Working", "WWO-")
   
   Cri = "Left([JobNo],4) = '" & Prefix & "'"
   NextNum = DMax("Val(Right([JobNo], 4))", "TableName", Cri) + 1
   
   NextNum = Format(NextNum, "0000")
   Me!JobNo.DefaultValue = DQ & Prefix & NextNum & DQ

   Set CBx = Nothing
Your Thoughts? . . .

See Ya! .  .  .  .  .  .

Be sure to see thread181-473997: How To Get Good Answers To Your Questions
Also FAQ181-2886: How can I maximize my chances of getting an answer?
                 

RE: Code to increase a field value by 1

Or even an update query...
 

Ian Mayor (UK)
Program Error
If people say I have bad breath, then why do they continue to ask me questions and expect me to answer them?
 

RE: Code to increase a field value by 1

I am trying to do something similar but without the combo box.  All of my "job numbers" are in the format JOB0001, JOB0002, JOB0003, etc.  

I have a form for adding new jobs and I would like the job number box on the form to look up the last job number used and increment it by 1.  

I cant seem to add the code to the default value field so where else should I add it?  Should I assign the code to a particular event instead?   

RE: Code to increase a field value by 1

(OP)
sorry i was away thursday and friday cause i had 2 seizures (ive got epilepsy) well i havent managed to make it work yet... but ill keep tryin today hopefully something will work! ill let you know what happens.

RE: Code to increase a field value by 1

(OP)
I tried your code AceMan1 and it gives the next record the right JobNo when i save it, but doesnt give the "current" record a JobNo the box just stays blank.  

RE: Code to increase a field value by 1

(OP)
ok and when i try to use Woking i get an error message saying "Invalid use of Null" with this highlighted:

Prefix = Switch(CBx = "Gatwick", "GWO-", _
                   CBx = "Working", "WWO-")
the Gatwick part works fine tho :)

RE: Code to increase a field value by 1

(OP)
haha ok yeah didnt work cause it's spelt woking not working :) however the jobno comes out without two 0's like this WWO-12 when it should be WWO-0012.

RE: Code to increase a field value by 1

(OP)
Private Sub JobLocation_AfterUpdate()

Dim CBx As ComboBox, Prefix As String, Cri As String
   Dim NextNum As Long, DQ As String
   
   Set CBx = Me!JobLocation
   DQ = """"
   Prefix = Switch(CBx = "Gatwick", "GWO-", _
                   CBx = "Woking", "WWO-")
   
   Cri = "Left([JobNo],4) = '" & Prefix & "'"
   NextNum = DMax("Val(Right([JobNo], 4))", "Orders", Cri) + 1
   
   NextNum = Format(NextNum, "0000")
   Me!JobNo.DefaultValue = DQ & Prefix & NextNum & DQ

   Set CBx = Nothing

End Sub

RE: Code to increase a field value by 1


There's a data type problem with:

CODE

Dim NextNum As Long
   '
   NextNum = Format(NextNum, "0000")
   '
Format returns a string!

Try the following instead:

CODE

   Dim CBx As ComboBox, Prefix As String, Cri As String
   Dim NextNum As Long, DQ As String
   
   Set CBx = Me!JobLocation
   DQ = """"
   Prefix = Switch(CBx = "Gatwick", "GWO-", _
                   CBx = "Woking", "WWO-")
   
   Cri = "Left([JobNo],4) = '" & Prefix & "'"
   NextNum = DMax("Val(Right([JobNo], 4))", "Orders", Cri) + 1
   Me!JobNo.DefaultValue = DQ & Prefix & Format(NextNum, "0000") & DQ

   Set CBx = Nothing

See Ya! .  .  .  .  .  .

Be sure to see thread181-473997: How To Get Good Answers To Your Questions
Also FAQ181-2886: How can I maximize my chances of getting an answer?
                 

RE: Code to increase a field value by 1

(OP)
nice one AceMan1 worked perfectly! What would i do without you guys!

RE: Code to increase a field value by 1

I managed to get mine working too so thanks also.  I used the code in a "On Enter" event without using a combo box.   

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close