Re: Use crosstab column heading in a calculation

Tech-Archive recommends: Fix windows errors by optimizing your registry

I would use code to set the parameter values in the sql like:
strSQL = strSQL & "TRANSFORM ..." & vbCrLf
strSQL = strSQL & "SELECT ..." & vbCrLf
strSQL = strSQL & "FROM tblSwapParameters AS Params, Params2 " & vbCrLf
strSQL = strSQL & "WHERE swapid=" & [Forms]![frmSwaps]![SwapID] & vbCrLf
strSQL = strSQL & " And somefield=" & [Forms]![frmSwaps]![Conf95] & vbCrLf
strSQL = strSQL & " GROUP BY swapid, ..." & vbCrLf
strSQL = strSQL & " PIVOT CalcMonth;"


--
Duane Hookom
MS Access MVP
--

"Bill R via AccessMonster.com" <u9289@uwe> wrote in message
news:5a8de36f0c9bb@xxxxxx
> Duane,
>
> I found a thread that purports to have solved the problem, but I can't
> seem
> to make it work. Here's the thread:
>
> http://www.accessmonster.com/Uwe/Forum.aspx/access-formscoding/24422/Display-crosstab-query-in-subform#5a8dd03e6294fuwe
>
>
> Here's my code in the On Current event of the main form:
>
> Private Sub Form_Current()
> Dim strSQL As String
> Dim db As DAO.Database
> Dim qdf As QueryDef
>
> Set db = CurrentDb
>
> strSQL = "PARAMETERS [Forms]![frmSwaps]![SwapID] Long, [Forms]![frmSwaps]!
> [Conf95] IEEEDouble;" & vbCrLf
> strSQL = strSQL & "TRANSFORM ..." & vbCrLf
> strSQL = strSQL & "SELECT ..." & vbCrLf
> strSQL = strSQL & "FROM tblSwapParameters AS Params, Params2" & vbCrLf
> strSQL = strSQL & "WHERE swapid=[Forms]![frmSwaps]![SwapID]" & vbCrLf
> strSQL = strSQL & "GROUP BY swapid, ..." & vbCrLf
> strSQL = strSQL & "PIVOT CalcMonth;"
>
> Set qdf = db.QueryDefs("qryFwdPrices")
> qdf.SQL = strSQL
> Me!sfrmFwdPrices.SourceObject = "Query.qryFwdPrices"
> Set db = Nothing
>
> End Sub
>
> Can you see where I have strayed from Jesper's example?
>
> Thanks,
>
> Bill
>
> Duane Hookom wrote:
>>You don't ask for much do you ;-)
>>
>>You can only show records in a subform where you know the fields so you
>>can
>>set the control sources. I have not seen solutions for dynamic subforms. I
>>have created solutions for dynamic fields in reports.
>>
>>I do create a somewhat dynamic subform in the Query By Form applet
>>available
>>for download at
>>http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane
>>
>>> Duane,
>>>
>>[quoted text clipped - 17 lines]
>>>>>>>>>>
>>>>>>>>>> Bill
>
> --
> Bill Reed
>
> "If you can't laugh at yoursel, laugh at somebody else"
>
> Message posted via http://www.accessmonster.com


.

Relevant Pages

  • RE: Work hours by Ken Sheridan.
    ... "Duane Hookom" wrote: ... Dim strMinutesSeconds As String ... Dim dtmDay As Date ... ' if start time after lunch subtract lunch time ...
    (microsoft.public.access.queries)
  • RE: Work hours by Ken Sheridan.
    ... Try delete the function from the class module and paste it into a ... "Duane Hookom" wrote: ... Dim strMinutesSeconds As String ... Dim dtmDay As Date ...
    (microsoft.public.access.queries)
  • RE: Work hours by Ken Sheridan.
    ... "Duane Hookom" wrote: ... Dim strMinutesSeconds As String ... Dim dtmDay As Date ... ' if start time after lunch subtract lunch time ...
    (microsoft.public.access.queries)
  • Re: Help with sub-form and after update code
    ... Duane Hookom ... MS Access MVP ... Dim intTotalNumberOfFaults As Integer ... Dim intTotalScoringPenalties As Integer ...
    (microsoft.public.access.formscoding)
  • RE: SQL stored procedure executing twice
    ... "Duane Hookom" wrote: ... Microsoft Access MVP ... Dim stDocName As String ...
    (microsoft.public.access.modulesdaovba)