Solved Datasheet and Recordset issue (2 Viewers)

Local time
Tomorrow, 03:38
Joined
Sep 14, 2020
Messages
38
Hi,
Using Office 365 on latest OS - new to MS Access.
I have created a client browser window with a datasheet (sub-form) of clients (limited columns from table).
On the browser window I have added navigation buttons (as opposed to using the systems navigation toolbar).
I have written the code and tried a couple methods (not sure what I'm doing).
My initial code with no checking worked until I got an error when clicking 'next record" when at the last record.
I am trying to catch the error before it happens and now the next button does not work.
I suspect my issue is the record set does not have any relationship with the datasheet.
Any advice will be most welcome.
Code:
Private Sub btnNext_Click()

    Dim db As DAO.Database
    Dim rstClients As Recordset
    
    On Error GoTo Err_btnNext_Click
    
    Set db = CurrentDb
    Set rstClients = db.OpenRecordset("tblClients", dbOpenDynaset)
    Me.dshClientList.SetFocus
'    With rstClients
'        If .AbsolutePosition = .RecordCount - 1 Then
'            MsgBox "At last record"
'        Else
'            DoCmd.GoToRecord , , acNext
'        End If
'    End With
    
'    If Not rstClients.EOF 'Then
        rstClients.MoveNext
'    End If
    
    Me.Form.SetFocus
    
Exit_btnNext_Click:
    Set rstClients = Nothing
    Set db = Nothing
    Exit Sub
    
Err_btnNext_Click:
    MsgBox Err.Description
    Resume Exit_btnNext_Click
End Sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:38
Joined
May 7, 2009
Messages
16,504
is the Button on the Main Form?
replace "yourTable" (without quote) with your subform name on the below code:
Code:
Private Sub btnNext_Click()

    Dim rstClients As Recordset
    Dim theSubForm As Form
    On Error GoTo Err_btnNext_Click
    
    Set theSubForm = Me!yourTable.Form
    Set rstClients = theSubForm.RecordsetClone
    
    With rstClients
        If Not (.BOF And .EOF) Then
            rstClients.MoveNext
        End If
        If Not .EOF Then
            theSubForm.Bookmark = .Bookmark
        End If
    End With
    
Exit_btnNext_Click:
    Set rstClients = Nothing
    Set theSubForm = Nothing
    Exit Sub
    
Err_btnNext_Click:
    If Err.Number <> 3021 Then
        MsgBox Err.Description
    End If
    Resume Exit_btnNext_Click
End Sub
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:38
Joined
Sep 21, 2011
Messages
10,618
Hi,
Using Office 365 on latest OS - new to MS Access.
I have created a client browser window with a datasheet (sub-form) of clients (limited columns from table).
On the browser window I have added navigation buttons (as opposed to using the systems navigation toolbar).
I have written the code and tried a couple methods (not sure what I'm doing).
My initial code with no checking worked until I got an error when clicking 'next record" when at the last record.
I am trying to catch the error before it happens and now the next button does not work.
I suspect my issue is the record set does not have any relationship with the datasheet.
Any advice will be most welcome.
Code:
Private Sub btnNext_Click()

    Dim db As DAO.Database
    Dim rstClients As Recordset
   
    On Error GoTo Err_btnNext_Click
   
    Set db = CurrentDb
    Set rstClients = db.OpenRecordset("tblClients", dbOpenDynaset)
    Me.dshClientList.SetFocus
'    With rstClients
'        If .AbsolutePosition = .RecordCount - 1 Then
'            MsgBox "At last record"
'        Else
'            DoCmd.GoToRecord , , acNext
'        End If
'    End With
   
'    If Not rstClients.EOF 'Then
        rstClients.MoveNext
'    End If
   
    Me.Form.SetFocus
   
Exit_btnNext_Click:
    Set rstClients = Nothing
    Set db = Nothing
    Exit Sub
   
Err_btnNext_Click:
    MsgBox Err.Description
    Resume Exit_btnNext_Click
End Sub
You need to allow for BOF and EOF, when you are on the first or last record.
Most people will disable the Previous button if on the first record and Next if on the last record.

You need to apply similar logic that @arnelgp has supplied to the Previous button as well.
 
Local time
Tomorrow, 03:38
Joined
Sep 14, 2020
Messages
38
Thank you @arnelgp,
Yes, my navigation buttons are on the main form.
I understand my issue is the Recordset reflecting the data in my datasheet where you have guided me towards RecordsetClone.
However, I am struggling to understand your code Set theSubForm = Me!yourTable.Form
Firstly, is using "Me!yourTable.Form" the same as "Me.yourTable.Form" ?
I have tried a couple of options from your code without success.
I don't understand your reference "yourTable" ?
The name of my elements are:
  • client table - "tblClients"
  • main form - "frmClientBrowser" which includes the navigation buttons;
  • sub-form - "subfrmClientList"
    • Datasheet
I have attached images of forms (which may help)
I would appreciate understanding the connection (theory) so I can learn from here on.

Thanks
Peter
 

Attachments

  • frmClientBrowser - Design.jpg
    frmClientBrowser - Design.jpg
    173.1 KB · Views: 255
  • frmClientBrowser - View.jpg
    frmClientBrowser - View.jpg
    133.9 KB · Views: 289

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:38
Joined
May 7, 2009
Messages
16,504
You replace yourtable with subfrmclientList on the code i gave:

theSubForm = Me!subfrmclientList.Form
 
Local time
Tomorrow, 03:38
Joined
Sep 14, 2020
Messages
38
Thank you again @arnelgp,
No longer crashing due to code (including a spelling mistake I had)
I was presented with a Microsoft Access message "Microsoft Access can't find the field 'subfrmClientList' referred to in your expression.
Further thought and investigation, I found I used the 'Name' "dshClientList" for the sub-form.
Problem solved

Thank you also to @Gasman for the advice to disable buttons which I will do.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:38
Joined
Feb 19, 2002
Messages
36,453
ALWAYS disambiguate DAO and ADO objects -- I.e. DAO.Recordset, DAO.Database, etc. This avoids a problem if someone down the road adds ADO code to a formerly DAO only application or vice versa. MS caused a big kerfuckle in 2002 or 2003 when they switched the default library from DAO to ADO and broke a gazillion databases. Then they switched back the default to DAO with the next version and broke more. I doubt they'll do that again but it is easy to do it to yourself if you add ADO code to an application

Once you get the code working, generalize it so you can put it in a standard module and call it from any form. You don't want the same code to exist in multiple forms. If you can't figure out how to generalize it, start another question.
 
Local time
Tomorrow, 03:38
Joined
Sep 14, 2020
Messages
38
Thanks @Pat Hartman ,
I'm researching to ensure I avoid the DAO / ADO issue you raised.
I will also research the scope of modules with the hope of learning enough without posting a new thread.
Cheers Peter
 

Users who are viewing this thread

Top Bottom