Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2009
    Posts
    2

    Unanswered: Report Based on Crosstab Query with known Number of fields

    I have a simple report with the source coming from a cross tab query. There will always be 8 fields, but the names/labels of the fields will change depending on what years the user selects.

    I have modifed some code found in one of my access books to do this. But of course it is not working. rst.fields is not being recongized but me.recordsource is. The code is below. Any suggestions?

    Thanks, Jodi


    Private Sub Report_Open(Cancel As Integer)
    Dim intColCount As Integer
    Dim intControlCount As Integer
    Dim i As Integer
    Dim strName As String

    On Error Resume Next

    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.Open _
    Source:=Me.RecordSource, _
    ActiveConnection:=CurrentProject.Connection, _
    Options:=adCmdTable



    For i = 1 To 8
    strName = rst.Fields(i).Name
    Me.Controls("lblHeader" & i).Caption = strName
    Me.Controls("txtData" & i).ControlSource = strName
    Next i

    rst.Close

    End Sub

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    What do you mean it's not being recognized? If memory serves, Fields is zero based, so you'd want:

    strName = rst.Fields(i - 1).Name
    Paul

  3. #3
    Join Date
    Jul 2009
    Posts
    2

    Figured it out

    The reason why this code wasnt working was based my recordsource was based off of a crosstab query that was based off of a select query with a PARAMETER clause in the SQL as criteria. So when I took the criteria out of the select query and used this code, it finally works.

    Private Sub Report_Open(Cancel As Integer)

    Dim yr1 As String
    Dim yr2 As String
    Dim yr3 As String
    yr1 = Forms("Main").Controls("txtend3yr").Value - 2
    yr2 = Forms("Main").Controls("txtend3yr").Value - 1
    yr3 = Forms("Main").Controls("txtend3yr").Value

    Dim intControlCount As Integer
    Dim i As Integer
    Dim strName1 As String
    Dim strName2 As String
    Dim strName3 As String

    On Error Resume Next

    Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset("Select * from AnnualMSXPrev")

    intControlCount = rst.Fields.Count

    For i = 1 To intControlCount

    If rst.Fields(i).Name = yr1 Then
    strName1 = rst.Fields(i).Name
    Me.Controls("lblHeader1").Caption = strName1
    Me.Controls("txtData1").ControlSource = strName1
    End If

    If rst.Fields(i).Name = yr2 Then
    strName2 = rst.Fields(i).Name
    Me.Controls("lblHeader2").Caption = strName2
    Me.Controls("txtData2").ControlSource = strName2
    End If

    If rst.Fields(i).Name = yr3 Then
    strName3 = rst.Fields(i).Name
    Me.Controls("lblHeader3").Caption = strName3
    Me.Controls("txtData3").ControlSource = strName3
    End If
    Next i

    rst.Close

    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •