Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Apr 2012
    Posts
    16

    Unanswered: Combobox as a table field input to a query

    Hi,

    I am trying to make query that can search multiple fields in a single table based on a selection from a form combo box that has a list of field titles.

    Any help would be greatly appreciated!

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

    You would need to create your query, let's say qryyourname. Then create the query on the fly by running code from the OnClick event of a command button or from the AfterUpdate event of the combo box.

    You need to ensure that you have Microsoft DAO 3.6 Object Library selected as a reference in VBA. To do this Tools -> Macro -> Visual Basic Editor -> Tools -> References.

    Code:
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    Dim strVal As String
    
    strVal = Me.yourcontrolname.Value
    
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("yourqueryname")
    
    'Create query on the fly
    strSQL = "SELECT [tblyourtablename].[" & strVal & "] " & vbCrLf & _
    "FROM tblName;"
    
    qdf.SQL = strSQL
    
    DoCmd.OpenQuery "qryyourqueryname"
    
    Set db = Nothing
    Set qdf = Nothing
    Hope this helps,

    Dave.

    _____________

    Access 2000 User

  3. #3
    Join Date
    Apr 2012
    Posts
    16
    Dave,

    I think this is the right track. What is the proper syntax to add Group By and Having to the 'create query in the fly code. I am doing a wildcard "like" search in the fields.

    Thanks!

  4. #4
    Join Date
    Jan 2012
    Posts
    97
    SELECT tblName.FieldName
    FROM tblName
    GROUP BY tblName.FieldName
    HAVING (((tblName.FieldName) Like "yourcriteria*"));
    Hope this helps,

    Dave.

    _____________

    Access 2000 User

  5. #5
    Join Date
    Apr 2012
    Posts
    16
    Thanks for your time Dave! I was actually referring to the DAO VBA code to add the group by and having to the VBA code you posted. The query created by the vba code does not have any critera and shows all records in the field. The syntax for the SQL string in vba is different than if I just added it to the SQL view in the query.

    Thanks for your patience with this as I am still somewhat of an access rookie.

    Thanks!

  6. #6
    Join Date
    Jan 2012
    Posts
    97
    strSql = "SELECT tblName.FieldName " & vbCrLf & _
    "FROM tblName " & vbCrLf & _
    "GROUP BY tblName.FieldName " & vbCrLf & _
    "HAVING (((tblName.FieldName) Like ""yourcriteria*""));"

    Hopefully this is what you mean, you just need to change to suit your needs
    Hope this helps,

    Dave.

    _____________

    Access 2000 User

  7. #7
    Join Date
    Apr 2012
    Posts
    16
    Thanks Again Dave for your responses. I am still having syntax issues when incorporating the group by and having with your first post. Your first post seems like it's on the right track, but it modifies the query and removes all the in the imposed criteria.

    My queries draw from a text box. The purpose of the combo box is to select different fields within the table. I would like to develop one set of queries, and be able to run them on any field within the table using the combo box drop-down selection for the field.

  8. #8
    Join Date
    Jan 2012
    Posts
    97
    The code in the first post is intended to modify the SQL of a query as that is what you are looking to do. You need to modify the strSQL so that your SQL is run every time. The code posted initially simply demonstrates how to use the value selected from a combo box as the parameter for the field name. You need to copy your static SQL (i.e. the SQL from the query you want to see each time with your 'imposed criteria' and then replace any occurence of static field names with the variable from the combo box

    SELECT [tblFirstTable].[" & strVal & "]

    This way your query stays identical but for the variable strVal which changes as per user selections from the combo box. I can't write your SQL for you as I have no idea what your fields are or what your trying to achieve, all I know is that you want to vary the field name.
    Hope this helps,

    Dave.

    _____________

    Access 2000 User

  9. #9
    Join Date
    Apr 2012
    Posts
    16
    I think this is almost complete. A few minor bugs...
    __________________________________________________ _______________

    Private Sub ComboPA_AfterUpdate()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    Dim strVal As String

    strVal = Me.ComboPA.Value

    Set db = CurrentDb()
    Set qdf = db.QueryDefs("testqry")

    strSQL = "SELECT [Table1].[" & strVal & "] " & vbCrLf & _
    "FROM Table1;" & vbCrLf & _
    "GROUP BY [Table1].[" & strVal & "] " & vbCrLf & _
    "HAVING ((([Table1].[" & strVal & "]) Like "[Forms].[Form1].[Text1] & "*""));"

    qdf.SQL = strSQL

    DoCmd.OpenQuery "testqry"

    Set db = Nothing
    Set qdf = Nothing
    End Sub

    __________________________________________________ ________

    1) qdf.SQL = strSQL is giving:

    run-time error 3136
    "The LEVEL clause includes a reserved or argument that is misspelled or missing or the punctuation is incorrect."

    2) "HAVING ((([Table1].[" & strVal & "]) Like "[Forms].[Form1].[Text1]& "*""));"

    I dont think it like my critera coming from a textbox. After I update the critera, this line gives me a syntax error

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    examine the SQL you are actually sending is usually a second step if you get these sort of errors
    if you knwo there is nothign wrong with the syntax in VB then its often the SQL

    in this case

    2) "HAVING ((([Table1].[" & strVal & "]) Like "[Forms].[Form1].[Text1]& "*""));"
    probably shuld be

    2) "HAVING Table1.[" & strVal & "] Like '" & [Forms].[Form1].[Text1] & "*';"

    if you display the SQL using say a message box or examine the code line by line, element by element in the debug window. the advantage of the debug window is that you can alter code, then re run the code from any point (often making changes then restting the code to the entry point works
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Apr 2012
    Posts
    16
    Thanks healdem,

    I modified this line, but it still has a problem:

    "HAVING Table1.[" & strVal & "] Like '" & [Forms].[Form1].[Text1] & "*';"

    (Error 438): Object doesn't support this property or method

    My goal for the form, is to have a combo box that lists all the fields(columns) in a table. When the combo selection is made, a wildcard search can be made to locate items in that field using a textbox, a subform, and a search button. I am not sure if the way I am going is the easiest way to get this accomplished.

    Thanks for your help!

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what is the actual SQL you are using. not the VBA code but the SQL being sent to the storage engine. ie the value of strSQL
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Apr 2012
    Posts
    16
    Here is an example of the SQL I am trying to use.
    You can see, I was initially trying to use a combo box to input the field selection of a table within an access query. I have not figured out how to make that work, so I started down the path of modifiying a query of the fly within VBA using ADO. I would prefer to just use a simple access query, but not sure if it is possible to do what I am asking.

    Query SQL View (Not working):
    SELECT Table1.[Forms].[Form1].[Combo1]
    FROM Table1
    GROUP BY Table1.[Forms].[Form1].[Combo1], [Forms].[Form1].[Text1],
    HAVING (((Table1.[Forms].[Form1].[Combo1]) Like Forms!Form1!Text1 & "*") And (Not (Forms!Form1!Text1) Is Null));

    I have tried MANY different ways to get the combo in the field column of the query, but access keeps trying to change it to an expression.

    Thanks in advance...

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    no
    what is the SQL you are actuall sending, the actual value of strSQL.

    either set a break point and examine the variable or display it as a msgbox

    the fragment
    [Forms].[Form1].[Text1]
    may be better phrased as
    [Forms]![Form1]![Text1]
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    Join Date
    Apr 2012
    Posts
    16
    _______________________________________
    Private Sub ComboPA_AfterUpdate()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    Dim strVal As String

    strVal = Me.ComboPA.Value

    Set db = CurrentDb()
    Set qdf = db.QueryDefs("testqry")

    strSQL = "SELECT [Table1].[" & strVal & "] " & vbCrLf & _
    "FROM Table1;" & vbCrLf & _
    "GROUP BY [Table1].[" & strVal & "] " & vbCrLf & _
    "HAVING Table1.[" & strVal & "] Like '" & [Forms]![Form1]![Text1] & "*"

    MsgBox (strSQL)

    qdf.SQL = strSQL

    DoCmd.OpenQuery "testqry"

    Set db = Nothing
    Set qdf = Nothing
    End Sub
    _______________________________________

    With an "a" in the textbox and "Orders" in the combobox, The Msgbox returned:
    ______________________________
    SELECT [Table1].[Orders]
    FROM Table1;
    GROUP BY [Table1].[Orders]
    HAVING Table1.[Table1] Like 'a*
    ______________________________

    Then I get: run-time error 3136 when it hits:

    qdf.SQL = strSQL


    "The LEVEL clause includes a reserved or argument that is misspelled or missing or the punctuation is incorrect."
    Last edited by WildBB; 04-22-12 at 21:04. Reason: Mistake

Posting Permissions

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