increment or auto number

I'm having to look at your logic again. I thought we had it.

So are you saying you can't have the same CIN's in the same Region?
Which other two fields cannot be identical in the same region?
 


Reg CIN ID Rate Counter


01 XX12345X 1 9222 1

01 XX12345X 1 9223 2

01 XX12346X 1 9221 3


3 should be 1 but your code criteria looks at region
 
What you've just written now, doesn't comply with what you wrote in post #7:

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

Shouldn't the third line be:

01 XX12346X 1 9221 1 ?
 
yes it should be

i wrote that line because the code you have sent to me increments to 3 instead of starting at 1 because the code is looking at the region, region is same for previous record as well.
 
the CIN , and rate are the strings. everything else is a number .
 
It has to look at Region because it changes value, and when it changes it should default to 1. I believe you also confirmed that Region is also a number?
 
Yes when the region changes , the counter is starting at 1 but as i said if two CINs lets take AA12345A and AA12346A belong to the same region . the counter starts at 1 just one time where it should start at 1 twice because of different CINs. Region is of Number data type
 
I don't see how Range can be a number if it has a leading '0'? Access removes leading zeros for number data types.
 
For simplicity, I will treat everything as string. Use this code:

Code:
    Dim yRow As Long, rstClone As Recordset
    Dim oldRegion As String, oldCIN As String, oldID As String, oldRate As String
    
    Set rstClone = CurrentDb.OpenRecordset("[COLOR=Red]NameOfTable[/COLOR]").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
                    If CStr(!Region) = oldRegion Then
                        If (CStr(!CIN) & "") = oldCIN Then
                            If CStr(!ID) = oldID Then
                                If (CStr(!Rate) & "") = oldRate Then
                                    tempCounter = 1
                                End If
                            Else
                                tempCounter = 1
                            End If
                        Else
                            tempCounter = 1
                        End If
                    Else
                        tempCounter = 1
                    End If
                    
                    ' Update the counter field
                    .Edit
                    !counter = tempCounter
                    .Update
                    
                    ' Save the values for checking against the next record
                    oldRegion = CStr(!Region)
                    oldCIN = CStr(!CIN)
                    oldID = CStr(!ID)
                    oldRate = CStr(!Rate)
                    
                    ' Increment tempCounter
                    tempCounter = tempCounter + 1
                    
                    .MoveNext
                Next
                MsgBox "Finished updating."
            End If
        Else
            MsgBox "There are no records"
        End If
    End With
    rstClone.Close
    Set rstClone = Nothing
 
Last edited:
HI , I modified the code a little bit, i am considering start date also in the criteria.

As i said if there are 2 duplicate records in the table . The counter should be incremented .

In the example . the bolded records duplicate but the counter has to be incremented so , i pasted the code as well. please take a look at it.


Example

Reg CIn Id Rate Counter start date

01 AA12345A 1 9221 1 01/01/2010

01 AA12345A 1 9221 2 01/01/2010

01 AA12345A 1 9222 1 01/01/2010


The COde is , iadded oldDate as date in the declarations

For yRow = 0 To .RecordCount - 1
If ! region = oldRegion Then
If (CStr(!cin) & "") = oldCIN Then
If !id = oldID Then
If ! rate = oldRate And !start_date =oldDate Then

tempCounter = tempCounter ' change in the code assigned tempcounter = tempcounter instead of 1

End If
Else
tempCounter = 1
End If
Else
tempCounter = 1
End If
Else
tempCounter = 1
End If
 
Using a date wouldn't be necessary. If you're getting duplicate records during the update then it must mean that the data isn't sorted correctly, so lets explicitly declare the order in which we would like the columns to appear. Use this:

Code:
Set rstClone = CurrentDb.OpenRecordset("SELECT Region, CIN, ID, Rate FROM tbl_participant_services " & _
                                            "ORDER BY Region, CIN, ID, Rate;").Clone
 
HI VBanet, there is nothing wrong with the data, there are duplicate records in the table. I need to increment them up so thats why i am using the date as another criteria. Do you think the code is fine .
 
I should have been more precise, I was referring to the records within the recordset, not your table. Insert that new code into the original solution and post back with the outcome. Your amendment wouldn't produce the right results and like I previously stated, it's not necessary.
 
Hi VBA, THank YOu. I think i annoyed you. Sorry for that.

A quickie , i need to disable the special keys like f11 depending up on the user login. Do you know how to do that programmatically in vba
 
Hi VBA, THank YOu. I think i annoyed you. Sorry for that.

A quickie , i need to disable the special keys like f11 depending up on the user login. Do you know how to do that programmatically in vba

Haha! Not at all mothi. Since this isn't a related topic could you please create a new thread (for the benefit of others) and I'm sure you will get a response.
 

Users who are viewing this thread

Back
Top Bottom