Solved CurrectRecord in Subform (1 Viewer)

Local time
Tomorrow, 01:59
Joined
Sep 14, 2020
Messages
38
Good evening,
I know what I am trying to achieve, however, the theory using VBA has the better of me.
I have a Form, "frmClinetBrowser" with a subForm, "subfrmClientList" (data grid of Client information, "dshClientList") which allows the user to browse the list of Clients (see attached).
I have replaced the standard navigation buttons with my own which are on the Form (not the subForm).
I have an on_click event for each button which works. e.g.
Code:
Private Sub btnNext_Click()
    
    On Error GoTo Err_btnNext_Click
    
    Forms![frmClientBrowser]![dshClientList].SetFocus
    DoCmd.GoToRecord , , acNext
    
Exit_btnNext_Click:
    Exit Sub
    
Err_btnNext_Click:
    MsgBox Err.Description
    Resume Exit_btnNext_Click
    
End Sub

I have code in Form_Current sub which does not work.
The code is designed to "disable" First and Previous command buttons if at Record 1. and "disable" Last and Next command buttons if at last Record.
I have a separate command button, New which will open a new form to enter a new clients information.
The Code below is for records in the Form, not the subForm - borrowed from an example I found.
My problem is I don't understand the VBA theory to access the correct information in the subForm from the Form to disable the relevant navigation buttons.
Code:
Private Sub Form_Current()

    On Error GoTo Err_Form_Current
        
        If Me.CurrentRecord = 1 Then
            Me.btnFirst.Enabled = False
            Me.btnPrevious.Enabled = False
        Else
            Me.btnFirst.Enabled = True
            Me.btnPrevious.Enabled = True
        End If
        If Me.CurrentRecord = Me.Recordset.RecordCount Then
            Me.btnLast.Enabled = False
        Else
            Me.btnLast.Enabled = True
        End If
        If Me.CurrentRecord >= Me.Recordset.RecordCount Then
            Me.btnNext.Enabled = False
        Else
            Me.btnNext.Enabled = True
        End If
    
Exit_Form_Current:
    Exit Sub
    
Err_Form_Current:
    MsgBox Err.Description
    Resume Exit_Form_Current

End Sub
I encounter an error "You entered an expression that has an invalid reference to the property CurrentRecord" which I assume is related to my overall problem.
I hope I have explained myself clearly and someone can assist me.
Thanking you
Peter
 

Attachments

  • Client Browser Form.jpg
    Client Browser Form.jpg
    121.9 KB · Views: 160

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:59
Joined
May 7, 2009
Messages
16,504
you should move the current Event
to the Current event of the Subform
Code:
Private Sub Form_Current()

    On Error GoTo Err_Form_Current
    With Me  
        If .CurrentRecord = 1 Then
            .Parent!btnFirst.Enabled = False
            .Parent!btnPrevious.Enabled = False
        Else
            .Parent!btnFirst.Enabled = True
            .Parent!btnPrevious.Enabled = True
        End If
        If Me.CurrentRecord = Me.Recordset.RecordCount Then
            .Parent!btnLast.Enabled = False
        Else
            .Parent!btnLast.Enabled = True
        End If
        If Me.CurrentRecord >= Me.Recordset.RecordCount Then
            .Parent!btnNext.Enabled = False
        Else
            .Parent!btnNext.Enabled = True
        End If
    End With
Exit_Form_Current:
    Exit Sub
   
Err_Form_Current:
    MsgBox Err.Description
    Resume Exit_Form_Current

End Sub
 
Local time
Tomorrow, 01:59
Joined
Sep 14, 2020
Messages
38
Thank you @arnelgp for your reply.
I initially struggled to understand your reference to .Parent as the command buttons are on the Parent Form, until I read your comment properly, moving the code to the subForm.
Whilst I get a better response, the result is wrong.
When the form loads all the buttons are disabled.
Do I need to ?? somehow ?? set the 'pointer' to the first record?
Regards
Peter
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:59
Joined
May 7, 2009
Messages
16,504
you can create a custom Class, so there
is central code (and can be re-used by many forms).
see Load event of Form1.
see the class.
 

Attachments

  • customNavigationButton.accdb
    652 KB · Views: 169

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:59
Joined
Feb 19, 2002
Messages
36,453
I don't like GUI's that don't follow standards. Since both the main form and the subform can have navigation controls, putting those for the subform on the main form violates the expectations of the standard user.

Also, although custom buttons might in some cases be visually more appealing, they are in no way better than the built in controls. In fact, in early 2006 at a conference to preview A2007 at the MS campus in Redmond, I suggested adding a "name" feature to each navigation bar to avoid confusion. The MS Engineers agreed and my idea made it into the final build. So of course I use the built in navigation:) My position with clients is - I can do whatever you want but built in Access features are free. Custom coding requires extra work and testing so I charge for it.
 
Local time
Tomorrow, 01:59
Joined
Sep 14, 2020
Messages
38
Thank you @arnelgp and @Pat Hartman for your replies,
@arnelgp - thank you the guidance with Classes, you are stretching my knowledge of Access which I appreciate and will embrace.
@Pat Hartman - thank you also for your comments, comments that are sound and valid, especially with my experience with Quality Systems. However, my experience suggests the importance of meeting Customer Expectations is high and the Access navigation is not very user friendly. I would suggest Users do not know that there is a subForm within a Form, just that there is a list of clients they can browse on a form. Also this database is for myself. I am developing it to get out of using a combination of spreadsheets and Outlook Contacts and for the challenge in learning to develop an Access Database - no commercial aspect to it. But like I said, thank you for your comments, all of which are valid.:)
Regards
Peter
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:59
Joined
Feb 19, 2002
Messages
36,453
the Access navigation is not very user friendly
That is an opinion I don't happen to share. Users don't need to know anything about subforms. The interface just needs to be clear to them which navigation controls go with which data. Using the caption property of the integrated navigation bar goes a long way to clarifying what is being controlled by the navigation controls.
 

Users who are viewing this thread

Top Bottom