INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

add sequential number into a field for each group of records

add sequential number into a field for each group of records

(OP)
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

RE: add sequential number into a field for each group of records

(OP)
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

CODE -->

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 

RE: add sequential number into a field for each group of records

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

ID      value    Position 
 1        1         1
 1        0         2
 1        0         3
 2        0         1
 2        1         2
 2        0         3
 

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.

RE: add sequential number into a field for each group of records

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/

RE: add sequential number into a field for each group of records

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 ?

RE: add sequential number into a field for each group of records

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

CODE --> vba

Set RS = CurrentDb.OpenRecordset("test") 

I would actually use:

CODE --> vba

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

RE: add sequential number into a field for each group of records

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/

RE: add sequential number into a field for each group of records

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

-----

RE: add sequential number into a field for each group of records

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close