Results 1 to 5 of 5
  1. #1
    kid is offline Novice
    Windows 10 Access 2007
    Join Date
    Jan 2020
    Posts
    20

    Report Query from VBA for mulitple table same structure

    I linked my access with excel sheets. So I have more than ten tables in access in same structure.


    And I designed a form with a button and a combobox in which loads all the table name by using the following query

    Code:
    SELECT MSysObjects.Name
    FROM MSysObjects
    WHERE (((MSysObjects.Type)=1 Or (MSysObjects.Type)=6) AND ((Left([Name],4))<>'MSys'))
    ORDER BY MSysObjects.Name;
    I designed a common report. Now I want load the report on button click , based on the combobox selected table data.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Unfortunately that is not the way to do it. You should just have the one table with an additional field to indicate the source (equivalent of your table names), then you can just use the filter parameter for docmd.openreport

    You can achieve this using a union query to combine all the tables into one, including your source field, have your report use the union query and use a filter on the source field. But it is not a particularly efficient way of doing it.

    The only other way I can think that might work is to pass the table name as an openarg then have some code in the report open event to change the report recordsource i.e.

    me.recordsource=me.openargs

    but I've not tested it so you'll have to try it and see.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I saw something like this a few month ago while browsing for some code.

    It was something like

    Report record source is a saved query names "qryReport1"
    you have the combo box (I'll call it "cboTableSelect")
    button click to open report then
    sSQL = "Select Field1, Vield2, Field3 FROM " & Me.cboTableSelect
    "qryReport1" SQL is modified by saving the sSQL variable to "qryReport1" query def
    Report is opened. (sorting and grouping is done in report). Filtering can be done using the docmd.OpenReport WHERE option


    Found something... something like this
    Code:
    Option Compare Database
    Option Explicit
    
    Public Sub btnReport_Click()
        Dim db As DAO.Database
        Dim qdf As DAO.QueryDef
        Dim sSQL As String
    
        Set db = CurrentDb()
        Set qdf = db.QueryDefs("qryReport1")
    
        sSQL = "Select Field1, Vield2, Field3 FROM " & Me.cboTableSelect
        qdf.SQL = sSQL
    
        DoCmd.OpenReport "Report1", acViewPreview, , "Field3 = 'ABC'"  'WHERE filter Field3 = 'ABC'
    
        Set qdf = Nothing
        Set db = Nothing
    End Sub
    Warning: untested code!!

  4. #4
    kid is offline Novice
    Windows 10 Access 2007
    Join Date
    Jan 2020
    Posts
    20
    Looks like, near to solution... I will check and inform... Thank you guys

  5. #5
    kid is offline Novice
    Windows 10 Access 2007
    Join Date
    Jan 2020
    Posts
    20
    Thank You ssanfu. Your findings works fine

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

Similar Threads

  1. Replies: 8
    Last Post: 03-07-2015, 08:25 PM
  2. Help with mulitple count fields in one query
    By SDCragg in forum Queries
    Replies: 1
    Last Post: 03-01-2015, 02:01 AM
  3. Replies: 3
    Last Post: 01-31-2013, 01:03 PM
  4. Mulitple Group By in Query
    By rooster in forum Queries
    Replies: 4
    Last Post: 08-25-2012, 04:58 PM
  5. Query Can it be Done? Mulitple finds
    By Canadiangal in forum Queries
    Replies: 3
    Last Post: 02-28-2010, 03:45 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