Results 1 to 8 of 8
  1. #1
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496

    report data source at runtime

    if i could get a bit of direction plz
    it ought to be simple, yet somehow, i've never had to, and i'm stuck

    have a report, query as its data source; click the report on its own and it runs properly
    call the report with the following code, and it behaves like it has no data at all (it ought to)
    ( [ID] is an integer )

    Code:
        gsDocName = "rptSubmtl"
        gsSQL = "qry_rptSubmtl.ID = " & Me.cboSubmtList
        DoCmd.OpenReport gsDocName, acPreview, , gsSQL
    dblclick the query, and it yields data, and the alias [ID] is there; and in particular, data for the specific [ID]

    as always, my appreciation and thnx in advance,
    m.

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    I think you need to reference the combo box from within the query. Something like:
    Forms![NameOfYourForm]!cmbSubmtList
    assuming that cmbSubmtList is the name of a control on your form.
    Set the Record Source of thr report to the name of the query. Then you would only need:
    DoCmd.OpenReport "[NameOfYourReport]", acViewPreview
    to open the report.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Try justID=me.cboSubmtList
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    To use the report filter parameter, create a WHERE condition (without the use of the word WHERE) but use a report field name. The method loads the report with records that are filtered according to the constraints you put on the report field(s):

    DoCmd.OpenReport gsDocName, acPreview, , reportFieldName = Me.cboSubmtList

    You must use proper delimiters if the value being passed by the combo is a date or is text (not if a number). Also need to ensure that you use the correct combo column if there is more than one.

    If you'd rather modify the report recordsource, you can do that too - after you open it. Or you can modify the sql of the query that the report is based on. Many ways to skin this cat, but I'm getting to methods that are much more complicated and not really necessary!
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496
    advice are spot-on and appreciated.
    turns out to only be part of the problem:

    in the query, there are several joined tables.
    the issue seems to be when there is (correctly) no record in one table to link it to another, and so on... the query returns empty.

    any suggestions on how to deal with this?

    with appreciation, as always, in advance,
    m.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You could start with what you think is the primary table and build a query that provides results. Then add 1 table at a time and test different joins. When it stops returning any records, you have found the part where there is no relationship to what was there before. If modifying joins doesn't solve the problem, sometimes you need to build a query that takes care of that issue, and often that is a Union query. IMO, union queries are often a sign that tables have not been properly designed so if you want an opinion on that, do post a zipped copy of your db as suggested.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496
    shortly after all of the below... I figured it out (YEAH). Thank you everyone for your assists.
    (if you want to further comment on my mess below, feel free to, but no need to; thanks yet again)



    soon as i started reading that last post, i thought: "o jeez... relationships; what i have never understood, never gotten right; well eventually i got one right (24yrs right) - I digress"

    here's what i got...
    and i'm certain its not...
    --> the goal is to provide a list of all the FixtureTag within a specific submittalID with related detail

    (tbeSubmittalDocs is included, yet could be pulled out (should be pulled..) and the info put on a subRpt since although there typically is only 1 record per SubmittalID - the table was set up b/c there 'could' be more -so duh...)

    Click image for larger version. 

Name:	qry_rptSubmtl_2022-02-23_20-59-51.jpg 
Views:	9 
Size:	219.2 KB 
ID:	47318



    SELECT tbeSubmittal.submittalID AS ID, tbeFixtureTypeDetails.Type AS FixtTag, tbeSubmittal.*, tbeSubmittalDetails.*, tbeSubmittalDocs.*, tblSubmittalActions.*, tbeFixtureTypeDetails.Manufacturer, tbeFixtureTypeDetails.SampleRequiredYN, tbeFixtureTypeDetails.SampleType, tbeSubmittal.TransmittalNo, tbeSubmittalDetails.*
    FROM tbeFixtureTypeDetails INNER JOIN ((tbeSubmittal INNER JOIN (tbeSubmittalDetails INNER JOIN tblSubmittalActions ON tbeSubmittalDetails.Action = tblSubmittalActions.SubmittalAction) ON tbeSubmittal.SubmittalID = tbeSubmittalDetails.SubmittalID) INNER JOIN tbeSubmittalDocs ON tbeSubmittal.SubmittalID = tbeSubmittalDocs.SubmittalID) ON tbeFixtureTypeDetails.Type = tbeSubmittalDetails.Type;

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

Similar Threads

  1. Replies: 2
    Last Post: 03-04-2019, 01:44 PM
  2. Use 2 Queries As Data Source For Report
    By chalupabatman in forum Reports
    Replies: 2
    Last Post: 01-03-2019, 12:32 PM
  3. chaging a subreport's source object at runtime
    By markjkubicki in forum Reports
    Replies: 4
    Last Post: 12-15-2015, 11:16 AM
  4. Replies: 2
    Last Post: 08-28-2015, 06:26 PM
  5. Replies: 2
    Last Post: 01-15-2015, 01:10 AM

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