Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2012
    Posts
    6

    Unanswered: Need code to allow user to specify which field to search

    This is my first post so I hope I do it correctly. I have a membership database with a table of 30 committees members can serve on (each committee is a separate field). On the data entry form, a check box is clicked for each desired committee. I would like to have a text box where the user can enter the committee/field they want search for. Without this function, I have to create a separate query (and report) for each committee.

    I am a novice with VBA and SQL and can't figure out the code that allows user input to pass along a parameter.

  2. #2
    Join Date
    Jan 2012
    Posts
    97
    Hi there,

    Not 100% on what your looking for, but from what I can gather...
    1. On your form create a combo box - lets call it combo1 for e.g.
    2. Set the data source of this combo box to your 'table of 30 committees' making sure that the bound column is the column that you want to be used as the search criteria (name of the committee?)
    3. In the query, set the criteria of the field you want to search on (committee name? or committee ID? to forms!yourformname!combo1

    This means that what ever value has been selected from the combo box will be used as the criteria for the query.

    Please ignore this if this wasn't what your looking for, only I couldn't be certain from your post.
    Hope this helps,

    Dave.

    _____________

    Access 2000 User

  3. #3
    Join Date
    Feb 2012
    Posts
    6
    Thanks for your reply Dave. That is not quite what I need. I need the user's input to determine which column/field is searched for a particular criteria (and most searches are the opposite). My idea is to have a textbox on a form with a search command button. The user will enter the column/field (committee) to which they want to search. The code for the search button would then use the textbox data to determine which field in the Committee table to search for the criteria, which will always be "yes". Does this make any sense at all?

  4. #4
    Join Date
    Jan 2012
    Posts
    97
    I think I know what you mean, slightly more complicated though, anyway:

    The code below will create the SQL for an existing query (myquery) on the fly and pass the value collected from txtSearch (your textbox) via a variable into the SQL for the query.

    Search button code:

    Code:
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    Dim strSearch As String
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("myquery")
    strSearch = "mytable." & Me.txtSearch.Value
    MsgBox (strSearch)
    strSQL = "SELECT mytable.myfield, mytable.myfield1, mytable.myfield2, mytable.myfield3, mytable.myfield4 " & vbCrLf & _
    "FROM mytable " & vbCrLf & _
    "WHERE " & strSearch & " =""Yes"";"
    qdf.SQL = strSQL
    DoCmd.OpenQuery "myquery"
    Set db = Nothing
    Set qdf = Nothing
    Also ensure your References Microsoft Data Access Objects (DAO) 3.6 Object Library is selected. In VBA window -> Tools -> References
    Hope this helps,

    Dave.

    _____________

    Access 2000 User

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    This might be of some use to you: http://www.dbforums.com/microsoft-ac...ml#post6329513
    George
    Home | Blog

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    What you have here is a database design that violates First Normal Form. You need to create a "Committees" table, and a "CommitteeMembership" table. The former will hold the details of the committees, the latter will hold the IDs of the committees and their members.

    Your query will then be much easier to write.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #7
    Join Date
    Feb 2012
    Posts
    6
    I have a Membership table and a Committees table linked by a unique ID #. Where am I going wrong?

  8. #8
    Join Date
    Feb 2012
    Posts
    6
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    Dim strSearch As String
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("myquery")
    strSearch = "mytable." & Me.txtSearch.Value
    MsgBox (strSearch)
    strSQL = "SELECT mytable.myfield, mytable.myfield1, mytable.myfield2, mytable.myfield3, mytable.myfield4 " & vbCrLf & _
    "FROM mytable " & vbCrLf & _
    "WHERE " & strSearch & " =""Yes"";"
    qdf.SQL = strSQL
    DoCmd.OpenQuery "myquery"
    Set db = Nothing
    Set qdf = Nothing

    Thanks again Dave for attempting to solve my delima. The debugger stops on the "DoCmd.OpenQuery "myquery" line. I checked to make 100% sure I had the correct query name, which I did. Also, when I select Microsoft Data Access Objects (DAO) 3.6 Object Library I get the error message "Name conflicts with existing module, project or object library". Sorry to be such a pain.

  9. #9
    Join Date
    Jan 2012
    Posts
    97
    As for your error message - see here XL2000: Error Message: Name Conflicts with Existing Module, Project, or Object Library

    Am puzzled as to why the code bugs out on this line. I have attached a copy that is working on my machine & I can't recreate your error. If your certain of the name I'm stumped as to why it is bugging out here?
    Attached Files Attached Files
    Hope this helps,

    Dave.

    _____________

    Access 2000 User

  10. #10
    Join Date
    Feb 2012
    Posts
    6
    Dave, I figured out what the problem is. My Committees table fields are formatted Yes/No - Checkboxes. So Access is looking for the constant True, not the word Yes. (I did it this way to keep it simple because this db will be used by senior ladies, many of which are computer challenged). Can this be fixed in your code or would it be best for me to just change the formatting on the fields to Text. THANKS for all your help!

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by SLL5150 View Post
    Dave, I figured out what the problem is. My Committees table fields are formatted Yes/No - Checkboxes. So Access is looking for the constant True, not the word Yes. (I did it this way to keep it simple because this db will be used by senior ladies, many of which are computer challenged). Can this be fixed in your code or would it be best for me to just change the formatting on the fields to Text. THANKS for all your help!
    strictly speaking thats wrong
    checkboxes are to do with the presentation of data, not the actual storage type. its like saying date columns are defined as mm/dd/yyyy (as god intended), whereas in reality inside Access JET they are stored as a decimal number indicating the number fo days since an arbitary point in time.

    checkbox fields are mapped as boolean, which depeding on the implementation is usually a small integer. inside your VBA code you should be comparing to vbtrue or vbfalse
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Feb 2012
    Posts
    6
    Quote Originally Posted by dave0810471 View Post
    As for your error message - see here XL2000: Error Message: Name Conflicts with Existing Module, Project, or Object Library

    Am puzzled as to why the code bugs out on this line. I have attached a copy that is working on my machine & I can't recreate your error. If your certain of the name I'm stumped as to why it is bugging out here?
    I went ahead and changed the format of my table. Your code works now! Thank you for all your help with my poorly designed database.

    Steve

  13. #13
    Join Date
    Jan 2012
    Posts
    97
    Your welcome.
    Hope this helps,

    Dave.

    _____________

    Access 2000 User

Posting Permissions

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