This must be obvious - Compile Error: Label not defined

[Keith Nichols]

New to VBA (and Access) so be gentle!

I have a short bit of code to hide a text box on a form dependant upon the contents of a combo box on the same form. It seems to work but runs the error handling and then stalls with a "Compile Error: Label not defined" message. If I mark the error handling as comments the code seems to work fine.

A quick scurry through the text books, Access VBA help, various peices of code gleaned from this site, and forum postings hasn't given me any clue as to where I am going wrong.

------------------------------------------------------------
Private Sub Form_Current()
On Error GoTo Err_Form_Current

Select Case [cbo_definite_indefinite]

Case "I"
txt_definite_contract_expiry_date.Visible = False

Case Else
txt_definite_contract_expiry_date.Visible = True
End Select

Err_Form_Current:
MsgBox Err.Description
Resume Form_Current

End Sub
------------------------------------------------------------

Incidentally, I used "Case" rather than "If/Then" as it was the first example I found to copy. Would an If/Then statement be more appropriate?
Regards,

Keith.

 

[WayneRyan]

Keith,

You can use Conditional Formatting and forget about the code.
See Access Help or search here.

Private Sub Form_Current()
On Error GoTo Err_Form_Current

Select Case [cbo_definite_indefinite]
  Case "I"
    txt_definite_contract_expiry_date.Visible = False
  Case Else
    txt_definite_contract_expiry_date.Visible = True
  End Select
Exit Sub <-- Add this so you won't "fall" into the Error Handler

Err_Form_Current:
MsgBox Err.Description
Resume Form_Current <-- Change to Exit Sub; Form_Current is an event
                        not a label.
End Sub

Wayne

 

[Keith Nichols]

Keith,

You can use Conditional Formatting and forget about the code.
See Access Help or search here.

Wayne

Hi Wayne,

Is that a better way to achieve the desired result?

I would still be interested to know where I am going wrong with my labelling if anyone can spot the error.

Regards,

 

[WayneRyan]

Keith,

In your previous example, you needed an Exit Sub after the
Case Statement. Without it, you won't exit the Subroutine,
you'll fall into the Error Handler.

When it falls into the Error handler, the Resume statement
tells it to Resume @ Form_Current. Form_Current is not a
label, it is a Subroutine. All you really need to do at
that point is the Exit Sub.

Your code is OK, and you are using the right event. Every
time your form navigates to a new record, the code will run
and properly set the visibility. Good so far.

You do have the option of using Conditional Formatting on
your form ... No code required. Search the forum here for
"Conditional" and also look in Access Help. There are
examples.

Wayne

 

[Keith Nichols]

Wayne,

I really ought to take the time to read replies thoroughly as I didn't notice you had corrected my code and highlighted the error. Many thanks.

I did try the conditional formatting but it didn't seem to be able to do what I wanted i.e. make it invisible. I could set the background and the text so they couldn't be seen but the field was still there and I couldn't set the border. Maybe this can be done with conditional formatting in code, but that seems to defeat the logic behind using it.

What I didn't say in my orignal posting is that I intend to put a second control, unbound and locked, in the same place with default "N/A" in it that will appear when the first field dissappears. This convoluted state of affairs is to get round the fact that the first field is a date format and so cannot accept the Not Applicable text.

And when I get all that working sweet as a nut, I will be copying it and editing it for the update event of the same control.

Hey but you learn things trying to add pollish to your aplication. Once again, thanks.

Regards,

Keith.