Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2005
    Posts
    79

    Unhappy Unanswered: QueryDef - Defeated/Deflated

    Hello,

    I have searched for the last few days pretty much everywhere and have made attempt after attempt to code, only to erase code, code, erase code, etc. After about 3 days, I feel defeated, deflated, and just very frustrated.

    I am trying to utilize one form. I have multiple queries already configured for this form. The additional queries are simply based on a user's Division and a report of a user requirement for training; 30 days out, 60 days out, 90 days out, and greater than 90 days but less than 120 days.

    I know that I am unable to change the recordsource for my report after it has loaded and that the change would need to take place in the report's open event (using QueryDef seems what would be the best option best on my research; unsure).

    I have a form (frmGpReports) with a combobox (ComboReport) and a command button (cmdSubmit). After a selection has been made and the command button (cmdSubmit) has been pressed. I open up the report (rptGroupReports) using the following command:

    Code:
    DoCmd.OpenReport "rptGroupReports", acViewPreview
    I am trying to open up the report based on whether the combo box from the other form says; "Within 60 Days" or "Within 90 Days", etc. On the Reports (rptGroupReports) open event I have the following. NOTE: I have literally torn the code out so many times that I cannot count. strReportHeader is an OpenArgs that passes a report title from the other form.

    Code:
    Dim db As DAO.Database
        Dim qdf As DAO.QueryDef
        Dim strSQL As String
        Set db = CurrentDb
        Set qdf = db.QueryDefs("qryGpAllDue")
        qdf.SQL = "SELECT * FROM tblEmployees;"
        'strSQL = "SELECT * FROM Employees WHERE ([dtClassRecertification]='Between Now() And Now()+90')"
         
         DoCmd.OpenReport "rptGroupDue", acViewReport, strSQL, , , strReportHeader
    Any help would be greatly appreciated. Signed, Frustrated, but determined
    Last edited by miracleblake; 08-30-13 at 15:50.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I probably wouldn't bother with a QueryDef for this. If the differences are simply in the date range, I'd let the report return all records and then use this method to restrict it:

    Open a second form to the record

    So you would build the wherecondition in your code, along the lines of:

    strWhere = "[dtClassRecertification] Between Now() And Now()+90"

    DoCmd.OpenReport "rptGroupDue", acViewReport, , strWhere , , strReportHeader
    Paul

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Or just apply a filter / remove afilter
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Nov 2005
    Posts
    79

    Smile Thank you

    Thank you for your extremely fast responses. I read what I wrote and I think the mispellings may denote my frame of mind. I went through and corrected what I could. Maybe my post along with your solutions may hope another deflated soul.

    I will try the first option. As I on a second screen and am unable to give proper credit to each name; can the second optioner possibly post code in regards to the filter on;filter off. I have tried that as well, with no luck which has alwo added to my frustration.

    Thanking you in advance. Trying to get to a happy place.

  5. #5
    Join Date
    Nov 2005
    Posts
    79

    Smile Pie On My Face

    I can not believe and I am in total shock on how simply easy this was. I do not know if I was experiencing a bad 24 hours, but the solution was so very simple. I am posting my final solution, which I simply attached to the submit button on the main Group printing form. The "Where" which I kept seeing in numerous postings continued to throw me off because I kept seeing references to SQL "SELECT...." statements. What I now know is that the strWhere is only a Filter, nothing more.

    Code:
    Private Sub cmdSubmit_Click()
    
    Dim strWhere As String
    strWhere = "[Division] = 'Human Resources'"
    DoCmd.OpenReport "rptGroupDue", acViewReport, , strWhere, , strReportHeader
    
    
    End Sub
    Thank you again.

    Quote Originally Posted by pbaldy View Post
    I probably wouldn't bother with a QueryDef for this. If the differences are simply in the date range, I'd let the report return all records and then use this method to restrict it:

    Open a second form to the record

    So you would build the wherecondition in your code, along the lines of:

    strWhere = "[dtClassRecertification] Between Now() And Now()+90"

    DoCmd.OpenReport "rptGroupDue", acViewReport, , strWhere , , strReportHeader

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Happy to help!
    Paul

Posting Permissions

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