Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    One Report different Query at Run time (2k, XP, 03)

    This may seem dumb, and I should probably know the answer, but I shall ask anyway. Is there a way for vb to call a a report using different queries? I know you can set filter, but I want to use a completely different data set based on user's selection.
    More detail:
    I have two tables with same field names. I have one report with same field names. I can use different reports, I suppose, but that doesn't seem very efficient. In the following example is there a creative way to use a different query for the same report, instead of different reports?
    If Me.chkYes = True Then
    stDocName = "rptShippingRpt"
    DoCmd.OpenReport stDocName, acViewPreview, , , acWindowNormal
    Else
    stDocName = "rptShippingRpt2"
    DoCmd.OpenReport stDocName, acViewPreview, , , acWindowNormal
    End If

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: One Report different Query at Run time (2k, XP, 03)

    You can set the Record Source of the report in the On Open event of the report.

    Private Sub Report_Open(Cancel As Integer)
    If Forms!frmSomething!chkYes = True Then
    Me.RecordSource = "ThisTable"
    Else
    Me.RecordSource = "ThatTable"
    End If
    End Sub

    Change frmSomething to the name of your form; if it contains spaces or punctuation, put square brackets [ ] around the form name.
    Replace ThisTable and ThatTable with the appropriate table or query names.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: One Report different Query at Run time (2k, XP, 03)

    An even easier approach I've found is to set the datasource of the report to a named, saved query and then take your user's selections and pass parameters to the sql property of that query (or even just write on the fly sql for it). That way, you don't have to change the recordsource of the report at runtime, which sometimes can be problematic, and you only have one querydef object to maintain:

    dim db as dao.database, qdef as querydef
    set db=currentdb
    set qdef = db.querydefs("yourqueryname")
    qdef.sql = "your sql string here"
    db.close
    set qdef=nothing
    set db=nothing

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: One Report different Query at Run time (2k, XP

    Yet another option (that is not available in 2K) is to pass the name of the query to the report using OpenArgs:

    So on the form:
    stDocName = "rptShippingRpt"
    If Me.chkYes = True Then
    strQueryname = "query1"
    Else
    strqueryname =" query2"
    End If
    DoCmd.OpenReport stDocName, acViewPreview, , , acWindowNormal, strqueryname

    Then in the onopen event of the report
    Private Sub Report_Open(Cancel As Integer)
    If not isnull(me.OpenArgs) Then
    Me.RecordSource = me.openArgs

    End If
    End Sub

    This is useful if you have more than pathway for opening the report.
    Regards
    John



  5. #5
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: One Report different Query at Run time (2k, XP

    Hans, Kathryn, and John,

    THANKS! So far I have successfully accomplished the goal using the first two (Hans and Kathryn), and will test John's later today. Thanks for the excellent ideas!

    Ken

  6. #6
    Lounger
    Join Date
    Dec 2004
    Location
    Fort Collins, Colorado, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: One Report different Query at Run time (2k, XP, 03)

    Hmmm ... let's make this a little broader ! Hans, if the query is 'parameterized' with a question msgbox, for example [What store do you want ?] in the query criterion, why do I find bad recordsets (i'm counting records to post a count on the form) that compute Recordset.count = 1 ... I tried the clone and it's a mirror of the recordset.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: One Report different Query at Run time (2k, XP, 03)

    Count is not a property of a recordset - RecordCount is the one you want.

  8. #8
    Lounger
    Join Date
    Dec 2004
    Location
    Fort Collins, Colorado, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: One Report different Query at Run time (2k, XP, 03)

    I realize that, Hans, but for sake of brevity I skipped over alot of code [img]/forums/images/smilies/smile.gif[/img] I have an open form, I have a user select a query name in a CBO, I take that name (a string) and "on update" for the CBO I want to count potential records, as if, that 'parameterized' query were actually run ... I can place the query name in the form's RecordSource, and do a requery .. but I get 'iffy' results ... sometimes the correct count, else a "1" (that's non-sense)... it's flippin' me out. Regards

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: One Report different Query at Run time (2k, XP, 03)

    The RecordCount property of a recordset is not always dependable. You may have to perform a MoveLast before you get an accurate count.

  10. #10
    Lounger
    Join Date
    Dec 2004
    Location
    Fort Collins, Colorado, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: One Report different Query at Run time (2k, XP, 03)

    I hope you are appreciated ... I went too MS, in the meantime, to see if I could dig out the fault mechanism ... sure enough ..."Calling the Requery method of a form's recordset (for example, Forms(0).Recordset.Requery) can cause the form to become unbound. To refresh the data in a form bound to a recordset, set the RecordSource property of the form to itself (Forms(0).RecordSource = Forms(0).RecordSource)." ... That's their way of saying it's "unstable" ! LOL But the second sentence is interesting ... we learn something every day. Thanks Hans

  11. #11
    Lounger
    Join Date
    Dec 2004
    Location
    Fort Collins, Colorado, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: One Report different Query at Run time (2k, XP, 03)

    I changed the queries to "select" queries to get away from any "parameter' issues .. I am unable to het a correct recordset count ... one time it's 598, the next zero, maybe even a '1' ... it's all over the place. I've made sure to close the recordset between requests and set the rescordset object to nothing ... there's no consistency in this ??? Regards Wayne

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: One Report different Query at Run time (2k, XP, 03)

    Try using expressions with DCount instead of relying on the RecordCoount property of recordsets.

Posting Permissions

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