add sequential number into a field for each group of records

[MrMode]

I have a string that I am able to split into separate records.
ID --- string of values
1 ---- 1, 0, 0
2 ---- 0, 1,0 etc

Each string has the same number of values (43 records).
ID ---- value ---- Position
1 ----- 1
1 ----- 0
1 ----- 0 etc

I need to append a sequential number to the Position field of each set of strings.
ID ---- value ---- Position
1 ----- 1 --------- 1
1 ----- 0 --------- 2
1 ----- 0 --------- 3
2 ----- 0 --------- 1
2 ----- 1 --------- 2
2 ----- 0 --------- 3

 

[MrMode]

I have a very ugly solution using the ItemsSelected value to enter the sequential number.

I have 3 lists on my form

lstRawData is the raw data with the strings
lstExtractedData is where the extracted strings are displayed
lstWorkAround is used by the vba to grab the lstExtractedData information for each of the items in lstRawData

SelectAll

    Dim varvalue
    Dim varvalue2
    Dim strsql

        With Me.lstRawData 
        For Each varvalue In .ItemsSelected
        
            With Me.lstWorkAround 
            .RowSource = _
                "SELECT tblStringData.RawDataID, tblStringData.ExtractedValue, tblStringData.Position, tblStringData.Autoid " & _
                "FROM tblStringData" & _
                "WHERE ((tblStringData.RawDataID) =" & Me.lstRawData .Column(0, varvalue) & " );"
            .ColumnCount = 4
            .ColumnWidths = "2cm;2cm;2cm;2cm"
            End With
            'Debug.Print Me.lstWorkAround .RowSource
            
                SelectByID
    
                    With Me.lstWorkAround 
                    For Each varvalue2 In .ItemsSelected
                        strsql = "UPDATE tblStringDataSET tblStringData.Position = '" & Me.lstWorkAround.ItemsSelected(varvalue2) + 1 & _
                                "' WHERE tblStringData.Autoid =" & Me.lstWorkAround .Column(4, varvalue2)
                    'Debug.Print strsql
                    CurrentDb.Execute strsql
                    
                    Next
                    End With
            Next
        End With

'Deselect everything in list0
    DeSelectAll

 

[Andrzejek]

What's the PK (Primary Key) field for this table?

[pre]
ID value Position
1 1 1
1 0 2
1 0 3
2 0 1
2 1 2
2 0 3
[/pre]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Blorf]

Maybe something like the following: It assumes your data is in order, if it's not, create a query putting it in order, save it, and replace MyTable with your query name. Also assumes your table is an access table, not s liked sql table, and based on there not being a primary key I am fairly certain that's correct.

Function DoIT()

Dim RS As Object
Dim X As Long
Dim Y As Long
Set RS = CurrentDb.Recordset("MyTable")
RS.MoveFirst
While Not RS.EOF()

If X <> RS.ID Then

X = RS.ID
Y = 1
​

End If

RS.Edit
RS.Position = Y
RS.Update
Y = Y + 1
RS.MoveNext
Wend
​

End Function

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That DonÆt.

http://www.thelegacyreturns.com/

 

[MITTENCAT]

Hi,

I'm trying to use this code and get a error here:

Set RS = CurrentDb.Recordsets("test")

Error says:
====
Run time error '3265'

Item not found in this collection

=====

Any ideas ?

 

[dhookom]

The syntax would be OpenRecordset. This assumes the table name is test.

Set RS = CurrentDb.OpenRecordset("test")

I would actually use:

Dim db As DAO.Database
Dim RS as DAO.Recordset
Set db = CurrentDb
Set RS = db.OpenRecordset("test")

Duane
Hook'D on Access
MS Access MVP

 

[Blorf]

Thank you Dhookom, Sorry about the type MittenCat.

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That DonÆt.

http://www.thelegacyreturns.com/

 

[MITTENCAT]

Thanks for this, I'm not used to coding so apologies for asking another problem I now have.

The change now gives me this error in the top line of code Function DoIT()in the following code:
-----
Compile error:

Method or data member not found

-----

 

[dhookom]

Which line is highlighted? I expect this might be a references issue. Check this link about setting references.

Duane
Hook'D on Access
MS Access MVP

 

[MITTENCAT]

Hi,

Thanks I got it sorted using this code:
------
Option Compare Database

Global GBL_Category As String
Global GBL_Icount As Long

Public Function increment(ivalue As String) As Long

If Nz(GBL_Category, "zzzzzzzz") = ivalue Then
GBL_Icount = GBL_Icount + 1

Else
GBL_Category = ivalue
GBL_Icount = 1
End If
increment = GBL_Icount
End Function