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
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
Set rstClone = CurrentDb.OpenRecordset("SELECT Region, CIN, ID, Rate FROM tbl_participant_services " & _
"ORDER BY Region, CIN, ID, Rate;").Clone
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