Code to increase a field value by 1 3

[marcfl]

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]

faq705-6727

 

[marcfl]

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

Thanks

 

[MajP]

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

 

[TheAceMan1]

How are ya marcfl . . .

You can use [blue]DMax[/blue] in conjunction with [blue]Left[/blue],[blue]Right[/blue], and [blue]Val[/blue] functions . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[TheAceMan1]

marcfl . . .

If your having trouble, try the following. It sets the [blue]DefaultValue[/blue] for [blue]JobNo[/blue] to the next increment for [blue]Working[/blue] or [blue]Gatwick[/blue]. 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. [purple]This is all under the assumption you want to increment for new records.[/purple]

[blue]   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))", "[purple][B][I]TableName[/I][/B][/purple]", Cri) + 1
   
   NextNum = Format(NextNum, "0000")
   Me!JobNo.DefaultValue = DQ & Prefix & NextNum & DQ

   Set CBx = Nothing[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[ProgramError]

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]

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?

 

[PHV]

I'd use the BeforeInsert event to calculate the new job number.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

So what happened to [blue]marcfl[/blue]? . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[marcfl]

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]

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]

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]

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.

 

[PHV]

What is YOUR actual code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[marcfl]

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]

There's a data type problem with:

[blue]Dim NextNum As Long
   '
   NextNum = Format(NextNum, "0000")
   '[/blue]

Format returns a string!

Try the following instead:

[blue]   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[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[marcfl]

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

 

[Tightpants]

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