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

Change Sequence of Remaining Records After Updating Number of Another

P: 14
I have a continuous form with list of Projects ordered by Business Unit Rank. The users want to be able to update the Business Unit Rank to any number and have the remaining numbers resequence. So if the user updates Business Unit Rank from 6 to 3, the current number 3 should change to 4, 4-5, 5-6, etc. without 1 and 2 being changed. If the user changes 6 to 1 for example then the other 1 should change to 2, 2-3, etc. This is the code I have now. Someone was helping me with this.
Expand|Select|Wrap|Line Numbers
  1. Private Sub ReRank()
  2.     Dim db As DAO.Database
  3.     Dim rst As DAO.Recordset
  4.     Dim strSQL As String
  5.     Dim n As Integer
  6.  
  7.     Me.Dirty = False
  8.  
  9.     strSQL = "SELECT BusUnitID, GrpUnitRank, StatusID, ProjID FROM t_Projects WHERE BusUnitID = " & cmbBusUnit & " and StatusID = " & Me.cmbStatus & " ORDER BY GrpUnitRank"
  10.     Set rst = CurrentDb.OpenRecordset(strSQL)
  11.     With rst
  12.         .MoveLast
  13.         n = 1
  14.         .MoveFirst
  15.         Do While Not .EOF
  16.             .Edit
  17.             .Fields("grpUnitRank") = n + 1
  18.             .Update
  19.             Debug.Print
  20.             n = n + 1
  21.             .MoveNext
  22.         Loop
  23.     End With
  24.     Set rst = Nothing
  25.  
  26.     Me.Requery
  27. End Sub
Aug 28 '18 #1

✓ answered by NeoPa

Hi Angela.

I suspect you've probably noticed already that the interface you've conceived doesn't work well from a logical standpoint. If I understand correctly, once you've updated the position of a record, and before the logic's run, you have two records with the same number.

There are two ways of handling the overall logic. One is with code-type logic and is processed using VBA, and the other is using batch-type logic and is processed in a SQL UPDATE.

Now, code is fine for this job but really needs to be treated as a single transaction (See DAO Transaction Processing - What is it?), which is fine if you're comfortable handling that. It's probably better though, to handle such an update as a batch - also contained within a single transaction, but more easily. I'll look at that here for you.

Now, in order to avoid the problem where we end up with two records having the same number, and separately to store which numbers are going to need to change, we'll handle some of this logic in VBA, and more especially Event procedures. To help visualise what's going on below consider some example data where we have nine records ordered #1 to #9. We will then update #7 to read 3 instead.

Consider what we need to do here :
  1. Ignore anything above #7. None of these will change during this process.
  2. Ignore anything below #3. None of these will change during this process.
  3. Update #7 to #3.
  4. Update all others between #3 & #6 to increment by one.

Considering the above steps though, what happens if, before we apply this logic #7 has already been updated to 3? We would have no #7 and two #3s. This would make it impossible to apply the logic reliably.

What I suggest we do at that point then, is to capture the update from 7 to 3 before the update is actually applied. At that point we note the new number as well as the old (NB. We must handle both upward and downward movements.) in order to build the SQL correctly. Assuming the control is called [txtRank] then the Event Procedure we're talking about will be txtRank_BeforeUpdate().

NB. While we're still in the Event Procedure the Field value will be locked for the current record. Therefore we need to execute the SQL to update the data elsewhere - after the record lock has been lost. You'll see later that we'll Cancel the update to the value in the control so we can't use the AfterUpdate() Event Procedure either. So, we'll have to use a technique whereby we use the Timer Event Procedure and simply trigger it once from our BeforeUpdate() Event Procedure.

If this all sounds complicated then it's only because it is ;-) However, I'll provide some sample code to illustrate it all that you can use and work with.

To start with we have to define a constant and some variables that will be accessible to all the code within the Form's module (At the top - outside all procedures.) :
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. '%FV = From Value; %TV = To Value; %PM = +/-
  5. '%LL = Lower limit; %UL = Upper Limit; %NL = New Line
  6. Private Const conSQL As String = _
  7.                "UPDATE [t_Projects]%NL" _
  8.              & "SET [GrpUnitRank]=IIf([GrpUnitRank]=%FV,%TV,[GrpUnitRank] %PM 1)%NL" _
  9.              & "WHERE  ([GrpUnitRank] Between %LL And %UL)"
  10.  
  11. Private lngFrom As Long, lngTo As Long
The SQL will need to be modified by the code before it can be used. %LL & %UL are the same as %FV & %TV but which matches which depends on the direction of movement (%PM). The variables store the value of the updated control before and after the user entered a change.

Next we have the BeforeUpdate Event Procedure :
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtRank_BeforeUpdate(Cancel As Integer)
  2.     'We never actually update this value from the form.
  3.     Cancel = True
  4.     With Me
  5.         With .txtRank
  6.             'Update not valid if new value either same as old OR > num recs.
  7.             If .Value = .OldValue _
  8.             Or .Value > DCount(Expr:="*", Domain:="[t_Projects]") Then _
  9.                 Exit Sub
  10.             lngFrom = .OldValue
  11.             lngTo = .Value
  12.         End With
  13.         'Set the Timer procedure to fire when this one completes.
  14.         .OnTimer = "[Event Procedure]"
  15.         .TimerInterval = 1
  16.     End With
  17. End Sub
When that's completed the update will have been reversed regardless, but assuming the tests have been passed it will then execute the OnTimer procedure below.
NB. At this point it's possible to make updates to all the records assuming there are no locks elsewhere.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Timer()
  2.     Dim strSQL As String
  3.     Dim dbVar As DAO.Database
  4.  
  5.     With Me
  6.         'This is a one-off so we disable it from running again.
  7.         .OnTimer = ""
  8.         .TimerInterval = 0
  9.     End With
  10.     'Do nothing if lngFrom or lngTo are not set.
  11.     If lngFrom = 0 _
  12.     Or lngTo = 0 Then _
  13.         Exit Sub
  14.     'Replace all the place-holders from conSQL with our values.
  15.     strSQL = Replace(conSQL, "%FV", lngFrom)
  16.     strSQL = Replace(strSQL, "%TV", lngTo)
  17.     strSQL = Replace(strSQL, "%PM", IIf(lngFrom > lngTo, "+", "-"))
  18.     strSQL = Replace(strSQL, "%LL", IIf(lngFrom > lngTo, lngTo, lngFrom))
  19.     strSQL = Replace(strSQL, "%UL", IIf(lngFrom > lngTo, lngFrom, lngTo))
  20.     strSQL = Replace(strSQL, "%NL", vbNewLine)
  21.     Set dbVar = CurrentDb()
  22.     'For now we ignore errors.
  23.     'That can be added later without need for my help.
  24.     On Error Resume Next
  25.     Call dbVar.Execute(Query:=strSQL, Options:=dbFailOnError)
  26.     If Err.Number = 0 Then
  27.         Call Me.Requery()
  28.         lngFrom = 0
  29.         lngTo = 0
  30.     End If
  31. End Sub
Using my MultiReplace() Function can certainly simplify the replacement part as all of the replacements can then be handled by a single call.

NB. This is all air-code so you may need to do some testing first. I've designed it in such a way as to make it easier to add failure handling. If the update fails then you can simply change where the Timer is reset so it's only done after success, and you can change the TimerInterval to something more meaningful than a single millisecond.

Share this Question
Share on Google+
30 Replies


NeoPa
Expert Mod 15k+
P: 31,494
Your post implies the code doesn't work as expected but you don't say what goes wrong where. This is useful information when posting a question.
Aug 29 '18 #2

P: 14
I attached screenshots to provide an example but I changed a record ranked 5 to 1 and it moved it to 2 instead. Also here is the code. I changed one line from the previous code. Instead of n+1 i changed it to just n.

Expand|Select|Wrap|Line Numbers
  1. Private Sub ReRank()
  2.     Dim db As DAO.Database
  3.     Dim rst As DAO.Recordset
  4.     Dim strSQL As String
  5.     Dim n As Integer
  6.  
  7.     Me.Dirty = False
  8.  
  9.     strSQL = "SELECT BusUnitID, GrpUnitRank, StatusID, ProjID FROM t_Projects WHERE BusUnitID = " & cmbBusUnit & " and StatusID = " & Me.cmbStatus & " ORDER BY GrpUnitRank"
  10.      Set rst = CurrentDb.OpenRecordset(strSQL)
  11.         With rst
  12.         .MoveLast
  13.         n = 1
  14.         .MoveFirst
  15.         Do While Not .EOF
  16.             .Edit
  17.             .Fields("grpUnitRank") = n
  18.             .Update
  19.             Debug.Print
  20.             n = n + 1
  21.             .MoveNext
  22.         Loop
  23.     End With
  24.     Set rst = Nothing
  25.  
  26.     Me.Requery
  27.  
  28.  
  29. End Sub
so it works sometimes but if i change the record ranked number 2 to 1 it will stay as 2 when the After Update Event fires. Well it doesn't matter if I put it in the After Update or call the proc from the Click Event of the column Header Label. Does the same thing. It seems like it might be looking at both number 1 rank'd and then alphabetizing by Project Name. Logically I need for it to change the former #1 to #2. Does that make sense?
Before.JPG


WhileChanging.JPG


AfterUpdate.JPG

Attached Images
File Type: jpg Before.JPG (32.5 KB, 280 views)
File Type: jpg WhileChanging.JPG (32.3 KB, 239 views)
File Type: jpg AfterUpdate.JPG (29.8 KB, 238 views)
Aug 29 '18 #3

PhilOfWalton
Expert 100+
P: 1,430
Angela,

Without writing the code for you, although, if you get stuck, I will try to help further.

Firstly, I am assuming that on the BeforeUpdate of the Rank you check that the value entered is > 0 and <= Maximum Rank

Secondly, for the method I am suggesting it requires that Duplicates are allowed for Rank.

What I am going to suggest is not in a logical order, but you will see why.

So after you update the Rank, create a Query that has the table in Rank Order. There should be one of the Ranks that appears twice. So what you need to do is determine which is the original Rank and which is the Rank you have just entered. So save the lower of the OldValue and new value of the Rank.

Run an update routine similar to the one that you have written, and add 1 to the existing Rank of all Ranks greater than the value you have saved.

Come back with any problems

Phil
Aug 29 '18 #4

twinnyfo
Expert Mod 2.5K+
P: 3,284
Since you have a continuous form, and not a datasheet, you could also add "Move Up"/"Move Down" command buttons to the detail sectoin of the form. By doing this, you only have to work with two records at a time. Assuming you allow duplicates (as Phil recommends) you find the record that has a rank of Me.grpUnitRank - 1 (or + 1, depending upon whether moving up or down), change that record's value and then assign the appropriate value to the record you want to move. Then, just requery the form.

This may be a bit more flashy and/or more involved, but it prevents the confusion that could eb caused by trying to remember which is the new value and which is the new value.

Just a thought....
Aug 29 '18 #5

PhilOfWalton
Expert 100+
P: 1,430
Please ignore my last post, it doesn't work.

Am having another look at it

Phil
Aug 29 '18 #6

NeoPa
Expert Mod 15k+
P: 31,494
Hi Angela.

I suspect you've probably noticed already that the interface you've conceived doesn't work well from a logical standpoint. If I understand correctly, once you've updated the position of a record, and before the logic's run, you have two records with the same number.

There are two ways of handling the overall logic. One is with code-type logic and is processed using VBA, and the other is using batch-type logic and is processed in a SQL UPDATE.

Now, code is fine for this job but really needs to be treated as a single transaction (See DAO Transaction Processing - What is it?), which is fine if you're comfortable handling that. It's probably better though, to handle such an update as a batch - also contained within a single transaction, but more easily. I'll look at that here for you.

Now, in order to avoid the problem where we end up with two records having the same number, and separately to store which numbers are going to need to change, we'll handle some of this logic in VBA, and more especially Event procedures. To help visualise what's going on below consider some example data where we have nine records ordered #1 to #9. We will then update #7 to read 3 instead.

Consider what we need to do here :
  1. Ignore anything above #7. None of these will change during this process.
  2. Ignore anything below #3. None of these will change during this process.
  3. Update #7 to #3.
  4. Update all others between #3 & #6 to increment by one.

Considering the above steps though, what happens if, before we apply this logic #7 has already been updated to 3? We would have no #7 and two #3s. This would make it impossible to apply the logic reliably.

What I suggest we do at that point then, is to capture the update from 7 to 3 before the update is actually applied. At that point we note the new number as well as the old (NB. We must handle both upward and downward movements.) in order to build the SQL correctly. Assuming the control is called [txtRank] then the Event Procedure we're talking about will be txtRank_BeforeUpdate().

NB. While we're still in the Event Procedure the Field value will be locked for the current record. Therefore we need to execute the SQL to update the data elsewhere - after the record lock has been lost. You'll see later that we'll Cancel the update to the value in the control so we can't use the AfterUpdate() Event Procedure either. So, we'll have to use a technique whereby we use the Timer Event Procedure and simply trigger it once from our BeforeUpdate() Event Procedure.

If this all sounds complicated then it's only because it is ;-) However, I'll provide some sample code to illustrate it all that you can use and work with.

To start with we have to define a constant and some variables that will be accessible to all the code within the Form's module (At the top - outside all procedures.) :
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. '%FV = From Value; %TV = To Value; %PM = +/-
  5. '%LL = Lower limit; %UL = Upper Limit; %NL = New Line
  6. Private Const conSQL As String = _
  7.                "UPDATE [t_Projects]%NL" _
  8.              & "SET [GrpUnitRank]=IIf([GrpUnitRank]=%FV,%TV,[GrpUnitRank] %PM 1)%NL" _
  9.              & "WHERE  ([GrpUnitRank] Between %LL And %UL)"
  10.  
  11. Private lngFrom As Long, lngTo As Long
The SQL will need to be modified by the code before it can be used. %LL & %UL are the same as %FV & %TV but which matches which depends on the direction of movement (%PM). The variables store the value of the updated control before and after the user entered a change.

Next we have the BeforeUpdate Event Procedure :
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtRank_BeforeUpdate(Cancel As Integer)
  2.     'We never actually update this value from the form.
  3.     Cancel = True
  4.     With Me
  5.         With .txtRank
  6.             'Update not valid if new value either same as old OR > num recs.
  7.             If .Value = .OldValue _
  8.             Or .Value > DCount(Expr:="*", Domain:="[t_Projects]") Then _
  9.                 Exit Sub
  10.             lngFrom = .OldValue
  11.             lngTo = .Value
  12.         End With
  13.         'Set the Timer procedure to fire when this one completes.
  14.         .OnTimer = "[Event Procedure]"
  15.         .TimerInterval = 1
  16.     End With
  17. End Sub
When that's completed the update will have been reversed regardless, but assuming the tests have been passed it will then execute the OnTimer procedure below.
NB. At this point it's possible to make updates to all the records assuming there are no locks elsewhere.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Timer()
  2.     Dim strSQL As String
  3.     Dim dbVar As DAO.Database
  4.  
  5.     With Me
  6.         'This is a one-off so we disable it from running again.
  7.         .OnTimer = ""
  8.         .TimerInterval = 0
  9.     End With
  10.     'Do nothing if lngFrom or lngTo are not set.
  11.     If lngFrom = 0 _
  12.     Or lngTo = 0 Then _
  13.         Exit Sub
  14.     'Replace all the place-holders from conSQL with our values.
  15.     strSQL = Replace(conSQL, "%FV", lngFrom)
  16.     strSQL = Replace(strSQL, "%TV", lngTo)
  17.     strSQL = Replace(strSQL, "%PM", IIf(lngFrom > lngTo, "+", "-"))
  18.     strSQL = Replace(strSQL, "%LL", IIf(lngFrom > lngTo, lngTo, lngFrom))
  19.     strSQL = Replace(strSQL, "%UL", IIf(lngFrom > lngTo, lngFrom, lngTo))
  20.     strSQL = Replace(strSQL, "%NL", vbNewLine)
  21.     Set dbVar = CurrentDb()
  22.     'For now we ignore errors.
  23.     'That can be added later without need for my help.
  24.     On Error Resume Next
  25.     Call dbVar.Execute(Query:=strSQL, Options:=dbFailOnError)
  26.     If Err.Number = 0 Then
  27.         Call Me.Requery()
  28.         lngFrom = 0
  29.         lngTo = 0
  30.     End If
  31. End Sub
Using my MultiReplace() Function can certainly simplify the replacement part as all of the replacements can then be handled by a single call.

NB. This is all air-code so you may need to do some testing first. I've designed it in such a way as to make it easier to add failure handling. If the update fails then you can simply change where the Timer is reset so it's only done after success, and you can change the TimerInterval to something more meaningful than a single millisecond.
Aug 29 '18 #7

PhilOfWalton
Expert 100+
P: 1,430
Angela

I was feeling bored, and Twinnyfo's suggestion of up & down buttons is an excellent idea.

This little offering will show you the principal, the advantage is you can easily see what is happening.

You will have to alter the recordsource and the StrSQL to add BusID and Status criteria.

Phil
Aug 29 '18 #8

P: 14
It was making sense right up to the time I saw the code for the Timer Event. Very creative. I will give it a try and get back to you.
Aug 29 '18 #9

P: 14
Phil, I will look at yours also. My first reply was to NeoPa. And I haven't even mentioned that I need to do a re-ranking from a single form when users are adding new Projects. I don't even want to think about that yet but I think that one should be easier, maybe.
Aug 29 '18 #10

NeoPa
Expert Mod 15k+
P: 31,494
AngelaIng:
It was making sense right up to the time I saw the code for the Timer Event.
That's good. Let's have a closer look at that code then. To look at that code (Form_Timer()) we first need to study conSQL from the first block.

There are, within that string, some place-holders that we later update with the appropriate values. The comment explains what's what. Look at that string carefully before proceeding and possibly use the test scenario outlined earlier to anticipate what the SQL string should look like once the place-holders have all been replaced. The scenario was to update record #7 to make it #3. In that case the place-holders would be :
%FV ==> From Value = 7
%TV ==> To Value = 3
%PM ==> +/- = +
%LL ==> Lower Limit = 3
%UL ==> Upper Limit = 7
%NL ==> New Line = vbNewLine
So, the SQL string that results after all the changes are made in Form_Timer() is :
Expand|Select|Wrap|Line Numbers
  1. UPDATE [t_Projects]
  2. SET [GrpUnitRank]=IIf([GrpUnitRank]=7,3,[GrpUnitRank] + 1)
  3. WHERE  ([GrpUnitRank] Between 3 And 7)
Notice that were the change the other way around (From #3 to #7 instead of #7 to #3.) then the Upper & Lower Limits would stay the same; the From & To Values would be reversed; the +/- would be '-' instead of '+'.

Most of the code in Form_Timer() is based around updating and running that SQL. A little at the start to check that we don't proceed with nonsense data, and a little at the end to reset the variables and the Form so that the same update can't be run again and the new data is shown on the form in its new order. So, ignoring that code, we can focus on lines #14 to #25.

Lines #14 to #20 simply replace the place-holders with the appropriate values.
Lines #21 to #25 simply set up the DAO.Database object and execute the SQL previously prepared.
Aug 29 '18 #11

P: 14
NeoPa, I attached the code to the Events you suggested and after I type in a new number to any record on the continuous form nothing happens it sits there forever. I can't click the close button on the form or navigate up or down. I can tab to a new field in the same record but that's it.

When I finally Right Click and choose to put in Design Mode I get the message shown in the screenshots. If I comment out the On Error Resume Next in the Form_Timer Event I get Run Time Error 3021 "No Current Record."




I'm thinking I need to capture the BusUnitID value and set the form's recordset again and then requery but I am not that good at this so excuse my ignorance that may be totally wrong. Can you advise?
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Timer()
  2.     Dim strSQL As String
  3.     Dim dbVar As DAO.Database
  4.  
  5.     With Me
  6.         'This is a one-off so we disable it from running again.
  7.         .OnTimer = ""
  8.         .TimerInterval = 0
  9.     End With
  10.     'Do nothing if lngFrom or lngTo are not set.
  11.     If lngFrom = 0 _
  12.     Or lngTo = 0 Then _
  13.         Exit Sub
  14.  
  15.     'Replace all the place-holders from conSQL with our values.
  16.     strSQL = Replace(conSQL, "%FV", lngFrom)
  17.     strSQL = Replace(strSQL, "%TV", lngTo)
  18.     strSQL = Replace(strSQL, "%PM", IIf(lngFrom > lngTo, "+", "-"))
  19.     strSQL = Replace(strSQL, "%LL", IIf(lngFrom > lngTo, lngTo, lngFrom))
  20.     strSQL = Replace(strSQL, "%UL", IIf(lngFrom > lngTo, lngFrom, lngTo))
  21.     strSQL = Replace(strSQL, "%NL", vbNewLine)
  22.     Set dbVar = CurrentDb()
  23.     'For now we ignore errors.
  24.     'That can be added later without need for my help.
  25.    ' On Error Resume Next
  26.     Call dbVar.Execute(Query:=strSQL, Options:=dbFailOnError)
  27.     If Err.Number = 0 Then
  28.         lngFrom = 0
  29.         lngTo = 0
  30.         Call Me.Requery()
  31.     End If
Attached Images
File Type: jpg YouandOtherUser.JPG (72.2 KB, 230 views)
File Type: jpg DataChangeswillbeLost.JPG (48.2 KB, 231 views)
File Type: jpg LooksLikeAfterReopening.JPG (51.1 KB, 228 views)
Aug 30 '18 #12

PhilOfWalton
Expert 100+
P: 1,430
Angela

I was still feeling bored, so have revamped the Db I sent to cover your concerns about adding & deleting projects.

I am still showing the rank, but I have a feeling that the number is pretty irrelevant. I suspect that what you really want is to show the projects in order of importance.

Phil
Aug 30 '18 #13

twinnyfo
Expert Mod 2.5K+
P: 3,284
@NeoPa

Brilliant idea to Cancel the Update and then use the Timer Event. Definitely a unique way of approaching things!
Aug 30 '18 #14

NeoPa
Expert Mod 15k+
P: 31,494
Thanks for the vote of confidence Twinny :-)

It actually shows you have the understanding of how complicated was the original requirement as well as the unusual nature of the solution.

I can tell you I went through a number of variations and bumped into various gotchas. The eventual solution was a result of trying to untangle the logic and make a path through the limitations such as having multiple copies of the same rank and being unable to run an UPDATE query while the record was still locked.

I was very happy to find a workable solution in the end so I feel it was worth the time and effort. A very interesting exercise.

@Angela.
I will respond to your post shortly, assuming I remain uninterrupted by work requirements for a while. All being well I'll have a response for you within the next couple of hours.
Aug 30 '18 #15

P: 14
This is a very complicated requirement and solution. I know you are putting a tremendous amount of thought into making this all work. I'm am putting a tremendous amount of time just to follow where your going with it. I think I understand the logic than I get an ahaha gotcha somewhere but that may be just me. Thanks so much
Aug 30 '18 #16

NeoPa
Expert Mod 15k+
P: 31,494
AngelaIng:
NeoPa, I attached the code to the Events you suggested and after I type in a new number to any record on the continuous form nothing happens it sits there forever. I can't click the close button on the form or navigate up or down. I can tab to a new field in the same record but that's it.
It would be interesting to see if you've set Event properties up properly to run the code, and if the code IS running whether or not it goes wrong anywhere. What are your debugging skills like (See Debugging in VBA)?

What version of Access are you running this with?
Is it possible for you to attach a ZIPped copy of this and any back-end files that may be necessary for me to try running it here. It's much easier to find problems when they're in front of you.

I believe this concept should work, but I haven't tested it for real yet.
Aug 30 '18 #17

P: 14
And NeoPa, this is the solution they want to use. So I'll keep trying to figure out what's going on in the meantime. I have to take off for an appointment in 30 minutes but will get back to it.
Aug 30 '18 #18

P: 14
I set watches and stepped through code to see the various values but I still think my debugging skills are not that good.

I have to run right now to go to an appt that's about an hour away so I won't be back until much later today but I will look at your link then and if I still can't figure it out if you don't mind I will either send you a copy of all the code. i could even send you the whole database but there are two of them. It's not a sql server back end a 2003 mdb back end and 2016 accdb front. I could import all the table objects into the 2016 front end and send it to you that way but then having them all in one might cause some behavioral changes. I don't know.
Aug 30 '18 #19

P: 14
or just send a copy of the code. Thanks, will catch up later
Aug 30 '18 #20

P: 14
I keep trying to send you a message and somethings going wrong. I will look at your instructions first. There's more to debugging than stepping through code. I glanced at your instructions on debugging and there's more for me to learn so let me work on it more when I GET Back and i will let you know
Aug 30 '18 #21

NeoPa
Expert Mod 15k+
P: 31,494
I have some changes to post.

I've created a copy for myself in 2010 and found a problem or two. You go for your appointment elsewhere and you should have some better code to play with when you get back.
Aug 30 '18 #22

P: 14
ok so i am a little late. I am in a hurry so I just read parts of your email but when I read the printed out version of the email I can see you asked for a copy of the code. By now I could have sent it to you. I will do both, send you a copy and try and figure it out on my own so I learn. Ok, it will be in about 6 hours and I know there's a time difference. Take care
Aug 30 '18 #23

NeoPa
Expert Mod 15k+
P: 31,494
Right. As I said earlier, I now have a proof of concept working perfectly in a very small Access 2010 ACCDB that I've attached in a ZIP file.

The main points I found that had to be addressed seem to line up nicely with your own experience and were about using Undo() to set the Control and the Form (Current record) back to before the operator even started to make changes to them. Using Cancel alone stops the change from being applied, but leaves the changed data for the operator to have another try at it. What I had to do was to clear any changes, first in the Control, and then in the Form, so that the changed record released it's lock.

Anyway, the latest code is now :
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. '%FV = From Value; %TV = To Value; %PM = +/-
  5. '%LL = Lower limit; %UL = Upper Limit; %NL = New Line
  6. Private Const conSQL As String = _
  7.                "UPDATE [t_Projects]%NL" _
  8.              & "SET [GrpUnitRank]=IIf([GrpUnitRank]=%FV,%TV,[GrpUnitRank] %PM 1)%NL" _
  9.              & "WHERE  ([GrpUnitRank] Between %LL And %UL)"
  10.  
  11. Private lngFrom As Long, lngTo As Long
  12.  
  13. Private Sub Form_Timer()
  14.     Dim strSQL As String
  15.     Dim dbVar As DAO.Database
  16.  
  17.     With Me
  18.         'This is a one-off so we disable it from running again.
  19.         .OnTimer = ""
  20.         .TimerInterval = 0
  21.     End With
  22.     'Do nothing if lngFrom or lngTo are not set.
  23.     If lngFrom = 0 _
  24.     Or lngTo = 0 Then _
  25.         Exit Sub
  26.     'Replace all the place-holders from conSQL with our values.
  27.     strSQL = Replace(conSQL, "%FV", lngFrom)
  28.     strSQL = Replace(strSQL, "%TV", lngTo)
  29.     strSQL = Replace(strSQL, "%PM", IIf(lngFrom > lngTo, "+", "-"))
  30.     strSQL = Replace(strSQL, "%LL", IIf(lngFrom > lngTo, lngTo, lngFrom))
  31.     strSQL = Replace(strSQL, "%UL", IIf(lngFrom > lngTo, lngFrom, lngTo))
  32.     strSQL = Replace(strSQL, "%NL", vbNewLine)
  33.     Set dbVar = CurrentDb()
  34.     'For now we ignore errors.
  35.     'That can be added later without need for my help.
  36.     On Error Resume Next
  37.     Call dbVar.Execute(Query:=strSQL, Options:=dbFailOnError)
  38.     If Err.Number = 0 Then
  39.         Call Me.Requery
  40.         lngFrom = 0
  41.         lngTo = 0
  42.     End If
  43. End Sub
  44.  
  45. Private Sub txtRank_BeforeUpdate(Cancel As Integer)
  46.     'We never actually update this value from the form.
  47.     Cancel = True
  48.     With Me
  49.         With .txtRank
  50.             'Update not valid if new value either same as old OR > num recs.
  51.             If .Value = .OldValue _
  52.             Or .Value > DCount(Expr:="*", Domain:="[t_Projects]") Then _
  53.                 Exit Sub
  54.             lngFrom = .OldValue
  55.             lngTo = .Value
  56.             'Undo pending change made to the value.
  57.             Call .Undo
  58.         End With
  59.         'Although this may lose other changes to the record it's necessary
  60.         'in order to release the lock on the current record.
  61.         Call .Undo
  62.         'Set the Timer procedure to fire when this one completes.
  63.         .OnTimer = "[Event Procedure]"
  64.         .TimerInterval = 1
  65.     End With
  66. End Sub
I've set up my database with the same names where necessary for the code to work so this code should work for you as-is.

The SQL created when I ran this in the scenario outlined in an earlier post was :
Expand|Select|Wrap|Line Numbers
  1. UPDATE [t_Projects]
  2. SET [GrpUnitRank]=IIf([GrpUnitRank]=7,3,[GrpUnitRank] + 1)
  3. WHERE  ([GrpUnitRank] Between 3 And 7)
Exactly as we'd want it to. And it worked perfectly every time.
Attached Files
File Type: zip AngelaIng.ZIP (44.1 KB, 18 views)
Aug 30 '18 #24

NeoPa
Expert Mod 15k+
P: 31,494
AngelaIng:
I am in a hurry so I just read parts of your email but when I read the printed out version of the email I can see you asked for a copy of the code. By now I could have sent it to you. I will do both, send you a copy and try and figure it out on my own so I learn.
That's a very commendable attitude Angela, and I don't want to discourage you from anything that will help you learn. That said, I suspect we now have some working code for you so I doubt you'll need to send me anything for now.

If, after trying out the new code (Paste it into the module BTW. Retyping code is a reliable cause of introducing problems.), you find you still have problems then we can think again about sharing your code and/or your two databases.

I'm not expecting to hear back from you until after you come back ;-) Hurry off if you haven't gone already, but you can be quietly confident you'll get it working quickly and easily on your return.
Aug 30 '18 #25

PhilOfWalton
Expert 100+
P: 1,430
@Neopa

Elegant bit of coding as ever, the swap routine is beautifully concise.

However, on the sample Db, it appears that you can't add a new project (on the form) as the Undo looses the new record.

Phil
Aug 30 '18 #26

NeoPa
Expert Mod 15k+
P: 31,494
Indeed Phil, and thank you.

My solution was an exercise as specified by the question in this thread. If more is desired then that can be considered elsewhere. There is certainly information available to the code to allow differentiation between an addition and an update. I would expect any such design to add the [GrpUnitRank] value automatically as the number of records covered.

I don't believe such a discussion fits in this thread however.
Aug 30 '18 #27

P: 14
NeoPa. This meets the requirement perfectly. I have to do something similar to the same table updating the sequence basedv on the group unit rank entered for a new project enteted into a data entry form. I can conceptualize how I think it should work. This one Iím atill trying to understand why the .undo was needed.
Aug 31 '18 #28

P: 14
Excuse my mispellings. Iím typing on my phone and my eyes arent so good
Aug 31 '18 #29

P: 14
Responding to Phils comment. Itís ok because new records arenít meant to be added on this form
Aug 31 '18 #30

NeoPa
Expert Mod 15k+
P: 31,494
AngelaIng:
This one Iím atill trying to understand why the .undo was needed.
That's because before .Undo() is called for the form the record is still dirty - IE. There is a change pending. It's back to the point just before you tab out of the Control. That is after the new value has been entered.

At this point both the Control and the form are dirty and so the record is locked. If we try to apply the UPDATE SQL when any of the records is dirty then the SQL will fail.
Aug 31 '18 #31

Post your reply

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