Dynamic Crosstab Report

[Guest]

I have a crosstab query that will vary by columns and rows anytime it is run.
My column labels work great (wrote similar code in Report Header on Format).
For the test run of the report I have 14 columns and 18 rows. My problem,
the first record repeats 18 times instead of showing me the 18 individual
records. I'm sure its something obvious...I hope you can help!

Here is the code I'm using

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim RST As DAO.Recordset
Dim db As DAO.Database
Dim i As Integer
Dim j As Integer

Set db = CurrentDb
Set RST = db.OpenRecordset("sel_Trailer Pools")

RST.MoveFirst

j = -1
i = 0

For i = 0 To RST.Fields.Count - 1

j = j + 1

Select Case j
Case 0
Me.unb0 = RST.Fields(i).Value
Case 1
Me.unb1 = RST.Fields(i).Value
etc through 40

End Select

Next i

RST.Close
Set RST = Nothing
End Sub

 

[Duane Hookom]

I believe you should be setting the Control Source property of the text
boxes in the On Open event of the report.
Me.unb0.ControlSource = "[" &RST.Fields(i).Name & "]"

 

[Guest]

Thank you for the quick response. I tried this but didn't have any luck so
I'm playing around some more. I tried this

Me!unb0.ControlSource = "[" & RST.Fields(i).Value & "]"

which gives me an error "Microsoft Jet Engine Database does not recognize
'[31]' as a valid field name or expression"

 

[Duane Hookom]

That's the error message I would expect if you use the field value.

"didn't have any luck" isn't too descriptive. If you want more help, you
must provide more information.

--
Duane Hookom
MS Access MVP
--

Thank you for the quick response. I tried this but didn't have any luck
so
I'm playing around some more. I tried this

Me!unb0.ControlSource = "[" & RST.Fields(i).Value & "]"

which gives me an error "Microsoft Jet Engine Database does not recognize
'[31]' as a valid field name or expression"

I believe you should be setting the Control Source property of the text
boxes in the On Open event of the report.
Me.unb0.ControlSource = "[" &RST.Fields(i).Name & "]"

 

[Guest]

Sorry, my coding is elementary at best and rusty.
When I tried this I got

run-time error '-2147352567 (80020009)':
You can't assign a value to this object.

When I debug, it takes me to the Sub Detail_Form

Me.unb0 = RST.Fields(i).Name

which I had

Me.unb0 = RST.Fields(i).Value

got the same run-time error, so I tried using .Value in what you gave me.

That's the error message I would expect if you use the field value.

"didn't have any luck" isn't too descriptive. If you want more help, you
must provide more information.

--
Duane Hookom
MS Access MVP
--

Thank you for the quick response. I tried this but didn't have any luck
so
I'm playing around some more. I tried this

Me!unb0.ControlSource = "[" & RST.Fields(i).Value & "]"

which gives me an error "Microsoft Jet Engine Database does not recognize
'[31]' as a valid field name or expression"

I believe you should be setting the Control Source property of the text
boxes in the On Open event of the report.
Me.unb0.ControlSource = "[" &RST.Fields(i).Name & "]"

 

[Duane Hookom]

That doesn't look like the code that I suggested to you. I would need to see
all the code from your report to see the big picture. You should identify
the line that causes the problem and the error message.

I don't like the "solution" that you are implementing. I prefer the more
flexible crosstab reports found at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

--
Duane Hookom
MS Access MVP
--

Sorry, my coding is elementary at best and rusty.
When I tried this I got

run-time error '-2147352567 (80020009)':
You can't assign a value to this object.

When I debug, it takes me to the Sub Detail_Form

Me.unb0 = RST.Fields(i).Name

which I had

Me.unb0 = RST.Fields(i).Value

got the same run-time error, so I tried using .Value in what you gave me.

That's the error message I would expect if you use the field value.

"didn't have any luck" isn't too descriptive. If you want more help, you
must provide more information.

--
Duane Hookom
MS Access MVP
--

Thank you for the quick response. I tried this but didn't have any
luck
so
I'm playing around some more. I tried this

Me!unb0.ControlSource = "[" & RST.Fields(i).Value & "]"

which gives me an error "Microsoft Jet Engine Database does not
recognize
'[31]' as a valid field name or expression"


:

I believe you should be setting the Control Source property of the
text
boxes in the On Open event of the report.
Me.unb0.ControlSource = "[" &RST.Fields(i).Name & "]"

 

[Guest]

I really appreciate your help, thank you! So I've ditched my original
approach and I've just incorporated your solution from crosstab.mdb.

I'm getting a compile error on cmdPreview_Click:

If updTrailerPools(Me.lboNumColumns) = 0 Then

method or data member not found on the .lboNumColumns

That doesn't look like the code that I suggested to you. I would need to see
all the code from your report to see the big picture. You should identify
the line that causes the problem and the error message.

I don't like the "solution" that you are implementing. I prefer the more
flexible crosstab reports found at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

--
Duane Hookom
MS Access MVP
--

Sorry, my coding is elementary at best and rusty.
When I tried this I got

run-time error '-2147352567 (80020009)':
You can't assign a value to this object.

When I debug, it takes me to the Sub Detail_Form

Me.unb0 = RST.Fields(i).Name

which I had

Me.unb0 = RST.Fields(i).Value

got the same run-time error, so I tried using .Value in what you gave me.

That's the error message I would expect if you use the field value.

"didn't have any luck" isn't too descriptive. If you want more help, you
must provide more information.

--
Duane Hookom
MS Access MVP
--

Thank you for the quick response. I tried this but didn't have any
luck
so
I'm playing around some more. I tried this

Me!unb0.ControlSource = "[" & RST.Fields(i).Value & "]"

which gives me an error "Microsoft Jet Engine Database does not
recognize
'[31]' as a valid field name or expression"


:

I believe you should be setting the Control Source property of the
text
boxes in the On Open event of the report.
Me.unb0.ControlSource = "[" &RST.Fields(i).Name & "]"

 

[Duane Hookom]

I would not expect an implementation of this solution to ask a user for the
number of columns to display. A report would normally be designed to
accommodate a maximum number of columns. I only added the ability to select
the number of columns to display as a way of showing the flexibility of the
reporting solution.

--
Duane Hookom
MS Access MVP
--

I really appreciate your help, thank you! So I've ditched my original
approach and I've just incorporated your solution from crosstab.mdb.

I'm getting a compile error on cmdPreview_Click:

If updTrailerPools(Me.lboNumColumns) = 0 Then

method or data member not found on the .lboNumColumns

That doesn't look like the code that I suggested to you. I would need to
see
all the code from your report to see the big picture. You should identify
the line that causes the problem and the error message.

I don't like the "solution" that you are implementing. I prefer the more
flexible crosstab reports found at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

--
Duane Hookom
MS Access MVP
--

Sorry, my coding is elementary at best and rusty.
When I tried this I got

run-time error '-2147352567 (80020009)':
You can't assign a value to this object.

When I debug, it takes me to the Sub Detail_Form

Me.unb0 = RST.Fields(i).Name

which I had

Me.unb0 = RST.Fields(i).Value

got the same run-time error, so I tried using .Value in what you gave
me.



:

That's the error message I would expect if you use the field value.

"didn't have any luck" isn't too descriptive. If you want more help,
you
must provide more information.

--
Duane Hookom
MS Access MVP
--

Thank you for the quick response. I tried this but didn't have any
luck
so
I'm playing around some more. I tried this

Me!unb0.ControlSource = "[" & RST.Fields(i).Value & "]"

which gives me an error "Microsoft Jet Engine Database does not
recognize
'[31]' as a valid field name or expression"


:

I believe you should be setting the Control Source property of the
text
boxes in the On Open event of the report.
Me.unb0.ControlSource = "[" &RST.Fields(i).Name & "]"

 

[Guest]

Ok, I compile successfully. I'm not clear on how this gets launched? Where
do you call the function? Sorry if that is a dumb question...I know enough
to be dangerous.

I also want to assign a number to my column alias because I will have at
least 31 maybe more. Here is the code I have

Function updTrailerPools(pbytNumColumns As Byte) As Long

On Error GoTo updTrailerPools_Err
Dim strErrMsg As String

Dim strSQL As String
Dim intAlias As Integer
Dim lngLoc As Long
Dim bytMaxColumns As Byte

Dim db As Database
Dim rs As Recordset

strSQL = "Delete * from tblDateAlias"
DoCmd.SetWarnings False
DoCmd.OpenQuery "mtq_temp trailer pools" 'need to generate appDates
DoCmd.RunSQL strSQL
DoCmd.OpenQuery "appDates" 'Distinct list of Location, SCAC, Day
DoCmd.OpenQuery "mtq_Trailer Pools" 'table view of final crosstab for report
DoCmd.SetWarnings True

bytMaxColumns = pbytNumColumns

Set db = CurrentDb
Set rs = db.OpenRecordset("tblDateAlias")

With rs
If Not (.EOF And .BOF) Then
.MoveFirst
Do While Not .EOF
lngLoc = !Location
intAlias = 40
Do While !Location = lngLoc
.Edit
!ColumnAlias = Int(intAlias)
.Update
intAlias = intAlias + 1
If intAlias = 40 + bytMaxColumns Then
intAlias = 40
End If
.MoveNext
If .EOF Then
Exit Do
End If
Loop
Loop
End If
End With

updTrailerPools_Exit:
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Function

updTrailerPools_Err:
Select Case Err
Case Else
updTrailerPools = Err.Number
Resume updTrailerPools_Exit
End Select
End Function

 

[Duane Hookom]

You would launch the code from a command button on a form. The code would
run to update some utility type tables and then open the report.

Your report can only fit a finite number of columns before you run off the
right side of the paper. This would be the bytMaxColumns. I'm not sure why
you changed:
intAlias = 65 'ascii value of 'A'
to
intAlias = 40


--
Duane Hookom
MS Access MVP
--

Ok, I compile successfully. I'm not clear on how this gets launched?
Where
do you call the function? Sorry if that is a dumb question...I know
enough
to be dangerous.

I also want to assign a number to my column alias because I will have at
least 31 maybe more. Here is the code I have

Function updTrailerPools(pbytNumColumns As Byte) As Long

On Error GoTo updTrailerPools_Err
Dim strErrMsg As String

Dim strSQL As String
Dim intAlias As Integer
Dim lngLoc As Long
Dim bytMaxColumns As Byte

Dim db As Database
Dim rs As Recordset

strSQL = "Delete * from tblDateAlias"
DoCmd.SetWarnings False
DoCmd.OpenQuery "mtq_temp trailer pools" 'need to generate appDates
DoCmd.RunSQL strSQL
DoCmd.OpenQuery "appDates" 'Distinct list of Location, SCAC, Day
DoCmd.OpenQuery "mtq_Trailer Pools" 'table view of final crosstab for
report
DoCmd.SetWarnings True

bytMaxColumns = pbytNumColumns

Set db = CurrentDb
Set rs = db.OpenRecordset("tblDateAlias")

With rs
If Not (.EOF And .BOF) Then
.MoveFirst
Do While Not .EOF
lngLoc = !Location
intAlias = 40
Do While !Location = lngLoc
.Edit
!ColumnAlias = Int(intAlias)
.Update
intAlias = intAlias + 1
If intAlias = 40 + bytMaxColumns Then
intAlias = 40
End If
.MoveNext
If .EOF Then
Exit Do
End If
Loop
Loop
End If
End With

updTrailerPools_Exit:
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Function

updTrailerPools_Err:
Select Case Err
Case Else
updTrailerPools = Err.Number
Resume updTrailerPools_Exit
End Select
End Function

 

[Guest]

I have success, thank you!