By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,560 Members | 949 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,560 IT Pros & Developers. It's quick & easy.

Using .Edit & .Update on a RecordSet

100+
P: 675
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?

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdReOrder_Click()
  2.     Call ReOrder
  3. End Sub
  4.  
  5. Private Sub ReOrder()
  6. Dim wkSQL As String
  7. Dim i As Integer
  8.     wkSQL = "SELECT Table1.Priority " & _
  9.             "FROM Table1" & _
  10.             "ORDER BY Table1.Priority;"
  11.     Set dbs = CurrentDb
  12.     Set rst = dbs.OpenRecordset(wkSQL)
  13.  
  14.     i = 1
  15.     Do Until rst.EOF
  16.         rst.Edit
  17.         rst!Priority = i
  18.         rst.Update
  19.         rst.MoveNext
  20.         i = i + 1
  21.     Loop
  22.     rst.Close
  23.     dbs.Close
  24.     Me.Requery
  25. End Sub
  26.  
Dec 7 '07 #1
Share this Question
Share on Google+
14 Replies


ADezii
Expert 5K+
P: 8,669
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?

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdReOrder_Click()
  2.     Call ReOrder
  3. End Sub
  4.  
  5. Private Sub ReOrder()
  6. Dim wkSQL As String
  7. Dim i As Integer
  8.     wkSQL = "SELECT Table1.Priority " & _
  9.             "FROM Table1" & _
  10.             "ORDER BY Table1.Priority;"
  11.     Set dbs = CurrentDb
  12.     Set rst = dbs.OpenRecordset(wkSQL)
  13.  
  14.     i = 1
  15.     Do Until rst.EOF
  16.         rst.Edit
  17.         rst!Priority = i
  18.         rst.Update
  19.         rst.MoveNext
  20.         i = i + 1
  21.     Loop
  22.     rst.Close
  23.     dbs.Close
  24.     Me.Requery
  25. End Sub
  26.  
Expand|Select|Wrap|Line Numbers
  1. Dim dbs As DAO.Database, rst As DAO.Recordset, wkSQL As String
  2. Dim i As Integer
  3.  
  4. wkSQL = "SELECT Table1.Priority " & _
  5.         "FROM Table1 ORDER BY Table1.Priority;"
  6.  
  7. Set dbs = CurrentDb
  8. Set rst = dbs.OpenRecordset(wkSQL)
  9.  
  10. i = 1
  11. Do Until rst.EOF
  12.   rst.Edit
  13.     rst!Priority = i
  14.   rst.Update
  15.     rst.MoveNext
  16.     i = i + 1
  17. Loop
  18.  
  19. rst.Close
  20. dbs.Close
  21.  
  22. Me.RecordSource = "Select * From Table1 Order By [Priority]"
Dec 8 '07 #2

100+
P: 675
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).
Dec 8 '07 #3

100+
P: 675
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
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdPriority _Click()
  2.     Call PriorityReorder 
  3.     Call PriorityReorder 
  4. End Sub
  5.  
is the way to go. I also have a
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdLowest_Click()
  2.     txtPriority  = 32000
  3.     Call PriorityReorder 
  4.     Call PriorityReorder 
  5. End Sub
  6.  
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
Dec 12 '07 #4

NeoPa
Expert Mod 15k+
P: 31,602
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?
Dec 13 '07 #5

100+
P: 675
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT Key, ptr->tMain, ... , Priority, . . .
  2. FROM tTasks
  3. WHERE ((ptr->tMain=OpenArgs))
  4. 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
Dec 14 '07 #6

NeoPa
Expert Mod 15k+
P: 31,602
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 :
Expand|Select|Wrap|Line Numbers
  1. Private dbs As DAO.Database
  2. Private rst As DAO.Recordset
  3.  
  4. Private Sub ReOrder()
  5.   Dim wkSQL As String
  6.   Dim i As Integer
  7.  
  8.   wkSQL = "SELECT [Priority] " & _
  9.           "FROM [tTasks] " & _
  10.           "ORDER BY [Priority]"
  11.   Set dbs = CurrentDb
  12.   Set rst = dbs.OpenRecordset(wkSQL)
  13.  
  14.   i = 10
  15.   Do Until rst.EOF
  16.     'Update of record relatively heavy workload.  Avoid if possible.
  17.     If rst!Priority <> i Then
  18.       rst.Edit
  19.       rst!Priority = i
  20.       rst.Update
  21.     End If
  22.     rst.MoveNext
  23.     i = i + 10
  24.   Loop
  25.   Call rst.Close
  26.   Set rst = Nothing
  27.   Call dbs.Close
  28.   Set dbs = Nothing
  29.   Call Me.ReQuery
  30. 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.
Dec 15 '07 #7

100+
P: 675
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
Jan 2 '08 #8

NeoPa
Expert Mod 15k+
P: 31,602
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.
Jan 3 '08 #9

100+
P: 675
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
Jan 4 '08 #10

NeoPa
Expert Mod 15k+
P: 31,602
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
Jan 4 '08 #11

100+
P: 675
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
Jan 4 '08 #12

NeoPa
Expert Mod 15k+
P: 31,602
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.
Jan 4 '08 #13

100+
P: 675
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
Jan 10 '08 #14

FishVal
Expert 2.5K+
P: 2,653
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?

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdReOrder_Click()
  2.     Call ReOrder
  3. End Sub
  4.  
  5. Private Sub ReOrder()
  6. Dim wkSQL As String
  7. Dim i As Integer
  8.     wkSQL = "SELECT Table1.Priority " & _
  9.             "FROM Table1" & _
  10.             "ORDER BY Table1.Priority;"
  11.     Set dbs = CurrentDb
  12.     Set rst = dbs.OpenRecordset(wkSQL)
  13.  
  14.     i = 1
  15.     Do Until rst.EOF
  16.         rst.Edit
  17.         rst!Priority = i
  18.         rst.Update
  19.         rst.MoveNext
  20.         i = i + 1
  21.     Loop
  22.     rst.Close
  23.     dbs.Close
  24.     Me.Requery
  25. End Sub
  26.  
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?
Jan 10 '08 #15

Post your reply

Sign in to post your reply or Sign up for a free account.