Compile error: label not defined (1 Viewer)

B

Bopsgtir

Registered User.
Local time
Today, 15:17
Joined
Jan 1, 2011
Messages
52
Can anyone tell me why i would be getting a compile error on the below code, i really cant work it out.

Option Compare Database

Private Sub GreenFilter_Click()

On Error GoTo err_Handler

DoCmd.OpenForm "FrmMissingData", acNormal, , , acFormEdit, acWindowNormal
DoCmd.Close acForm, "From1", acSavePrompt

err_handler_exit:
End Sub

err_Handler:
If Error = 2427 Then
MsgBox "there is no data"
vbCritical
Cancel = True

Else
MsgBox Err.Description
Resume
exit_NoDataFound

End Sub
 
MarkK

MarkK

bit cruncher
Local time
Today, 15:17
Joined
Mar 17, 2004
Messages
8,180
Immediately after your 'err_handler_exit:' you have 'End Sub' when you mean 'Exit Sub'. 'End Sub' termintes the routine and the compiler never sees the following code, which will also have errors when you fix this one.
Cheers,
Mark
 
B

Bopsgtir

Registered User.
Local time
Today, 15:17
Joined
Jan 1, 2011
Messages
52
ok i resolved that problem only to run into another

the new code is,

Code: 
Private Sub GreenFilter_Click()

On Error GoTo err_Handler

DoCmd.OpenForm "FrmMissingData", acNormal, , , acFormEdit, acWindowNormal
DoCmd.Close acForm, "From1", acSavePrompt

err_handler_exit:
Exit Sub

err_Handler:
If Error = 2427 Then
MsgBox "there is no data"
Cancel = True

Else
MsgBox Err.Description
Resume err_handler_exit
End If

End Sub

but now i get the runtime error 2427 that i was trying to capture when i debug that i get an error on the highlighted line of code.

Code: 
Private Sub Form_Open(Cancel As Integer)


Me.AllowAdditions = False

If IsNull(Employee_Number) Then
Employee_Number.Visible = True
Me.Refresh
Else
Employee_Number.Visible = False
Me.Refresh
End If

If IsNull(User_ID) Then
User_ID.Visible = True
Me.Refresh
Else
User_ID.Visible = False
Me.Refresh
End If

If IsNull(Work_Mobile) Then
Work_Mobile.Visible = True
Me.Refresh
Else
Work_Mobile.Visible = False
Me.Refresh
End If

[COLOR="red"]If Virgin_Email_Address = "@virginmedia.co.uk" Then[/COLOR]
Virgin_Email_Address.Visible = True
Me.Refresh
Else
Virgin_Email_Address.Visible = False
Me.Refresh
End If

If Fuel_Card_Required = True Then
If IsNull(Fuel_Card_Number) Then
Fuel_Card_Number.Visible = True
Me.Refresh
Else
Fuel_Card_Number.Visible = False
Me.Refresh
End If
Else
Fuel_Card_Number.Visible = False
End If

End Sub
 
B

Bopsgtir

Registered User.
Local time
Today, 15:17
Joined
Jan 1, 2011
Messages
52
Hi Mark ive just seen your response, many thanks for that but im now getting another error???
 
MarkK

MarkK

bit cruncher
Local time
Today, 15:17
Joined
Mar 17, 2004
Messages
8,180
And you can confirm you have an object called 'Virgin_Email_Address' on the form? So if you type 'Me.' when you hit the period (.) an intellisense window appears. Is Virgin_Email_Address in it?
 
MarkK

MarkK

bit cruncher
Local time
Today, 15:17
Joined
Mar 17, 2004
Messages
8,180
Also, Visible is a True or False thing, and so is IsNull(EmployeeNumber). In this respect we can say that Visible and IsNull(EmployeeNumber) vary directly with each other so the evaluation of one IS the value of the other. In this case we don't need an IF block, we can do a direct assignment, so...
Code: 
If IsNull(Employee_Number) Then
  Employee_Number.Visible = True
Else
  Employee_Number.Visible = False
End If
... is far more simply written ...
Code: 
Employee_Number.Visible = IsNull(Employee_Number)
See it? The visibility of the object is directly a function of whether it is null or not.
So your code could look like ...
Code: 
Private Sub Form_Open(Cancel As Integer)
  Me.AllowAdditions = False

  Employee_Number.Visible = IsNull(Employee_Number)
  User_ID.Visible = IsNull(User_ID)
  Work_Mobile.Visible = IsNull(Work_Mobile)
  Virgin_Email_Address.Visible = Virgin_Email_Address = "@virginmedia.co.uk"
  Fuel_Card_Number.Visible = Fuel_Card_Required AND IsNull(Fuel_Card_Number)

End Sub
And you don't need a refresh when the form opens.
Also, you may want to put this kind of functionality in the form's Current event, which fires every time a record is changed. Then this logic is applied for each record, not just the first one when the form opens.
Cheers,
Mark
 
B

Bopsgtir

Registered User.
Local time
Today, 15:17
Joined
Jan 1, 2011
Messages
52
Thank you mark for the help ive now modified my code.

a couple of questions though, if im using the same error handle of both codes be it the button click on form1 or the current event of frmmissingdata, why is the error not picked up on form1, then when it does go to the current event why does the handle not find the error 2427 and comes back with msgbox different error???

Code: 
Option Compare Database

Private Sub Form_Current()

Me.AllowAdditions = False

Employee_Number.Visible = IsNull(Employee_Number)
User_ID.Visible = IsNull(User_ID)
Work_Mobile.Visible = IsNull(Work_Mobile)
Virgin_Email_Address.Visible = Virgin_Email_Address = "@virginmedia.co.uk"
Fuel_Card_Number.Visible = Fuel_Card_Required And IsNull(Fuel_Card_Nuumber)

End Sub

Private Sub Form_Open(Cancel As Integer)

On Error GoTo err_Handler

Me.AllowAdditions = False

Employee_Number.Visible = IsNull(Employee_Number)
User_ID.Visible = IsNull(User_ID)
Work_Mobile.Visible = IsNull(Work_Mobile)
Virgin_Email_Address.Visible = Virgin_Email_Address = "@virginmedia.co.uk"
Fuel_Card_Number.Visible = Fuel_Card_Required And IsNull(Fuel_Card_Nuumber)

err_handler_exit:

Exit Sub

err_Handler:
If Error = 2427 Then
MsgBox "There Is No Missing Data"
Cancel = True

Else
MsgBox "Different Error"
Resume err_handler_exit
End If

End Sub
 
MarkK

MarkK

bit cruncher
Local time
Today, 15:17
Joined
Mar 17, 2004
Messages
8,180
A current event occurs when the form opens so there is no need to have that code in both event handlers. I would do this...
Code: 
Private Sub Form_Open(Cancel As Integer)
  Me.AllowAdditions = False
Exit Sub

Private Sub Form_Current()
on error goto handler
  Employee_Number.Visible = IsNull(Employee_Number)
  User_ID.Visible = IsNull(User_ID)
  Work_Mobile.Visible = IsNull(Work_Mobile)
  Virgin_Email_Address.Visible = Virgin_Email_Address = "@virginmedia.co.uk"
  Fuel_Card_Number.Visible = Fuel_Card_Required And IsNull(Fuel_Card_Nuumber)
  exit sub

handler:
  msgbox err & " " & err.description, vbexclamation
End Sub
As far as why you get the error, have you read my post #5? Are you sure that there is an object on the form with that name? Note that an '_' (underscore) character is not interchangable with a ' ' (space) character. I commonly prefix everything on a form with 'Me.' since then when you hit the '.' (period) character intellisense gives you a list of objects to choose from and naming issues are easily solved that way. If I wrote that code it would look like this...

Code: 
Private Sub Form_Current()
on error goto handler
  Me.EmployeeNumber.Visible = IsNull(Me.EmployeeNumber)
  Me.UserID.Visible = IsNull(Me.UserID)
  Me.WorkMobile.Visible = IsNull(Me.WorkMobile)
  Me.VirginEmailAddress.Visible = [COLOR="Red"]Nz(Me.VirginEmailAddress, "")[/COLOR] = "@virginmedia.co.uk"
  Me.FuelCardNumber.Visible = Me.FuelCardRequired And IsNull(Me.FuelCardNumber)
  exit sub

handler:
  msgbox err & " " & err.description, vbexclamation
End Sub
Note the modification in red which handles the case where that field might be null. But I never use spaces or underscores or any special characters in names of anything. Ever.
 
B

Bopsgtir

Registered User.
Local time
Today, 15:17
Joined
Jan 1, 2011
Messages
52
i did use the ME. and Virgin_Email_Address did come up, i see what you mean now about no spaces or underscores, one question was does the NZ do??
 
MarkK

MarkK

bit cruncher
Local time
Today, 15:17
Joined
Mar 17, 2004
Messages
8,180
Nz() tests the first parameter for null. If it's not null it returns the first parameter. If it is null it ruturns the second parameter. It's a sort of 'inline' way of removing nulls from an expression that must not fail. You could write your own Nz() function like this...

Code: 
function MyNz(value, valueifnull) as variant
  if isnull(value) then
    MyNz = valueifnull
  else
    MyNz = value
  end if
end function

Did you solve the 2427 error?
Cheers,
Mark
 
B

Bopsgtir

Registered User.
Local time
Today, 15:17
Joined
Jan 1, 2011
Messages
52
More of a work around really I could capture and error but couldn't capture that 2427 the work around I used was on my filter form I used a dcount to say how many records the filter would find if it was zero it would disable the control button. Only problem now is I followed your advice about spaces and underscores and as my database is only one table at the moment I decided to remane my fields and implement that going forward. Only problem now is I've tried to rewrite my dcount and I keep getting the syntax wrong.
 
R

r.harrison

It'll be fine (I think!)
Local time
Today, 23:17
Joined
Oct 4, 2011
Messages
134
Bopsgtir said:
Can anyone tell me why i would be getting a compile error on the below code, i really cant work it out.

Option Compare Database

Private Sub GreenFilter_Click()

On Error GoTo err_Handler

DoCmd.OpenForm "FrmMissingData", acNormal, , , acFormEdit, acWindowNormal
DoCmd.Close acForm, "From1", acSavePrompt

err_handler_exit:
End Sub

err_Handler:
If Error = 2427 Then
MsgBox "there is no data"
vbCritical
Cancel = True

Else
MsgBox Err.Description
Resume
exit_NoDataFound

End Sub
Click to expand...

Should the FROM1 be FORM1 ?
 
gemma-the-husky

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 23:17
Joined
Sep 12, 2006
Messages
15,651
it's the name of your form. most likely to be form1, i would think - but it's your app - so maybe it is something else entirely

are you sure there is a form "frmmissingdata". if that fails to open you will get a error 2501. (even if you trap it with a on no data test)
 
R

r.harrison

It'll be fine (I think!)
Local time
Today, 23:17
Joined
Oct 4, 2011
Messages
134
gemma-the-husky said:
it's the name of your form. most likely to be form1, i would think - but it's your app - so maybe it is something else entirely
Click to expand...

I'm hoping I'm not the only programmer to have been pulling my hair out over errors only to find out it's a simple typing error. Happens to us all.

Might not be the answer to the question, just noticed it and thought I'd mention it.
 
You must log in or register to reply here.

Similar threads

C
Close form; open a new form
Replies
11
Views
491
isladogs
isladogs
C
Solved Error 3467 object is closed
2
Replies
25
Views
1,058
ClaraBarton
C
Noson5434
Conditional Formatting
Replies
12
Views
728
theDBguy
theDBguy
C
Sendkeys not working anymore
Replies
7
Views
684
NauticalGent
NauticalGent
J
error in procedure
Replies
12
Views
277
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom