VB Code Schedule (1 Viewer)

[looma123]

Hi,

I have a form called New Project having 3 fields
- StartDate
- EndDate
- Visitno

And Subform called dbo_Job_new projects_Add having 3 fields
- PPM #
- PPM Date
- Visit Type

I require a code to add to a button once clicked the system will chck the startdate and enddate and visitno and add new schedule of visits in the sub form. e.g.:
startdate: 01/01/2023
enddate: 31/1/2023
visitno : 4

system will generate the following rows :

ppm # ppm Date Visit Type
1 02/01/2023 SA
2 02/04/2023 Q
3 02/07/2023 Q
4 02/10/2023 Q

note there is 3 types of visits
1. SA (System acceptance, always the first visit)
2. Q (Quarterly, every 3 months)
3. M (Monthly, used if the no of visits 12, then it should be SA-Q-M-M-Q-M-M-Q-M-M-Q-M)

 

[Gasman]

Please show what you have tried?
I am assuming you have at least made an attempt.?

Look at a loop using DateAdd() to increment the dates. Use either an append query or a recordset.
Look at the Mod() function to decide what type visit you need to enter.

 

[looma123]

Please show what you have tried?
I am assuming you have at least made an attempt.?

Look at a loop using DateAdd() to increment the dates. Use either an append query or a recordset.
Look at the Mod() function to decide what type visit you need to enter.

i tried the below as first step but not working

Dim StartDate As Date
Dim EndDate As Date
Dim Visitnumber As Integer
Dim i As Integer
StartDate = Me.Text119.Value
EndDate = Me.Text123.Value
Visitnumber = Me.Visitnumber.Value
For i = 0 To Visitnumber - 1
Me.PPMlatestdate_subform.Form![PPM Date].AddItem Format(DateAdd("m", i * 3, StartDate), "Short Date")
Next i

 

[Gasman]

Well no.
You need to add actual records for the subform and refresh the subform.
I expect you could also do it with the recordset for thesubform, but not something I have ever tried, or even thought of until seeing your code.
You have alsonot taken into account the different periods.

 

[MajP]

Public Sub FillSched(StartDate As Date, EndDate As Date, VisitNo As Integer)
  Dim NextDate As Date
  Dim strDate As String
  Dim strSql As String
  Dim strType As String
  Dim PPM_No As Integer

  Const Quarterly = 4
  Const Monthly = 12

  If IsDate(StartDate) And IsDate(EndDate) And EndDate > StartDate Then
    NextDate = StartDate + 1
    PPM_No = 1
    'must use this format regardless of regional settings
    strDate = "#" & Format(NextDate, "MM/DD/YYYY") & "#"
    strType = "'SA'"
    strSql = "Insert into tblPPM (PPM_No, PPM_Date, VisitType) Values (" & PPM_No & "," & strDate & ", " & strType & ")"
    CurrentDb.Execute strSql
    NextDate = DateSerial(Year(NextDate), Month(NextDate) + 1, Day(NextDate))
     strDate = "#" & Format(NextDate, "MM/DD/YYYY") & "#"
    Do Until NextDate > EndDate
     PPM_No = PPM_No + 1
     If (PPM_No + 1) Mod 3 = 0 Then
       MsgBox "He"
       strType = "'Q'"
       strSql = "Insert into tblPPM (PPM_No, PPM_Date, VisitType) Values (" & PPM_No & "," & strDate & ", " & strType & ")"
       CurrentDb.Execute strSql
     Else
       If visitNo = Monthly Then
          strType = "'M'"
          strSql = "Insert into tblPPM (PPM_No, PPM_Date, VisitType) Values (" & PPM_No & "," & strDate & ", " & strType & ")"
          CurrentDb.Execute strSql
       End If
     End If
    NextDate = DateSerial(Year(NextDate), Month(NextDate) + 1, Day(NextDate))
    Loop
  End If
End Sub