Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2008
    Posts
    74

    Exclamation Unanswered: Option Group & Comboboxes on a search form: Object Required Error!

    Hi folks!
    I'm having a Search Form in Access 2003 Database. Its purpose is to search for work requests depending on submitted date and request status.
    The form has following controls:

    - two comboboxes called cboFrom and cboTill, where the user can pick the date range from
    - Option Group with 4 radio buttons standing for request status.
    - subform based on a query where results are being displayed
    - button that triggeres the VB-code

    Whenever I try and run the code, it gives me an "Object Required" error. I don't even know if the SQL criteria works, and my piece of coding for the Option Group!!

    Code:
    Private Sub Command31_Click()
    
    
    Dim Criteria As String
    Dim Q As QueryDef
    Dim DB As Database
    Dim frm As Form, ctl As Control, sfrm As Form
    Dim strSQL As String
    Dim Date1, Date2
    
    
    Set Date1 = Me!cboFrom.Value
    Set Date2 = Me!cboTill.Value
    
    'error comes in this line:
    Set Criteria = "BETWEEN" & Date1 & "AND" & Date2 & ""
    Debug.Print Criteria
    
    Select Case Me!OptGroup
    
    Case 1
       Criteria1 = Chr(34) & "Request Denied" & Chr(34)
    Case 2
       Criteria1 = Chr(34) & "Request Implemented" & Chr(34)
    Case 3
       Criteria1 = Chr(34) & "Request Cancelled" & Chr(34)
    Case 4
       Criteria1 = Chr(34) & "Is Not Null" & Chr(34)
    
    End Select
    
    Set DB = CurrentDb()
    Set Q = DB.QueryDefs("qry_AllRequestsSearch")
    
    Q.SQL = "SELECT tbl_RequestTracker.[Request Source], " & _
                   "tbl_RequestTracker.[Date Submitted]," & _
                   "tbl_RequestTracker.ReqNr, tbl_RequestTracker.[Brief Description]," & _
                    "tbl_RequestTracker.[Requestor Given Name], tbl_RequestTracker.[Requestor Surname]," & _
                    "tbl_RequestTracker.[Current Request Status], tbl_RequestTracker.Estimator," & _
                    "tbl_RequestTracker.[Originator Cost Centre]" & _
                     "FROM tbl_RequestTracker WHERE [Date Submitted]" & _
                     "IN(" & Criteria & ") AND [Current Request Status]" & _
                     "IN(" & Criteria1 & ");"
                     
                     
    Q.Close
    
    
    Set frm = Forms("FrmAllRequestsSearch")
    Set ctl = frm.Controls("SubFrmAllRequestsSearch")
    Set sfrm = ctl.Form
    sfrm.Requery
                     
    
    End Sub
    Any suggestions are more than welcome!!

    Best,
    OfficeDummy
    Last edited by OfficeDummy; 06-24-08 at 14:37.

  2. #2
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151

    Just some obvious ones to check....

    First I would dim the date variables as dates

    Set Criteria = "BETWEEN" & Date1 & "AND" & Date2 & ""

    It looks like the 'Criteria' string would come out something like;
    "BETWEEN6/24/2006AND6/25/2006 >>assuming those were the dates, that is

    I think it should be something like this;

    Set Criteria = "Between #" & Date1 & "# And #" & Date2 & "# "

    Also I would change the sql string

    Case 1
    Criteria1 = Chr(34) & "Request Denied" & Chr(34)
    Criteria1 = "= 'Request Denied'"
    Case 2
    Criteria1 = Chr(34) & "Request Implemented" & Chr(34)
    Criteria1 = "= 'Request Implemented'"
    Case 3
    Criteria1 = Chr(34) & "Request Cancelled" & Chr(34)
    Criteria1 = "= 'Request Cancelled'"
    Case 4
    Criteria1 = Chr(34) & "Is Not Null" & Chr(34)
    Criteria1 = "'Is Not Null'"


    "FROM tbl_RequestTracker WHERE [Date Submitted] " & _
    Criteria & " and [Current Request Status] " & _
    Criteria1

    Also, make sure you leave a space on the end of your sql lines before the " & _ or at the beginning of the next one " ......." & _ You did on some and not on others.

    Hope that last part makes sense!
    Good Luck!

    Stu
    --If its free, take it for what its worth!

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Which line is generating the error?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Jan 2008
    Posts
    74
    Thanks, all!

    I modified the code slightly, and it works!

    But I can't get the subform to display the query results.
    My form is isn't based on anything, but my subform is based on the query "qry_AllRequestsSearch". The VB-Code executes this query as SQL-String, and it works fine. It just won't show on the subform.

    My form is called "FrmAllRequestsSearch", the subform is called "SubFrmAllRequestsSearch".

    Does someone have any suggestions?

    Code:
    Dim Criteria As String
    Dim Q As QueryDef
    Dim DB As Database
    Dim frm As Form, ctl As Control, sfrm As Form
    Dim Date1, Date2 As Date
    
    
    Date1 = Me!cboFrom.Value
    Date2 = Me!cboTill.Value
    
    Criteria = "BETWEEN #" & Date1 & "# AND #" & Date2 & "#"
    
    
    Select Case Me!OptGroup
    
    Case 1
       Criteria1 = Chr(34) & "Request Denied" & Chr(34)
    Case 2
       Criteria1 = Chr(34) & "Request Implemented" & Chr(34)
    Case 3
       Criteria1 = Chr(34) & "Request Cancelled" & Chr(34)
    Case 4
       Criteria1 = Chr(34) & "Is Not Null" & Chr(34)
    
    End Select
    
    Set DB = CurrentDb()
    Set Q = DB.QueryDefs("qry_AllRequestsSearch")
    
    Q.SQL = "SELECT tbl_RequestTracker.[Request Source], " & _
                   "tbl_RequestTracker.[Date Submitted], " & _
                   "tbl_RequestTracker.ReqNr, tbl_RequestTracker.[Brief Description], " & _
                    "tbl_RequestTracker.[Requestor Given Name], tbl_RequestTracker.[Requestor Surname], " & _
                    "tbl_RequestTracker.[Current Request Status], tbl_RequestTracker.Estimator, " & _
                    "tbl_RequestTracker.[Originator Cost Centre] " & _
                     "FROM tbl_RequestTracker WHERE tbl_RequestTracker.[Date Submitted] " & _
                     "" & Criteria & " AND tbl_RequestTracker.[Current Request Status] = " & _
                     "" & Criteria1 & ";"
                     
                     
    Q.Close
    
    
    Set frm = Forms("FrmAllRequestsSearch")
    Set ctl = frm.Controls("SubFrmAllRequestsSearch")
    Set sfrm = ctl.Form
    sfrm.Requery
    Thanks a lot.

    Best,
    OD
    Last edited by OfficeDummy; 06-25-08 at 11:40.

  5. #5
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151

    Not sure....

    Hi,

    I'm not sure why the subform isn't showing the value, but I think if you end up with a Case 4, your query will bomb.

    Case 4
    Criteria1 = Chr(34) & "Is Not Null" & Chr(34)
    End Select

    "FROM tbl_RequestTracker WHERE tbl_RequestTracker.[Date Submitted] " & _
    "" & Criteria & " AND tbl_RequestTracker.[Current Request Status] = " & _
    "" & Criteria1 & ";"

    If Case 4 is valid then the final part of your string will read something like;
    AND tbl_RequestTracker.[Current Request Status] = Is Not Null;

    That won't work.

    :-)Stu
    --If its free, take it for what its worth!

  6. #6
    Join Date
    Jan 2008
    Posts
    74
    Hi,
    Case 4 is actually when there is no criteria, i.e. show all records. I'll try to come up with something different...

    Right now, my problem is why the subform isn't showing the records! According to my VB knowledge, it really should...

    Any suggestions? Getting really desperate here!

  7. #7
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151

    Maybe....

    It doesn't look like to me that the query definition to the subform's recordsource is getting updated. I'm a little weak on doing it this way but it looks like you never set the actual query defs of qry_AllRequestsSearch to the SQL that you've created. You need to redefine the query definition of qry_AllRequestsSearch with Q.SQL


    Hope this helps!
    --If its free, take it for what its worth!

  8. #8
    Join Date
    Jan 2008
    Posts
    74
    Thanks, stuschmied!
    But how can it be done??

  9. #9
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151

    Could it be this simple?

    I just tried to dupe what is going on in a simple mdb I'm playing with. Try commenting out the Q.Close command.

    I think this might make it work for you. Of course the actual SQL code behind that query is now set to the SQL string you just created and will remain that way until the module is run again.

    :-)Stu
    --If its free, take it for what its worth!

Posting Permissions

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