Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Paul1 is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2014
    Posts
    50

    Question How to navigate through unbound form with buttons starting from record opened via OpenArgs parameter

    My database uses unbound forms exclusively. A listbox on the main form is bound to a query via the RowSource property. When you doubleclick the listbox a new form opens and the selected row is displayed in the new form which allows you to edit the table via the OpenRecordset method. The requirement now is to be able to use navigation buttons to scroll backwards and forwards through the table records in this form.

    Please see the code below. If I give the recordset module level scope by placing the declaration at the top of the form module, then I can scroll backwards and forwards through the recordset from a button that runs code in a different procedure i.e. cmdPrevious_Click(). Only I want to be able to scroll backwards and forwards from the record that is opened via the OpenArgs parameter and at the moment, the backwards navigation button for example, takes me to the BOF. As you can see, I have tried using bookmarks but these are not working and I get error 3159 - not a valid bookmark. I would appreciate some help as to how I can do this.
    Many thanks.
    Paul

    Code:
    Option Compare Database
    Option Explicit
    
    Dim mlngUserID As Long, mstrBookmark As String
    Dim db As DAO.Database, qdf As DAO.QueryDef, rst As DAO.Recordset
    
    Private Sub Form_Open(Cancel As Integer)
    On Error GoTo Err_Form_Open
        Dim qdf2 As DAO.QueryDef, rst2 As DAO.Recordset
        Dim strSQL As String, strSQLadd As String
        
        If Not IsNull(Me.OpenArgs) Then
            Set db = CurrentDb
        
            mlngUserID = Me.OpenArgs
        
            Set qdf = db.QueryDefs("qryUsers")
            
            Set rst = qdf.OpenRecordset  'open recordset for later (ie. move previous, move next etc)
            
            strSQL = qdf.SQL
    
            strSQLadd = "WHERE [tblUsers].[User ID] = " & mlngUserID & " ORDER BY tblUsers.Surname"
    
            strSQL = Replace(strSQL, "ORDER BY tblUsers.Surname;", strSQLadd)
          
            Set qdf2 = db.CreateQueryDef("", strSQL)
    
            Set rst2 = qdf2.OpenRecordset()
            
            mstrBookmark = rst2.Bookmark
                    
            With rst2
                Me.txtForename = !Forename
                Me.txtSurname = !Surname
                Me.txtUsername = !Username
                Me.txtEmail = !Email
                Me.txtTelephoneExt = ![Tel Ext]
                Me.cboDivision_PSG = !Division_PSG
                Me.txtSection = !Section
                Me.cboWorkspace = !Workspace
                Me.cboReportUser = ![Report User]
                Me.chkReportOwner = ![Report Owner]
                Me.chkLaunchPadAccount = ![Launch Pad Account]
                Me.txtNotes = !Notes
            End With
                
        End If
        
    Exit_Form_Open:
        rst2.Close
        Set rst2 = Nothing
        qdf2.Close
        Set qdf2 = Nothing
        Set db = Nothing
    Exit Sub
        
    Err_Form_Open:
        Call MsgBox(Err.Description, vbExclamation, "Error #: " & Err.Number)
        Resume Exit_Form_Open
    End Sub
    
    Private Sub cmdPrevious_Click()
    On Error GoTo Err_cmdPrevious_Click
    
        rst.Bookmark = mstrBookmark
        rst.MovePrevious
        If rst.BOF Then
            MsgBox "at beginning"
            rst.MoveFirst
        End If
    
        With rst
            Me.txtForename = !Forename
            Me.txtSurname = !Surname
            Me.txtUsername = !Username
            Me.txtEmail = !Email
            Me.txtTelephoneExt = ![Tel Ext]
            Me.cboDivision_PSG = !Division_PSG
            Me.txtSection = !Section
            Me.cboWorkspace = !Workspace
            Me.cboReportUser = ![Report User]
            Me.chkReportOwner = ![Report Owner]
            Me.chkLaunchPadAccount = ![Launch Pad Account]
            Me.txtNotes = !Notes
        End With
    
    Exit_cmdPrevious_Click:
    Exit Sub
    
    Err_cmdPrevious_Click:
        Select Case Err.Number
        Case Else
            Call MsgBox(Err.Description, vbExclamation, "Error #: " & Err.Number)
            Resume Exit_cmdPrevious_Click
        End Select
    End Sub


  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'll point out that you close the recordset in the open event, so it is certainly not available in the click event.

    That said, why go through the extra work of using unbound forms? They have their place, I've used them myself, but I don't see the point here. You're going to pull all the data over the wire for the recordset anyway, so it's not like there's a performance gain.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Paul1 is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2014
    Posts
    50
    Thanks for your reply. This database is a series of data entry forms. The way I understand it is that with bound forms the data is saved into the table as soon as it is entered and you can only cancel the insert using some dirty function. I want the user to click the save button in order to save the record. I don't think you can do that with bound forms?

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You can, though it requires some effort. Some people bind the form to a temp table, then append into the real table when the save button is clicked. Your question doesn't deal with data entry though, it deals with navigating existing records.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Paul1 is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2014
    Posts
    50
    I must admit I don't know how to bind the form to a temp table. Is the effort required to do this still less than using unbound forms which I am familiar with?

    Also, I am only closing rst2 which is used to get a value for the bookmark (which is then saved to a variable). rst is not closed and is based on the query ("qryUsers") for the underlying table that I want to navigate, so I would still appreciate if you can suggest why the bookmark is not working?

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Rather than temp table I should say local table. A table that you empty upon starting the form, let the user add a record or records, then on a save button run an append query to populate the main table from the local table.

    Sorry, I missed that there were 2 recordsets. Where exactly do you get the error? Can you attach the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Paul1 is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2014
    Posts
    50
    Thanks. I will attach the db as soon as I am able to replace the personal data with some dummy data.

  8. #8
    Paul1 is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2014
    Posts
    50

    Question

    To run the db, click Users and then double click a record. This opens the edit record form. When you try to use the back navigation button then you get Error 3159 - not a valid bookmark. Please note that if you comment out the line rst.Bookmark = mstrBookmark in the procedure cmdPrevious_Click() of formfrmEditUser then the backwards navigation will work but does not start from the current record but rather from BOF. I would appreciate if you can explain why the bookmark does not work here.
    Many thanks.
    Attached Files Attached Files
    Last edited by Paul1; 06-21-2017 at 04:35 AM. Reason: OK I worked out how to upload the db

  9. #9
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    if you want to use mstrBookmark as a variable in the 'back' button you have to pass the value to the function, as it is the value of mstrBookmark within your on click event is an undefined variable so it doesn't know what to do with it. You're defining mstrBookmark in your ON OPEN event but not in the cmdPrevious_Click event. Essentially you would need to set up the same bookmarking as you have on your frmEditUser for frmViewUsers to work or be able to pass the mstrBookmark Value to the on click event.

  10. #10
    Paul1 is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2014
    Posts
    50
    Thanks. I think that the value of mstrBookmark is available to the on click event because I gave the variable module level scope at the top of the form code in frmEditUser.

    Code:
    
    
    Code:
    Option Compare Database
    Option Explicit
    
    
    Dim mlngUserID As Long, mstrBookmark As Variant
    Dim db As DAO.Database, qdf As DAO.QueryDef, rst As DAO.Recordset
    
    
    Private Sub Form_Open(Cancel As Integer)
    On Error GoTo Err_Form_Open


  11. #11
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    you can use the variable, yes, but it's not populated in the on click event, it's essentially a null value.

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    When I comment out this line:

    rst.Bookmark = mstrBookmark 'comment out this line and navigation works but only when not at BOF

    it appears to work, even at BOF (I get the message box "at beginning").
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by rpeare View Post
    you can use the variable, yes, but it's not populated in the on click event, it's essentially a null value.
    For clarity, the variable is "set" in the sense that there's a line of code setting it, but it isn't "set" in that it doesn't hold a value you can use. If you examine the variable while the code is running, you'll see the "essentially null" value rpeare describes.

    And further, there's no point to that line. Since the recordset is left open, it will still be at the same record as when it was set in the open event, or either click event. That line would set it to where it already is.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  14. #14
    Paul1 is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2014
    Posts
    50
    Thank you both for your answers which I appreciate.


    I do apologize but I am struggling to fully understand your explanations.


    I thought that when you declare a variable with form level scope, in this case mstrBookmark, and then assign it a value as I have done, then it is available to all of the procedures in that form and it will retain its value until either its value is changed or the form closes and then the variable goes out of existence.


    I am trying to set the bookmark to be the location in the recordset based on OpenArgs. I understand that you are saying that I need to set the bookmark in the previous button event as I have already done in the open form event although I'm not really sure how to do this. Would you please be able to provide a little code snippet or some pseudo code?

  15. #15
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'm saying you don't need to set it in the click events. Visualize a book as the recordset that you open to page 7 in the open event. When the click event runs, the book is still open to page 7. You don't have to find page 7 again because you left it open.
    Last edited by pbaldy; 06-22-2017 at 08:05 AM. Reason: fix goof
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Navigate attachment using buttons on form
    By saleemsadique in forum Access
    Replies: 2
    Last Post: 02-18-2014, 03:15 PM
  2. Replies: 11
    Last Post: 04-05-2013, 11:35 AM
  3. Replies: 5
    Last Post: 01-27-2013, 06:04 PM
  4. Buttons on form to navigate through records
    By emilyrogers in forum Forms
    Replies: 2
    Last Post: 07-19-2011, 10:17 AM
  5. Replies: 2
    Last Post: 03-25-2010, 12:11 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