Got it!
The only problem I currently foresee is when a job is being done and there is no data to populate the crosstab, it still has all the headers from the previous time the code ran (granted all rows are empty). Granted I would "assume" my users wouldn't open the form until there is data they actually want to see.
Code:
Public Sub Form_Open(Cancel As Integer)
Dim strContractor As String, strSql As String, strQuery As String
strContractor = Contractorlist()
strQuery = "ContractorCountQry_Crosstab"
strSql = "PARAMETERS [Forms]![JobQuote]![JobID] Short;"
strSql = strSql & " TRANSFORM First(ContractorCountQry.Count) AS FirstOfCount"
strSql = strSql & " SELECT ContractorCountQry.TypeName"
strSql = strSql & " FROM ContractorCountQry "
strSql = strSql & " WHERE (((ContractorCountQry.JobID)=[Forms]![JobQuote]![JobID]))"
strSql = strSql & " GROUP BY ContractorCountQry.TypeName"
strSql = strSql & " PIVOT ContractorCountQry.Contractor IN (" & strContractor & ")"
CurrentDb.QueryDefs(strQuery).SQL = strSql
Me.RecordSource = strSql
End Sub
Public Function Contractorlist() As String
Dim lngCount As Long
Dim strSql As String
Dim strContractor As String
giMaxContractor = 8
strSql = " SELECT tblContractors.Contractor" & _
" FROM tblContractors INNER JOIN tblContractorJob ON tblContractors.ContractorID = tblContractorJob.ContractorID" & _
" GROUP BY [Contractor]" & _
" ORDER BY [Contractor]"
With CurrentDb.OpenRecordset(strSql)
Do Until .EOF
lngCount = lngCount + 1
If lngCount > giMaxContractor Then Exit Do
strContractor = strContractor & Chr(34) & !Contractor & Chr(34) & ","
.MoveNext
Loop
End With
If Right(strContractor, 1) = "," Then
Contractorlist = Left(strContractor, Len(strContractor) - 1)
End If
End Function
The only problem I currently foresee is when a job is being done and there is no data to populate the crosstab, it still has all the headers from the previous time the code ran (granted all rows are empty). Granted I would "assume" my users wouldn't open the form until there is data they actually want to see.