Compile Error: Invalid user of property

[jwill]

I'm getting this error and its annoying me because I don't know why I'm getting it. It's design to be the "previous button" to move to the previous record. I've compacted & repaired and exported all objects to another database & I'm still getting this error. What's irritating me the most is that I have the EXACT SAME CODE on another form and it's working as designed . What's even worse than that is I have a control right next to it that calls my "next_btn" function that is designed the same way and it works. My code is below. Somebody please help me, thanks in advance.

Private Sub prev_lbl_Click()
'navigation module
prev_btn
End Sub

This the code that it calls:

Public Function prev_btn()
On Error GoTo Err_prev_btn

DoCmd.GoToRecord , , acPrevious

Exit_prev_btn:
    Exit Function

Err_prev_btn:
    'error_handling module
    GlobalErrHandler
    Resume Exit_prev_btn

End Function

Here is the "globalerrhandler" function

Sub GlobalErrHandler()

  ' Comments: Main procedure to handle errors that occur.

  Dim strError As String
  Dim lngError As Long
  Dim intErl As Integer
  Dim strMsg As String

  ' Variables to preserve error information
  strError = err.Description
  lngError = err.Number
  
  Select Case lngError
  
  Case 2105
    MsgBox "Information in a required field is missing"
  Case 2046
    MsgBox "The delete button is not available.  "
    
    
  Case Else
  ' Prompt the user with information on the error:
  strMsg = "Error: (" & lngError & ") " & strError
  MsgBox strMsg
    
  End Select
    
End Sub

[Rod]

Have you debugged? Which statement is triggering the error?

[jwill]

I've tried and it stops right at the prev_lbl_click procedure

[June7]

The event prev_lbl_Click is for a label or button control? I guess either should work. Code looks good. Want to provide db for analysis? Follow instructions at bottom of my post.

Do previous/next buttons trigger message if they are on first/last record? One way to handle that:

    
Dim rs As DAO.Recordset Set rs = Form_SampleInfo.RecordsetClone
rs.Bookmark = Form_SampleInfo.Bookmark
rs.MovePrevious
If Not rs.BOF Then
    DoCmd.GoToRecord acForm, "SampleInfo", acPrevious
Else
    rs.MoveFirst
    MsgBox "First record."
    Form_SampleInfo.btnPrevious.Enabled = False
End If
Form_SampleInfo.btnNext.Enabled = True

[Rod]

Just a thought - have you something else also named 'prev_btn?' From your description of the error

... it stops right at the prev_lbl_click procedure

it sounds like a compile time error and not a run time error. Is this so?

Yup, you can use the label click event but this is a poor substitute for a command button: no enabled property; no toggle type reaction when clicked, etc.

PS It's also unconventional to use a function without a return value - it's OK, it will work, but why not specify a sub?

[jwill]

@Rod.....not in the same module. Also, I am fairly new to vba (not really new to access) but some of the best practices have eluded me since I largely learned by self study, so thanks for the input.

@june7....I can send you a piece of it, it's pretty big

[jwill]

@Rod you were right. I have a command button still on my form named "prev_btn". I stopped using the buttons because they're ugly. I've just been using raised labels or just lettering. I don't need to hide what I've been using them for so it works ok. Microsoft should do more w/ the buttons. Anyway, the name of the button was messing with the (now sub) call. I thought as long as I didn't see it anywhere in the code for that module I was ok, I was wrong And all that fuss and calling Access crap, LOL. I just deleted the buttons and it works fine now. Thanks everyone for your quick response and help.

[Rod]

If you are seeing the statement, Private Sub prev_lbl_Click() highlighted with a yellow background and the message reads, "Invalid use of property" rather than "user" then it's undoubtedly a compile time error. Now sometimes code gets corrupted with unprintable characters (myself, I haven't seen a case of this for years) and the only way to solve it is to delete and retype the corrupted line. As you don't know which line, then delete and retype the whole procedure.

Otherwise your procedure has only one statement, prev_btn. This has to be the statement that is offending the compiler. Now either (favourite) it is a name contention with a property somewhere else - a name property perhaps - or (improbable) it is because you are calling a function without a return value. I have actually tested this latter condition and get no error so my money's on the name contention.

The run time errors you get if positioned at the first/last records of the result set are something like, "Cannot go to specified record" and nothing like what you describe. June7's code example is a good one to put in your toolkit; I would personally use rs.MovePrevious rather than the DoCmd but either will work.

A reminder on how VBA run time error handling works. If there is no current user error handler specified (through an On Error Goto statement) then VBA backtracks along the calling chain/hierarchy using the first user error handler it encounters. If no user error handler is found VBA uses it's own error handler, the one that issues the message with buttons including End, Debug, Help along the bottom. I mention this because prev_lbl_Click() has no error handler yet you posted the called procedure and your error handling procedure as if you expected the VBA error handling to move forward. I stress again that I writing about run time errors.

PS You posted again while I was writing this. There you go Watson, when you have eliminated the impossible, whatever remains, however improbably, must be the solution. Labels or command buttons - very much up to you. Glad it's solved.

[June7]

How did Access allow two controls with the same name? Never seen that. Bizarre!

[jwill]

How did Access allow two controls with the same name? Never seen that. Bizarre!

It wasn't 2 controls w/ the same name it was the control & and the procedure that had the same name "prev_btn"

[jwill]

@june7....thanks for the code sample too. I will be using that

[June7]

Using rs.MovePrevious instead of the DoCmd won't move the recordselector on form.

[jwill]

That's fine I don't use the record selectors on my forms anyway

[June7]

Doesn't matter if the RecordSelector bar is actually available to the user, the point is that DoCmd moves focus (the record pointer) to the previous/next (as the case may be) record and displays that record as the current record on form.

[jwill]

oh ok I understand, thanks