Chris said:
I tried using a crosstab query, I only have 2 fields in my
table what do I use as the "value"?
the Sub field again. You have to use an aggravation function (no,
aggregate, just a joke) I suggest you use First.
The trouble with a crosstab query on a report, though, is that a
crosstab query columns vary by nature, as the data changes. Reports
cannot follow this, you have to bind specific fields to specific, well,
fields. (controls to data fields, that should be).
Maybe for this purpose it's best you use some function that creates a
string with all these Sub entries, for any Dist supplied. You then
create a query that gets all DISTINCT Dist values from the table, and
calls that function.
I should have some EnumField function (muffles somewhere in a
coffin-like toolkit) here it is
Function EnumField(rs As Recordset, FieldNo As Variant, Optional
vSeparator = vbNewLine, Optional separatorAfterLast = True) As String
'fieldno can be number or string, don't care - as long as it exists in
the recordset, of course
Dim cRes As String
On Error GoTo err_EnumField
cRes = ""
If Val(FieldNo) > 0 Then
FieldNo = Val(FieldNo)
ElseIf FieldNo = "0" Then
FieldNo = 0
Else
End If
If rs.RecordCount > 0 Then
rs.MoveFirst
Do Until rs.EOF
cRes = cRes & rs(FieldNo) & vSeparator
rs.MoveNext
Loop
End If
If Not separatorAfterLast Then
If cRes <> "" Then
cRes = Left(cRes, Len(cRes) - Len(vSeparator))
End If
End If
EnumField = cRes
exit_EnumField:
Exit Function
err_EnumField:
Select Case Err
Case Else
Select Case Standaardfout("EnumField")
Case vbAbort
Resume exit_EnumField
Case vbRetry
Resume
Case vbIgnore
Resume Next
End Select
End Select
End Function
Function Standaardfout(Optional cFlag) As Long
'ROUTINE : Standaardfout
'PURPOSE : uniforme foutmelding geven aan de gebruiker, als ik het niet
meer weet
'INPUT : cFlag, optioneel: te vermelden volgpunt (in code opnemen, dus)
'OUTPUT : long: {vbAbort, vbRetry, vbIgnore}
Dim cMsg As String
Dim nErr As Long
Dim nRes As Long
DoCmd.Echo True
nErr = Err.number
If IsMissing(cFlag) Then
cMsg = "Fout nummer " & nErr
Else
cMsg = "Fout nummer " & nErr & " in " & cFlag
End If
cMsg = cMsg & ".@ De beschrijving bij deze fout is
" & Err.Description
nRes = MsgBox(cMsg, vbAbortRetryIgnore)
Standaardfout = nRes
End Function
Unfortunately the Standaardfout routine is in Dutch. Can you live with that?
