Results 1 to 8 of 8
  1. #1
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754

    Private Sub vs. Public Sub in module for on current event?


    Good morning all,
    I am asking some advise more than anything in this post. I use the code below in most every form i make. Would it be considered good programing to just make a module and put this is as public subs and call for it in on current or would this be a bad idea. I only ask as I don't know! I just seems to me that this should go into a module. And if so, how would I create a variable in the form name for the error code to specify the form that it is using? I didn't put the code in for the First, Last, Prev, and Next but I would also do that. Just didn't want to paste all that code, please assume it goes in too as public subs.

    Code:
    Private Sub Form_Current()
        Dim rst As DAO.Recordset
        Dim nCount As Integer, nPosition As Integer
        nCount = Me.Recordset.RecordCount
        
        On Error GoTo Err_Handler
        
        If nCount = 0 Then
            MsgBox "No Records exist yet.", vbOKOnly, "  R E S T R I C T I O N  "
            Exit Sub
        End If
        
        Set rst = Me.RecordsetClone
        rst.MoveLast
        rst.MoveFirst
        nCount = rst.RecordCount
        nPosition = Me.CurrentRecord
        Me!TxtRecordNo = nPosition & " of " & nCount
        CmdFirst.Enabled = True
        CmdPrev.Enabled = True
        CmdNext.Enabled = True
        CmdLast.Enabled = True
        'disable as appropriate
        If nCount = 1 Then
            CmdFirst.Enabled = False
            CmdPrev.Enabled = False
            CmdNext.Enabled = False
            CmdLast.Enabled = False
        ElseIf nPosition = 1 Then
            CmdFirst.Enabled = False
            CmdPrev.Enabled = False
        ElseIf nPosition = nCount Then
            CmdLast.Enabled = False
            CmdNext.Enabled = False
        End If
        
    Exit_Handler:
        If Me.Dirty Then Me.Dirty = False
        Set rst = Nothing
        Exit Sub
    Err_Handler:
        Select Case Err
            Case Else
                MsgBox "Error " & Err & ": " & Error$, vbExclamation, "Form_Current()"
                Resume Exit_Handler
        End Select
    End Sub
    Thank you,
    Dave

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you can - call it something like frmCurrent and make it a function

    you will need to include a parameter to pass the form and you will need to change the me. designations to reference the parameter e.g.

    Code:
    function frmCurrent(frm as form)
    
    with frm
    
        ncount=.recordset.recordcount
        …
        ….
    
    end with
    
    end function
    to call it put

    =frmCurrent([FORM])

    against the current event (where you see [Event Procedure])

    or if you have other things happening in the current event, put

    frmCurrent me

    as the first line of code

  3. #3
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    NavigatonButtons-davegri.zip
    Attached DB has 2 forms calling the common navigation code in a module.

  4. #4
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Thank you all,
    This really helps and is very nice advice!
    Thanks
    Dave

  5. #5
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Hi Davegri,
    I had a question on this.
    I see on the frmAttendees, on the on current event, do I need all the Error and exit code (InRED) in that event when it is in the module itself?
    Was not real clear, see below,
    Thanks

    Private Sub Form_Current()
    On Error GoTo Form_Current_Error
    cboSearch = Null
    Call subCommon_Form_Current(Me.Form)
    Form_Current_EXIT:
    Exit Sub
    Form_Current_Error:
    Select Case Err
    Case Else
    'Call fcnLogError(Err.Number, Err.Description, " in Form_Current of VBA Document Form_frmAttendees", , True)
    End Select
    Resume Form_Current_EXIT

    End Sub

  6. #6
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    do I need all the Error and exit code
    Error procedure code isn't strictly necessary for the sub to perform its duties.
    Such code is optional, and up to the user to decide whether it is of benefit.

  7. #7
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Maybe I worded it wrong?
    Will the error code in the module fire if not in the on current event like above when I call that module (Public Sub)?

  8. #8
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Will the error code in the module fire if not in the on current event like above when I call that module (Public Sub)?
    The error code would be invoked for ANY error that occurs in the sub.

    'Call fcnLogError(Err.Number, Err.Description, " in Form_Current of VBA Document Form_frmAttendees", , True)

    If the above code is uncommented it should be changed to a more appropriate message for your application:
    Code:
    Msgbox Err & ", " & Err.Description
    Last edited by davegri; 05-11-2019 at 11:27 AM. Reason: format

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

Similar Threads

  1. Replies: 4
    Last Post: 01-25-2019, 09:03 PM
  2. Public Functions and Private Subs
    By d9pierce1 in forum Programming
    Replies: 15
    Last Post: 11-28-2018, 09:25 AM
  3. Changing private sub dim to public sub dim
    By Stephenson in forum Programming
    Replies: 14
    Last Post: 10-05-2015, 02:59 PM
  4. Turning Private Function Into Public
    By g4tv4life in forum Programming
    Replies: 1
    Last Post: 02-04-2014, 05:31 PM
  5. Replies: 9
    Last Post: 12-20-2010, 08:05 PM

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