Attempting to add reformatted data to a subform

[rem5]

Option Compare Database

This pulls data from tables and attempts to reformat it to display mor
like a spreadsheet in the subform of this main form. The linked field
pass the name of the project for which the data needs to be displayed.
I've been able to create the table with the data, but I can't find a wa
to have the subform display the data from the new table.

Thanks in advance.


Private Sub Form_Load()


'***********************************************************************************
'The idea is to set up a table with each person's name followed b
the number of
'hours they worked on this specific project this week.

'***********************************************************************************

Dim db As DAO.Database
Dim frmForm As Access.Form
Dim frmParent As Access.Form
Dim tbldefHours As DAO.TableDef
Dim tbldefWeeks As DAO.TableDef
Dim tbldefForm As New DAO.TableDef
Dim fldHours As DAO.Fields
Dim fldWeeks As DAO.Fields
Dim fldForm As DAO.Fields
Dim fld As DAO.Field
Dim indHours As DAO.Indexes
Dim indWeeks As DAO.Indexes
Dim indForm As DAO.Indexes
Dim ind As DAO.Index
Dim rcdsetHours As DAO.Recordset
Dim rcdsetWeeks As DAO.Recordset
Dim rcdsetForm As DAO.Recordset
Dim rcd As DAO.Recordset
Dim saveClicked As Events
Dim resetClicked As Events
Dim Project As String
Dim strSQL As String
Dim i As Integer
Dim j As Integer
Dim k As Integer

'Set the database object to the current database
Set db = CurrentDb

'Sets the form object to the Project List and its subform
Set frmParent = Me

'Defines the Hours Planned and Weeks tables
Set tbldefHours = db.TableDefs("Hours Planned")
Set tbldefWeeks = db.TableDefs("Weeks")

'Defines the Recordsets for the Tables
Set rcdsetHours = tbldefHours.OpenRecordset
Set rcdsetWeeks = tbldefWeeks.OpenRecordset


'Defines the Fields for the Tables
Set fldHours = rcdsetHours.Fields
Set fldWeeks = rcdsetWeeks.Fields

'Defines the Indexes for the Tables
Set indHours = tbldefHours.Indexes
Set indWeeks = tbldefWeeks.Indexes

'Converts all Week Starting Dates from Table Weeks to Field Name
for New Form
'j keeps track of what record the loop is on
j = 0
'The first field should be the person's name.
tbldefForm.CreateField ("Name")
Do While j < rcdsetWeeks.RecordCount
'Does the actual conversion and stores it as a new field o
integers
Set fld
tbldefForm.CreateField(CStr(rcdsetWeeks.Fields(0).Value), dbInteger)
'Adds the fields to the new table
tbldefForm.Fields.Append fld

'The following is for debugging purposes:
'If all the dates have been converted, exit the loop as a
error will occur
'otherwise during Debug.Print
'If Not (j < rcdsetWeeks.RecordCount) Then
' Exit Do
'End If

'Stores the current count of records in i
i = tbldefForm.Fields.Count

'Advances the loop counter
j = j + 1
'Moves to the next week to be converted
rcdsetWeeks.MoveNext
Loop

'Now that the fields and table are set up, the hours need to b
sorted by the current
'project in the window.
Project = frmParent.Recordset.Fields(1).Value
strSQL = "SELECT [Hours Planned].Name, [Hours Planned].[Wee
Starting Date], Sum([Hours Planned].Hours) AS Hours FROM [Hour
Planned] WHERE ((([Hours Planned].Project)= """ & CStr(Project) & """)
GROUP BY [Hours Planned].Name, [Hours Planned].[Week Starting Date
ORDER BY [Hours Planned].Name, [Hours Planned].[Week Starting Date];"
Set rcd = db.OpenRecordset(strSQL, dbOpenSnapshot)

'Fields have been set up; the table is set up; the Employees hav
been sorted. Now
'the records needed to be sorted and input into the new table
tbldefForms.
Set ind = tbldefForm.CreateIndex
'Set rcdsetForm.Fields = tbldefForm.Fields
i = 0
Do While i < rcd.RecordCount
j = 0
tbldefForm.Fields(j).Value = rcd.Fields("Name").Value
Do While tbldefForm.Fields(j).Value = rcd.Fields("Name").Value
If tbldefForm.Fields(j).Name = rcd.Fields("Week").Valu
Then
tbldefForm.Fields(j).Value = rcd.Fields("Hours").Value
rcd.MoveNext
i = i + 1
Else
j = j + 1
End If
Loop
Loop

'tbldefForms has been put together and completed. This is suppose
to update the subform and display
'the information from tbldefForms. Here is where the problem is.
Set frmForm = Form![Hours Planned subform]
frmForm.RecordSource = "SELECT * FROM tbldefForm;"
End Sub

 

[John Nurick]

A few things come to mind on a quick look through you code:

1) Why not use a crosstab query rather than creating a new table each
time?

2) Don't you need to Append the new TableDef to the database's TableDefs
collection before you can use it?

Option Compare Database

This pulls data from tables and attempts to reformat it to display more
like a spreadsheet in the subform of this main form. The linked fields
pass the name of the project for which the data needs to be displayed.
I've been able to create the table with the data, but I can't find a way
to have the subform display the data from the new table.

Thanks in advance.


Private Sub Form_Load()


'***********************************************************************************
'The idea is to set up a table with each person's name followed by
the number of
'hours they worked on this specific project this week.

'***********************************************************************************

Dim db As DAO.Database
Dim frmForm As Access.Form
Dim frmParent As Access.Form
Dim tbldefHours As DAO.TableDef
Dim tbldefWeeks As DAO.TableDef
Dim tbldefForm As New DAO.TableDef
Dim fldHours As DAO.Fields
Dim fldWeeks As DAO.Fields
Dim fldForm As DAO.Fields
Dim fld As DAO.Field
Dim indHours As DAO.Indexes
Dim indWeeks As DAO.Indexes
Dim indForm As DAO.Indexes
Dim ind As DAO.Index
Dim rcdsetHours As DAO.Recordset
Dim rcdsetWeeks As DAO.Recordset
Dim rcdsetForm As DAO.Recordset
Dim rcd As DAO.Recordset
Dim saveClicked As Events
Dim resetClicked As Events
Dim Project As String
Dim strSQL As String
Dim i As Integer
Dim j As Integer
Dim k As Integer

'Set the database object to the current database
Set db = CurrentDb

'Sets the form object to the Project List and its subform
Set frmParent = Me

'Defines the Hours Planned and Weeks tables
Set tbldefHours = db.TableDefs("Hours Planned")
Set tbldefWeeks = db.TableDefs("Weeks")

'Defines the Recordsets for the Tables
Set rcdsetHours = tbldefHours.OpenRecordset
Set rcdsetWeeks = tbldefWeeks.OpenRecordset


'Defines the Fields for the Tables
Set fldHours = rcdsetHours.Fields
Set fldWeeks = rcdsetWeeks.Fields

'Defines the Indexes for the Tables
Set indHours = tbldefHours.Indexes
Set indWeeks = tbldefWeeks.Indexes

'Converts all Week Starting Dates from Table Weeks to Field Names
for New Form
'j keeps track of what record the loop is on
j = 0
'The first field should be the person's name.
tbldefForm.CreateField ("Name")
Do While j < rcdsetWeeks.RecordCount
'Does the actual conversion and stores it as a new field of
integers
Set fld =
tbldefForm.CreateField(CStr(rcdsetWeeks.Fields(0).Value), dbInteger)
'Adds the fields to the new table
tbldefForm.Fields.Append fld

'The following is for debugging purposes:
'If all the dates have been converted, exit the loop as an
error will occur
'otherwise during Debug.Print
'If Not (j < rcdsetWeeks.RecordCount) Then
' Exit Do
'End If

'Stores the current count of records in i
i = tbldefForm.Fields.Count

'Advances the loop counter
j = j + 1
'Moves to the next week to be converted
rcdsetWeeks.MoveNext
Loop

'Now that the fields and table are set up, the hours need to be
sorted by the current
'project in the window.
Project = frmParent.Recordset.Fields(1).Value
strSQL = "SELECT [Hours Planned].Name, [Hours Planned].[Week
Starting Date], Sum([Hours Planned].Hours) AS Hours FROM [Hours
Planned] WHERE ((([Hours Planned].Project)= """ & CStr(Project) & """))
GROUP BY [Hours Planned].Name, [Hours Planned].[Week Starting Date]
ORDER BY [Hours Planned].Name, [Hours Planned].[Week Starting Date];"
Set rcd = db.OpenRecordset(strSQL, dbOpenSnapshot)

'Fields have been set up; the table is set up; the Employees have
been sorted. Now
'the records needed to be sorted and input into the new table,
tbldefForms.
Set ind = tbldefForm.CreateIndex
'Set rcdsetForm.Fields = tbldefForm.Fields
i = 0
Do While i < rcd.RecordCount
j = 0
tbldefForm.Fields(j).Value = rcd.Fields("Name").Value
Do While tbldefForm.Fields(j).Value = rcd.Fields("Name").Value
If tbldefForm.Fields(j).Name = rcd.Fields("Week").Value
Then
tbldefForm.Fields(j).Value = rcd.Fields("Hours").Value
rcd.MoveNext
i = i + 1
Else
j = j + 1
End If
Loop
Loop

'tbldefForms has been put together and completed. This is supposed
to update the subform and display
'the information from tbldefForms. Here is where the problem is.
Set frmForm = Form![Hours Planned subform]
frmForm.RecordSource = "SELECT * FROM tbldefForm;"
End Sub

 

[rem5]

Thank you very much John for your response.

To answer your questions, I can't use a crosstab query since I need the
information to be editable in the form itself. If someone is looking at
a Project and decides that Employee X should work 5 hours on it next
week, that needs to be recorded. After this, I was planning on using
similar code to read any edits to tbldefForm and transpose them back to
their respective tables. If there is a way to do so using a crosstab
query, please tell me either how to do it or where I can find the
information to figure it out.

As far as needing to Append the new TableDef, appending usually gives
me an error saying that it does not work for this specific instance.
Not sure why that happens, so I was just going to have the subform
recalculate itself on each update or when the Parent form changed. I'd
turn the transformation code into a separate function that could be
called up by OnOpen, OnClick, etc. Slightly redundant, but it's the
best work-around I could come up with this fast.

I tried adding a db.TableDefs.Append(tbldefForm) but this is the error
I always get: 3251. Operation is not supported for this type of
object.
Looking at the VB help files, I draw a blank.

Thanks again,

Ricardo

A few things come to mind on a quick look through you code:

1) Why not use a crosstab query rather than creating a new table
each
time?

2) Don't you need to Append the new TableDef to the database's
TableDefs
collection before you can use it?