Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
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 now!
  • 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 1Helpful Member!(3) 

marcfl (TechnicalUser) (OP)
11 Jun 08 10:22
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.
MajP (TechnicalUser)
11 Jun 08 10:54
marcfl (TechnicalUser) (OP)
11 Jun 08 11:06
ah.... sorry i did look must have missed it :s

Thanks
MajP (TechnicalUser)
11 Jun 08 11:19
No problem. There are a lot of FAQs, after a while you remember what is out there.
Helpful Member!(2)  TheAceMan1 (Programmer)
11 Jun 08 13:00
How are ya marcfl . . .

You can use DMax in conjunction with Left,Right, and Val functions . . .

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?
                 

TheAceMan1 (Programmer)
11 Jun 08 22:18
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?
                 

ProgramError (Programmer)
12 Jun 08 7:39
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?
 

Tightpants (TechnicalUser)
19 Jun 08 18:55
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?   
Helpful Member!  PHV (MIS)
19 Jun 08 19:01
TheAceMan1 (Programmer)
19 Jun 08 22:44

So what happened to marcfl? . . .

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?
                 

marcfl (TechnicalUser) (OP)
23 Jun 08 4:29
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.
marcfl (TechnicalUser) (OP)
23 Jun 08 4:43
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.  
marcfl (TechnicalUser) (OP)
23 Jun 08 4:55
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 :)
marcfl (TechnicalUser) (OP)
23 Jun 08 7:48
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.
marcfl (TechnicalUser) (OP)
23 Jun 08 8:15
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
TheAceMan1 (Programmer)
23 Jun 08 8:37

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?
                 

marcfl (TechnicalUser) (OP)
23 Jun 08 8:55
nice one AceMan1 worked perfectly! What would i do without you guys!
Tightpants (TechnicalUser)
28 Jun 08 17:41
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