Crosstab Query 1

[cneill]

Hi Everyone,

I have added a number of text boxes to a form (more than I will ever need) and am using the following code to populate them, because the number of field will vary each time the Crosstab query runs.

Public Sub Form_Load()

Set rst = Me.Form.RecordsetClone
For i = 0 To rst.Fields.Count - 1
If i < 16 Then
Me("Text" & i + 1).ControlSource = rst.Fields(i).Name
Me("Label" & i + 1).Caption = rst.Fields(i).Name
Me("Text" & i + 1).ColumnHidden = False
Me("Text" & i + 1).ColumnWidth = -2
End If
Next
For i = i + 1 To 16
Me("Text" & i).ColumnHidden = True
Next
Set rst = Nothing

End Sub

This works really well, I am now trying to add some totals in the Form Footer for each column, I have tried various ideas but the text box field in the footer just says error. Can anyone give me an idea how I might be able to do this?

Thanks

Neill

 

[dhookom]

Have you tried something like:

    Me("TextSum" & i + 1).ControlSource = "=Sum([" & rst.Fields(i).Name & "])"

Duane
Hook'D on Access
MS Access MVP

 

[cneill]

Hi dhookom,

Thanks but this is not working either, I get #Error in each of the Form Footer Text Boxes

Any other thoughts

Thanks

Neill

 

[cneill]

Hi dhookom,

Done some more checking your code works but only if I interrupt the code and use F8 to manually go through the code, is there a way to slow down the execution of the code?

Thanks

CNEILL

 

[dhookom]

May be you just need to add a recalc or something after the code has run.

Duane
Hook'D on Access
MS Access MVP

 

[cneill]

Hi Duane,

The Recalc works thanks very much.

The Crosstab query now populates the form correctly, but when I use it as a subform I get the #error in the form footer again.

any thoughts?

thanks neill

 

[dhookom]

Is the error in the subform footer? Can you share your complete code?

Duane
Hook'D on Access
MS Access MVP

 

[cneill]

Hi Duane,

It is is the Subform footer the sumtext fields, as the text boxes are unbound I needed to remove the subforms record source and add it to the subforms onload. Also for your info the Subform becomes visible and the code runs when when I use an after update event from a combobox on the main form, the after update event is:-
Call Me.WeeklyAttendees.Form.Form_Load
Me.WeeklyAttendees.Requery
Me.WeeklyAttendees.Visible = True

Public Sub Form_Load()

Dim rst As DAO.Recordset
Dim i As Integer
Set rst = Me.Form.RecordsetClone
For i = 0 To rst.Fields.Count - 1
If i < 19 Then

Me("Label" & i + 1).Caption = rst.Fields(i).Name
Me("Text" & i + 1).ControlSource = rst.Fields(i).Name
Me("SumText" & i + 1).ControlSource = ("=Sum([" & rst.Fields(i).Name & "])")
Me("Text" & i + 1).ColumnHidden = False
Me("Text" & i + 1).ColumnWidth = -2
Me.Recalc
End If
Next
For i = i + 1 To 19
Me("Text" & i).ColumnHidden = True

Next

Set rst = Nothing

Me.RecordSource = "QryVenueAttendees1"

End Sub

Thanks for your help, let me know if you need anymore info

Thanks

Neill

 

[dhookom]

Have you attempted to set the recordsource first?

Duane
Hook'D on Access
MS Access MVP

 

[cneill]

Hi Duane,

Sorry for the delay in replying, was away the weekend.

Set the recordsource first, still getting error
tried setting the form as a popup, no error
so I only get the error when the form is a subform

any other thoughts?

thanks neill

 

[dhookom]

Considered adding code to recalculate.

Duane
Hook'D on Access
MS Access MVP

 

[cneill]

What do you mean by "adding code to recalculate"?
I don't know how to do this.

 

[dhookom]

I meant to say requery. This would be code like:

Me.Requery

Duane
Hook'D on Access
MS Access MVP

 

[cneill]

Tried this is various points of the code, still the same error

 

[dhookom]

Not sure where to go next. Can you share the SQL view of the record source?

Duane
Hook'D on Access
MS Access MVP

 

[cneill]

Here is the SQL for the record source

PARAMETERS [Forms]![FrmMain]![Venue] Short, [Forms]![FrmMain]![TaxYear] Short, [Forms]![FrmMain]![Period] Short;
TRANSFORM IIf(Count([Attended]) Is Null,0,Count([Attended])) AS Attended1
SELECT TblAttendees.AttendeeName AS Attendee, Sum(TblFinances.Income) AS [Total Paid], Count(TblFinances.Attended) AS Total, ([Total Paid]/[Total]) AS [Avg]
FROM ((TblAttendees INNER JOIN (TblTaxYears INNER JOIN TblFinances ON TblTaxYears.TaxYearID = TblFinances.TaxYearID) ON TblAttendees.AttendeeID = TblFinances.AttendeeID) INNER JOIN TblVenues ON TblFinances.VenueID = TblVenues.VenueID) INNER JOIN TblPeriods ON TblFinances.PeriodID = TblPeriods.PeriodID
WHERE (((TblFinances.PeriodID)=[Forms]![FrmMain]![Period]) AND ((TblFinances.TaxYearID)=[Forms]![FrmMain]![TaxYear]) AND ((TblFinances.VenueID)=[Forms]![FrmMain]![Venue]))
GROUP BY TblAttendees.AttendeeName, TblFinances.TaxYearID
ORDER BY TblAttendees.AttendeeName
PIVOT TblFinances.AttendedDate;

Thanks

 

[dhookom]

I would limit the form display to a specific number of dates and then use a modification of this solution faq703-5466

Duane
Hook'D on Access
MS Access MVP

 

[cneill]

I have achieved this on the Summary Subform, showing a summary for each month, which works find, but I do need to show the dates attendeed on another subform.

I have found another piece of code that I have adapted which runs very fast compared to the other version

Dim mydb As Database
Dim rst As Recordset
Dim ctr As Integer
Dim lblObj As Label
Dim txtObj As TextBox
Dim txtObj1 As TextBox

Set mydb = CurrentDb

Set rst = Me.Form.RecordsetClone

For ctr = 0 To rst.Fields.Count - 1

Set lblObj = Me("Label" & ctr)
Set txtObj = Me("text" & ctr)
Set txtObj1 = Me("Sumtext" & ctr)

lblObj.Caption = rst.Fields(ctr).Name
txtObj.ControlSource = rst.Fields(ctr).Name
txtObj1.ControlSource = ("=Sum([" & rst.Fields(ctr).Name & "])")


Next

I am now using this for a Popup form which works great. so I am going to park the subform error problem for now
I just need to find away to hide the unused Text Boxes each time the code runs, so after they have been populated, I think I need a second code sequence to check if there are any values in the text boxes, then hide any that have no values.

I have tried

For ctr = 0 To rst.Fields.Count - 1

If IsNull(txtObj) Or txtObj < 0 Or txtObj = "" Then

txtObj.Visible = False
Else
txtObj.Visible = True
End If

Next

This does not work, any thoughts?