Create folders dynamically...

[raghuprabhu]

Hi All,

I have attached a small database. There are three tables “tblFYear” , “tblPayPeriodAndDates” and two forms “frmMain” and “frmPayPeriodAndDates”.

The data source for frmMain is the “qryPayPeriodAndDates”. This shows the next pay day is 04-Aug-2011 and we are in pay period 03/2012

Question 1. How do I dynamically change the tick box in "frmPayPeriodAndDates" from 04-Aug-2011 to 18-Aug-2011 after 04-Aug-2011 when the database is opened?

Question 2. According to the table “tblFYear “ we are in financial year 2012 ie today lies between 01-Jul-2011 and 30-Jun-2012.

When the frmMain loads, it checks to see if a folder “Reports” is there. If not it creates new folder “Reports”.

Then it checks to see if the folder FYear2012 is there. If not it creates a folder “FYear2012”. How do I dynamically make the folder depending on the financial year?

After 30-Jun-2012 I want it to create folder “FYear2013”

Similarly we are in pay period 03/2012 according to the table “tblPayPeriodAndDates”. It has to create folder “PP0312” After 04-Aug-2011 it must create folder “PP0412”. How do I dynamically create the folders?

Thanks for your help in advance.

Raghu Prabhu
Melbourne, Oz

 

[Taruz]

Hi Raghu..


1.

this, to form frmPayPeriodAndDates..:

Private Sub Form_Open(Cancel As Integer)

CurrentDb.Execute "update tblPayPeriodAndDates set HaveActioned=0"

CurrentDb.Execute "update tblPayPeriodAndDates set HaveActioned = -1 " _
                & " where table2ID in (select top 1 table2ID " _
                & " from tblPayPeriodAndDates where PayDay>Date() )"
End Sub

2.

For this the main form..:

Private Sub Form_Load()

Dim activeDir As String
Dim FY, PP As String

FY = "\FYear" & Right(PayPeriod, 4)
PP = "\PP" & Left(PayPeriod, 2) & _
             Right(PayPeriod, 2)


activeDir = CurrentProject.Path
   
    If Dir(activeDir & "\Reports", vbDirectory) = "" Then
        MkDir activeDir & "\Reports"
    End If
    
activeDir = CurrentProject.Path & "\Reports"


    If Dir(activeDir & FY, vbDirectory) = "" Then
        MkDir activeDir & FY
    End If
    
activeDir = CurrentProject.Path & "\Reports" & FY
    If Dir(activeDir & PP, vbDirectory) = "" Then
        MkDir activeDir & PP
    End If
    
End Sub