Memo field containing text and numbers - need to update by adding 1 to the number

[sw1085]

Hi everyone

I wondered if someone could spot an obvious error in this code as it appears not be updating correctly the data in this memo field that i have. It leaves the text in the field. My fields or formatting etc is unchangeable as these tables are linked to another system outside Access.

I basically used the arrow keys to go along 30 characters to get the "A" in the field data. The number after the A needs to be updated after my script has been run. I am basically transferring data from one table to another and giving each a reference number based on the number in this field and increasing it by 1 each time. So the A0004120 is the next number to be used but the data after that is not meant to be there. Strangely enough when I click on the number in Access for the other fields - it seems to then remove the first bit of text "Internal Receipt No" for example and only show me the "A0000001" allowing me to edit it but not in "Purchase Invoice Number" and I think that is because of what my code below is doing to increase the number.

I would prefer some kind of code to search for the letter "A" in the memo field then update the numbers after the A by adding 1 because then I dont need to do anything to the spaces before and after the "AXXXXXXX" as the system outside Access reads the data in a set way. Is there such a code someone could help with in place of the code below?

Anyway thanks for reading and I hope someone could take some moments out to help.

rs3.Open "SELECT (SELECT Left([SUN_DATA],30) AS SnDT1 FROM dbo_SSRFMSC WHERE (((dbo_SSRFMSC.SUN_DB)='" & RS!SUN_DB & "') AND ((dbo_SSRFMSC.SUN_TB)='SQN') AND ((Trim([KEY_FIELDS]))='PI   A'))) & 'A00' & Right('0' & Max(Right([ANAL_T9],5)+1),5) & (SELECT Right([SUN_DATA],901) AS SnDT3 FROM dbo_SSRFMSC WHERE (((dbo_SSRFMSC.SUN_DB)='" & RS!SUN_DB & "') AND ((dbo_SSRFMSC.SUN_TB)='SQN') AND ((Trim([KEY_FIELDS]))='PI   A'))) AS AnT9 FROM [PRF-User Entry] WHERE ((([PRF-User Entry].SUN_DB)='" & RS!SUN_DB & "'));", con, 1
        rsu3.Open "UPDATE dbo_SSRFMSC SET dbo_SSRFMSC.SUN_DATA ='" & [rs3]![AnT9] & "' WHERE (((dbo_SSRFMSC.SUN_DB)='" & [RS]![SUN_DB] & "') AND ((dbo_SSRFMSC.SUN_TB)='SQN') AND ((Trim([KEY_FIELDS]))='PI   A'));", con, 2

Best regards

SW

[June7]

Data in the Sun field looks like should be at least 2 fields.

Why does the 4th record show two Purchase Invoice Numbers? That really throws a kink into any code structure. And the 7th record has a 15 character number - another kink.

[sw1085]

I agree it should be in two fields but I cannot change the structure or field formatting of that table so I am trying to work around the issue.

The addition of "Purchase Invoice Numbers" twice is an error in the code which I am trying to resolve. Instead of updating the value from A0004017 to A0004020 it appears to be leaving the old value in. All I am trying to achieve is my records are transfer from one table to another and each record is allocated a number so first A0004017 would have been used, then A0004018 and so on.

We are really only focussing on the row which has "SUN_TB" = "SQN" and "KEY_FIELD" = "PI A" so I didn't think there would be a problem.

Is there not that can simply find "A0" if the above criteria is met to get it on the correct row then go to the last number in the sequence of numbers so 7 in this case and then increase by 1 to go to A0004018? Is there such a way to do this with VB code.

Data in the Sun field looks like should be at least 2 fields.

Why does the 4th record show two Purchase Invoice Numbers? That really throws a kink into any code structure. And the 7th record has a 15 character number - another kink.

[June7]

Yes, it can be done but consistency of data structure is critical. If the 'number' part will always be 7 digits and "A" will always be the prefix, try:

x represents the SUN_DATA field

Left(x, InstrRev(x,"A")) & Format(Val(Mid(x,InstrRev(x,"A")+1))+1,"0000000")

[sw1085]

June

Thanks very much for your piece of code.

Can you possibly help me update my previous code with this? I am struggling to update it with what you wrote below but it looks like t will provide exactly what I needed which is to keep the formatting and spaces etc all the same and simply add 1 to the number as we loop through the transfer of a record from one table to another.

Appreciate your taking the time to assist.

SW

Yes, it can be done but consistency of data structure is critical. If the 'number' part will always be 7 digits and "A" will always be the prefix, try:

x represents the SUN_DATA field

Left(x, InstrRev(x,"A")) & Format(Val(Mid(x,InstrRev(x,"A")+1))+1,"0000000")

[June7]

What you show is not a complete procedure. It looks like you are trying to open two recordsets. However, recordsets only work with SELECT sql, not action sql like DELETE, UPDATE, INSERT. Use DoCmd.RunSQL or CurrentDb.Execute to run action SQL. You can run the sql by building the string in VBA or by reference to an Access query object.

Use Access query designer to build working UPDATE query then paste its sql string into VBA procedure if you want the sql completely in VBA, edit the string as appropriate for VBA.

This mod to my suggested expression doesn't rely on what the letter prefix is, as long as the pattern of string ending in 7 digits is reliable.
Left(x, Len(x)-7) & Format(Val(Right(x,7))+1,"0000000")

[sw1085]

Hi June7

Thanks for your reply. Any help you can give to correct my code or make it more efficient would be very much appreciated and if your in London ever, I will certainly have a beer with you. I've had other threads open to try to solve this and I am almost there now. It all works except the updating of this memo field without any change to formatting or spaces etc in the memo field. Just +1 to the seven digit number.

Thanks again

It may be easier if I simply upload my database but here is my full procedure - now I am getting a Syntax error on the highlighted line:

If you think the database might be easier for you to help me with then let me know.

Option Compare Database


Private Sub Command0_Click()
    
    Dim con As Object
    Dim RS As Object
    Dim RSs As Object
    Dim Sql1 As String
    Dim Sql2 As String
    
    Dim rsi1 As Object
    Set rsi1 = CreateObject("ADODB.Recordset")
    
    
    Set con = Application.CurrentProject.Connection
    
    Sql1 = "SELECT DISTINCT [PRF-User Entry].SUN_DB, (select val(HIGH_JRNAL) from dbo_SALFMSC where dbo_SALFMSC.D_Base=[PRF-User Entry].SUN_DB and dbo_SALFMSC.A_B='A') AS GtIniRef, (SELECT Val(Right(Trim([dbo_SSRFMSC].[SUN_DATA]),7)) AS SUN_DATA FROM dbo_SSRFMSC WHERE (((dbo_SSRFMSC.SUN_DB)=[PRF-User Entry].[SUN_DB]) AND ((dbo_SSRFMSC.SUN_TB)='SQN') AND ((Trim([dbo_SSRFMSC].[KEY_FIELDS]))='PI   A'))) AS GtT9Rf FROM [PRF-User Entry] WHERE ((([PRF-User Entry].SUPP_CODE) Is Not Null) AND (([PRF-User Entry].[GROSS AMOUNT]) Is Not Null) AND (([PRF-User Entry].READY)=True));"
    'SELECT DISTINCT [PRF-User Entry].SUN_DB, (select HIGH_JRNAL from dbo_SALFMSC where dbo_SALFMSC.D_Base=[PRF-User Entry].SUN_DB and dbo_SALFMSC.A_B="A") AS GtHJRf, (SELECT Val(Right(Trim([dbo_SSRFMSC].[SUN_DATA]),7)) AS SUN_DATA FROM dbo_SSRFMSC WHERE (((dbo_SSRFMSC.SUN_DB)=[PRF-User Entry].[SUN_DB]) AND ((dbo_SSRFMSC.SUN_TB)='SQN') AND ((Trim([dbo_SSRFMSC].[KEY_FIELDS]))='PI   A'))) AS GtT9Rf, [PRF-User Entry].TrnsRefNo FROM [PRF-User Entry] WHERE ((([PRF-User Entry].SUPP_CODE) Is Not Null) AND (([PRF-User Entry].[GROSS AMOUNT]) Is Not Null) AND (([PRF-User Entry].READY)=True));"


    Set RS = CreateObject("ADODB.Recordset")
    Set RSs = CreateObject("ADODB.Recordset")
    RS.Open Sql1, con, 1
    
    Dim i As Integer
    For i = 1 To RS.RecordCount
        Dim rs1 As Object
        Dim rs3 As Object
        Dim rsu1 As Object
        Dim rsu2 As Object
        Dim rsu3 As Object
            
        Set rs1 = CreateObject("ADODB.Recordset")
        Set rs2 = CreateObject("ADODB.Recordset")
        Set rs3 = CreateObject("ADODB.Recordset")
        Set rsu1 = CreateObject("ADODB.Recordset")
        Set rsu2 = CreateObject("ADODB.Recordset")
        Set rsu3 = CreateObject("ADODB.Recordset")
        
        rs1.Open "SELECT [PRF-User Entry].SUN_DB, [PRF-User Entry].JRNAL_NO, [PRF-User Entry].TrnsRefNo FROM [PRF-User Entry] WHERE ((([PRF-User Entry].SUN_DB)='" & RS!SUN_DB & "') AND (([PRF-User Entry].SUPP_CODE) Is Not Null) AND (([PRF-User Entry].[GROSS AMOUNT]) Is Not Null) AND (([PRF-User Entry].READY)=True));", con, 1
        Dim j As Integer
        Dim k As Integer
        j = 1
        k = 0
        Do While rs1.EOF = False
            rsu1.Open "UPDATE [PRF-User Entry] SET [PRF-User Entry].JRNAL_NO =" & RS!GtIniRef + j & ",[PRF-User Entry].ANAL_T9 ='PI" & Right("00000" & RS!GtT9Rf + k, 5) & "' WHERE ((([PRF-User Entry].TrnsRefNo)=" & rs1!TrnsRefNo & "));", con, 2
            j = j + 1
            k = k + 1
            rs1.MoveNext
        Loop
        rs2.Open "SELECT Right('0000000' & Max([JRNAL_NO]),7) AS LstJrnRef, Max(Right([ANAL_T9],5))+1 AS LstAnl9 FROM [PRF-User Entry] WHERE ((([PRF-User Entry].SUN_DB)='" & RS!SUN_DB & "'));", con, 1
        rsu2.Open "UPDATE dbo_SALFMSC SET dbo_SALFMSC.HIGH_JRNAL ='" & rs2!LstJrnRef & "' WHERE (((dbo_SALFMSC.D_BASE)='" & RS!SUN_DB & "') AND ((dbo_SALFMSC.A_B)='A'));", con, 2
        rs3.Open "SELECT Replace([SUN_DATA],Mid([SUN_DATA],31,38),(SELECT 'A00' & Right('0' & Max(Right([ANAL_T9],5)+1),5) AS AnT9 FROM [PRF-User Entry] WHERE ((([PRF-User Entry].SUN_DB)=dbo_SSRFMSC.SUN_DB)))) AS SD FROM dbo_SSRFMSC WHERE (((dbo_SSRFMSC.SUN_DB)='" & RS!SUN_DB & "') AND ((dbo_SSRFMSC.SUN_TB)='SQN') AND ((Trim([KEY_FIELDS]))='PI   A'));", con, 1
        rsu3.Open "UPDATE dbo_SSRFMSC SET dbo_SSRFMSC.SUN_DATA ='" & [rs3]![SD] & "' WHERE (((dbo_SSRFMSC.SUN_DB)='" & [RS]![SUN_DB] & "') AND ((dbo_SSRFMSC.SUN_TB)='SQN') AND ((Trim([KEY_FIELDS]))='PI   A'));", con, 2
        
        RS.MoveNext
    Next i
    
    Sql2 = "SELECT [PRF-User Entry].TrnsRefNo,[PRF-User Entry].SUN_DB FROM [PRF-User Entry] WHERE ((([PRF-User Entry].SUPP_CODE) Is Not Null) AND (([PRF-User Entry].[GROSS AMOUNT]) Is Not Null) AND (([PRF-User Entry].READY)=True));"
                                                                                            
    RSs.Open Sql2, con, 1
    
    Do While RSs.EOF = False
        sqla = " SELECT Left([PRF-User Entry].[SUPP_CODE] & Space(15),15) AS ACCNT_CODE, Val(DatePart('yyyy',[PRF-User Entry].PERIOD) & Right('000' & DatePart('m',[PRF-User Entry].PERIOD),3)) AS PERIOD, Val(DatePart('yyyy',[PRF-User Entry].[INV_DATE]) & Right('00' & DatePart('m',[PRF-User Entry].[INV_DATE]),2) & Right('00' & DatePart('d',[PRF-User Entry].[INV_DATE]),2)) AS TRANS_DATE, [PRF-User Entry].[JRNAL_NO] AS JRNAL_NO, 1 AS JRNAL_LINE, [PRF-User Entry].[GROSS AMOUNT] AS AMOUNT, 'C' AS D_C, ' ' AS ALLOCATION, 'PI   ' AS JRNAL_TYPE, 'OZG  ' AS JRNAL_SRCE, Left([PRF-User Entry].[TREFERENCE] & Space(15),15) AS TREFERENCE, Left([PRF-User Entry].[DESCRIPTN] & Space(15),15) AS " & vbCrLf & _
        "DESCRIPTN, Val(DatePart('yyyy',Now()) & Right('00' & DatePart('m',Now()),2) & Right('00' & DatePart('d',Now()),2)) AS ENTRY_DATE, Val(DatePart('yyyy',Now()) & Right('000' & DatePart('m',Now()),3)) AS ENTRY_PRD, 0 AS DUE_DATE, 0 AS ALLOC_REF, 0 AS ALLOC_DATE, 0 AS ALLOC_PERIOD, Space(1) AS ASSET_IND, Space(10) AS ASSET_CODE, Space(5) AS ASSET_SUB, Left([PRF-User Entry].[CONV_CODE] & Space(5),5) AS CONV_CODE, 0 AS CONV_RATE, 0 AS OTHER_AMT, Left([PRF-User Entry].[OTHER_DP] & Space(1),1) AS OTHER_DP, Space(5) AS CLEARDOWN, Space(1) AS REVERSAL, Space(1) AS LOSS_GAIN, Space(1) AS ROUGH_FLAG, Space(1) AS IN_USE_FLAG, Space(15) AS ANAL_T0, Left([PRF-User Entry].[ANAL_T1] & Space(15),15) AS ANAL_T1, Left([PRF-User Entry].[ANAL_T2] & Space(15),15) AS " & vbCrLf & _
        "ANAL_T2, Left([PRF-User Entry].[ANAL_T3] & Space(15),15) AS ANAL_T3, Left(Right(Trim([PRF-User Entry].[SUPP_CODE]),3) & Space(15),15) AS ANAL_T4, Left([PRF-User Entry].[ANAL_T5].[value] & Space(15),15) AS ANAL_T5, Left([PRF-User Entry].[ANAL_T6] & Space(15),15) AS ANAL_T6, Left([PRF-User Entry].[ANAL_T7].[value] & Space(15),15) AS ANAL_T7, Left([PRF-User Entry].[ANAL_T8] & Space(15),15) AS ANAL_T8, Left([PRF-User Entry].[ANAL_T9] & Space(15),15) AS ANAL_T9, 0 AS POSTING_DATE, '' AS ALLOC_IN_PROGRESS, 0 AS HOLD_REF, Space(3) AS HOLD_OP_ID, Space(3) AS LAST_CHANGE_USER_ID, 0 AS LAST_CHANGE_DATE, Space(3) AS ORIGINATOR_ID FROM [PRF-User Entry] where [PRF-User Entry].TrnsRefNo=" & RSs!TrnsRefNo
        rsi1.Open "INSERT INTO dbo_SALFLDG" & RSs!SUN_DB & sqla, con, 2
    
        sqlb = " SELECT Left('271' & Space(15),15) AS ACCNT_CODE, Val(DatePart('yyyy',[PRF-User Entry].PERIOD) & Right('000' & DatePart('m',[PRF-User Entry].PERIOD),3)) AS PERIOD, Val(DatePart('yyyy',[PRF-User Entry].[INV_DATE]) & Right('00' & DatePart('m',[PRF-User Entry].[INV_DATE]),2) & Right('00' & DatePart('d',[PRF-User Entry].[INV_DATE]),2)) AS TRANS_DATE, [PRF-User Entry].[JRNAL_NO] AS JRNAL_NO, 2 AS JRNAL_LINE, -1*[PRF-User Entry].[VAT] AS AMOUNT, 'D' AS D_C, ' ' AS ALLOCATION, 'PI   ' AS JRNAL_TYPE, 'OZG  ' AS JRNAL_SRCE, Left([PRF-User Entry].[TREFERENCE] & Space(15),15) AS TREFERENCE, Left([PRF-User Entry].[DESCRIPTN] & Space(15),15) AS " & vbCrLf & _
        "DESCRIPTN, Val(DatePart('yyyy',Now()) & Right('00' & DatePart('m',Now()),2) & Right('00' & DatePart('d',Now()),2)) AS ENTRY_DATE, Val(DatePart('yyyy',Now()) & Right('000' & DatePart('m',Now()),3)) AS ENTRY_PRD, 0 AS DUE_DATE, 0 AS ALLOC_REF, 0 AS ALLOC_DATE, 0 AS ALLOC_PERIOD, Space(1) AS ASSET_IND, Space(10) AS ASSET_CODE, Space(5) AS ASSET_SUB, Left([PRF-User Entry].[CONV_CODE] & Space(5),5) AS CONV_CODE, 0 AS CONV_RATE, 0 AS OTHER_AMT, Left([PRF-User Entry].[OTHER_DP] & Space(1),1) AS OTHER_DP, Space(5) AS CLEARDOWN, Space(1) AS REVERSAL, Space(1) AS LOSS_GAIN, Space(1) AS ROUGH_FLAG, Space(1) AS IN_USE_FLAG, Space(15) AS ANAL_T0, Left([PRF-User Entry].[ANAL_T1] & Space(15),15) AS ANAL_T1, Left([PRF-User Entry].[ANAL_T2] & Space(15),15) AS " & vbCrLf & _
        "ANAL_T2, Left([PRF-User Entry].[ANAL_T3] & Space(15),15) AS ANAL_T3, Left(Right(Trim([PRF-User Entry].[SUPP_CODE]),3) & Space(15),15) AS ANAL_T4, Left([PRF-User Entry].[ANAL_T5].[value] & Space(15),15) AS ANAL_T5, Left([PRF-User Entry].[ANAL_T6] & Space(15),15) AS ANAL_T6, Left([PRF-User Entry].[ANAL_T7].[value] & Space(15),15) AS ANAL_T7, Left([PRF-User Entry].[ANAL_T8] & Space(15),15) AS ANAL_T8, Left([PRF-User Entry].[ANAL_T9] & Space(15),15) AS ANAL_T9, 0 AS POSTING_DATE, '' AS ALLOC_IN_PROGRESS, 0 AS HOLD_REF, Space(3) AS HOLD_OP_ID, Space(3) AS LAST_CHANGE_USER_ID, 0 AS LAST_CHANGE_DATE, Space(3) AS ORIGINATOR_ID FROM [PRF-User Entry] where [PRF-User Entry].TrnsRefNo=" & RSs!TrnsRefNo
        rsi1.Open "INSERT INTO dbo_SALFLDG" & RSs!SUN_DB & sqlb, con, 2
    
        sqlc = " SELECT Left([PRF-User Entry].[ACCNT_CODE] & Space(15),15) AS ACCNT_CODE, Val(DatePart('yyyy',[PRF-User Entry].PERIOD) & Right('000' & DatePart('m',[PRF-User Entry].PERIOD),3)) AS PERIOD, Val(DatePart('yyyy',[PRF-User Entry].[INV_DATE]) & Right('00' & DatePart('m',[PRF-User Entry].[INV_DATE]),2) & Right('00' & DatePart('d',[PRF-User Entry].[INV_DATE]),2)) AS TRANS_DATE, [PRF-User Entry].[JRNAL_NO] AS JRNAL_NO, 3 AS JRNAL_LINE, -1*[PRF-User Entry].[NET AMOUNT] AS AMOUNT, 'D' AS D_C, ' ' AS ALLOCATION, 'PI   ' AS JRNAL_TYPE, 'OZG  ' AS JRNAL_SRCE, Left([PRF-User Entry].[TREFERENCE] & Space(15),15) AS TREFERENCE, Left([PRF-User Entry].[DESCRIPTN] & Space(15),15) AS " & vbCrLf & _
        "DESCRIPTN, Val(DatePart('yyyy',Now()) & Right('00' & DatePart('m',Now()),2) & Right('00' & DatePart('d',Now()),2)) AS ENTRY_DATE, Val(DatePart('yyyy',Now()) & Right('000' & DatePart('m',Now()),3)) AS ENTRY_PRD, 0 AS DUE_DATE, 0 AS ALLOC_REF, 0 AS ALLOC_DATE, 0 AS ALLOC_PERIOD, Space(1) AS ASSET_IND, Space(10) AS ASSET_CODE, Space(5) AS ASSET_SUB, Left([PRF-User Entry].[CONV_CODE] & Space(5),5) AS CONV_CODE, 0 AS CONV_RATE, 0 AS OTHER_AMT, Left([PRF-User Entry].[OTHER_DP] & Space(1),1) AS OTHER_DP, Space(5) AS CLEARDOWN, Space(1) AS REVERSAL, Space(1) AS LOSS_GAIN, Space(1) AS ROUGH_FLAG, Space(1) AS IN_USE_FLAG, Space(15) AS ANAL_T0, Left([PRF-User Entry].[ANAL_T1] & Space(15),15) AS ANAL_T1, Left([PRF-User Entry].[ANAL_T2] & Space(15),15) AS " & vbCrLf & _
        "ANAL_T2, Left([PRF-User Entry].[ANAL_T3] & Space(15),15) AS ANAL_T3, Left(Right(Trim([PRF-User Entry].[SUPP_CODE]),3) & Space(15),15) AS ANAL_T4, Left([PRF-User Entry].[ANAL_T5].[value] & Space(15),15) AS ANAL_T5, Left([PRF-User Entry].[ANAL_T6] & Space(15),15) AS ANAL_T6, Left([PRF-User Entry].[ANAL_T7].[value] & Space(15),15) AS ANAL_T7, Left([PRF-User Entry].[ANAL_T8] & Space(15),15) AS ANAL_T8, Left([PRF-User Entry].[ANAL_T9] & Space(15),15) AS ANAL_T9, 0 AS POSTING_DATE, '' AS ALLOC_IN_PROGRESS, 0 AS HOLD_REF, Space(3) AS HOLD_OP_ID, Space(3) AS LAST_CHANGE_USER_ID, 0 AS LAST_CHANGE_DATE, Space(3) AS ORIGINATOR_ID FROM [PRF-User Entry] where [PRF-User Entry].TrnsRefNo=" & RSs!TrnsRefNo
        rsi1.Open "INSERT INTO dbo_SALFLDG" & RSs!SUN_DB & sqlc, con, 2
        rsu1.Open "UPDATE [PRF-User Entry] SET [PRF-User Entry].READY = False, [PRF-User Entry].POSTED_TO_SUN = True WHERE ((([PRF-User Entry].TrnsRefNo)=" & RSs!TrnsRefNo & "));", con, 2
        RSs.MoveNext
    Loop
End Sub

What you show is not a complete procedure. It looks like you are trying to open two recordsets. However, recordsets only work with SELECT sql, not action sql like DELETE, UPDATE, INSERT. Use DoCmd.RunSQL or CurrentDb.Execute to run action SQL. You can run the sql by building the string in VBA or by reference to an Access query object.

Use Access query designer to build working UPDATE query then paste its sql string into VBA procedure if you want the sql completely in VBA, edit the string as appropriate for VBA.

This mod to my suggested expression is more flexible, doesn't matter what the letter prefix is, as long as the pattern of string ending in 7 digits is consistent.
Left(x, Len(x)-7) & Format(Val(Right(x,7))+1,"0000000")