Incrementing Del No After Current No

**DMT Dave** · 08-03-2023, 03:04 AM

Hi Guy's, bit of a brain freeze here, this is something I should be able to do in my sleep but a lapse in working out the correct method !!

I have a table with a set of deliveries of deliveries on a particular day for a particular driver

A new delivery has been imported with a DelNo of 0 (Zero)

Let say there are 6 deliveries on the selected day for the selected driver ie:

Del No Driver
0 Joe Bloggs (New Record)
1 Joe Bloggs
2 Joe Bloggs
3 Joe Bloggs
4 Joe Bloggs
5 Joe Bloggs
6 Joe Bloggs

If i change del no 0 to 4, I now want to adjust every record that is currently 4 to 5,6 and 7

1 Joe Bloggs
2 Joe Bloggs
3 Joe Bloggs
4 Joe Bloggs (New Record)
5 Joe Bloggs
6 Joe Bloggs
7 Joe Bloggs

Brain freeze will not let me adjust this!!!

Sorry for asking for something that is so simple!!!!

iCurrentDelNo returns 4 and iMaxDelNo returns 6

I think just recordset adjustment

Current start on this

Code:

Dim dtDelDate As Date
Dim iMaxDelNo As Integer, iNewDelNo As Integer, iCurrentDelNo As Integer
Dim sDriver As String, sDay As String, sSQL As String
Dim rs As DAO.Recordset


dtDelDate = Me.DelDate
sDriver = Forms!frmRouteEdit!cboDriver
sDay = Forms!frmRouteEdit!cboDay


iCurrentDelNo = Me.DelNo


iMaxDelNo = DMax("DelNo", "tblRoutes2", "[Driver] = '" & sDriver & "' And [DayName] = '" & sDay & "'")


MsgBox (iCurrentDelNo & vbCrLf & iMaxDelNo)


Set rs = CurrentDb.OpenRecordset("Select * From tblRoutes2 " _
        & "WHERE DayName = '" & sDay & "' " _
        & "AND Driver = '" & sDriver & "' " _
        & "AND DelNo >= " & iCurrentDelNo)

**DMT Dave** · 08-03-2023, 03:10 AM

Just tried:

Code:

Set rs = CurrentDb.OpenRecordset("Select * From tblRoutes2 " _
        & "WHERE DayName = '" & sDay & "' " _
        & "AND Driver = '" & sDriver & "' " _
        & "AND DelNo >= " & iCurrentDelNo)
        
    With rs
        Do Until rs.EOF
            .Edit
            !DelNo = iCurrentDelNo + 1
            rs.MoveNext
        Loop
    End With

Not adjusting

**Welshgasman** · 08-03-2023, 03:13 AM

I would just use a query that updates value with value +1 for anything above your starting value.
THEN amend the 0 to the new value, not before.

**DMT Dave** · 08-03-2023, 03:47 AM

WGM, as always thank you

I have created this, the change of new del no is a physical change in the del no text box
(changing 0 to 4)

The fear of messing records up, is this the kind of update you refer to ??

Code:

sSQL = "UPDATE tblRoutes2 SET tblRoutes2.DelNo = " & iCurrentDelNo & "+ 1 " _        & "WHERE (((tblRoutes2.DayName)='" & sDay & "') " _
        & "AND ((tblRoutes2.Driver)='" & sDriver & "') " _
        & "AND ((tblRoutes2.DelNo)> " & iCurrentDelNo & "));"
    
        DoCmd.SetWarnings False
            DoCmd.RunSQL sSQL
        DoCmd.SetWarnings True
        
        If Me.Dirty Then Me.Dirty = False

**DMT Dave** · 08-03-2023, 04:41 AM

I can't get this update to work!!

Have i wrote the update statement correct ? !!!

the way i read it i would believe is correct but obviously not!!

Kindest

**Welshgasman** · 08-03-2023, 06:48 AM

Always Debug.print the sql.

Then you can copy that into the sql window and run it from there, or pick up any syntax errors if you cannot still spot them.

I would always start with a Select query to ensure I am getting the correct data, before converting to an update query.

I also prefer the
SstrSQL = strSQL & " WHERE....." method rather than line continuation.

I would expect your addition to be with the field, not added as a string?

AGAIN, a Debug.print would confirm one way or the other.

**orange** · 08-03-2023, 07:52 AM

Dave,

Here is a mockup to insert a new record in the middle of an ordered list. Just 1 way, but there are others.

Code:

 ----------------------------------------------------------------
' Procedure Name: DaveDelivery
' Purpose: Routine to insert a new record into an established list of delivery numbers
'         Must increase the existing delivery numbers after the position of the new record,
'         allowing for insertion of a new record at the known position in the list
' In the sample there are 6 records, need to insert a new record 4,
'so in original  4 becomes 5, 5 becomes 6 , 6 becomes 7, 1 2 an3 don't change and a new 4 is inserted
'
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 03-Aug-23
' ------------------------MOCK UP FOR------------------------------
' ----------https://www.accessforums.net/showthread.php?t=88489&p=514877#post514877-----------------
Sub DaveDelivery()
10        On Error GoTo DaveDelivery_Error
          Dim newNum As Integer
20        newNum = 4
          Dim db As DAO.Database
          Dim rs As DAO.Recordset
30        Set db = CurrentDb
          Dim i As Integer
          Dim recCount As Integer
          'NOTE  Only DelNo is in the Select for rs
40        Set rs = db.OpenRecordset("select delno from tblDave order by delno")
50        rs.MoveLast
60        recCount = rs.RecordCount

'       starting with last record in the recordset
'       adjust the DelNo
70        For i = recCount To newNum Step -1
80            rs.Edit
90            rs!DelNo = rs!DelNo + 1
100           Debug.Print i; rs!DelNo
110           rs.Update
120           rs.Move -1
130       Next i
         'DelNo s have been adjusted to allow insert of new record
         'insert the new record .... only explicitly insert DelNo
140       rs.AddNew
150       rs!DelNo = newNum
170       rs.Update

180       On Error GoTo 0
DaveDelivery_Exit:
190       Exit Sub

DaveDelivery_Error:

200       MsgBox "Error " & Err.Number & " (" & Err.Description & "), line " & Erl & " in Procedure DaveDelivery" _
              & "  Module  ZZ_ScratchPad "
210       GoTo DaveDelivery_Exit
End Sub

Simpler, sql approach:

1: Update tblDave
set delNo = delNo +1 where delno >= 4;

2: insert into tblDave (delno, otherinfo)
values (4,"newDeliveryRecord")

**DMT Dave** · 08-03-2023, 11:36 AM

Thank you Orange, just read through the RS option, the record is already added prior to adjusting delivery no, so I am guessing everything after your suggestion 'Insert the new record would be already completed ?

I guess i am editing as the new record with a 0 zero delivery no, but will read through thoroughly at the method in which what i was struggling with, when in my initial post, this is something i should be able to do in my sleep

Thank the lord for you guy's to give people like me a nudge and good advice..

Will look at both suggestions and adjust accordingly....

**DMT Dave** · 08-03-2023, 12:30 PM

Orange and WGM, thank you.

@Orange, both approaches worked well, here is my end code, the only part that this does:

because there is already a delno 4 in the table, then i change my new del no from 0 to 4 thus creating 2 records with a del 4, then the one i change on after update also goes to 5 along with the old 4, so the end of code, i have told it to -1

Code:

Dim dtDelDate As DateDim iMaxDelNo As Integer, iNewDelNo As Integer, iCurrentDelNo As Integer, iDelNo As Integer
Dim sDriver As String, sDay As String, sSQL As String
Dim rs As DAO.Recordset




dtDelDate = Me.DelDate
sDriver = Forms!frmRouteEdit!cboDriver
sDay = Forms!frmRouteEdit!cboDay
iDelNo = Me.DelNo


sSQL = "Update tblRoutes2 " _
    & "set delNo = delNo +1 where delno >= " & iDelNo


Debug.Print sSQL




If Me.Dirty Then Me.Dirty = False


    DoCmd.SetWarnings False
        DoCmd.RunSQL sSQL
    DoCmd.SetWarnings True
    
Me.DelNo = Me.DelNo - 1

**Welshgasman** · 08-03-2023, 12:40 PM

Cannot see that working as you do not take into account all the other criteria? You will change every drivers deliveries?

**DMT Dave** · 08-03-2023, 01:07 PM

Yes, correct, i have changed and added criteria's, this now works perfect, thanks WGM and Orange

Code:

Dim dtDelDate As DateDim iMaxDelNo As Integer, iNewDelNo As Integer, iCurrentDelNo As Integer, iDelNo As Integer
Dim sDriver As String, sDay As String, sSQL As String, strPC As String, sSQL2 As String
Dim rs As DAO.Recordset




dtDelDate = Me.DelDate
sDriver = Forms!frmRouteEdit!cboDriver
sDay = Forms!frmRouteEdit!cboDay
iDelNo = Me.DelNo
strPC = Me.PostCode




sSQL = "Update tblRoutes2 " _
    & "set delNo = delNo +1 where delno >= " & iDelNo


Debug.Print sSQL




If Me.Dirty Then Me.Dirty = False


    DoCmd.SetWarnings False
        DoCmd.RunSQL sSQL
    DoCmd.SetWarnings True
    
Set rs = CurrentDb.OpenRecordset("Select * from tblRoutes2 WHERE PostCode = '" & strPC & "'")


    With rs
        .Edit
        !DelNo = iDelNo
        .Update
        .Close
    End With
    


If Me.Dirty Then Me.Dirty = False


    DoCmd.SetWarnings False
        Forms!frmRouteEdit!frmRouteEditDS.Requery
        
    DoCmd.SetWarnings True

**DMT Dave** · 08-03-2023, 01:09 PM

I should have perhaps mentioned i am changing this on after Updatedate in the delno field direct on a form datasheet, whenever is have a form in datasheet view, i always name the end of the form with DS

**Welshgasman** · 08-03-2023, 01:14 PM

I do not see any additional criteria?
If you have just updated the records with your query and your new record is sitting in the form as number 4, then all you have to do is force the save with If Me.Dirty ?
You only have to do it once as well?

**DMT Dave** · 08-03-2023, 01:20 PM

Point taken totally, also i have it's just dawned on me from your previous comment regarding update all delivery number 4's

i need to add driver and dayName criteria to the following update ?

Code:

sSQL = "Update tblRoutes2 " _
    & "set delNo = delNo +1 where delno >= " & iDelNo

**Welshgasman** · 08-03-2023, 01:25 PM

Well I do not know your data structure, but it would be a good guess that you need them as well?

Incrementing Del No After Current No

Thread Tools

Incrementing Del No After Current No

Similar Threads

Help with incrementing a counter

Incrementing Numbers

Integer not incrementing

Incrementing a value in a form

Incrementing a field

Posting Permissions