go to the new record... 2

[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

 

[RoyVidar]

Somewhere in there (before the append?), try:

[tt]docmd.gotorecord,,acnewrec[/tt]

Roy-Vidar

 

[daniels012]

Got a runtime error (5)
at this line

a = Split([SampleNumber], "-")

Michael

 

[RoyVidar]

Just tested those copy/append thingies for the first time. On my setup, after the pasteappend, I'm located on the new (newly appended) record. There shouldn't be any need to move...

In the initial code, you're using three ways of addressing one control, try just the following (just a rewrite, but with declaration of the array):

[tt] dim strNo() as string
strNo = split(Me!SampleNumber.value, "-")
strNo(1) = val(strNo(1))+1
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend
Me!SampleNumber.value = Join(strNo, "-")[/tt]

If you mean you don't visually see the correct record, you could try moving to the last record

[tt]docmd.gotorecord,,aclast[/tt]

Roy-Vidar

 

[daniels012]

Again,
I am getting runtime error "5"

strNo = Split(Me!SampleNumber.Value, "-")

The code used to work just fine???
Now all the sudden I'm getting this runtime error??

 

[RoyVidar]

If you have code that worked fine, then use it.

What is the value of Me!SampleNumber when it's run (use msgbox me!samplenumber or debug.print me!samplenumber)

Could perhaps use split(cstr(me!samplenumber.value),"-")?

Is something different now from when it was working? Different machine, version, os...? Is there a problem with missing references? (in VBE - Tools | References - check for any marked as missing)

Roy-Vidar

 

[daniels012]

The only difference is when I tried to add the code you suggested. Then I removed it and... no luck?

I'm not sure what is up?

 

[daniels012]

This is the input mask for Sample Number if that helps?

>LLL\-000;0;_

 

[CloudRise]

If you display a unique record key on the form and then set the focus on it you can then use the findrecord command to go to that record. Your example has you going to a control not a record.

Hope this helps !

 

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

Have you tried to replace this:
dim strNo() as string
By this:
Dim strNo As Variant

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

 

[PHV]

Another way:
[SampleNumber]=Left([SampleNumber],InStr([SampleNumber],"-")) & (1+Val(Mid([SampleNumber],InStr([SampleNumber],"-")+1)))

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

 

[daniels012]

Would I add this as a function in the code? And Where?

Michael

 

[daniels012]

I tried Dim strNo As Variant with no luck

 

[PHV]

Can you please post your actual code for the Click event procedure ?

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

 

[daniels012]

Here it is :

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

 

[PHV]

And which line is highlighted when you get the error "5" ?
If the Split line, what is displayed when you let the mouse pointer on [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]

This is the area that is highlighted:
a = Split([SampleNumber], "-")
When I have the mouse over "a" it says empty
When I have the mouse over "SampleNumber" is says WIL-215

WIL-215 is the previous Sample number

 

[PHV]

Quite strange ... (all seems normal)
You may try to replace this:
DoCmd.GoToControl "SampleNumber"
a = Split([SampleNumber], "-")
a(1) = a(1) + 1
[SampleNumber] = Join(a, "-")
By this:
Me!SampleNumber.SetFocus
Me!SampleNumber=Left(Me!SampleNumber,InStr(Me!SampleNumber,"-")) & (1+Val(Mid(Me!SampleNumber,InStr(Me!SampleNumber,"-")+1)))

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

 

[daniels012]

Yes!
Thank You so much. This seems to be working very well. I even added another line at the end

DoCmd.GotoControl "SpecialInstructions"

It seems to be doing fine!!

Thank You for your patience... I am very new when it comes to coding. And you are very kind

Michael