Using .Edit & .Update on a RecordSet

[OldBirdman]

I have a table (Table1) and I want my users to be able to control the priority. For this question, consider only 2 fields, Priority (as Integer) and Desc (as String).

My form (Form1, Continuous) displays bound textboxes, txtPriority and txtDesc. The user changes the values in txtPriority, and then presses cmdReOrder. This should renumber Priority (in Table1) from 1 to n, and display the records in the new order.

The below code works perfectly, IF CALLED TWICE. In other words, if the statement "Call ReOrder" is 2 identical lines of code, this works as I intended. What little item am I missing here?

Private Sub cmdReOrder_Click()
    Call ReOrder
End Sub
 
Private Sub ReOrder()
Dim wkSQL As String
Dim i As Integer
    wkSQL = "SELECT Table1.Priority " & _
            "FROM Table1" & _
            "ORDER BY Table1.Priority;"
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(wkSQL)
 
    i = 1
    Do Until rst.EOF
        rst.Edit
        rst!Priority = i
        rst.Update
        rst.MoveNext
        i = i + 1
    Loop
    rst.Close
    dbs.Close
    Me.Requery
End Sub
 

[ADezii]

I have a table (Table1) and I want my users to be able to control the priority. For this question, consider only 2 fields, Priority (as Integer) and Desc (as String).

My form (Form1, Continuous) displays bound textboxes, txtPriority and txtDesc. The user changes the values in txtPriority, and then presses cmdReOrder. This should renumber Priority (in Table1) from 1 to n, and display the records in the new order.

The below code works perfectly, IF CALLED TWICE. In other words, if the statement "Call ReOrder" is 2 identical lines of code, this works as I intended. What little item am I missing here?

Private Sub cmdReOrder_Click()
    Call ReOrder
End Sub
 
Private Sub ReOrder()
Dim wkSQL As String
Dim i As Integer
    wkSQL = "SELECT Table1.Priority " & _
            "FROM Table1" & _
            "ORDER BY Table1.Priority;"
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(wkSQL)
 
    i = 1
    Do Until rst.EOF
        rst.Edit
        rst!Priority = i
        rst.Update
        rst.MoveNext
        i = i + 1
    Loop
    rst.Close
    dbs.Close
    Me.Requery
End Sub
 

Dim dbs As DAO.Database, rst As DAO.Recordset, wkSQL As String
Dim i As Integer
 
wkSQL = "SELECT Table1.Priority " & _
        "FROM Table1 ORDER BY Table1.Priority;"
 
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(wkSQL)
 
i = 1
Do Until rst.EOF
  rst.Edit
    rst!Priority = i
  rst.Update
    rst.MoveNext
    i = i + 1
Loop
 
rst.Close
dbs.Close
 
Me.RecordSource = "Select * From Table1 Order By [Priority]"

[OldBirdman]

Unless I am missing something, the only differences I can find between my code and the solution code is the change of the last line from "Me.Requery" to Me.RecordSource = "Select * From Table1 Order By [Priority]" and the inclusion of "Dim dbs As DAO.Database, rst As DAO.Recordset", which I had, at the top of the Module, as they are used elsewhere, and omitted (unfortunately) in error when converting the code to a test case.

Also, the code as presented is missing a blank (FROM Clause), and therefore would not work. Error also in presentation, as it obviously could not work as shown.

I always try to remove all code not necessary to the problem. I don't want someone to have to work to figure out what I'm doing, just look at the problem.

The results are unchanged. Changing from Me.ReQuery to Me.RecordSource ". . ." gives same problem, and as actual form is based on a more complex query, I will leave it my way.

I have 2 solutions that do work. 1) Call ReOrder twice, or 2) Loop thru the code from the "Set dbs = CurrentDb" thru "Me.ReQuery" twice. Both work, but I don't understand why they should, and why once thru doesn't.

OldBirdman
P.S. Will be away from this computer until next Wednesday (California time=+8).

[OldBirdman]

After much testing (with Debug.Print), the statement that does not work is "rst.Update". "rst!Priority = i" is done, but the record is not updated.

I have created a new subroutine "PriorityReorder" in order to isolate the code for testing.

If I call the Sub "PriorityReorder" twice, it will work. I have stopped the code at the "End Sub" statement and dragged the ??? I don't have the language for this ?? I drag the statement executing arrow back to the set dbl = . . . statement and run, and it works.

I have moved the ??? What is the generic term for "Dim" statements? Declare? again, I have trouble phrasing my questions ??? I have moved the Dim statements for dbs and rst into the Sub. No help.

I put all the statements of the sub in a loop (for j=1 to 2 . . . next j). Except for the Dim statements, of course. This should be the same as manually moving the active statement. This does not work.

The only way this Sub will work is to call it twice. In my program, it is actually a subroutine called by cmdPriority _Click. So now

Private Sub cmdPriority _Click()
    Call PriorityReorder 
    Call PriorityReorder 
End Sub
 

is the way to go. I also have a

Private Sub cmdLowest_Click()
    txtPriority  = 32000
    Call PriorityReorder 
    Call PriorityReorder 
End Sub
 

and of course a cmdHighest_Click

I am abandoning my attempt at neat, understandable code. I will go with the 2 calls to the subroutine, as it works ("If it ain't broke, don't fix it" seems to apply here). I'm not going to spend any more time here, as I have too many other questions and problems.

OldBirdman

[NeoPa]

As a matter of curiosity, what does the operator enter into a Priority field when he wants the priority changed?
It seems the values start off as 1 to number of records, so what should he change one record to to adjust the list?

Also, what is the recordsource for the form this happens on? Is it similar to the recordset rst?

[OldBirdman]

Hey! Funny you should ask that. The same question occurred to me.

I will change the priority to number by 10, so the user can use integers. Currently the form allows decimal numbers, so .5 or 1.1 will work. I did not mention before, for simplicity, but each record displayed has a "Top" & "Bottom" button. Pressing the "Top", for example, changes the Priority to -32000 and then calls ReOrder. This will probably be the most frequently used way to reorder priorities.

The form's RecordSource is an SQL Statement, to retreive all records with a field linked to the Key of the main form.

SELECT Key, ptr->tMain, ... , Priority, . . .
FROM tTasks
WHERE ((ptr->tMain=OpenArgs))
ORDER BY Priority;

Don't tell me the above SQL is not valid, I know, but present it this way for clarity. The OpenArgs is properly concatenated into the string, and the string is assigned to .RecordSource.

Since my last post, I find that sometimes 2 calls to Sub = ReOrder is not enough, and I must press the cmdReOrder button again. I still don't know why this is so, but the statement "rst.Update" in my code apparently does not always do the update.

Also, I would like the Priority field on the "New Record" to show a Priority of 10 more than the last (current) record. I can know what the value should be. But the "Default Value" is part of the table, not the form or control, so I'm stuck. I could also assign it "BeforeUpdate", but I do not know how to know whether I am creating a new record or editing an old one.

OldBirdman

[NeoPa]

Right, I'm assuming that [Table1] from the SQL in post #1 == [tTasks] in post #6.
I'm a big fan of not flooding a thread with a lot of irrelevant information, so I appreciate your attempts to show your SQL more briefly. Unfortunately, I can't follow it as it stands, so would you mind dropping in the whole lot. I've asked for it so if it's too enormous then that's my lookout ;) If it's set up by the OnOpen event procedure, a copy of that may help instead.
In the mean time, a suggestion for the re-prioritising code may help somewhat. As many records won't need updating at all, it's better not to update them. The code will also include an increment of ten and ADezii's declaration changes :

Private dbs As DAO.Database
Private rst As DAO.Recordset
 
Private Sub ReOrder()
  Dim wkSQL As String
  Dim i As Integer
 
  wkSQL = "SELECT [Priority] " & _
          "FROM [tTasks] " & _
          "ORDER BY [Priority]"
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset(wkSQL)
 
  i = 10
  Do Until rst.EOF
    'Update of record relatively heavy workload.  Avoid if possible.
    If rst!Priority <> i Then
      rst.Edit
      rst!Priority = i
      rst.Update
    End If
    rst.MoveNext
    i = i + 10
  Loop
  Call rst.Close
  Set rst = Nothing
  Call dbs.Close
  Set dbs = Nothing
  Call Me.ReQuery
End Sub

I would also consider removing the Me.ReQuery line from this procedure. It costs in performance and should not need doing multiple times even if the rest does.

[OldBirdman]

Again, thank your for all who contributed to this thread.

I just wanted you to know that if the first statement of my "ReSequence" subroutine were "Me.Dirty = False", the problems were solved.

I would assume that this statement would either simply remove the OnDirty, or would update all fields that had been changed. Apparently it does the update. It then requeries for the form, so when I loop thru the table, the order is correct, and the changes are made to a table that has been updated with all changes. I think that before, the table was not updated, even with a close statement.

OldBirdman

[NeoPa]

My understanding of how the Dirty flag SHOULD work is that when Access comes to save away any changes (happens when you move focus away from the record on a form as well as various other triggers) it will only execute the save if it finds the data has been changed. To determine this it checks the Dirty flag. If the Dirty flag is FALSE then it ignores the save and continues on (regardless of whether any data has actually been changed).

After doing various tests (Access 2K on Win 2K Server) I found that, regardless of the state of the Dirty flag at the time, the save was executed if data had been changed. Doesn't make much sense to me, but that's just what I found.

To produce the results I wanted (to whit cancel a pending update) I found I had to use the Me.Undo() procedure. This resets the data back to it's original position, but does successfully cancel the update.

I don't believe the Me.Dirty flag being changed causes any other side-effects like requerying or updating any data.

I hope this is some help.

[OldBirdman]

I tried to do it "by the book", and it wouldn't work. I tried me.dirty=false, and all my issues went away! Poof!

Your comments make sense to me. So, if it works but is not logical, should I not use the method? I think not!

I had called my "resequence" twice each time I wanted to run it. This worked 90% of the time. Other times I clicked the cmdButtonReSequence again, and maybe again, before the code worked correctly.

As 2 of you attempted to solve my issues, and as I found a solution that works (it hasn't failed yet in 4 days), I posted the answer to my own question. FYI.

OldBirdman

[NeoPa]

That's cool OB.
I'm glad you found a resolution and thankful you posted to say so. I just thought I'd share what I learned from my checking in the hope that it may help others to understand the situation a little more clearly (I wish I could).
If your solution works for you then I certainly won't try to argue with that :D

[OldBirdman]

My criteria was to create a recordset based on a continuous form, change almost every record in the recordset, requery the form to display the resulting changes, and allow the user to do it again.

Altho I knew the form was "Dirty", the form was based on an SQL statement assigned to the RecordSource for the form. The recordset used the same SQL statement, but obviously was not the same recordset.

My problem, therefore, was to get the recordset underlying the form to sync with the recordset used to renumber (re-sequence) the pictures. Me.Dirty = False apparently forced Access to save the data for not only the form, but for any recordset using the underlying table by way of an SQL statement.

Perhaps all this should be done by dancing under a Full Moon while swinging a dead chicken from a staff and chanting the appropriate verse to B.Gates. I don't know, but I dislike the threads without closure. If solved by someone other than the originator, then closure is usually a simple "Thank You". If solved by the originator, then closure requires an explanation, and that is what I attempted, perhaps a little clumsy.

OldBirdman

[NeoPa]

You're absolutely right OB.
I hope nothing I posted gave you to feel I was either arguing, criticising or disagreeing with you.
In fact, it's always a pleasure dealing with you as your manners are exemplary.

[OldBirdman]

No, your responses are always polite and gentle. It is my pleasure, NeoPa, to have you as an Expert on this forum.

There are many experts here on this forum, and I appreciate it. This is the only forum to which I am subscribed (but I do look at other forums for solutions before I post a question). I know that many questions posted here are posted on other forums simultaneously. I don't do that. I like the 'gentle' approach that this site offers.

ANY RESPONSE deserves consideration. I cannot say I understand every response, but I do try. Very often, a response is only a clue to the answer. If so, I feel that if I solved the issue. an explanation of closure is necessary.

Because the environment that my program operates in is not reproducable, this thread should be closed. The fact is that 'OnDirty=False' apparently causes Access to make that statement true. This might be noted by you experts for future problems. All updates are carried out, so then the form is 'Not Dirty'.

Thanks again to all who responded.

OldBirdman

[FishVal]

I have a table (Table1) and I want my users to be able to control the priority. For this question, consider only 2 fields, Priority (as Integer) and Desc (as String).

My form (Form1, Continuous) displays bound textboxes, txtPriority and txtDesc. The user changes the values in txtPriority, and then presses cmdReOrder. This should renumber Priority (in Table1) from 1 to n, and display the records in the new order.

The below code works perfectly, IF CALLED TWICE. In other words, if the statement "Call ReOrder" is 2 identical lines of code, this works as I intended. What little item am I missing here?

Private Sub cmdReOrder_Click()
    Call ReOrder
End Sub
 
Private Sub ReOrder()
Dim wkSQL As String
Dim i As Integer
    wkSQL = "SELECT Table1.Priority " & _
            "FROM Table1" & _
            "ORDER BY Table1.Priority;"
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(wkSQL)
 
    i = 1
    Do Until rst.EOF
        rst.Edit
        rst!Priority = i
        rst.Update
        rst.MoveNext
        i = i + 1
    Loop
    rst.Close
    dbs.Close
    Me.Requery
End Sub
 

Hi, OB.

Just a guess.

Is that possible that you have a unique index on [Priority] field?
If so then RecordSet.Update may fail when the code changes [Priority] field value to that having duplicate in a records that are not changed so far.

Regards,
Fish

P.S. Did you check the table contents after the first code run?