increment or auto number

[mothi]

Hi VBAinet. it is storing all 1's in the table .

 

[vbaInet]

Zip, upload and attach your db and I'll take a look.

 

[mothi]

Hi i cannot do that because it contains all the confidential information . But i will try to figure out the code. so give me some time. if i can't , then i will come back to you.

 

[mothi]

Hi VBA Inet, I guess the increment part is not working.i guess because the increment code comes after updating the field in the code.

it is storing 1's becasue of

rs.edit
!counter = tempCounter
.update

but the increment code is not working

tempCounter = tempCounter+1

 

[vbaInet]

Looking at your table of values it seems that there should be more than one step. I think you need to explain the logic in more detail. My code only does one check on ALL fields.

 

[vbaInet]

Actually, you won't need to. I think I understand your logic. There must be at least 3 unique values for the counter to be incremented. I'm working on something now.

 

[mothi]

OK. i need the counter field to increment based on four fields (Region,CIN, ID, Rate) which are primary keys .
Region = number data type
CIn=text
ID=NUmber
Rate=NUmber

Criteria:
1)So the lets take CIN(AA12345A)belongs to region 1 with ID 1 and rate 9221. the counter should store 1 in the table for that record.

2) if the another record has the same CIN(AA12345A) , region(1), id(1) but the rate is changed to 9222. so the counter has to store 2.

3) another record has the Same CIN(AA12345A) , but region is(2) , id is 1 again and the rate is 9221. the counter should start at 1 because CIN belongs to another region (2).

4) the CIN is different (AA12346A) , region is (1), ID(1) and rate is 9221. the counter has to start at 1 because the CIN is different.

 

[mothi]

HI VBA inet, actually in my reply under criteria first 2 points are working . but when either CIN or region or plan changes it is not working

 

[vbaInet]

Basically, this is how I understand it. What you're saying is essentially this:

1. Range is the most unique key.
2. For every change in range, regardless of the other values, the counter must be 1.
3. If the range of the current record is the same as the previous record then, if there are 2 or more unique values then it must increase counter. Else it would be 1.

Right path?

 

[mothi]

Exactly......

 

[vbaInet]

Phew mothi lol. Right, let's try this:

    Dim yRow As Long, rstClone As Recordset
    Dim oldRegion As String, oldCIN As String, oldID As Long, oldRate As Long, tempCounter As Long, changeCount As Long
    
    Set rstClone = CurrentDb.OpenRecordset("NameOfTable").Clone
    tempCounter = 1
    changeCount = 0     ' Keeps a track of how many fields are unique. If there at least then increment counter, else set counter to 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 ((!Region & "") = oldRegion) Then
                        changeCount = IIf((!CIN & "") = oldCIN, changeCount + 1)
                        changeCount = IIf(!ID = oldID, changeCount + 1)
                        changeCount = IIf(!Rate = oldRate, changeCount + 1)
                        If changeCount < 2 Then
                            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 = !Region
                    oldCIN = !CIN
                    oldID = !ID
                    oldRate = !Rate
                    
                    ' Increment tempCounter
                    tempCounter = tempCounter + 1
                    
                    ' Reset changeCount
                    changeCount = 0
                    
                    .MoveNext
                Next
                MsgBox "Finished updating."
            End If
        Else
            MsgBox "There are no records"
        End If
    End With

By the way, for this to work properly, your fields must be sorted in the order that showed. That is:

Region -> CIN -> ID -> Rate

 

[vbaInet]

I just amended one line, so copy and paste it all again.

 

[vbaInet]

I always forget to close the recordset, bad habit Add this line at the very end of the code:

rstClone.Close

 

[mothi]

I guess this is working


Dim yRow As Long, rstClone As Recordset
Dim oldRegion As Long, oldCIN As String, oldID As Long, oldRate As Long, tempCounter As Long

Set rstClone = CurrentDb.OpenRecordset("Table").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 Or (!cin & "") = oldCIN Or (!id) = oldID Or (!rate) = oldRate) = False Then
tempCounter = 1
End If

If ((!cin & "") = oldCIN) = False Then
tempCounter = 1
End If

If ((!id & "") = oldID) = 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
.Edit
tempCounter = tempCounter + 1
.MoveNext
Next
' MsgBox "Finished updating."
' End If
' Else
' MsgBox "There are no records"
End If
End With

 

[vbaInet]

I think if you look carefully at what it's done you will find errors. You wouldn't normally use OR gates and then equate it to FALSE. True or False, False or True, or True Or True will always equate True, so that means your first check will never suffice unless you have False Or False.

Also using & "" with !ID is irrelevant. You only use that with strings or controls.

What did my last code produce?

 

[mothi]

wait for like 10 mins

 

[vbaInet]

replace this bit too:

                        changeCount = IIf((!CIN & "") = oldCIN, changeCount + 1, 1)
                        changeCount = IIf(!ID = oldID, changeCount + 1, changeCount)
                        changeCount = IIf(!Rate = oldRate, changeCount + 1, changeCount)

 

[mothi]

pointing at IIF statements

its saying argument optional compile error

 

[vbaInet]

Did you see my last post re amending those lines? You probably didn't because you posted a minute after. I noticed after I had a second look.

 

[mothi]

Hi VBAINET, its not gonna work because you are looking at the region . if the region is not equal to old region u r setting counter 1 but what if 2 CINs belong to the same region , So the counter does not start 1 for both of them , it just increments by 1 .