Private Sub Form_Open(Cancel As Integer)
Dim strUser As String
Dim strSQL As String
Dim MonthFile As String, strDate As String, intMonth As Integer
Dim MonthVal(6) As Integer, y As Integer
Dim MonthString(6) As String
Dim qd As QueryDef, db As Database
Dim rs As ADODB.Recordset
 
Dim sqlDel As String
 
frmSubform.Form.RecordSource = ""
sqlDel = "DROP TABLE tblProjectionsTemp"
DoCmd.RunSQL sqlDel
 
Set db = CurrentDb
Set qd = db.QueryDefs("qryProjections")
 
MonthFile = "H:\Database\Monthstart.txt"
 
Open MonthFile For Input As #1
Input #1, strDate
Close #1
 
intMonth = Month(DateValue(strDate) - 10)
 
For y = 0 To 6 Step 1
  MonthVal(y) = intMonth + y
  MonthString(y) = Left(MonthName(MonthVal(y)), 3)
Next y
 
strUser = Environ("Username")
Me.Caption = strUser & ": Six Month Projections"
 
qd.Sql = "SELECT tblProjections.[Job Number], tblFullJoblist.[Job Name], tblProjections.[Last Month] AS " & MonthString(0) & ", tblProjections.[Current Month] AS " & MonthString(1) & ", tblProjections.Month2 AS " & MonthString(2) & ", tblProjections.Month3 AS " & MonthString(3) & ", tblProjections.Month4 AS " & MonthString(4) & ", tblProjections.Month5 AS " & MonthString(5) & ", tblProjections.Month6 AS " & MonthString(6) & ", tblProjections.employee INTO tblProjectionsTemp " _
         & "FROM tblProjections INNER JOIN tblFullJoblist ON tblProjections.[Job Number] = tblFullJoblist.[Job Number] " _
         & "WHERE (((tblProjections.employee)='" & strUser & "'));"
 
qd.Execute
 
frmSubform.SourceObject = "Table.tblProjectionsTemp"
frmSubform.Locked = False
 
With frmSubform.Form
  .AllowAdditions = False
  .AllowDeletions = True
  ![Job Name].Locked = True
  ![Job Name].ColumnWidth = 3.248 * 1440
  ![Job Number].Locked = True
End With
 
 
End Sub