Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Location
    Michigan
    Posts
    44

    Unanswered: Report with SQL Query

    I have a search form, and based on what user selects to search on, it will run a query and then loads up the Report and then adjust the report results to the query results:

    Now, this works with Forms but it doesnt with Report, because with report when its loaded..its fixed!

    so I was wondering if anyone has a solution to this or away around it..
    here is partial of my code


    sSQL = "SELECT tblProcessInfo.*, tblPermInfo.LastName, tblPermInfo.Firstname FROM tblProcessInfo,tblPermInfo WHERE tblPermInfo.SID = tblProcessInfo.SID AND"
    sSQLOriginal = sSQL

    ' If specified Advisor
    If Not IsNothing(Me.rpAdvisor) Then
    ' .. build the predicate
    varWhere = "[Advisor] LIKE '" & Me.rpAdvisor & "*'"
    stCaption = stCaption & "Advisor = " & Me.rpAdvisor & ". "

    End If

    ' Do by Program Type
    If Not IsNothing(Me.rpProgType) Then
    ' .. build the predicate
    ' Note: taking advantage of Null propogation
    ' so we don't have to test for any previous predicate
    varWhere = (varWhere + " AND ") & "[ProgramType] LIKE '" & Me.rpProgType & "*'"
    stCaption = stCaption & "Program Type = " & Me.rpProgType & ". "



    sSQL = sSQL & varWhere


    DoCmd.OpenReport "rpProcess", acViewPreview

    Reports![rpProcess].Report.RecordSource = sSQL
    Reports![rpProcess].Report.Caption = stCaption



    now I know I can do
    strWhere = "[SID] = " & Forms![frmSearch]![SID]
    DoCmd.OpenReport "rpProcess", acViewPreview, , strWhere
    but then its not a query, and it only retrieves data from the form...which wont work in my case i guess..

    let me know what you guys think..

    thanks

  2. #2
    Join Date
    Apr 2004
    Location
    Michigan
    Posts
    44

    Unhappy hmm

    anyone?

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Create the datasource for the report in the On Open event for the report itself?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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