Hi all,
Would appreciate some help with VBA; I cannot seem to get my head around this logic. I have a vehicle scheduling application which works well; the user inputs start & end dates, and the interval that the schedule should take place. A schedule is generated and the resulting 'service dates' are then written to the Dates table. So far so good.
I have added 5 fields (all yes/no) to these records, and, depending on certain criteria elsewhere in the database set by the user, 2 of these fields are marked as true or false. Again, so far so good.
I now want to be able to control the 3 remaining fields by additional criteria that the user would input:
The first is the titles of the fields - which I would do by using parameter fields.
The second is to allow users to be able to determine whether they wish to have the field(s) marked as 'True' when a Vehicle Schedule is generated depending on the interval indicated, using the following 2 criteria:
- Whether they wish to have the field marked as true on every service date, on alternate dates or every 3rd date etc and
- When they wish that sequence to start – on the first service date generated, or the second etc.
I believe that the best way to control the above would be to have 2 further control fields for each of the 3 yes/no fields - each accepting positive integers (say 1 to 5) and then build these numbers into the generation process. That's the bit that's causing a headache! Is there a way of building something into a loop process that misses every other, or every third etc?
The code for the existing generation is included below; any help would be much appreciated:
Would appreciate some help with VBA; I cannot seem to get my head around this logic. I have a vehicle scheduling application which works well; the user inputs start & end dates, and the interval that the schedule should take place. A schedule is generated and the resulting 'service dates' are then written to the Dates table. So far so good.
I have added 5 fields (all yes/no) to these records, and, depending on certain criteria elsewhere in the database set by the user, 2 of these fields are marked as true or false. Again, so far so good.
I now want to be able to control the 3 remaining fields by additional criteria that the user would input:
The first is the titles of the fields - which I would do by using parameter fields.
The second is to allow users to be able to determine whether they wish to have the field(s) marked as 'True' when a Vehicle Schedule is generated depending on the interval indicated, using the following 2 criteria:
- Whether they wish to have the field marked as true on every service date, on alternate dates or every 3rd date etc and
- When they wish that sequence to start – on the first service date generated, or the second etc.
I believe that the best way to control the above would be to have 2 further control fields for each of the 3 yes/no fields - each accepting positive integers (say 1 to 5) and then build these numbers into the generation process. That's the bit that's causing a headache! Is there a way of building something into a loop process that misses every other, or every third etc?
The code for the existing generation is included below; any help would be much appreciated:
Code:
Sub Generate()
''''''''''''''''''''''''''''''
' GENERATION PROCESS
''''''''''''''''''''''''''''''
''''''''''''''
' Defines Variables for Generation
''''''''''''''
Dim Date1 As Date, Date2 As Date
Dim Numb As Integer
Dim dbs As Database
Dim rstDates As Recordset
Set dbs = CurrentDb
Set rstDates = dbs.OpenRecordset("Dates")
Numb = (Me![InspectionInterval]) * 7
Date1 = (Me![FirstInspection]) - ((Me![InspectionInterval]) * 7)
Date2 = (Me![LastInspectionDate])
'''''''''''''''''''''''''''''''''''''''''''''
' Generates the Schedule
'''''''''''''''''''''''''''''''''''''''''''''
Generate:
With rstDates
Do
Date1 = Date1 + Numb
.AddNew
!ServiceDate = Date1
!ServiceMonth = !ServiceDate
!VehicleID = (Me![VehicleID])
!VehicleGroup = (Me![VehicleGroupId])
!Service = True
!PMI = True
If Me![VehicleGroupText] = "Trailer Unit" Then
!Trailerservice = True
!PMI = False
End If
.Update
Loop Until Date1 > (Date2 - Numb)
End With
MsgBox "A Schedule has been generated" & Chr(10) & Chr(10) & "for this Vehicle"
End Sub
