Next Number with Text and Numbers 1

[daniels012]

I have a button I use to copy data to new form. It works great but I would like to add a number to a text field. Is this possible?

IE: The sample number is WIL-215. I have the code below so far: How can I make the next SampleNumber WIL-216 automatically?

Private Sub CopySample_Click()
Me!SampleNumber.SetFocus
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend
DoCmd.GoToControl "SampleNumber"
End Sub

Or is there a way?

Thank You
Michael

 

[TheAceMan1]

How are ya daniels012 . . . . .

Will the [blue]sample number[/blue] always contain the single dash ([purple]-[/purple])?

See Ya! . . . . . .

 

[daniels012]

Hello AceMan1!!
Yes,
There is always a dash. ASH-232 BLM-105 etc.

Michael

 

[PHV]

If ac2k or above:
...
DoCmd.GoToControl "SampleNumber"
a = Split([SampleNumber], "-")
a(1) = a(1) + 1
[SampleNumber] = Join(a, "-")
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[daniels012]

That worked GREAT!!
I wish I could do and know code as well as some of you!

Thank you so very MUCH!
Michael

 

[daniels012]

In this code It's not taking me to the new record. I see it add the data with the new number WIL-216. Then I start to edit the sheet and I find I am in record WIL-215. I can scroll and get to WIL-216, but sheet doesn't automatically go there? Any help would be great.

Private Sub CopySample_Click()
Me!SampleNumber.SetFocus
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend
DoCmd.GoToControl "SampleNumber"
a = Split([SampleNumber], "-")
a(1) = a(1) + 1
[SampleNumber] = Join(a, "-")
End Sub

I guess I need something to take me to the new record??

Thank You,
Michael

 

[daniels012]

OK
since the a= split does not work!
Is there another way to add the next number to the sample number? Sample Numbers are like: WIL-215 or STU-201 etc.
I need it to put the next number in if possible, ie:
WIL-216 or STU-202

Thank You,
Michael

 

[PHV]

since the a= split does not work
What happens ?
You said last week it worked great ...

 

[daniels012]

It was working great,
For some reason it gives me a runtime error "5"
on the first line a=split etc.

Michael

ps. I have another post that I have more info on what it is doing.

 

[PHV]

Have you tried to put a breakpoint on the Split line to see what is the value of [SampleNumber] ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[daniels012]

I don't know the process to do that? Breakpoint?

Michael