GoTo Error 3

[accessvbahelp]

Hello Everyone! How are you doing? I am very new at writing VBA code (first time ever!) Please be patient with me...

I need to create a section in my Private Sub where my approve button will only work when the Revision for each drawing includes "Rev" This is what I have and it is not working.

Dim strTempRev As String
Dim strDwgNumb As String
Dim strRev As String

'testing revision contents to verify the approvals are only done on drawings that are under revision
Rev = Forms("frmMain")!Revision.Value 'Gets revision letter from form
TempRev = Right(Rev, 3)
If TempRev = "Rev" Then   'checking that revision contains "rev"
        GoTo 30  'will continue through code
    Else
        MsgBox "This drawing is not under revision please verify the part number, Otherwise see the Engineering Manager."
        GoTo 57

Code to continue through the Approve Function is here
(removed to keep the post short, and not to bore you)

End If
End Sub

When the TempRev equals "Rev" I want the code to start from the beginning of the next section which is Line 30. When TempRev does not equal "Rev" I want a message box to appear, and when OK is clicked in the message box I want the code to stop or End Sub, which is line 57.

Now when I click the Approve button, I get a Compile Error: Label not defined and "GoTo 57" is highlighted.

What am I doing wrong? Is there a better way to create this?

Thanks for all of your help!!!

 

[Andrzejek]

Rev = Forms("frmMain")!Revision.Value 'Gets revision letter from form
TempRev = Right(Rev, 3)
If TempRev [blue]<>[/blue] "Rev" Then   'checking that revision contains "rev"
    MsgBox "This drawing is not under revision please verify the part number, Otherwise see the Engineering Manager."
[blue]    Exit Sub[/blue]
End If

Code to continue through the Approve Function is here
(removed to keep the post short, and not to bore you)

End Sub

If you use GoTo, you need to Go To a label in your code, so 30 or 57 are line numbers, not lables.
But, don't use GoTo's, use them only in On Error GoTo MyErrorHandler

Have fun.

---- Andy

 

[TheAceMan1]

How are ya accessvbahelp . . .

A label marker is a name plus a colon on the far left of the VBA window. Note labels in [purple]purple[/purple] below:

[blue]   If TempRev = "Rev" Then   'checking that revision contains "rev"
      GoTo [purple][b]RevOK[/b][/purple]  'will continue through code
   Else
      MsgBox "This drawing is not under revision please verify the part number, Otherwise see the Engineering Manager."
      GoTo [purple][b]RevNotOK[/b][/purple]
   End If


[purple][b]RevOK[/b][/purple]:
   [green]'
   'Code for RevOK
   '[/green]
   Exit Sub

[purple][b]RevNotOK[/b][/purple]:
   [green]'
   'Code for RevNotOK
   '[/green]

End Sub[/blue]

As mentioned by [blue]Andrzejek[/blue] ... don't use GoTo's except for error handlers. Other than that its not recommended for structured VBA code (bad habit!). Besides, your [blue]If Then Else[/blue] statement already splits up the code for you and should look like:

[blue]   If TempRev = "Rev" Then [green]'revision ok[/green]
      [green]'
      'revision ok code here
      '[/green]
   Else [green]'revision not ok[/green]
      MsgBox "This drawing is not under revision ..."
      [green]'
      'revision not ok code here
      '[/green]
   End If[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[accessvbahelp]

AceMan1 you are amazing! Thank you!

 

[accessvbahelp]

Where can you recommend VBA training? I am teaching myself and learning a lot from on here.
Thanks again!

 

[Andrzejek]

accessvbahelp, which solution did you go with? I see that you went with TheAceMan1's one, but which one? I hope it was not the one with GoTo's...

Have fun.

---- Andy

 

[randy700]

Seems to me, Andy gave the same suggestion.

Randy

 

[SkipVought]

Structured code!!!

On occasion, I will use the GoSub...Return structure, when I see the same code being repeated at more than one place in my code, usually 3 or more statements. Often it is code that is executed in a loop, and then then same code get executed after the loop is exited.

   do while SomeCondition
     '.....
     GoSub SomeStuff 
     '.....
   loop
   GoSub SomeStuff
'.....
   Exit

SomeStuff:
'multiple use code
'.......
  Return

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Andrzejek]

Just to clearify, do you mean:

[blue]Private Sub MySub()[/blue]
   do while SomeCondition
     '.....
     GoSub SomeStuff 
     '.....
   loop
   GoSub SomeStuff
'.....
[blue]Exit Sub[/blue]

SomeStuff:
'multiple use code
'.......
  Return[blue]
End Sub[/blue]

Good stuff to know Thanks.

Have fun.

---- Andy

 

[accessvbahelp]

I did not use the GoTo. Can you explain why those should not be used?

 

[SkipVought]

yes!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[TheAceMan1]

Howdy SkipVought . . .

Referencing your post [blue]13 Dec 11 15:51[/blue] ... I didn't say you couldn't use [blue]Goto[/blue] or [blue]Gosub[/blue]. I just don't recommend it (doesn't make for easy reading, espcially during troubleshooting). I would typically make [blue]SomeStuff[/blue] a seperate routine or function. Its a [blue]code structure choice[/blue] ...

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

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

Howdy randy700 . . .

[blue]Seems to me, Andy gave the same suggestion.[/blue]

In my post [blue]13 Dec 11 4:20[/blue] I did say:

[blue]As mentioned by [purple]Andrzejek[/purple] ... don't use GoTo's except for error handlers.[/blue]

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

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[SkipVought]

Yes, it's a style choice. If the GoSub code is too long, then a separate routine would also be my choice.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[accessvbahelp]

Ok... remember I said you need to be patient, well I didn't know what <> meant so it took me awhile to figure it out. (don't laugh, I've never written code before!) Now that I know what it means I went with Andrzejek solution! Thank you everyone!!!