Page 1 of 3 123 LastLast
Results 1 to 15 of 39
  1. #1
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Create a query from listbox selections (Access 2002)

    I have two parts to my question, the first part being the most important, as I'm sure it will give me a gentle shove in the right direction:

    1. I have a form with a list box on it that displays the columns of my largest table. I want my users to be able to select multiple values from that list box, click ok, and a query will be made (in read-only) displaying only those columns selected.

    2. Compounding on top of problem 1, I want a selection from another list box that is linked to one of the columns in that table to apply a filter to the query that is created.

    I have searched high and low, and all I've found was using the list box or combo box to apply a filter to an existing query. I want this to be a 1-time use type of thing.

    Thanks!
    Jeremy
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Create a query from listbox selections (Access 2002)

    What specifically do you need help with? A multiselect listbox can be populated with a fields list, but you have to write code to generate the query SQL using the selected fields. You'll find various threads with code samples if you search on multiselect. I don't understand the second question.
    Charlotte

  3. #3
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a query from listbox selections (Access 2002)

    No Ma'am, my listbox is already populated. I want my users to select multiple fields, and the query that is created to only show those selected fields. The second question is in addition to the query creation. Please ignore part 2 until I can get part 1 working...part 2 is adding a filter to the query created...
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Create a query from listbox selections (Access 2002)

    Yes, you said that. But what are you asking for help with, writing the code to handle the multiselect, creating the query in code, making the query read-only, or what?
    Charlotte

  5. #5
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a query from listbox selections (Access 2002)

    Hans, after I select a value it comes up with a prompt to insert criteria for fields (expr 1; expr 2; etc)...and if I continue to click 'ok' it opens the query with fields 1, 2, 3, etc, instead of the columns I selected. I have DAO 3.6 Object Library set as a reference, and I've followed the naming convention to a 'T'.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Create a query from listbox selections (Access 2002)

    Can you provide information about your list box? What is its row source, number of columns etc.?

  7. #7
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a query from listbox selections (Access 2002)

    Row Source Type = Field List
    Row Source = tblPersonnel
    Column Count = 1
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Create a query from listbox selections (Access 2002)

    Oops, sorry, my mistake. Trying to do too many things at the same time. Replace

    strSQL = strSQL & ", [" & varItem & "]"

    with

    strSQL = strSQL & ", [" & Me.lbxFields.ItemData(varItem) & "]"

    where lbxFields is the name of the list box.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Create a query from listbox selections (Access 2002)

    Added: the line

    strSQL = strSQL & ", [" & varItem & "]"

    should be

    strSQL = strSQL & ", [" & Me.lbxFields.ItemData(varItem) & "]"


    1. You can assemble an SQL string in code, then set the SQL of a query to this string. You must set a reference to the Microsoft DAO 3.6 Object Library in Tools | References... in the Visual Basic Editor. The following assumes that you have a list box lbxFields and a command button cmdSomething. The table is named tblSomething and you have already created a query qryMyQuery.

    Private Sub cmdSomething_Click()
    Dim varItem As Variant
    Dim strSQL As String

    If Me.lbxFields.ItemsSelected.Count = 0 Then
    MsgBox "Please select one or more fields.", vbExclamation
    Me.lbxFields.SetFocus
    Exit Sub

    For Each varItem In Me.lbxFields.ItemsSelected
    strSQL = strSQL & ", [" & varItem & "]"
    Next varItem

    strSQL = "SELECT " & Mid(strSQL, 3) & " FROM tblSomething"

    CurrentDb.QueryDefs("qryMyQuery").SQL = strSQL
    ' code to do something with the query goes here.
    End Sub

    2. You can expand the above code to add a WHERE clause to the SQL string.

  10. #10
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a query from listbox selections (Access 2002)

    Now I get an error that says: You canceled the previous operation
    and it takes me to the vba editor. Here is what i have so far:

    Private Sub Command95_Click()
    Dim varItem As Variant
    Dim strSQL As String

    If Me.lstColumns.ItemsSelected.Count = 0 Then
    MsgBox "Please select one or more fields.", vbExclamation
    Me.lstColumns.SetFocus
    Exit Sub
    End If
    For Each varItem In Me.lstColumns.ItemsSelected
    strSQL = strSQL & ", [" & Me.lstColumns.ItemData(varItem) & "]"
    Next varItem

    strSQL = "SELECT " & Mid(strSQL, 3) & " FROM Personnel"

    CurrentDb.QueryDefs("qryMakeQuery").SQL = strSQL
    ' code to do something with the query goes here.
    DoCmd.OpenQuery "qryMakeQuery", acViewNormal, acReadOnly


    End Sub
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  11. #11
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a query from listbox selections (Access 2002)

    Charlotte,
    My trouble is creating/running the query in code. I'm learning as fellow loungers are posting, and I hope to not become redundant in what I ask. This is something new to me, and once I get this query running I can make a copy and tweak it to see what happens. If it doesn't work I have nothing to work off of. Thanks!
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Create a query from listbox selections (Access 2002)

    Have you created a query named qryMakeQuery? It doesn't matter what it looks like, since its SQL will be replaced.
    Which line is highlighted when the error occurs?

  13. #13
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a query from listbox selections (Access 2002)

    DoCmd.OpenQuery "qryMakeQuery", acViewNormal, acReadOnly

    Yes, I have a query called qryMakeQuery.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Create a query from listbox selections (Access 2002)

    If you got that far, the SQL of the query should have been changed.
    - Can you open the query directly from the database window after the error has occurred?
    - If so, does it contain the columns specified in the list box?
    - If not, can you open the query in design view?
    - If so. what does the design look like?

  15. #15
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a query from listbox selections (Access 2002)

    Hans, I can open it directly from the DB menu. It gives me a prompt "Enter Parameter Value". If I open it in design view, It shows the number of fields I selected, only they're listed as Expr1: [ ] Expr2: [ ] Expr3: [ ] and so on...I must call it a night, so I've no rush for any suggestions, but as always I am forever grateful for your willingness to pass knowledge and help <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

Page 1 of 3 123 LastLast

Posting Permissions

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