Good afternoon everybody.
I'm writing a DB, taking inspiration frm an old XLSX job I made about 10 years ago. I got stuck on a Sub where I've tried to access a recordset through a loop on some fields of a table.
I realize that this is a rookie question, but if some member would help me in this, I can only thank him/her a lot. Thank you.
Here's the code wtih comments:
Private Sub cmdCalcoloGiorniAnnuali_Click()
'------ I HAVE A TABLE FILLED FROM USERS IN WHICH TWO COLUMNS (FIELDS) CONTAIN THE STARTING AND THE ENDING
'------ DATE OF FINANCIAL COMPETENCE OF A NUMBER OF ITEMS. THE NUMBER CAN VARY FROM 20 TO 1500.
'------ MY GOAL IS TO SPLIT THIS COMPETENCE INTO THE YEARS OF ITS DURATION. EVERY YEAR ITS OWN AMOUNT, DEPENDING ON THE DURATION DAYS.
'------ TABLES ARE EXCEL FILES.
'------ I MADE THIS JOB WITH EXCEL VBA ABOUT 10 YEARS AGO; NOW I WOULD LIKE TO "TRANSLATE" IT INTO MSACCESS.
'----- DECLARATIONS
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Tf As DAO.TableDef
Dim Fld As DAO.Field
'----- SETTINGS AND OPENINGS
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("SviluppoRisconti")
Set Tf = Db.TableDefs("SviluppoRisconti")
'----- TO ACCESS FIELDS, I MUST FIND THE LOWER AND THE HIGHER NUMBER OF THE YEAR. THE DECLARATION OF VARIABLE IS FOLLOWING
'----- IMMEDIATELY LATER THE CYCLE
Dim AnnoMin As Integer 'LOWER NUMBER OF THE YEAR
Dim AnnoMax As Integer 'HIGHER NUMBER OF THE YEAR
Dim AnnoDataMinore As Integer
Dim AnnoDataMaggiore As Integer
AnnoMax = 2000 'LOWER YEAR CANNOT BE LOWER THAN 2000
AnnoMin = 2099 'HIGHER YEAR CANNOT BE HIGHER THAN 2099
Do Until Rs.EOF
AnnoDataMinore = Year(Rs!DATAIN) '"DATAIN" IS THE STARTING DATE OF THE FINANCIAL ITEM
If AnnoDataMinore < AnnoMin Then AnnoMin = AnnoDataMinore
AnnoDataMaggiore = Year(Rs!DATAFI) '"DATAFI" IS THE ENDING DATE OF THE FINANCIAL ITEM
If AnnoDataMaggiore > AnnoMax Then AnnoMax = AnnoDataMaggiore
Rs.MoveNext
Loop
'----- CALCULATION OF THE DAYS OF FINANCIAL COMPETENCE FOR EACH ITEM OF THE TABLE.
'----- DECLARATION OF VARIABLES FOLLOWS.
Dim NomeCampo As String 'FIELD NAME
Dim DIR As Date 'STARTING COMPETENCE DATE OF THE FINANCIAL ITEM
Dim FRI As Date 'ENDING COMPETENZE DATE OF THE FINANCIAL ITEM
Dim CPD As Date 'CAPODANNO (I.E. JANUARY 1ST)
Dim SSV As Date 'SAN SILVESTRO (I.E. DECEMBER 31ST)
Dim GiorniCompetenza As Integer 'RESULTING COMPETENCE DAYS
'----- HERE I LOOSE MYSELF. CANNOT ACCESS TO FIELDS NAMED WITH "G_" ("GIORNI" IN ITALIAN, DAYS)
'----- PLUS THE NUMBER OF THE YEAR. (FOR INSTANCE "G_2025"); IT WILL CONTAIN (IN THIS CASE)
'----- THE DAYS OF DURATION OF YEAR 2025 FOR THE ITEM OF THE RECORD. IT COULD RANGE FROM 0 TO 365.
'----- THE LAST ROWS OF THE PRESENT SUB ARE NOT COMPLETE AND STATEMENTS SHOULD BE IMPROVED; I WILL WHEN DEBUGGING WILL "SMILE" TO ME.
For ix = AnnoMin To AnnoMax Step 1
NomeCampo = "G_" & ix
'----- so, "NomeCampo" (i.e. FieldName) becomes "G_2016", "G_2017", G_2018" and so on. These are fields existing in the table.
'----- The following would be the code to update the recordset, but doesn't work.
For Each Fld In Tf.Fields
If Left(Fld.Name, 2) = "G_" Then
'----------
Debug.Print Fld.Name 'check OK
'----------
Do Until Rs.EOF
'----------
'Debug.Print Fld.Name 'no result. Maybe did I reverse the loops?
'----------
CPD = DateSerial(ix, 1, 1)
SSV = DateSerial(ix, 12, 31)
If DIR < CPD And FRI < CPD Then
GiorniCompetenza = 0
End If
If DIR < CPD And FRI = CPD Then
GiorniCompetenza = 1
End If
If DIR < CPD And FRI < SSV Then
GiorniCompetenza = DateDiff("d", DIR, FRI)
End If
If DIR < CPD And FRI > SSV Then
GiorniCompetenza = DateDiff("d", DIR, SSV)
End If
If DIR = CPD And FRI = CPD Then
GiorniCompetenza = 1
End If
If DIR = CPD And FRI < SSV Then
GiorniCompetenza = DateDiff("d", DIR, FRI)
End If
If DIR = CPD And FRI > SSV Then
GiorniCompetenza = DateDiff("d", DIR, SSV)
End If
If DIR > CPD And FRI < SSV Then
GiorniCompetenza = DateDiff("d", DIR, FRI)
End If
If DIR > CPD And FRI > SSV Then
GiorniCompetenza = DateDiff("d", DIR, SSV)
End If
If DIR = SSV Then
GiorniCompetenza = 1
End If
If DIR > SSV Then
GiorniCompetenza = 0
End If
'Rs.Edit
'----------
'Debug.Print CPD, SSV, DIR, FRI, GiorniCompetenza 'no result. Where's my mistake?
'----------
'Rs.Fields("NomeCampo") = GiorniCompetenza
'Rs!NomeCampo = GiorniCompetenza
'Rs.Update
Rs.MoveNext
Loop
End If
Next
Next ix
Rs.Close
Db.Close
MsgBox "Got the bottom."
End Sub
Private Sub cmdChiudiForm_Click()
DoCmd.Close
End Sub
I'm writing a DB, taking inspiration frm an old XLSX job I made about 10 years ago. I got stuck on a Sub where I've tried to access a recordset through a loop on some fields of a table.
I realize that this is a rookie question, but if some member would help me in this, I can only thank him/her a lot. Thank you.
Here's the code wtih comments:
Private Sub cmdCalcoloGiorniAnnuali_Click()
'------ I HAVE A TABLE FILLED FROM USERS IN WHICH TWO COLUMNS (FIELDS) CONTAIN THE STARTING AND THE ENDING
'------ DATE OF FINANCIAL COMPETENCE OF A NUMBER OF ITEMS. THE NUMBER CAN VARY FROM 20 TO 1500.
'------ MY GOAL IS TO SPLIT THIS COMPETENCE INTO THE YEARS OF ITS DURATION. EVERY YEAR ITS OWN AMOUNT, DEPENDING ON THE DURATION DAYS.
'------ TABLES ARE EXCEL FILES.
'------ I MADE THIS JOB WITH EXCEL VBA ABOUT 10 YEARS AGO; NOW I WOULD LIKE TO "TRANSLATE" IT INTO MSACCESS.
'----- DECLARATIONS
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Tf As DAO.TableDef
Dim Fld As DAO.Field
'----- SETTINGS AND OPENINGS
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("SviluppoRisconti")
Set Tf = Db.TableDefs("SviluppoRisconti")
'----- TO ACCESS FIELDS, I MUST FIND THE LOWER AND THE HIGHER NUMBER OF THE YEAR. THE DECLARATION OF VARIABLE IS FOLLOWING
'----- IMMEDIATELY LATER THE CYCLE
Dim AnnoMin As Integer 'LOWER NUMBER OF THE YEAR
Dim AnnoMax As Integer 'HIGHER NUMBER OF THE YEAR
Dim AnnoDataMinore As Integer
Dim AnnoDataMaggiore As Integer
AnnoMax = 2000 'LOWER YEAR CANNOT BE LOWER THAN 2000
AnnoMin = 2099 'HIGHER YEAR CANNOT BE HIGHER THAN 2099
Do Until Rs.EOF
AnnoDataMinore = Year(Rs!DATAIN) '"DATAIN" IS THE STARTING DATE OF THE FINANCIAL ITEM
If AnnoDataMinore < AnnoMin Then AnnoMin = AnnoDataMinore
AnnoDataMaggiore = Year(Rs!DATAFI) '"DATAFI" IS THE ENDING DATE OF THE FINANCIAL ITEM
If AnnoDataMaggiore > AnnoMax Then AnnoMax = AnnoDataMaggiore
Rs.MoveNext
Loop
'----- CALCULATION OF THE DAYS OF FINANCIAL COMPETENCE FOR EACH ITEM OF THE TABLE.
'----- DECLARATION OF VARIABLES FOLLOWS.
Dim NomeCampo As String 'FIELD NAME
Dim DIR As Date 'STARTING COMPETENCE DATE OF THE FINANCIAL ITEM
Dim FRI As Date 'ENDING COMPETENZE DATE OF THE FINANCIAL ITEM
Dim CPD As Date 'CAPODANNO (I.E. JANUARY 1ST)
Dim SSV As Date 'SAN SILVESTRO (I.E. DECEMBER 31ST)
Dim GiorniCompetenza As Integer 'RESULTING COMPETENCE DAYS
'----- HERE I LOOSE MYSELF. CANNOT ACCESS TO FIELDS NAMED WITH "G_" ("GIORNI" IN ITALIAN, DAYS)
'----- PLUS THE NUMBER OF THE YEAR. (FOR INSTANCE "G_2025"); IT WILL CONTAIN (IN THIS CASE)
'----- THE DAYS OF DURATION OF YEAR 2025 FOR THE ITEM OF THE RECORD. IT COULD RANGE FROM 0 TO 365.
'----- THE LAST ROWS OF THE PRESENT SUB ARE NOT COMPLETE AND STATEMENTS SHOULD BE IMPROVED; I WILL WHEN DEBUGGING WILL "SMILE" TO ME.
For ix = AnnoMin To AnnoMax Step 1
NomeCampo = "G_" & ix
'----- so, "NomeCampo" (i.e. FieldName) becomes "G_2016", "G_2017", G_2018" and so on. These are fields existing in the table.
'----- The following would be the code to update the recordset, but doesn't work.
For Each Fld In Tf.Fields
If Left(Fld.Name, 2) = "G_" Then
'----------
Debug.Print Fld.Name 'check OK
'----------
Do Until Rs.EOF
'----------
'Debug.Print Fld.Name 'no result. Maybe did I reverse the loops?
'----------
CPD = DateSerial(ix, 1, 1)
SSV = DateSerial(ix, 12, 31)
If DIR < CPD And FRI < CPD Then
GiorniCompetenza = 0
End If
If DIR < CPD And FRI = CPD Then
GiorniCompetenza = 1
End If
If DIR < CPD And FRI < SSV Then
GiorniCompetenza = DateDiff("d", DIR, FRI)
End If
If DIR < CPD And FRI > SSV Then
GiorniCompetenza = DateDiff("d", DIR, SSV)
End If
If DIR = CPD And FRI = CPD Then
GiorniCompetenza = 1
End If
If DIR = CPD And FRI < SSV Then
GiorniCompetenza = DateDiff("d", DIR, FRI)
End If
If DIR = CPD And FRI > SSV Then
GiorniCompetenza = DateDiff("d", DIR, SSV)
End If
If DIR > CPD And FRI < SSV Then
GiorniCompetenza = DateDiff("d", DIR, FRI)
End If
If DIR > CPD And FRI > SSV Then
GiorniCompetenza = DateDiff("d", DIR, SSV)
End If
If DIR = SSV Then
GiorniCompetenza = 1
End If
If DIR > SSV Then
GiorniCompetenza = 0
End If
'Rs.Edit
'----------
'Debug.Print CPD, SSV, DIR, FRI, GiorniCompetenza 'no result. Where's my mistake?
'----------
'Rs.Fields("NomeCampo") = GiorniCompetenza
'Rs!NomeCampo = GiorniCompetenza
'Rs.Update
Rs.MoveNext
Loop
End If
Next
Next ix
Rs.Close
Db.Close
MsgBox "Got the bottom."
End Sub
Private Sub cmdChiudiForm_Click()
DoCmd.Close
End Sub
