Results 1 to 15 of 15
  1. #1
    Larryg is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2015
    Posts
    50

    Navigation buttons on UNBOUND form

    Hello All;
    I have an unbound form (i.e. record source = blank). It has about 15 text box controls (all reflect "unbound" in design mode). There are 2 controls with control sources of "=[frmDate]" and "=(GetUserName())" (which function fine).
    The form opens with blank fields and a blank text box, and a search button is used to kick off the search action (once the desired search term is entered), and the results (record #1) are populated in the respective fields. (works fine)
    I use vba SQL code to query the table and populate the form (all works fine).
    I use: Debug.Print rs.Fields("AddressLine1") & " " & rs.Fields("AddressLine2") & " " & Me.CurrentRecord; "" and correct info is populated in the Intermediate window (complete with increasing CurrentRecord count number)i.e. search works as desired.
    I use: Me.txtRecordDisplay = "Record # " & Me.CurrentRecord & " of " & lngRSCount to display Record# and Total Count. (and this displays correctly also).

    The issue is navigating through the recordset once the form is populated.
    I use:
    Private Sub cmd_MoveNext_Click()
    Dim db As DAO.database
    Dim rs As DAO.Recordset
    Dim RecordCount As Integer
    Set db = CurrentDb


    'Set rs = Me.Recordset
    Set Forms("frm_LogMailEntry_EDIT").Recordset = rs
    'Set rs = Me.Recordset.Clone

    'If rs.RecordCount + 1 = rs.RecordCount Then
    'Me.Cmd_MoveNext.Enabled = False
    'Else
    DoCmd.GoToRecord acActiveDataObject, , acNext
    Debug.Print rs.Fields("AddressLine1") & " " & rs.Fields("AddressLine2") 'to test functionality

    'rs.MoveNext

    'Me.AddressLine1 = rs![AddressLine1]
    'RecordCount = RecordCount + 1
    'End If
    End Sub

    An error 2105, "You can't go to the specified record" occurs on the line "DoCmd.GoToRecord acActiveDataObject, , acNext".
    Using "rs.MoveNext" produces an error #91, Object variable or With block variable not set"
    Using only the below code on the MoveNext button produces NO error, but record values do not change on the form, i.e. does nothing.
    Dim rs As DAO.Recordset
    Set rs = Me.Recordset
    rs.MoveNext

    I have tried several different configurations (hence the rem lines) but none work. I even tried using the "Wizzard" for button creation, which was unsuccessful, as well as turning on Navigation Buttons in the property Sheet and neither attempt worked (although the latter indicated the records were changing, but nothing changed on the form).
    Any suggestions would be greatly appreciated.

    Thanks...

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you dont need code to navigate thru a form.
    bind it to a dataset and move on.
    dont re-invent the wheel.

  3. #3
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    An unbound form doesn't have a recordset. You are populating the unbound fields with VBA, but the form itself doesn't "know" anything about where that data came from. If you want to be able to navigate through a form's recordset, you have to have a table or query as the form's record source - there is no way around that.

    You could always create, maintain, navigate through a recordset and display data entirely using VBA, I guess - but why would you want to?

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    It sounds like you are assigning a recordset to the Form after the form opens. In this case, you have a bound form. So I would just use Docmd.GoToRecord
    https://msdn.microsoft.com/en-us/lib.../ff194117.aspx

    And I was use the AcRecord constants for Next, Previous, etc.
    https://msdn.microsoft.com/en-us/lib.../ff192641.aspx

    If you want to use DAO, create a recordset clone of the form's recordset and then use the Bookmark method. Something like this ...
    https://www.accessforums.net/showthr...074#post303074

  5. #5
    Larryg is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2015
    Posts
    50
    Quote Originally Posted by ItsMe View Post
    It sounds like you are assigning a recordset to the Form after the form opens. In this case, you have a bound form. So I would just use Docmd.GoToRecord
    https://msdn.microsoft.com/en-us/lib.../ff194117.aspx

    And I was use the AcRecord constants for Next, Previous, etc.
    https://msdn.microsoft.com/en-us/lib.../ff192641.aspx

    If you want to use DAO, create a recordset clone of the form's recordset and then use the Bookmark method. Something like this ...
    https://www.accessforums.net/showthr...074#post303074
    ItsMe... tried suggestion #1:

    DoCmd.GoToRecord , , acNext
    Debug.Print rs.Fields("AddressLine1") & " " & rs.Fields("AddressLine2")

    This displayed the next record in the intermediate window, but the [text box] record# of # didn't change, nor did the data on the form.

    Do I need to:

    With rs
    Me.AddressLine1 = rs![AddressLine1]
    Me.AddressLine2 = rs![AddressLine2]
    i.e. basically treat the move as a new search?

    Thanks for the reply ItsMe...

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    I am not understanding why you are mixing the Docmd and the DAO recordset. When you create a DAO recordset, it is a recordset in addition to any existing recordsets, e.g. a form's recordset. Even if you create a clone of an existing recordset, it is a second recordset.

    Determine which recordset you want to navigate. rs.MoveNext and DoCmd.GoToRecord , , acNext serve different purposes.

    Place
    DoCmd.GoToRecord , , acNext

    behind a button click event to move a bound form's recordset to the next record. The user will see the next record when the user clicks the button. End of story for the Docmd thing.

  7. #7
    Larryg is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2015
    Posts
    50
    Quote Originally Posted by ItsMe View Post
    I am not understanding why you are mixing the Docmd and the DAO recordset. When you create a DAO recordset, it is a recordset in addition to any existing recordsets, e.g. a form's recordset. Even if you create a clone of an existing recordset, it is a second recordset.

    Determine which recordset you want to navigate. rs.MoveNext and DoCmd.GoToRecord , , acNext serve different purposes.

    Place
    DoCmd.GoToRecord , , acNext

    behind a button click event to move a bound form's recordset to the next record. The user will see the next record when the user clicks the button. End of story for the Docmd thing.
    OK... so now all I have is:

    Private Sub cmd_MoveNext_Click() 'the button on the form
    DoCmd.GoToRecord , , acNext
    End Sub

    (those 3-lines, nothing else)
    Nothing happens when the button is clicked (i.e. data does not change, (and no error)). Leaving in the Dubug.Print line caused an error of Variable Not Defined on the rs.Fields portion of the Debug.Print line, so I rem'ed it out. However after doing that I can't see if the record is moving forward on the intermediate screen (for testing only, not production).

    I know in my mind this should be working, but its not. Could there be something about the form design that is causing this to fail ? Its a single form, not continuous, and no subform. Record #1 displays on the form, clicking the "Next" button moves to the next record in the intermediate window (before this latest code change), and the record pointers appear to be functioning. Maybe I'll try creating a brand new form with limited fields to see if there is any change. Other than that, I'm stuck... and puzzled.

    Thanks for your replies... they are greatly appreciated.

  8. #8
    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 Larryg View Post
    Do I need to:

    With rs
    Me.AddressLine1 = rs![AddressLine1]
    Me.AddressLine2 = rs![AddressLine2]
    i.e. basically treat the move as a new search?
    If it's really an unbound form GoToRecord won't work, and the answer to the above is yes. With an unbound form, you have to do all the work that Access would do for you with a bound form.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    Larryg is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2015
    Posts
    50
    Quote Originally Posted by pbaldy View Post
    If it's really an unbound form GoToRecord won't work, and the answer to the above is yes. With an unbound form, you have to do all the work that Access would do for you with a bound form.
    pbaldy...

    I guess it could be said that it's truly an unbound form... right up to the point that it loads the desired records into the Recordset. So, is any form that displays data as a result of a strSQL=Select * From // Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset) statement an unbound form at that point? I don't know, just asking.

    It would seem, in my case anyway, that once the records are loaded into the Recordset, (me.recordset) I should be able to navigate around within that Recordset. Am I way off-base here?

    Thanks...

  10. #10
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    It would seem, in my case anyway, that once the records are loaded into the Recordset, (me.recordset) I should be able to navigate around within that Recordset. Am I way off-base here?
    No, not really. You have established that you have the required data in your recordset, and can navigate through it (I think). BUT, since the controls on your form are all unbound, they will not update automatically as you move from one record to another - you have to do that yourself with VBA.

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If you're doing this, it's an unbound form:

    Me.AddressLine1 = rs![AddressLine1]

    If you're doing

    Me.RecordSource = "Whatever"

    then it's not.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    Larryg is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2015
    Posts
    50
    Quote Originally Posted by John_G View Post
    No, not really. You have established that you have the required data in your recordset, and can navigate through it (I think). BUT, since the controls on your form are all unbound, they will not update automatically as you move from one record to another - you have to do that yourself with VBA.
    John_G... Yes, you are correct, via the intermediate window, I can see the records in the current recordset, and can watch them change as I click the next and/or previous buttons. So, now I will code the controls and see if I can see the data populate the controls on the form.

    Thanks...

  13. #13
    Larryg is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2015
    Posts
    50
    Quote Originally Posted by Larryg View Post
    John_G... Yes, you are correct, via the intermediate window, I can see the records in the current recordset, and can watch them change as I click the next and/or previous buttons. So, now I will code the controls and see if I can see the data populate the controls on the form.

    Thanks...
    John_G...

    Bingo... that did it. Records now changing (and displaying on the form) as I navigate forward and backward using the buttons on the form. I have some clean up to do, but I think I'm in good shape now.

    Thanks...

  14. #14
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Glad we could help.

    Just out of curiosity - is there any particular reason you are using an unbound form and controls? If you don't want any data to be changed, you can always lock the controls to prevent editing.

  15. #15
    Larryg is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2015
    Posts
    50
    Quote Originally Posted by John_G View Post
    Glad we could help.

    Just out of curiosity - is there any particular reason you are using an unbound form and controls? If you don't want any data to be changed, you can always lock the controls to prevent editing.
    Reason: This form is an edit form, however there may be some as view only. I want more control over the data on the form, and the user's use of such. I also am saving more data than is in the table, such a date/time updated, user updating the record, form from which the data was updated. My thinking is... I can control more with an unbound form than with a bound form. Maybe not, but that was my thinking. I suspect there are opinions either way.

    Now onto getting the buttons to function to enable true/false as the BOF and EOF are encountered. I certainly see that it would be easier to let Access do the work, but at this point maybe its simply the challenge. Is there medication for that ??? lol

    Thanks...

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

Similar Threads

  1. Replies: 2
    Last Post: 04-01-2016, 11:13 AM
  2. Filter buttons in navigation form
    By Charles CL in forum Forms
    Replies: 1
    Last Post: 02-05-2016, 01:04 PM
  3. Replies: 4
    Last Post: 09-22-2015, 09:06 AM
  4. Replies: 1
    Last Post: 06-01-2011, 06:44 PM
  5. Replies: 0
    Last Post: 12-12-2009, 04:45 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