Results 1 to 11 of 11
  1. #1
    ASWilliams is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    10

    Assigning updated recordset to form not working in VBA

    Hello Forum,

    I'm trying to update a form's recordset (after I update it) using VBA. My code doesn't break at any point during execution but the form doesn't display the new values.

    As you can see in the code below, I have written a simple loop to print the underlying records to the Immediate window, which shows the data as I expect it to be. As you can also see I've tried to refresh the form using all methods I can think of.



    Code:
    Public Sub CompareTableValues(ByRef rs1 As ADODB.Recordset, ByVal rs2 As ADODB.Recordset, ByRef FrmToUpdate As Form)    'rs1 is the one to update; rs2 is the reference.
        rs1.Close
        rs1.LockType = adLockBatchOptimistic
        rs1.Open
        
        If Not rs1.BOF Then rs1.MoveFirst
        
        Do Until rs1.EOF
            rs2.Find rs2.Fields(0).Name & "='" & rs1.Fields(0).value & "'", 0, adSearchForward, adBookmarkFirst
            If rs2.EOF = True Or rs2.BOF = True Then
                rs1.Fields(3).value = "No"
            Else
                rs1.Fields(3).value = "Yes"
            End If
            rs1.MoveNext
        Loop
        
        'move cursors to first record
        rs1.MoveFirst
        rs2.MoveFirst
        
        rs1.Update
        
        Set FrmToUpdate.Recordset = rs1
        FrmToUpdate.Requery
        FrmToUpdate.Repaint
        FrmToUpdate.Recalc
        FrmToUpdate.Refresh
        
        'checks values in form recordset
        Do Until FrmToUpdate.Recordset.EOF
            For i = 0 To FrmToUpdate.Recordset.Fields.Count - 1
                Debug.Print FrmToUpdate.Name & "; Record (" & frmToUpdate.AbsolutePosition & "); " & FrmToUpdate.Recordset.Fields(i).Name & ": " & FrmToUpdate.Recordset.Fields(i).value
            Next i
            FrmToUpdate.Recordset.MoveNext
        Loop
        
    End Sub
    If anyone can see what I'm missing (i.e. why isn't the data displayed on my form updating while its recordset is appearing to have been updated) I'd be most grateful (so I can move onto the next issue!).

    Many 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 don't need code to do this.
    this is what update queries are for, and they run a lot faster than thIs looping code.

  3. #3
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    I use DAO when I can and rarely have I used ADO. So, I may not have a perfect understanding of each ADO method you have used here. Having said that, I think you should be placing a break point in your code so you can step through your loop in Debug Mode.

    What you are doing here does not make sense to me ...
    Code:
    rs2.Find rs2.Fields(0).Name & "='" & rs1.Fields(0).value & "'",
    rs2.Fields(0).Name is going to be the name of the first column within the Recordset. The first column name will be dependent on the SQL or how the table was created.

    For instance
    SELECT tableName.ColumnOne FROM tableNAme
    rs2.Fields(0).Name will be ColumnOne

    I do not see how you will be able to Find on the Name property.
    rs2.Find rs2.Fields(0) & "='" & rs1.Fields(0).value & "'", ...
    Makes more sense

  4. #4
    ASWilliams is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    10
    Thanks for your response, ranman. I'm using this code because my data resides in an SQL Server database and because I don't want to update the actual data (I'm adding an indicator to the form that shows when a value exists in another lookup table). To add a little background: this is part of an audit process prior to a dataset being imported into an SQL Server database. I load the data into a temporary table in access and summarise distinct values in given columns. In another subform, I show distinct values in the corresponding table in my SQL database; I then show whether or not there is a match in the data to be imported and provide a mechanism to amend the new data prior to it being imported.

    That all said, all of the processes I aim to achieve with this project so far are working as expected. My problem currently is the fact that my recordset is being updated but the form isn't displaying it as I expect it to.

    Hope that makes sense.

  5. #5
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You have to open a recordset in Edit mode if you want to update it: rs1.Edit
    I don't use ADO much; not sure I would invoke rs.Open because I presume you've correctly created the recordset in the calling procedure and are passing it to your sub. It should already exist. Perhaps you are substituting Open for Edit based on the assumption that this is how you put it in Edit mode?

    You might want to test if the rs is updatable if it's possible that someone else has effected a lock on it: If rs1.Updatable...
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    ASWilliams is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    10
    ItsMe: thanks also for your response. The line of code you query works fine: the way I've set up my queries and code necessitates it. My loop is also working fine (the resulting recordset is exactly what I need it to be). My problem is that my form doesn't reflect what is in the updated recordset (the results of my 'debug.print' loop is shown below):

    frmFieldValues_DS; Record (1); SiteType: VALUE 1
    frmFieldValues_DS; Record (1); NumRecs: 1
    frmFieldValues_DS; Record (1); Change Value: Edit
    frmFieldValues_DS; Record (1); Has Match: Yes
    frmFieldValues_DS; Record (2); SiteType: VALUE 2
    frmFieldValues_DS; Record (2); NumRecs: 2
    frmFieldValues_DS; Record (2); Change Value: Edit
    frmFieldValues_DS; Record (2); Has Match: Yes
    frmFieldValues_DS; Record (3); SiteType: VALUE 3
    frmFieldValues_DS; Record (3); NumRecs: 2
    frmFieldValues_DS; Record (3); Change Value: Edit
    frmFieldValues_DS; Record (3); Has Match: Yes

    I hope this clarifies my post a little...

    Thanks

  7. #7
    ASWilliams is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    10
    Quote Originally Posted by Micron View Post
    You have to open a recordset in Edit mode if you want to update it: rs1.Edit
    I don't use ADO much; not sure I would invoke rs.Open because I presume you've correctly created the recordset in the calling procedure and are passing it to your sub. It should already exist. Perhaps you are substituting Open for Edit based on the assumption that this is how you put it in Edit mode?

    You might want to test if the rs is updatable if it's possible that someone else has effected a lock on it: If rs1.Updatable...
    Thanks for your reply, Mircron. To my understanding, ADO recordsets don't require Edit mode. This is demonstrated in the updated results I achieve (as shown in the 'debug.print' loop outputs, below):

    frmFieldValues_DS; Record (1); SiteType: VALUE 1
    frmFieldValues_DS; Record (1); NumRecs: 1
    frmFieldValues_DS; Record (1); Change Value: Edit
    frmFieldValues_DS; Record (1); Has Match: Yes
    frmFieldValues_DS; Record (2); SiteType: VALUE 2
    frmFieldValues_DS; Record (2); NumRecs: 2
    frmFieldValues_DS; Record (2); Change Value: Edit
    frmFieldValues_DS; Record (2); Has Match: Yes
    frmFieldValues_DS; Record (3); SiteType: VALUE 3
    frmFieldValues_DS; Record (3); NumRecs: 2
    frmFieldValues_DS; Record (3); Change Value: Edit
    frmFieldValues_DS; Record (3); Has Match: Yes

    I use the open method so that I'm able to change the LockType of the recordset.

  8. #8
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    To my understanding, ADO recordsets don't require Edit mode
    I will take your word for that since as I said, I rarely use ADO. However, my experience with DAO suggests it is necessary. I based my post on the sample code provided here
    where you'll see the statement
    To edit a record, use the Edit method to copy the contents of the current record to the copy buffer. If you don't use Edit first, an error occurs when you use Update or attempt to change a field's value.
    I concede that you aren't generating an error since you haven't reported one, but I have no idea if you've intentionally suppressed that (if possible).
    The fact that you are changing the values in a recordset which resides in memory (and can therefore debug.print those changes) does not mean you have altered the original query or table based recordset upon which the in-memory rs is derived from. I would think the .Edit method would be required to do that by copying the record to the buffer first - unless I don't understand ADO recordsets, which is quite possible.

    I have never had an updating issue with DAO recordsets; maybe that is or is not because I've always used .Edit first.

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @Micron
    That link is for DAO, not ADO.

    I also have rarely use ADO, if at all.
    I do have to agree that ADO does not have the "EDIT" command. But you do need to use the "UPDATE" command.


    From https://msdn.microsoft.com/en-us/lib.../jj250294.aspx
    Use the Update method to save any changes you make to the current record of a Recordset object since calling the AddNew method or since changing any field values in an existing record. The Recordset object must support updates.
    So I think you should try adding the update command before the move next command
    Code:
    <snip>
        Do Until rs1.EOF
            rs2.Find rs2.Fields(0).Name & "='" & rs1.Fields(0).value & "'", 0, adSearchForward, adBookmarkFirst
            If rs2.EOF = True Or rs2.BOF = True Then
                rs1.Fields(3).value = "No"
            Else
                rs1.Fields(3).value = "Yes"
            End If
    
            rs1.Update
    
            rs1.MoveNext
        Loop
    <snip>

  10. #10
    ASWilliams is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    10
    Thank you for your comments, @ssanfu. Unfortunately, moving the .Update line within the loop has made no noticeable difference (although I thought it was in with a shot when I read your post). You are, however, correct about ADO having no .Edit method.

    @micron, I appreciate your continued support on this one (it's proving to be stubborn!). Your point about me changing the recordset is interesting.

    The fact that you are changing the values in a recordset which resides in memory (and can therefore debug.print those changes) does not mean you have altered the original query or table based recordset upon which the in-memory rs is derived from.
    To the best of your knowledge and experience (granted you are not too familiar with ADO), do you think changing the form's recordset property to the newly updated recordset should work (as per code below)?

    Code:
    Set FrmToUpdate.Recordset = rs1
    FrmToUpdate.Requery
    FrmToUpdate.Repaint
    FrmToUpdate.Recalc
    FrmToUpdate.Refresh
    I would have thought that the debug outputs (which references the form's recordset) show me that I have indeed updated the form.

    Code:
    Debug.Print FrmToUpdate.Name & "; Record (" & FrmToUpdate.Recordset.AbsolutePosition & "); " & FrmToUpdate.Recordset.Fields(i).Name & ": " & FrmToUpdate.Recordset.Fields(i).value
    In my humble view, it would appear that the form's recordset has been updated, but the form is not updating, suggesting that the .Requery method (and my other desperate attempts!) isn't working. Do you think this could be it?

    Thanks

  11. #11
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    @ssanfu: thanks for the correction. Sometimes it doesn't pay to have too many browser tabs open at the same time (easy to get them confused). After further checking, I see that ADO has no .Edit method.
    Code:
    To the best of your knowledge and experience (granted you are not too  familiar with ADO), do you think changing the form's recordset property  to the newly updated recordset should work (as per code below)?
    As I mentioned, I suspect you are passing the recordset to a procedure, which can only be done if the rs resides in memory. But from what I know of rs's I would have thought the update should affect what's in the buffer, but I do believe you have to close the rs first to affect the source but I don't see where you do that (and again, we don't know what the source is - query or table - and if it's even updatable). Try closing the rs before requerying the form. If that doesn't do it, check the original table/query and see if the changes have been made there after the procedure runs. If not and it is an updatable source it suggests the changes have not been written to disk. If it is changed, the problem might be what the form is based on - which may not be the recordset you are updating in code.

    Consider posting a zipped version of your db if all our suggestions are not solving the issue. I'd like to know more about what's going on before your procedure is called.
    Last edited by Micron; 10-19-2016 at 02:34 PM. Reason: correction

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

Similar Threads

  1. DATEPART not working in VBA recordset
    By merebag in forum Access
    Replies: 4
    Last Post: 09-02-2016, 12:34 PM
  2. Replies: 8
    Last Post: 03-12-2016, 02:06 PM
  3. Recordset FindFirst Not Working
    By ShoresJohn in forum Programming
    Replies: 5
    Last Post: 03-01-2012, 06:59 PM
  4. Assigning controls not working
    By SemiAuto40 in forum Programming
    Replies: 5
    Last Post: 10-20-2011, 06:53 PM
  5. Working with ADO Recordset & Excel
    By jgelpi16 in forum Programming
    Replies: 7
    Last Post: 03-15-2011, 01:58 PM

Tags for this Thread

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