Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    d9pierce1 is offline Expert
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754

    Public Functions and Private Subs


    Hello All,
    Is it possible to call out a public function =GotoOnCurrent and also have a Private Sub OnCurrent event on the same form? How does that work if possible?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Yes, a public function in a general module can be called by a form that also has code in OnCurrent event.

    Why would you think this would cause an issue? What is the code?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    d9pierce1 is offline Expert
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Hi and thanks, I just dont know how to do it. Below is code! I am also having a bug on previous record on Public Function. I have no idea why this is creating a bug????

    Code:
    Private Sub Form_Current()
    
    ' Provide a record counter for using with
    ' custom navigation buttons (when not using
    ' Access built in navigation)
    
        Dim rst AsDAO.Recordset
        Dim lngCount AsLong
    
        Set rst =Me.RecordsetClone
    
        With rst
            .MoveFirst
            .MoveLast
            lngCount =.RecordCount
        End With
    
    'Show the result of the record count in the text box(txtRecordNo)
    
        Me.TxtRecordNo= Me.CurrentRecord & " of " & lngCount
    
    End Sub
    
    
    
    
    
    
    Option Compare Database
    
    Public Function GotoCurrent()
        On Error ResumeNext
        IfScreen.ActiveForm.CurrentRecord = 1 Then
           Screen.ActiveForm.CmdPrev.Enabled = False
           Screen.ActiveForm.CmdFirst.Enabled = False
        Else
           Screen.ActiveForm.CmdPrev.Enabled = True
           Screen.ActiveForm.CmdFirst.Enabled = True
        End If
        IfScreen.ActiveForm.CurrentRecord = Screen.ActiveForm.Recordset.RecordCount Then
           Screen.ActiveForm.CmdLast.Enabled = False
        Else
           Screen.ActiveForm.CmdLast.Enabled = True
        End If
        IfScreen.ActiveForm.CurrentRecord >= Screen.ActiveForm.Recordset.RecordCountThen
           Screen.ActiveForm.CmdNext.Enabled = False
        Else
           Screen.ActiveForm.CmdNext.Enabled = True
        End If
    
    End Function
    
    Public Function GotoFirst()
    
        'remove abilityto add records
       Screen.ActiveForm.AllowAdditions = False
    
        'go to thefirst record!
       DoCmd.RunCommand acCmdRecordsGoToFirst
    
    End Function
    Public Function GotoLast()
    
        'remove abilityto add records
       Screen.ActiveForm.AllowAdditions = False
    
        'go to the lastrecord!
       DoCmd.RunCommand acCmdRecordsGoToLast
    
    End Function
    Public Function GotoNew()
    
        'create newrecord
       Screen.ActiveForm.AllowAdditions = True
    
       DoCmd.RunCommand acCmdRecordsGoToNew
    
    End Function
    
    Public Function GotoNext()
    
        'button to goto the next record
       Screen.ActiveForm.AllowAdditions = False
       DoCmd.RunCommand acCmdRecordsGoToNext
    
    
    
    End Function
    
    Public Function GotoPrevious()
    
        'button to goto the previous record
       Screen.ActiveForm.AllowAdditions = False
       DoCmd.RunCommand acCmdRecordsGoToPrevious
    
    End Function

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What do you mean by 'bug' - an error message, wrong results, nothing happens?

    Cannot call both procedures from the OnCurrent event property. However, possibly call the GoToCurrent function within the OnCurrent sub, maybe just before End Sub:

    x = GoToCurrent()
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    d9pierce1 is offline Expert
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Hi June7,
    Than you and what I ment by bug was an error to debug. I get an error when I run the GotoCurrent and its with the previous record command. Debug highlights the DoCmd.RunCommand.acCmdRecords gotoPrevious and if you hover over, it says Previous =66 what ever that means. I think it has something to do with the first line and the =1 but not sure?

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Exactly how are you calling the GotoCurrent?

    I don't have code like this. Would have to debug your db if you want to provide follow instructions at bottom of my post.

    Learn debug techniques. Set breakpoint and step through code.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    d9pierce1 is offline Expert
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    StateTest - Copy.zipHi,
    I am calling on the GotoCurrent() in form properties On Current =GotoOnCurrent()
    I dont know how to call my on my Private Sub Form_Current ???? Have no idea how to call that to this or vise versa.
    Now, I have attached a simple db zip to this so you can see what I am talking about on the error.
    When you open form state, the First and Previous buttons should be set to Enabled = False and that's where the error is coming from. I have tried everything I can to get this to work.

    So, I need to figure out how to fix the debug error, and how to call on Private Sub Form_Current?

    Thanks
    Dave

  8. #8
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Navigation-davegri-v01.zip
    This should get you going...

  9. #9
    d9pierce1 is offline Expert
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    thank you very much

  10. #10
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I have never used code like (or even see it used)
    Code:
    Screen.ActiveForm.CmdPrevious.Enabled = False
    so I used davegri's dB and modified the code.


    Every module should have these two lines at the top
    Code:
    Option Compare Database
    Option Explicit

    I also added code to change the enabled states of the nav buttons.....
    Attached Files Attached Files

  11. #11
    d9pierce1 is offline Expert
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Thanks so much for doing this. It does work so I may just have to go with it. I really dont understand why the code below doesn't work for it. When the form opens it is at record = 1 so the First and Previous should be enabled = False but they are not. Now if I go to next or last and then back this code works just fine. Driving me nuts why it doesn't work when the form opens. I must be missing a line of code before it, but I have no idea what it would be.
    Thanks a million,
    Dave


    Code:
    Option Compare Database
    
    Public Function GotoCurrent()
        On Error ResumeNext
        IfScreen.ActiveForm.CurrentRecord = 1 Then
           Screen.ActiveForm.CmdPrev.Enabled = False
           Screen.ActiveForm.CmdFirst.Enabled = False
        Else
           Screen.ActiveForm.CmdPrev.Enabled = True
           Screen.ActiveForm.CmdFirst.Enabled = True
        End If
        IfScreen.ActiveForm.CurrentRecord = Screen.ActiveForm.Recordset.RecordCount Then
           Screen.ActiveForm.CmdLast.Enabled = False
        Else
           Screen.ActiveForm.CmdLast.Enabled = True
        End If
        IfScreen.ActiveForm.CurrentRecord >= Screen.ActiveForm.Recordset.RecordCountThen
           Screen.ActiveForm.CmdNext.Enabled = False
        Else
           Screen.ActiveForm.CmdNext.Enabled = True
        End If
    
    End Function

  12. #12
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Navigation-davegri-v02.zip

    If you want your original code to work, see this.

  13. #13
    d9pierce1 is offline Expert
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Thanks davegri,
    I thank you so much however, that's not my code, yes, it does work for doing what I wanted to do, but it is event procedures in a private sub doing it, not a call to a public function as I was working on. The whole point of doing this public function was that I have a new db with a ton of forms and for the most part similar button controls such as First, last, next, prev, add, edit, cancel, undo, close, save, delete. so with the public function I could just copy and paste the buttons on my forms and they would work without having to copy and paste a ton of code for a private sub. Everything works as far as buttons go, the only error I have is when the form or forms are open, I cant get the code in above post 11 to enable = false the first and prev buttons when I first open the form. It works fine if you go next or last first, then hit prev or first. But that was my whole purpose in using a public function for buttons that are identical through out forms.
    Thanks
    Dave

  14. #14
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    I cant get the code in above post 11 to enable = false the first and prev buttons when I first open the form
    Where are you calling the code? OnLoad or OnCurrent?
    Post # 11 shows a public function BTW.

    You also are not declaring Option Explicit which you really should. It will save you lots of aggravation.

  15. #15
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    I cant get the code in above post 11 to enable = false the first and prev buttons when I first open the form. It works fine if you go next or last first, then hit prev or first.
    Post #12 will do that using your code.
    The code below would go on all forms. The red line would call your public function telling it which form to use.
    Code:
    Option Compare Database
    Option Explicit
    
    
    Private Sub cmdFirst_Click()
        Call GotoFirst
    End Sub
    
    
    Private Sub cmdLast_Click()
        Call GotoLast
    End Sub
    
    
    Private Sub cmdNext_Click()
        Call GotoNext
    End Sub
    
    
    Private Sub cmdPrev_Click()
        Call GotoPrevious
    End Sub
    
    
    Private Sub form_current()
    ' Provide a record counter for using with
    ' custom navigation buttons (when not using
    ' Access built in navigation)
        Dim rst As DAO.Recordset
        Dim lngCount As Long
        Set rst = Me.RecordsetClone
        With rst
            .MoveFirst
            .MoveLast
            lngCount = .RecordCount
        End With
        Set rst = Nothing
    'Show the result of the record count in the text box(txtRecordNo)
        Me.txtRecordNo = Me.CurrentRecord & " of " & lngCount
        'Add this call to set the buttons...
        Call GotoCurrent(Me.Name)
    End Sub

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 07-16-2017, 05:48 PM
  2. Changing private sub dim to public sub dim
    By Stephenson in forum Programming
    Replies: 14
    Last Post: 10-05-2015, 02:59 PM
  3. Replies: 5
    Last Post: 01-08-2015, 12:40 PM
  4. Turning Private Function Into Public
    By g4tv4life in forum Programming
    Replies: 1
    Last Post: 02-04-2014, 05:31 PM
  5. Creating/Using Public Subs
    By sparlaman in forum Programming
    Replies: 3
    Last Post: 05-19-2011, 03:29 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