Results 1 to 6 of 6
  1. #1
    swichman is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jan 2016
    Posts
    4

    Trying to Create Recordset In Linked Database for Crosstab Query Report

    Hello
    I am trying to create a recordset using the below code in a linked database in order to format a report and determine the number of columns as the report is driven by a crosstab query.
    I am getting a runtime error 3066 on the bold line below: Query must have at least one destination field.

    I am having a difficult time changing a bit of my code since I split the database and this is one that is giving me real trouble. Any suggestions would be tremendously helpful.
    Thanks
    Sean

    Private Sub Report_Open(Cancel As Integer)
    'Create underlying recordset for report using criteria entered on
    'form.


    Dim dbsReport As DAO.Database
    Dim rstReport As DAO.Recordset
    Dim intX As Integer
    Dim qdf As QueryDef
    Dim frm As Form
    Dim prm As Parameter

    Dim StrName As String

    StrName = "C:\Users\swichman\Documents\Rosewich\RosewichData base\BackEnd\Rosewich_be.accdb"


    ' Set database variable to current database.


    Set dbsReport = DBEngine.Workspaces(0).OpenDatabase(StrName)


    Set qdf = dbsReport.QueryDefs(Me.RecordSource)
    For Each prm In qdf.Parameters
    prm.Value = Eval(prm.Name)
    Next prm


    ' Open Recordset object.
    Set rstReport = qdf.OpenRecordset()

    ' Set a variable to hold number of columns in crosstab query.
    intColumnCount = rstReport.Fields.Count

    End Sub

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    You want to set filter parameters of CROSSTAB query?

    Why do you need to look at backend to reference query? Queries should be in the frontend.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    swichman is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jan 2016
    Posts
    4
    I figured that as well but when I ran the report with my split database it did not work (ie the report contents came up empty). So i figured it was due to the seek command not working when the database split and no source data being available. So I changed the Set DbsReport database to the correct path on my computer. My original code for the report worked fine before the database was split. It is pasted below minus the variable definitions.
    Thanks!
    Sean

    ' Set database variable to current database.
    Set dbsReport = CurrentDb



    ' Open QueryDef object.
    ' Set qdf = dbsReport.QueryDefs("CrosstabQuery")

    Set qdf = dbsReport.QueryDefs(Me.RecordSource)
    For Each prm In qdf.Parameters
    prm.Value = Eval(prm.Name)
    Next prm


    ' Open Recordset object.
    Set rstReport = qdf.OpenRecordset()

    ' Set a variable to hold number of columns in crosstab query.
    intColumnCount = rstReport.Fields.Count

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Query must be in the frontend. Can't link to queries. So referencing backend to set query parameters makes no sense.

    Your original code should work.

    What is 'seek' command?

    Why Eval() function?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    swichman is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jan 2016
    Posts
    4
    It is actually some code I got online. Its purpose is to get the columns counted based on the amount of columns in the crosstab query and establish the column count in the report and then store that variable for later use (intColumnCount). There is some code later to populate unbound text boxes in the report header under the header format event procedure. I have included it below. I believe the eval function here is due to the fact that the columns counts in the crosstab are variable so it can deal this when creating the column count in the report (the name might be a string or an expression) and it turns the name into the value. I get an error 3241 if I take it out of the code data type mismatch.

    I do not know what is wrong and I realize that queries are in the front end obviously. The article that got me started on why I might be having problems is pasted below as well. It seems that the crosstab query is just coming back empty and the code doesnt function at all because the report just comes up with no data and empty text boxes so I figured the problem started with no data being available from the fact that the database variable is linked and not local.

    Any further help would be appreciated. I saw that there was another related post on formatting crosstab query reports in this forum so I will check that out. Cant Access just make this easier? There are so many applications where this would be handy...

    http://www.fmsinc.com/microsoftacces...ter/index.html

    Private Sub Detail1_Format(Cancel As Integer, FormatCount As Integer)
    ' Place values in text boxes and hide unused text boxes.

    Dim intX As Integer
    ' Verify that not at end of recordset.
    If Not rstReport.EOF Then
    ' If FormatCount is 1, place values from recordset into text boxes
    ' in detail section.
    If Me.FormatCount = 1 Then
    For intX = 1 To intColumnCount
    ' Convert Null values to 0.
    Me("Col" + Format(intX)) = xtabCnulls(rstReport(intX - 1))
    Next intX

    ' Hide unused text boxes in detail section.
    For intX = intColumnCount + 2 To conTotalColumns
    Me("Col" + Format(intX)).Visible = False
    Next intX


    ' Move to next record in recordset.
    rstReport.MoveNext
    End If
    End If


    End Sub

  6. #6
    swichman is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jan 2016
    Posts
    4
    Sorry. I just solved my own stupid problem.

    It was just how I was viewing the report.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 05-17-2017, 07:41 AM
  2. Replies: 13
    Last Post: 01-13-2016, 04:08 PM
  3. Replies: 13
    Last Post: 10-04-2015, 09:52 AM
  4. Replies: 5
    Last Post: 12-04-2012, 02:46 AM
  5. Create a recordset for a report
    By vjboaz in forum Programming
    Replies: 0
    Last Post: 11-18-2008, 03:28 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums