increment or auto number

[mothi]

Hi , I got a table with all the records in it. I have four fields specifically, Region (which stores numbers n like 01, 02, 03) and ID( format is XX00000X) , Id ( which has values starting from 1) and rate ( vlaue such as 9223,9224,9225). i need a counter based on the above specified fields.

Region CIN ID Rate Counter


01 XX12345X 1 9222 1

01 XX12345X 1 9223 2

01 XX12345X 2 9222 1

01 XX12345X 2 9223 2

So as you have seen in the above table as soon as ID changed keeping all the other fields the same . the counter has to start from 1 again. I Do not know how to do that please help me as soon as possible

 

[MStef]

Hello mothi!
Look a "DemoRateCounterA2002.mdb" (attachment, zip).
It can be done on next way:
1)Put an Index on Rate field(Table1), Sort Order Ascending.
2)Make a Module "GetCounter", (look at module1).
3)Make an UPDATE query, (Look at Query1UPD).
4)Make a Form with command button to Run a query.
Look at Form1, look at VBA.
5) Open Form and click on command button.
6) Look at Table. Now you can remove Index if you want.

 

[mothi]

Thanks a lot for responding in such short time . I need one more help which is a bit complicated. I need the same thing to be incremented but now based on four fields actually ( Region, CIN, ID , Rate) .See in the bolded data, with region and cin changing , the increment should be unique . because the table has got only one row of that kind (in a much more simpler way , the combination of fields should be unique)



Region CIN ID Rate Counter


01 XX12345X 1 9222 1

01 XX12345X 1 9223 2

02 XX12345X 1 9222 1

02 XX12345X 2 9223 2

02 XX12346X 2 9222 1

03 XX12345X 2 9223 1

 

[vbaInet]

It would be a matter of concatenating all three fields. The ampersand (&) operator does this. An example (based on your spec):

=[Region] & " " [CIN_ID] & " " & [RateCounter]

I don't know how MStef's program performs the rate counter (i.e. not had a look ), so I should ask if it restricts the generated numbers to a certain length?

 

[mothi]

Hi Rate and COunter are both separate fields . I need the counter field to be incremented based on CIN, Region,ID and Rate.

 

[vbaInet]

Increment based on CIN, Region, ID and Rate? Please explain.

 

[mothi]

In the below provided table, Counter will contain nothing. I need to populate that field with incremental number by using code.

In the First 2 rows, there is change in the rate column but keeping all the fields same. So For the Region =01 with CIN=XX12345X , ID =1 has two records 9222,9223 . so I need to populate 1 and 2 values in the counter field.

When you come to the third and fourth row , this time there is a change in region “02” and all the other records are same compared top 2 rows so the counter again has to start from 1 because all the data belongs to different region.

When you come to 5th row, there is a change in the CIN “ XX121346X” so the counter again has to start from 1 . But if you observe the 6th row now there is a change in ID field 1 compared to 5th row so the counter has to be 1 . I am sorry if iam confusing you. But please help me


Region CIN ID Rate Counter


01 XX12345X 1 9222 1

01 XX12345X 1 9223 2

02 XX12345X 1 9222 1

02 XX12345X 2 9223 2

02 XX12346X 2 9222 1

02 XX12346X 1 9222 1

03 XX12345X 2 9223 1

 

[vbaInet]

Are you referring to updating existing data or for new records?

 

[mothi]

Existing records in the table.

 

[vbaInet]

Here you go:

Dim yRow As Long, rstClone As Recordset
    Dim oldRegion As String, oldCIN As String, oldID As Long, tempCounter As Long
    
    Set rstClone = CurrentDb.OpenRecordset("[COLOR=Blue]NameOfTable[/COLOR]").Clone

    With rstClone
        If .BOF = False Then
            If MsgBox("Backup your table before continuing" & vbCrLf & "Continue?", vbYesNo) = vbYes Then
                ' Fill the recordset
                .MoveLast
                .MoveFirst
                For yRow = 0 To .RecordCount - 1
                    ' Reset tempCounter to 1 if the conditions are not met. Else tempCounter will continue to add 1
                    If (!Region & "") <> oldRegion And (!CIN & "") <> oldCIN And (!ID) <> oldID Then
                        tempCounter = 1
                    End If
                    
                    ' Update the counter field
                    .Edit
                    !counter = tempCounter
                    .Update
                    
                    ' Save the values for checking against the next record
                    oldRegion = !Region
                    oldCIN = !CIN
                    oldID = !ID
                    
                    ' Increment tempCounter
                    tempCounter = tempCounter + 1
                    .MoveNext
                Next
                MsgBox "Finished updating."
            End If
        Else
            MsgBox "There are no records"
        End If
    End With

I've not tested this code. Ensure that you test it on a backup of your table. Change the text in blue to the correct text. From the looks of your Region field, since it is prefixed with a '0' I assumed it's saved as a string because numbers don't behave like that. Let me know if there any problems.

 

[mothi]

HI VbaInet .
Thank YOu first of all for sending it. It is working partly. The code worked fine and it is working based on the region actually. Like in this case , the region is 01 for all the three records, ID is also 1 and CIN is also the same


Condition worked with the code you have sent

Region CIN ID Rate Counter


01 XX12345X 1 9222 1

01 XX12345X 1 9223 2

01 XX12345X 1 9224 3



Main Condition is the COMbination of ( Region CIN ID Rate) is Primary Key.


Other conditions which should work are

If you take below example , the first two records works with the code you have sent because the only thing that changes is the rate and counter is getting incremented for those 2 records . But for the third record, the ID changed so the counter has to start from 1 again for that record.

4th Record. The CIN has changed and the region is also changed , the counter starts at 1 again

5 th Record . Only CIN has changed, the counter should start at 1 again

Region CIN ID Rate Counter ( correct values


01 XX12345X 1 9222 1

01 XX12345X 1 9223 2

01 XX12345X 2 9224 1


02 XX12346X 1 9225 1

02 XX12347X 1 9225 1

 

[mothi]

when i changed "and" to "or" . the counter is storing all 1's in it.

 

[vbaInet]

I will adjust the code and resend it.

 

[vbaInet]

Rate is definitely a number? And was I correct to assume that Region is a string?

 

[vbaInet]

Amended:

    Dim yRow As Long, rstClone As Recordset
    Dim oldRegion As String, oldCIN As String, oldID As Long, oldRate As Long, tempCounter As Long
    
    Set rstClone = CurrentDb.OpenRecordset("NameOfTable").Clone
    tempCounter = 1
    
    With rstClone
        If .BOF = False Then
            If MsgBox("Backup your table before continuing" & vbCrLf & "Continue?", vbYesNo) = vbYes Then
                ' Fill the recordset
                .MoveLast
                .MoveFirst
                For yRow = 0 To .RecordCount - 1
                    ' Reset tempCounter to 1 if the conditions are not met. Else tempCounter will continue to add 1
                    If ((!Region & "") = oldRegion And (!CIN & "") = oldCIN And (!ID) = oldID And (!Rate) = oldRate) = False Then
                        tempCounter = 1
                    End If
                    
                    ' Update the counter field
                    .Edit
                    !counter = tempCounter
                    .Update
                    
                    ' Save the values for checking against the next record
                    oldRegion = !Region
                    oldCIN = !CIN
                    oldID = !ID
                    oldRate = !Rate
                    
                    ' Increment tempCounter
                    tempCounter = tempCounter + 1
                    .MoveNext
                Next
                MsgBox "Finished updating."
            End If
        Else
            MsgBox "There are no records"
        End If
    End With

Untested. Try it anyway.

 

[mothi]

Yes you are right rate is a number and region is a string . i forgot to make you awarre of that . apologies.

 

[mothi]

Hey VBAinet. One more thing is that i will be leaving now but i will be online in 2hours. So Would you be available then ?

 

[vbaInet]

If the forces of nature does not pull me into bed, then I will be available I'm up till late most nights anyway.

 

[mothi]

i will see you then . Thanks alot besides whether the code is gonna work or not . Genuinely , thanks

 

[vbaInet]

You're welcome. Just post back after testing the code.