Seeking Algorithm Advice - Rippledown Increment Problem

[RonL]

Hello all. I have a bound form containing two fields (among others), call them ShotNum and ShotNumSuffix. The first is an integer, the second is either a 0-length string or an alphabetic character. The combination of ShotNum & ShotNumSuffix is unique for each record. (There is also an autonumbered ID.) Most records have a different ShotNum and blank ShotNumSuffix, but there will be small groups where ShotNum is the same and ShotNumSuffix varies. (The user wants ShotNumSuffix to designate that a record was (effectively) inserted after the one before. He doesn't want the ShotNum for existing records to increment, because the data are actually a transcription from worksheets that undergo manual, iterative insertions after distribution to other users.)

I already have code for keeping the "inserted" ShotNum equal to the prior record, and incrementing the ShotNumSuffix. (And the recordsource is a query that sorts on these two fields, so a requery takes care of showing the desired order in the form.) Problem now is how to deal with situation where the user wants an insertion where there exist beyond the "insertion point" records with the same ShotNum. Those records already have ShotNumSuffix values which will need to be incremented. IOW, I want to:

1. Bookmark the current record and store its ShotNum and ShotNumSuffix values;
2. Select for all records that have the same ShotNum as the current record;


3. Create a new one and assign the same ShotNum as stored in step 1, and an increment of the ShotNumSuffix from step 1;
4. Increment ShotNumSuffix for all the REMAINING records (in the selection).

I list the steps like that because I know how to do 1-3. I have a good character increment subroutine. I'm just not sure of the simplest way to accomplish step 4.

To summarize, I want to take a subset of records that have the same value in one field, and a sequential value in a second field, effectively insert a new record in the middle of them, and increment just the second field for the "inserted" record and any for which the value of that second field is the same as or higher than the new one. A "conditional ripple down" if you will.

I suspect this is simple, but am not experienced enough to see it clearly. I think I could create a recordset from a selection of all records with the same ShotNum and do what I want by looping through. But how would I identify the one record in that new set which was the current one in the form's recordset, and how would I then get the processed records to replace their counterparts in the form's recordset? Is there a way to loop through a selection without creating a separate recordset for it? Should I somehow be using the form's recordsetclone?

Thanks for any guidance,

Ron

[ssanfu]

If I understand correctly, you have:

ShotNum    ShotNumSuffix
5
5       A
5       B
5       C

6
6       A
6       B
6       C
6       D

7
7       A

8
8       A
8       B

----------------
You want to

6
6    A
Insert new record here
6    B
6    C
6    D

-----------------
which would results in:

6
6    A
6    B
6    C (was B)
6    D (was C)
6    E (was D)

Am I close?

For the NEW record, how is the suffix determined? Some one says "I want this new record suffix to be 'B'"?

[RonL]

If I understand correctly, you have:

ShotNum    ShotNumSuffix
5
5       A
5       B
5       C

6
6       A
6       B
6       C
6       D

7
7       A

8
8       A
8       B

----------------
You want to

6
6    A
Insert new record here
6    B
6    C
6    D

-----------------
which would results in:

6
6    A
6    B
6    C (was B)
6    D (was C)
6    E (was D)

Am I close?

On the nose! (In reality, the majority of records will be a single shotnumber with "" for the suffix, but that's immaterial.)

For the NEW record, how is the suffix determined? Some one says "I want this new record suffix to be 'B'"?

I call that an "insert mode" situation. I want the code to do the increments. (They can override after record is created if they wish.) So they'd click the 6A record to make it current and hit Ctrl-I. I trap for that in the Keydown event, like this:

If (KeyCode = vbKeyI And ((Shift And acCtrlMask) = acCtrlMask)) Then
strMode = "Insert"
curshotnumber = Nz(Me.ShotNumber)
curshotnumsuffix = IIf(IsNull(Me.ShotNumSuffix), "", Me.ShotNumSuffix)
strRecMarker = Me.Bookmark
DoCmd.GoToRecord , , acNewRec
Call rklMakeShotNumber(strMode, curshotnumber, curshotnumsuffix)
Me.ShotNumber = curshotnumber
Me.ShotNumSuffix = strShotNumSuffix
Me.Requery
Me.Bookmark = strRecMarker
DoCmd.GoToRecord , , acNext, 1
KeyCode = 0
End If

That mode variable signals the called routine to increment the suffix but not the number. I could post the called routine as well, but note: elegant I'm not. And it's not sufficiently sophisticated to do what I want ie. exactly what you show, so what happens now is that the new record you've labeled 6b is actually 6e, and the original 6b through 6d remain unaltered. I'm not looking for code, btw, just a viable approach.

FWIW, for most data entry, they'd simply tab to a new record, OnCurrent fires and sends an "append mode" parameter to the called routine, which consequently increments only the ShotNumber. (BTW, I learned the hard way that the AcNewRecord parameter of DoCmd.GoToRecord also triggers the current event, so I trap out if On Current is fired while in "insert mode.")

Thanks for taking time, -Ron

[alcapps]

RonL nailed it..

if you not looking for code..
outline is
now that you tagged the record to insert already with the ctl I .. control insert.. nice by the way..

in vba code you would

take your key number and your character convert your character to ascii.. 65..90
so if you upper case the letters.. always.. a-z equals 65 - 90
so if you insert was as A key field was 6 you can say
filter record ids = 6 order by ascii values
so in this case you would have A= 65,B=66,C=67,D=68
loop the the records
if the char value > 65 (value char value that had the key selected)
make them their value + 1
EndLOOP
add new record key field 6 + char value(65) + 1
finished

that way you added on char value to each existing record above your key field..

then added in A=65 + 1 to equal a B record in.

requery the form and you are done..

Hope this helps..
url

[RonL]

Thanks for the idea about using the ASC() function in a loop. (It also reminds me to incorporate enforcement of case restriction. Am using lower case at moment, so don't want user to override accidentally with upper case.)

But I'm wasting lot of time trying to figure out how to set up the loop. The idea is to do it in a recordset, right? (I assume that's what you mean when you say I nailed it?) Within the rklMakeShotNumber sub referenced above, I'm trying:

Set rst = Me.Recordset
    With rst
    .Filter = "ShotNumber = curshotnumber"
    '.SetFilter = True
    rst.MoveFirst
    rst.MoveNext  <=IOW, move to either EOF of the filtered set, or the record with ShotNumSuffix='a'
    Do While Not rst.EOF
    rst.Edit
    If Asc(ShotNumSuffix) >= intSuffixMarker Then
    ShotNumSuffix = IncrementSuffix(ShotNumSuffix)
    rst.Update
    rst.MoveNext

    End If
    Loop
    Set rst = Nothing
    End With

There's probably a lot wrong. Better to think of it as pseudo code. For one thing, the debug shows ShotNumSuffix is null. It's supposed to be "a" from the example above. Curshotnum is "6" from above example.

I suspect I'm not referencing the form controls properly in the rst. Guess I don't really understand how to use the recordset object in this context, so I humbly request more specific guidance.

-Ron

[alcapps]

Ron are the records in order already? when you get to the point of adding a new record?

in recordset.. world

if you are at the insert point. the old code you had was close.

before you add the new record like you do in your first code.


just before you add the record..

put in a loop to check for the key until it changes then you are done..

so add
'you want to make sure that you know when to stop..

tempkey = rst

.ShotNumber

tempNewSufix =

chr(asc(ucase(rst!ShotNumSuffix))+1)

rst.movenext
do while not rst.eof
if temp key <> rst.

ShotNumber

then
'you found the end when the shotnumber changes
exit loop
end if
'change the letter here..
rst.edit
rst!

ShotNumSuffix = chr(asc(ucase(rst!ShotNumSuffix))+1)
'taking the upper case value of shotnumSuffix.. converting it to a asc number adding 1 to it then converting that to a letter.

rst.update
rst.movenext
loop
then do the rest like it did before making sure you do the same to the original suffix
save a new record..
tempNewSufix to fill that part on the new record..

hope this helps..

[ssanfu]

I would use the form before update so it would happed automatically and not require a button click.

Open a recordset on the ShotNumber, ordered by ShotNumber suffix DESC, then re-letter the records.

This code is semi-tested. I tried a few test cases.... it should be pretty close...
You will have to change the table name and possibly the field names.

Private Sub Form_BeforeUpdate()

   Dim rst As DAO.Recordset
   Dim sSQL As String

   Dim strShotNumSuffix As String
   Dim oldShot As Integer
   Dim newShot As Integer
   Dim intShotNumber As Integer
   Dim RC As Integer
   Dim i As Integer

   'variable                   control on form
   intShotNumber = Me.ShotNumber
   strShotNumSuffix = UCase(Nz(Me.ShotNumSuffix))

   'create SQL for recordset
   sSQL = "SELECT ShotTable.ShotNumber, ShotTable.ShotNumSuffix"
   sSQL = sSQL & " FROM ShotTable"
   sSQL = sSQL & " WHERE ShotTable.ShotNumber  = " & intShotNumber
   sSQL = sSQL & " ORDER BY ShotTable.ShotNumSuffix DESC;"

   Set rst = CurrentDb.OpenRecordset(sSQL)
   If rst.BOF And rst.EOF Then
      'no records so just allow new record to be saved
      '      MsgBox "No records"
   Else
      'recordset has one or more records
      rst.MoveLast
      RC = rst.RecordCount
      rst.MoveFirst

      Select Case RC
         Case 1
            'current record ShotNumSuffix  is NULL
            If IsNull(strShotNumSuffix) And strShotNumSuffix = "" Then
               rst.Edit
               rst!ShotNumSuffix = "A"
               rst.Update
            ElseIf IsNull(strShotNumSuffix) And Not IsNull(strShotNumSuffix) Then
               'just allow record to be saved
               '               MsgBox "Save record"
            End If
         Case Is > 1
            'if new ShotNumSuffix < rst.ShotNumSuffix then re-letter ShotNumSuffix
            For i = RC To 1 Step -1
               'if new ShotNumSuffix > rst.ShotNumSuffix or
               '    new ShotNumSuffix = rst.ShotNumSuffix then inc letter
               oldShot = Asc(Nz(rst!ShotNumSuffix, 0))
               If oldShot >= Asc(strShotNumSuffix) Then
                  rst.Edit
                  rst!ShotNumSuffix = Chr(Asc(rst!ShotNumSuffix) + 1)
                  rst.Update
               End If
               rst.MoveNext
            Next
      End Select
   End If
   
   'clean up
   rst.Close
   Set rst = Nothing


End Sub

Remember, do a lot of testing

[RonL]

I haven't forgotten this thread and I want to thank you guys for taking time to help. I'll make a few comments, then post the code I've worked up, then maybe ask a few questions.

alcapps - Yes, the records were ordered by shotnum, shotnumsuffix. The increment function I'd been using utilizes the asc() notions you suggested, but you got me thinking about doing everything without calling that function. I'm not clear if you are suggesting that I do *all* the necessary looping before "inserting" the new record, but if you look below, you'll see that's what I've done.

ssanfu -

I would use the form before update so it would happed automatically and not require a button click.

Not sure what you refer to here. There's no button click for regular appending a record, and for the "insert", I want the app to respond to a specific keyboard combo.

Open a recordset on the ShotNumber, ordered by ShotNumber suffix DESC, then re-letter the records.

This code is semi-tested. I tried a few test cases.... it should be pretty close...
You will have to change the table name and possibly the field names.

Private Sub Form_BeforeUpdate()

   Dim rst As DAO.Recordset
   Dim sSQL As String

   Dim strShotNumSuffix As String
   Dim oldShot As Integer
   Dim newShot As Integer
   Dim intShotNumber As Integer
   Dim RC As Integer
   Dim i As Integer

   'variable                   control on form
   intShotNumber = Me.ShotNumber
   strShotNumSuffix = UCase(Nz(Me.ShotNumSuffix))

   'create SQL for recordset
   sSQL = "SELECT ShotTable.ShotNumber, ShotTable.ShotNumSuffix"
   sSQL = sSQL & " FROM ShotTable"
   sSQL = sSQL & " WHERE ShotTable.ShotNumber  = " & intShotNumber
   sSQL = sSQL & " ORDER BY ShotTable.ShotNumSuffix DESC;"

   Set rst = CurrentDb.OpenRecordset(sSQL)
   If rst.BOF And rst.EOF Then
      'no records so just allow new record to be saved
      '      MsgBox "No records"
   Else
      'recordset has one or more records
      rst.MoveLast
      RC = rst.RecordCount
      rst.MoveFirst

      Select Case RC
         Case 1
            'current record ShotNumSuffix  is NULL
            If IsNull(strShotNumSuffix) And strShotNumSuffix = "" Then
               rst.Edit
               rst!ShotNumSuffix = "A"
               rst.Update
            ElseIf IsNull(strShotNumSuffix) And Not IsNull(strShotNumSuffix) Then
               'just allow record to be saved
               '               MsgBox "Save record"
            End If
         Case Is > 1
            'if new ShotNumSuffix < rst.ShotNumSuffix then re-letter ShotNumSuffix
            For i = RC To 1 Step -1
               'if new ShotNumSuffix > rst.ShotNumSuffix or
               '    new ShotNumSuffix = rst.ShotNumSuffix then inc letter
               oldShot = Asc(Nz(rst!ShotNumSuffix, 0))
               If oldShot >= Asc(strShotNumSuffix) Then
                  rst.Edit
                  rst!ShotNumSuffix = Chr(Asc(rst!ShotNumSuffix) + 1)
                  rst.Update
               End If
               rst.MoveNext
            Next
      End Select
   End If
   
   'clean up
   rst.Close
   Set rst = Nothing


End Sub

Remember, do a lot of testing

Again, thanks for putting time in on that. My intuition told me there's gotta be a way to avoid a Select Case and I do like the idea of moving forward in a loop with Do while not .eof better than backward in a For...Next. I'm sure there's a way to adapt either of the approaches you guys suggest, and if I started from scratch, I'd probably end up with a scheme closer to yours. However, I kept trying to modify what I'd already started, spending a *lot* of time (btw, making all manner of mistake - syntactical, logical, misunderstanding things like the fact that you can't put a null in a string variable, not knowing whether .requery in a recordset does the same thing as me.requery, etc. etc.). Finally, I took a breather, and then it came to me, literally while trying to sleep one night, why not do all the looping and incrementing *before* creating the new record. IOW, make a "space" for the new record then pop it in. That way I don't need to worry about the loop getting hung up by the new record (which seemed to be what was happening with the half-baked things I'd been trying.) Hence the code below, which so far seems to do the job. (No declarations shown, and still need to add error checking and some data validation eg. restrict to lower case.)

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)

If (KeyCode = vbKeyI And ((Shift And acCtrlMask) = acCtrlMask)) Then

If IsNull(Me.ShotNumSuffix) Or Me.ShotNumSuffix = "" Then
Me.ShotNumSuffix = " "
End If
lngSuffixMarker = Asc(Me.ShotNumSuffix)
curshotnumber = Me.ShotNumber
strRecMarker = Me.Bookmark

Me.Requery  'This is NECESSARY.  The recordset doesn't seem to work properly without it.
Call ReorderSuffixesForCurShotNumber
Me.Requery
Me.Bookmark = strRecMarker
DoCmd.GoToRecord , , acNext, 1
KeyCode = 0
End If

End Sub

Sub ReorderSuffixesForCurShotNumber()

Set rst = Me.Recordset
    rst.Filter = "ShotNumber = " & curshotnumber
   
    Set rstFilt = rst.OpenRecordset
    With rstFilt
    
   .MoveFirst
  ' MsgBox ("Right after .movefirst, rstFilt!shotnumsuffix is " & rstFilt!ShotNumSuffix)
   strFindFirstCriteria = "Asc([ShotNumSuffix]) = "
    .FindFirst strFindFirstCriteria & lngSuffixMarker
   ' MsgBox (".nomatch is " & .NoMatch)
    
   .MoveNext
  'MsgBox ("Just before entering Do: ShotNumSuffix is " & !ShotNumSuffix)
    Do While Not .EOF
        
     !ShotNumSuffix = Chr(Asc(!ShotNumSuffix) + 1)
         .Update
       .MoveNext

    Loop
    
    .AddNew             'incrementing done, now construct the new record and pop it in.

    !ShotNumber = curshotnumber
    lngSuffixMarker = IIf(lngSuffixMarker < 96, 96, lngSuffixMarker) 'In case current suffix was null or blank.    
    !ShotNumSuffix = Chr(lngSuffixMarker + 1)
    !ShotDescr = "This is the new record.  Suffix is  " & !ShotNumSuffix
    .Update
    '.Requery  'apparently not needed; anyway, don't understand what .requery does for an rst filtered in this manner.
    .Close
   End With
    Set rstFilt = Nothing
    
    'rst.Close   'one of these causes recordsource for form to be nulled.  How come?
    'Set rst = Nothing
End Sub

So I've copied me.recordset into a surrogate recordset object, set the filter there, not by SQL, but using the rst.filter property. (Any hazard in this?) Then copied that filtered set into a second recordset (I'm now two recordsets removed from me.recordset) Then I go directly to the record corresponding to the one the cursor was on in the form, and do the incrementing loop from there. Then pop in the new record.

Questions: Any special implications using a recordset filtered this way? From all I've read, you should close/set to nothing a recordset when done with it, but if I do that with the non filtered one, it unbinds my form completely ie. the recordsource (which is a query) disappears. Why? And re. .FindFirst, does it automatically search from the top of the set? ie. can you invoke it from any position or do you need a .movefirst first? (I'm learning how recordsets are used, and I know they're objects, but I still don't really get them. )

Any comments on how to make the above more robust would be appreciated. I actually have a good incrementing function (from the net), and plan eventually to use it in place of !ShotNumSuffix = Chr(lngSuffixMarker + 1) .


Thanks again, -Ron

[alcapps]

Great your doing what we suggested.

I would only add a couple things.
one .. keep the original lngsuffixmarker before you move next an increment the loop.
tmpsuffixmarker = lngsuffixmarker

after you do the loop

use tmpsuffixmarker to increment in the new record.

two..
you should never have a blank. if you have a blank you will need to stop saving the record.
have that check early.
if Len(Trim(Nz(lngsuffixmarker,""))) = 0 then
exit sub
end if
this code checks for null and assigns = "" if it is null
it removes all the spaces to blank string
then check the len of the string = 0 then exits..

anyway I hope this helps

[ssanfu]

Not sure what you refer to here. There's no button click for regular appending a record, and for the "insert", I want the app to respond to a specific keyboard combo

So they'd click the 6A record to make it current and hit Ctrl-I.

I took this to mean a button.... but it really doesn't matter how the code is executed.

To summarize, I want to take a subset of records that have the same value in one field, and a sequential value in a second field, effectively insert a new record in the middle of them, and increment just the second field for the "inserted" record and any for which the value of that second field is the same as or higher than the new one. A "conditional ripple down" if you will.

why not do all the looping and incrementing *before* creating the new record. IOW, make a "space" for the new record then pop it in.

That is what my code example does..
Lets say you have records:

ShotNum  ShotNumSuffix
-----------------------
6
6              A
6              B
6              C
6              D
6              E

Now you want to add a "New" 6 B record. In the table there is no order - it is a bit bucket, everything mixed together. But you do have to deal with the possibility of duplicate suffixes for a given shotnum record.

If you just add the new record, you have two records with a B suffix. So a "hole" needs to be created to be able to sort the records properly.
If you loop forward, changing the current "B" record to a "C", you now have two "C" records (instead of two "B" records). How do you know which "C" record to change to a "D"?

But if you loop backwards, start at the last record "E" and increment it to a "F", there is a hole. If you then change the current "D" to an "E", you have just moved the hole closer to where the new record should be. If you continue that process (a ripple down) - current "C" to "D" and Current "B" to "C" , ther is now a hole for the NEW record "B". There are never duplicate shotnumber suffixes.

The beauty is, you don't have to know what the highest suffix record is... you start at the highest record, in this case "E", and move backwards to the NEW record position, incrementing the suffixes.
The "Select Case" structure is to handle cases where there are no records for the shotnumber or there is one record for the shotnumber (shotnumsuffix is empty).


This is just an explanation of how my code works. As long as you get the results you want, all is good.

--------------------------
One other thing...
I noticed in your code you do not have any declarations. (ex. Dim intShotNumber As Integer)
I strongly suggest you always declare your variables - this will save you hours of debugging. Trying to find where a letter was added, dropped or changed in a variable is REALLY frustrating. Been there, done that (way to much in the spaghetti code Basic days )
There is a setting to require variable declarations (only affects new code pages once set).

'you should always have these two lines at the top of every code page
Option Compare Database
Option Explicit <<- this is what requires variable declarations.

[RonL]

..............

But if you loop backwards, start at the last record "E" and increment it to a "F", there is a hole. If you then change the current "D" to an "E", you have just moved the hole closer to where the new record should be. If you continue that process (a ripple down) - current "C" to "D" and Current "B" to "C" , ther is now a hole for the NEW record "B". There are never duplicate shotnumber suffixes.

The beauty is, you don't have to know what the highest suffix record is... you start at the highest record, in this case "E", and move backwards to the NEW record position, incrementing the suffixes. ..............

I didn't see a .AddNew in your code snippet, so I'm not sure how the "hole" is filled. Also, in the first case of your select, seems like the first record is having its intentionally blank suffix replaced by ""a", thereby losing that first record. Will study more. Understood the virtue of avoiding creating two records in the loop with the same suffix. That's the insight I came to, albeit much later than you guys did . Note that my do loop starts after the insertion point, even if that point is the first record. (Assuming .FindFirst works the way I think it does. So far, not a problem.) Don't need to know recordcount here either.

This is just an explanation of how my code works. As long as you get the results you want, all is good.

Roger that, and thanks again for the guidance.
--------------------------

I noticed in your code you do not have any declarations. (ex. Dim intShotNumber As Integer)
I strongly suggest you always declare your variables - this will save you hours of debugging. Trying to find where a letter was added, dropped or changed in a variable is REALLY frustrating. Been there, done that (way to much in the spaghetti code Basic days )
There is a setting to require variable declarations (only affects new code pages once set).

'you should always have these two lines at the top of every code page
Option Compare Database
Option Explicit <<- this is what requires variable declarations.

Right. Think I mentioned I was omitting the declarations. Was just trying to show the logical core. Will be sure to use Option Explicit. Thanks again. -Ron

[alcapps]

Are you still able to get th code to work?

[RonL]

Yes thx. Marked solved.

[ssanfu]

I know this is marked solved.. just answering your questions...

I didn't see a .AddNew in your code snippet, so I'm not sure how the "hole" is filled.

In your first post you state

I have a bound form

The code is not inserting a new record via the recordset; the bound form handles that (the new record fills the hole). As soon as you enter anything in a new record, it is created in the table. An ".AddNew" is not needed. If there was an ".AddNew" (in my code), there would be two "New" records created.

Also, in the first case of your select, seems like the first record is having its intentionally blank suffix replaced by ""a", thereby losing that first record.

That was handling the case where there might already be a shotnum of 6 with a blank shotnumsuffix and you wanted to insert an New shotnum 6 with a New blank shotnumsuffix. Then the current blank shotnumsuffix should become "A". Just trying to cover all possibilities.

Anyway, glad you have it working the way you want.

[RonL]

The code is not inserting a new record via the recordset; the bound form handles that (the new record fills the hole). As soon as you enter anything in a new record, it is created in the table. An ".AddNew" is not needed. If there was an ".AddNew" (in my code), there would be two "New" records created.

Ok, thanks, now I really do need to study differences between our codes, since I am adding the new record from the recordset. It seems to work, but maybe it's not really robust. I'm still bothered by fact that I can't close that first recordset without nullifying the recordsource of the form.... .

That was handling the case where there might already be a shotnum of 6 with a blank shotnumsuffix and you wanted to insert an New shotnum 6 with a New blank shotnumsuffix. Then the current blank shotnumsuffix should become "A". Just trying to cover all possibilities.

Ah, ok. I should have explained at outset, user never wants the initial shotnum repeated without a suffix. The suffix tells him he inserted the record. (No suffix means it was an appendation - ie. simulating adding a new row at bottom of a spreadsheet.)

Thx again. -Ron