Results 1 to 11 of 11
  1. #1
    kmrickert is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2021
    Posts
    10

    Question Converted Macro/Module - Compile Error

    I'm trying to call a converted macro when a button is clicked. But get "Compile Error: Invalid Use of Property". I've called modules before and I can't see what would be different this time.


    Private Sub Save_Contribution_Click()
    NEW_CONTRIBUTION
    End Sub




    -----------------------------------------------------

    This is the converted macro (module):



    '------------------------------------------------------------
    ' NEW_CONTRIBUTION
    '
    '------------------------------------------------------------
    Function NEW_CONTRIBUTION()


    On Error GoTo NEW_CONTRIBUTION_Err


    With CodeContextObject
    On Error GoTo 0
    If (.AMOUNT > 0) Then
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.RefreshRecord
    Beep
    MsgBox "Saved Record", vbInformation, "SAVED:"


    Else
    DoCmd.RefreshRecord
    Beep
    MsgBox "Amount is missing.", vbExclamation, "ENTER AMOUNT:"
    End If

    [Forms]![CONTRIBUTIONS].Refresh
    End With




    NEW_CONTRIBUTION_Exit:
    Exit Function


    NEW_CONTRIBUTION_Err:
    MsgBox Error$
    Resume NEW_CONTRIBUTION_Exit


    End Function

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    a) always state on what line the error occurs (or did I miss that?) or you'll just get guesses. Those guesses might actually be about an error that hasn't been discovered yet.
    b) please post code within code tags (# on posting toolbar) with indentation

    Likely that .Amount isn't a property of CodeContextObject.
    - there is no need for a With block here
    - you don't seem to be using any property of that application property so why is it even being referenced? What is this supposed to be doing?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    kmrickert is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2021
    Posts
    10
    I get the error "Compile Error: Invalid Use of Property" and the "Private Sub Save_Contribution_Click()" is highlighted.

  4. #4
    kmrickert is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2021
    Posts
    10
    Code:
    '------------------------------------------------------------
    ' NEW_CONTRIBUTION
    '
    '------------------------------------------------------------
    Function NEW_CONTRIBUTION()
    On Error GoTo NEW_CONTRIBUTION_Err
    
    
        With CodeContextObject
            On Error GoTo 0
            If (.AMOUNT > 0) Then
                DoCmd.RunCommand acCmdSaveRecord
                DoCmd.RefreshRecord
                Beep
                MsgBox "Saved Record", vbInformation, "SAVED:"
    
    
            Else
                DoCmd.RefreshRecord
                Beep
                MsgBox "Amount is missing.", vbExclamation, "ENTER AMOUNT:"
            End If
            
        [Forms]![CONTRIBUTIONS].Refresh
        End With
    
    
    
    
    NEW_CONTRIBUTION_Exit:
        Exit Function
    
    
    NEW_CONTRIBUTION_Err:
        MsgBox Error$
        Resume NEW_CONTRIBUTION_Exit
    
    
    End Function

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I don't get that result. It looks more like what I suggested.

    Click image for larger version. 

Name:	aErr.jpg 
Views:	13 
Size:	24.7 KB 
ID:	45420
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    kmrickert is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2021
    Posts
    10
    So should I specify the name of the Form to check that the amount has been entered?
    Code:
    If (FORMS!NEW_CONTRIBUTION_ENTRY!AMOUNT > 0)

  7. #7
    kmrickert is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2021
    Posts
    10
    Note, the amount checking was working fine when it was a simple macro. The language was written automatically when the macro was converted.

  8. #8
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Quote Originally Posted by kmrickert View Post
    Note, the amount checking was working fine when it was a simple macro. The language was written automatically when the macro was converted.
    So why the change?
    Why does it even need to be a function? Is this going to be used elsewhere all the time?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  9. #9
    kmrickert is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2021
    Posts
    10
    I need to refresh another form after save and I wasn't sure how to do this in a macro. Any "Best Practice" suggestions are welcome.

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You ask a lot more questions than you answer when you are asked.
    When you use a WITH block you are going to write a bunch of lines and say "do this" and "do that" and "do this other"... WITH this 'thing'
    and everything we're going to enact/test/set/alter any aspect of that 'thing' must belong to/apply to that thing. If [Amount] doesn't belong to/refer to the 'thing' then you don't use a With block. So in the continued absence of what it is that this code is supposed to do, your suggestion in post 6 seems as good as anything else.

    The code wizard does not always correctly interpret a macro, and very seldom does a good job even when it works.
    Last but not least, the only time you must create a function is when it is going to be called by a commandbar, ribbon, shortcut menu or as the event property setting for a control event, and when you need it to return a value. Other than that, IMO it hardly matters what it is if the procedure isn't returning anything as it does the job either way. If writing for myself and the procedure will not return a value, then I'd do it as a sub, but with the code converter, you get what you get.
    Last edited by Micron; 06-04-2021 at 02:16 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    kmrickert is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2021
    Posts
    10
    I don't mean to be confusing. Thank you for your input.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 07-25-2019, 04:25 PM
  2. Auto run converted Macro
    By steven7 in forum Macros
    Replies: 3
    Last Post: 06-09-2016, 11:26 AM
  3. Using macro converted to VBA in other functions
    By gaker10 in forum Programming
    Replies: 4
    Last Post: 08-19-2014, 07:44 AM
  4. Replies: 8
    Last Post: 06-27-2013, 11:58 AM
  5. Module Error in Macro
    By TimMoffy in forum Modules
    Replies: 3
    Last Post: 06-20-2012, 09:52 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums