Create a query from listbox selections (Access 2002)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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…

Reply | Quote

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?

Reply | Quote

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!

Reply | Quote

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.

Reply | Quote

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’.

Reply | Quote

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

Reply | Quote

Row Source Type = Field List
Row Source = tblPersonnel
Column Count = 1

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

DoCmd.OpenQuery “qryMakeQuery”, acViewNormal, acReadOnly

Yes, I have a query called qryMakeQuery.

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

There should have been field names between the brackets. Set a breakpoint at the beginning of the On Click procedure (click in the first line and press F9). When you click the command button, the code will pause. You can single-step through it by repeatedly pressing F8. You can inspect the value of variables by hovering the mouse pointer over it. You’ll be able to see how strSQL develops. Fo you see anything unexpected?

Reply | Quote

It runs the query, gives me the text box to enter parameter value, and if i do nothing i click ok and i get this error:

Run time error ‘2001’
You canceled the previous operation.

And it takes me to the VBA Editor with the last line highlighted, the one I identified before.

As I work through the code, I hover over each item, here is what I see:
varItem = Empty
StrSQL = “Select [],[],[] from personnel”

The ItemsSelect.Count works fine, showing the number of items I selected every time. Continuing through the code hovering:

Me.lstColumns.ItemData(varItem) = Null

In the following section of code, as I hover over strSQL in any line, I get: strSQL = “”
__________________________________________________________________________________________
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
__________________________________________________________________________________________

Hope that explanation proves useful. I’ll try to provide a stripped down copy soon, especially if we can’t identify the problem here.

Thanks again!
Jeremy

Reply | Quote

I don’t understand what is happening, varItem should never be empty . We will need to see the database.

Reply | Quote

Hans, when I try to pull the pertinent stuff off my database into a new Access file, it gives me a networking error, and I can only see the way the form looks, as if there’s no code attached to it. When I try to go to the VBA editor, it gives me a blank message box. So I’m having no luck with posting an example. Is there a different way to do this? Like manually putting in the fields in the list box? If so………how? Thanks for your help
Jeremy

Reply | Quote

This, together with the anomalous behavior you reported earlier, makes me think that your database has become (partially) corrupted. Try to import as many database objects as possible into a new database. You will have to reconstruct the rest. If you have a non-corrupt backup, that may help too.

Reply | Quote

Here’s the example file….
Error message
___________________________________________
The expression On Click you entered as the event property setting produced the following error:
Error accessing file. Network connection may have been lost.

*The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event, or macro.

Reply | Quote

The database you posted is indeed seriously corrupt: the VBA code is damaged beyond repair. I managed to salvage the table, query and form, but without the code; I reconstructed it from this thread.

Apart from that, the problem was that the Bound Column property of the list box had been set to 0; it must be 1.

Repaired database attached.

Reply | Quote

How is it damaged, I don’t understand how that happens or what it does, (except make my database not work anymore). I’m trying your solution now. Thanks for your help!

Jeremy

Reply | Quote

Corruption happens. Why?

Reply | Quote

I used JetComp (do a search in this forum) to attempt to repair the database. This didn’t help. I then created a blank new database and imported the table, query and form. The form was imported, but an empty code module. I copied the Command95_Click procedure from this thread into the form module. I set the Bound Column of the list box to 1. That’s it.

Reply | Quote

All right. Well, since creating my new database, this code works. Thank you! Now I added a where clause, I added it after the “FROM tblPersonnel” statement …I did it as such:

strSQL = “SELECT” & Mid(strSql, 3) & “FROM tblPersonnel” & “WHERE ((tblpersonnel.comp)=[forms]![frmlogin]![txtuser])

and I get the error: “Syntax error in FROM clause”, however I don’t know what I’m doing wrong…

Reply | Quote

You left out the spaces after SELECT, before FROM and before WHERE. Try

strSQL = “SELECT ” & Mid(strSQL, 3) & ” FROM tblPersonnel” & ” WHERE Comp=[Forms]![frmLogin]![txtUser]”

Reply | Quote

Well I’ll be……works like a charm (no surprise). Thanks

Reply | Quote

I have expanded my listbox to span across multiple tables. Some of those tables are in a 1 to Many relationship. When I create a query and don’t select records from the tables with many records for each PK, I get MANY duplicates. My listbox is populated from a query that brings 5 tables together (2 of which allow multiple records). I’m assuming it has something to do with that, but I haven’t the slightest clue what. Any ideas?

Reply | Quote

It depends on the way you handle the multiple tables in the SQL string you assemble. See if things improve if you start it with

strSQL = “SELECT DISTINCT ” & …

Reply | Quote

Hans,
That narrows my selection down to 576 (the number should be 1057 if I only choose 1 to 1 tables).

Reply | Quote

Please provide more information or post a stripped down copy of the database.

Reply | Quote

Hans, here’s the SQL created:

SELECT DISTINCT [RANK], [LNAME], [FNAME], [MI], [SSN], [MOS], [SVC], [COMPANY], [WORK_SEC], [BLD_TYP], [EAS], [DOB]
FROM qryListBox
WHERE (company = [forms]![frmmakequery]![cbomakequerycofilter] or [forms]![frmmakequery]![cbomakequerycofilter] is null) AND (mos = [forms]![frmmakequery]![txtMOS] or [forms]![frmmakequery]![txtMOS] is null);

I think the problem lies in my query, shown in attachment.

Reply | Quote

What exactly is the problem? First you complained about duplicates, then that you only got 576 records instead of 1067.

Reply | Quote

You are using inner joins, which means that any record in personnel which does not have a match in any of the other tables will be dropped. Try outer joins instead unless your intent was to return only those persons who had matching records in all the other tables.

Reply | Quote

How do I specify an Outer Join in SQL? I tried replacing “INNER” with “OUTER”, I guess it’s not that simple?

Reply | Quote

The easiest way to to use the query grid and right click on on each relation line, then edit the relationship. What you want is all records from Personnel and only matching records from the other tables. Note that an outer join can lead to multiple records if you don’t set the Unique Value property to True for the query.

Reply | Quote

I you guys (and gals, to be politically correct, and to avoid later tongue-lashings). That works perfect.

Charlotte, as always, then you for giving me the “you might wanna think about this…” additional information.

Reply | Quote

There are two kinds of outer join: a LEFT JOIN and a RIGHT JOIN

SELECT … FROM A LEFT JOIN B ON … means: display ALL records from A and only matching records from B.
SELECT … FROM A RIGHT JOIN B ON … means: display ALL records from B and only matching records from A.

The easiest way to find out what you need is to create a select query in design view. Double click the join line between two tables to specify the join type, then switch to datasheet view to check that the query works as intended. If so, switch to SQL view to see how the join is defined in SQL.

Reply | Quote