I am trying to develop a report that is based off a crosstab query. The query takes the results of time incremental quality control tests and bases the time increments as the column headings. There are a total of 28 possible column headings for this purpose.
Rather than makes a report with 28 columns for the time headings, and then another 3 columns for a Project, Date and Mix Number (which would be used to group and sort the results) , I want to be able to utilize something similar to the dynamic heading report used in the solutions9.mdb offered by Microsoft. I have reviewed the report module for that particular database but have not been able to figure out how to alter it for my needs as it was intended. For one it is based on a date period that the user selected, whereas mine will always be the last 30 records based chronologically (by date NOT by entry.) This being the case it does not need a refering form to get date information, although it should not be able to be opened unless the main form (frmMixes) is open because the Mix Number should refer back to the main form.
Just to give a better break down....
Main Form [frmMixes]
Report opened via a command button [OpenRptFlex] on a mounted subform [frmPours] of the main form. The report when opened refers back to [frmPours]![MixID] field.
The report is based on a crosstab query [qryMixesToAllTest_crs] that was based on another query [qryMixesToTest_pt1] based off three tables [tblMixes],[tblePours],[tblTests].
I have tried to use the CanShrink property for this purpose but it will a lot of space since an estimated 8-10 of the time increments will be used for any particular MixID, and this will generate a lot of unused blank space between fields.
If you are unfamilar with the Microsoft example of a dynamic report i have included the code for it below:
Thanks again.
Private Sub OpenRptFlex_Click()
On Error GoTo Err_OpenRptFlex_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Mix Flexure Break Report (Last 30 Tests)" 'Specifying coding for string stDocName
stLinkCriteria = "[MixID]=" & "'" & Me![MixID] & "'" 'Specifying coding for string stLinkCriteria
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
'Syntax should be DoCmd.OpenReport "MyReportName", acNormal, , "[MyId]=[Forms]![MyID]"
Exit_OpenRptFlex_Click:
Exit Sub
Err_OpenRptFlex_Click:
MsgBox Err.Description
Resume Exit_OpenRptFlex_Click
End Sub
Private Sub OpenRptShrk_Click()
On Error GoTo Err_OpenRptShrk_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Mix Shrinkage Break Report (Last 30 Tests)" 'Specifying coding for string stDocName
stLinkCriteria = "[MixID]=" & "'" & Me![MixID] & "'" 'Specifying coding for string stLinkCriteria
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
Exit_OpenRptShrk_Click:
Exit Sub
Err_OpenRptShrk_Click:
MsgBox Err.Description
Resume Exit_OpenRptShrk_Click
End Sub
Private Sub ProjectID_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmContractors", , , , acFormEdit, acDialog
End Sub
Private Sub OpenContractors_Click()
On Error GoTo Err_OpenContractors_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmContractors"
stLinkCriteria = "[ContractorID]=" & Me![ProjectID]
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormEdit, acDialog
Exit_OpenContractors_Click:
Exit Sub
Err_OpenContractors_Click:
MsgBox Err.Description
Resume Exit_OpenContractors_Click
End Sub
Private Sub ProjectID_Enter()
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
End Sub
Rather than makes a report with 28 columns for the time headings, and then another 3 columns for a Project, Date and Mix Number (which would be used to group and sort the results) , I want to be able to utilize something similar to the dynamic heading report used in the solutions9.mdb offered by Microsoft. I have reviewed the report module for that particular database but have not been able to figure out how to alter it for my needs as it was intended. For one it is based on a date period that the user selected, whereas mine will always be the last 30 records based chronologically (by date NOT by entry.) This being the case it does not need a refering form to get date information, although it should not be able to be opened unless the main form (frmMixes) is open because the Mix Number should refer back to the main form.
Just to give a better break down....
Main Form [frmMixes]
Report opened via a command button [OpenRptFlex] on a mounted subform [frmPours] of the main form. The report when opened refers back to [frmPours]![MixID] field.
The report is based on a crosstab query [qryMixesToAllTest_crs] that was based on another query [qryMixesToTest_pt1] based off three tables [tblMixes],[tblePours],[tblTests].
I have tried to use the CanShrink property for this purpose but it will a lot of space since an estimated 8-10 of the time increments will be used for any particular MixID, and this will generate a lot of unused blank space between fields.
If you are unfamilar with the Microsoft example of a dynamic report i have included the code for it below:
Thanks again.
Private Sub OpenRptFlex_Click()
On Error GoTo Err_OpenRptFlex_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Mix Flexure Break Report (Last 30 Tests)" 'Specifying coding for string stDocName
stLinkCriteria = "[MixID]=" & "'" & Me![MixID] & "'" 'Specifying coding for string stLinkCriteria
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
'Syntax should be DoCmd.OpenReport "MyReportName", acNormal, , "[MyId]=[Forms]![MyID]"
Exit_OpenRptFlex_Click:
Exit Sub
Err_OpenRptFlex_Click:
MsgBox Err.Description
Resume Exit_OpenRptFlex_Click
End Sub
Private Sub OpenRptShrk_Click()
On Error GoTo Err_OpenRptShrk_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Mix Shrinkage Break Report (Last 30 Tests)" 'Specifying coding for string stDocName
stLinkCriteria = "[MixID]=" & "'" & Me![MixID] & "'" 'Specifying coding for string stLinkCriteria
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
Exit_OpenRptShrk_Click:
Exit Sub
Err_OpenRptShrk_Click:
MsgBox Err.Description
Resume Exit_OpenRptShrk_Click
End Sub
Private Sub ProjectID_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmContractors", , , , acFormEdit, acDialog
End Sub
Private Sub OpenContractors_Click()
On Error GoTo Err_OpenContractors_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmContractors"
stLinkCriteria = "[ContractorID]=" & Me![ProjectID]
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormEdit, acDialog
Exit_OpenContractors_Click:
Exit Sub
Err_OpenContractors_Click:
MsgBox Err.Description
Resume Exit_OpenContractors_Click
End Sub
Private Sub ProjectID_Enter()
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
End Sub
