Crosstab Query Form

[EhWot]

Hi All,

Scratching my head somewhat over this one..

I've a Crosstab Query that sums Savings per month, so the column headings are: SavingID, TotalSaving then (E.g) 01/04/03, 01/05/03, 01/06/03 etc.

These Date Column Headings will grow in number as the database gets used, which isn't a problem for the QUERY (This automaticlly presents all the source data), but the Form I'm using to present the Crosstab will not present any 'New' Months.

Is there any way to make the Form ALWAYS reflect the contents of the underlaying Crosstab Query?? I.e. any 'New' Fields..

 

[guitardave78]

you may need to set the relationship to the underlying table to show all from the left (join properties)

 

[EhWot]

Hi,

Not sure thats the problem as the Query works fine: it's just that I can't get the Form based on that (Crosstab) Query to show any 'New' Fields.

 

[WildHare]

There is a way. I've been doing dynamic crosstabs like this for the last year or so. I've developed a technique to create dynamic forms and reports based on the 'across' values in the cross tab dataset.

I will try to condense it here, if you have additional questions, drop me a line at the email addy at the end of this message.

First, you need to place UNBOUND labels controls on your form/report to represent each of the possible 'across' columns. If you want a form/report to be able to show 12 months, you'll need 12 unbound labels. These guys will hold the COLUMN headings, e.g. "1/3/03", '1/4/03" and so on. It's best to NAME them with consecutive numbering, like "ColHead1", "ColHead2" and so on.

Then add the same number of unbound text boxes in the detail area of the form/report. You will need a line of unbound text boxes for each row-heading guy in the table. NAME them the same way, e.g. "Val1", "Val2" etc.

Now, here comes the tricky part.

On the OPEN event of the form or report, you are going to shove the appropriate VALUES into the label and text box's CONTROL SOURCE property. What you do is build a small loop that sets the control source of the labels and then the text boxes to the appropriate fields in the dataset.

For example, I have a dynamic report that uses a START date and and END DATE. It gathers 7 days of data, and then produces the report. My BegDate and EndDate fields are the grabbed from a form. Here's the code, with the important bits in bold:


Private Sub Report_Open(Cancel As Integer)
Dim intCurrDay As Integer, lngCurrDay As Long
On Error GoTo Report_open_error

For intCurrDay = 0 To 6
lngCurrDay = DateAdd("d", intCurrDay, Forms!FBWeekly!Begdate)

' set the row value control sources to the seven days selected, which match the FIELDS in the x-tab query

Me("COL" & Trim(CStr(intCurrDay) + 1)).ControlSource = Format$(lngCurrDay, "mmm dd yyyy&quot


'do the same for the column headings.

Me("txtColHead" & Trim(CStr(intCurrDay) + 1)).ControlSource = _
"=Format$(DateAdd(" & _
Chr$(34) & "d" & Chr$(34) & ", " & intCurrDay & _
" , Forms![FBWeekly]![BegDate])," & Chr$(34) & "mmm dd" & Chr$(34) & &quot"


Next

DoCmd.Maximize

Exit Sub

Report_open_error:
MsgBox Err.Description
Cancel = True
Exit Sub

End Sub

The looping basically builds control source values that match my columns in the dataset, e.g. "20 Apr 2003", "21 Apr 2003", etc etc.

Now in your case, you need to make the controls VISIBLE when you need them too. My reports are a little different, they always have 7 columns. But I think you get the idea?

Let me know if this is totally confusing, and I'll try to come up with a clearer picture.

Jim









Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[EhWot]

Thanks Jim, I'd never have though of that! I'll give this a go - I'll give you a shout if I get stuck.


Many thanks!

Greg