Querying From Listbox data 2

[CptCrusty1]

I'd like to run a query where the contents of a listbox are part of a Where IN(listbox) type setup. Is this possible?

 

[GingerR]

If you have MULTISELECT = NONE, just reference the List Box like any other control. Otherwise you have to write code to put the multiple selections from the list box into a temporary table (or a recordset) then use that for your where statement. Search the Forms forum or Access HELP for list box code.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[FancyPrairie]

Check out this FAQ faq181-5497. It contains a function that will build and return the Where clause for you (without the word Where). It works for single and multi-select list boxes, comboboxes, text boxes, ranges, option groups and check boxes. You only have to do 3 things to make it work.

1. Create a new module and copy and paste the code from the FAQ into the new module.
2. Set you Tag properties as specified in the FAQ.
3. Open you report as specified in the FAQ

 

[CptCrusty1]

GingerR,

Thanks for the reply. My problem is, I'm so new to reference controls in VBA, I just am not sure how to even begin. Could you provide a little more detail? Thanks a million...

Crusty.

 

[GingerR]

WHERE x = Forms!FormName!ListBoxName

or just put it in the criteria row of a query.

Again, for multiselect list boxes, you have to look up the code in the forum or in HELP...

If you want more detailed help, you should post your existing code, what you are trying to do, your form/control names, and what is not working for you.



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[FancyPrairie]

CptCrusty1,

What to you plan to do with the query? Is a report based on this query? Or are you trying to filter a form based on this query?

 

[CptCrusty1]

FancyPrairie,

As far as I can tell, I've followed your instructions to the letter. The report is not pulling from the listbox. My dataset gives me everything.

What I have is paired list boxes. I followed some instructions (Access Interfaces: Creating Paired Listbox Controls, Part 1 and 2, its on the MSDN website, author Rebecca M. Riordan.

The table (tblCentralLocBoolean) with the Location numbers has two columns, Location_Num (Text), InSelectedLocation (Boolean). The listbox 1 is essentially populated by a query selecting the records with boolean=no, and vise versa for Listbox 2. The user is going to select the numbers he wants to query by which will be "moved" to Listbox 2.

I pasted your totally awesome code into a module (module1) and Where=tblCentralLocBoolean.Location_Num,TEXT; into the TAG property for the Second Listbox since that is the one with the criteria for the query. I F8'd through the Module and watched the statement BuildWhere = strWhere to see if the variable strWhere picked up my Tag property. Alas, nothing ever populated.

What am I possibly missing. I am in dire straights here. Is there another way to do this? There will be another set of listboxes just like the first, but pulling from a different table. When one is enabled, the other set is disabled via code. Also, I have two ActiveX Calenders on the form for specifying a date range for the report.

Please help. I appreciate it greatly.

Thanks.
Richard.

 

[CptCrusty1]

One other thing, Fancy, When I select criteria and move it to the second listbox, and run, I get an error "Invalid Use of Null." This is only if I haven't touched anything inside the second list box. If I've touched something, i.e., removing one, a value in the listbox will remain highlighted. In this case, I get a dialogbox asking for a value for the tblCentralLocBoolean.Location_Num field. If I put a value in, I get the entire dataset. There has got to be an easier way to do this.....

 

[GingerR]

What is your Record Source for your Report? Please post the sql statement here.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[CptCrusty1]

Ginger,

The Report's RecordSource is a test qry called qryTest. There is no WHERE statement is it is provided by the TAG line. Also, the code that I installed from Fancy is what is supposed to be filling in the Where statement. I've also disabled the MultiSelect. It is now at None.

I'm starting to zero in and I've noticed that when I open the form, select Location_Num's to be sent to lbDestination (the second listbox which should populate the Where statement), the query runs, albeit with a complete dataset instead of the limited one I"m after. If I remove the numbers (send them back to the lbSource listbox) and try again, I get an error which is "illegal use of Null Value" having something to do with the code from Fancy. If I close the form, and start over, it works, again, with a complete data set. I'm getting closer, slowly, but I'm getting inexorably close. Especcially thanks to you guys. My SQL is as follows:


SELECT
tblSales_Item_data.Location_Num,
tblSales_Item_data.Opr_Del_Dt,
tblSales_Item_data.Product_Item_ID,
tblSales_Item_data.Item_Cd_Desc,
tblSales_Item_data.Item_ID,
tblSales_Item_data.Attr1,
tblSales_Item_data.Attr2,
tblSales_Item_data.Attr3
FROM tblSales_Item_data;

 

[GingerR]

Hi there. I've never used Fancy's code. Paste here what your TAG is and I'll try to help.

I just use the standard way of getting listbox.itemselected and putting them into a temp table and running reports/queries joined to that table.

If you are using Multiselect = NONE, all I would do is change the recordsource of the report to:

Select * from tblSales_Item_Data where Location_Num = Forms!FormName!lbListBoxName

change FormName to the name of your form and change lbListBoxName to the name of your first list box.

I'm sick so it's hard for me to pick thru Fancy's code at the moment. I started reading it, so let me know what your TAG is and any other items you had to tweak per Fancy's code and I'll see if I can figure out the problem.

g

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[FancyPrairie]

The code works. You probably don't have the tag property quite right. Suppose your SQL statement did contain the Where clause, would it look something like this?

SELECT
tblSales_Item_data.Location_Num,
tblSales_Item_data.Opr_Del_Dt,
tblSales_Item_data.Product_Item_ID,
tblSales_Item_data.Item_Cd_Desc,
tblSales_Item_data.Item_ID,
tblSales_Item_data.Attr1,
tblSales_Item_data.Attr2,
tblSales_Item_data.Attr3
FROM tblSales_Item_data
Where tblSales_Item_data.Location_Num In (2,3);

If so, then the Tag Property for the list box should look like this (Assuming the Location_Num is defined as a Long Integer)

Where=tblSales_Item_data.Location_Num,Long;

or it could, optionally, look like this:

Where=tblSales_Item_data.Location_Num,Long,=;

Also, make sure your syntax for the docmd.openreport is correct:

Docmd.OpenReport "rptName",acViewPreview",,BuildWhere(Me)


To test it, instead of issuing the DoCmd.OpenReport, issue this command instead:

MsgBox BuildWhere(Me)

 

[CptCrusty1]

Fancy and Ginger. I must say. You both are quite tenacious at solving this problem for me, who should be fixing the dumb thing to begin with. However.

Fancy:

My TAG is "Where=tblCentralLocBoolean.Location_Num,TEXT;" as the records are numbers in Text format, i.e. 0107, 0703. I put your "test" message box into the Command Button Click event and got absolutley nothing IF, nothing was highlighted on the list box with the criteria numbers. But if I click on a number, I get....

(tlbCentralLocBoolean.Location_Num in (0107)) as an example. Changing the listbox MultiSelect property to anything other than None is not really an option. Why should the user have to 'select' his criteria, then reselect his highlighted criteria to tell the query which ones were selected.... does that make sense... I haven't had any coffee today.. Maybe thats why I'm kinda stupid today.

Ginger.

I built another example of paired listboxes. this time I have a source table and a destination table. The source listbox is bound to the source table and so on. As a test, I ran the above SQL and used a sub-query in the Where statement that referenced the destination table which only has the one column. The Where statement is:

Select yadda yadda
From Cha.Cha.Cha
Where Zipade.Doda IN
(Select * From tblDestination)

This gave me the result dataset that I was looking for.

Fancy and Ginger.
I tried modifying the TAG as Fancy said and the result was the same. I'm going to try building some code to move a selected record from one table to another. If this works, well.. you guys (gals?) have been burning braincells on my behalf for nothing except a few smileys, etc. If you guys (gals?) can figure out what I've screwed up with Fancy's code? Maybe we'll all learn something. so...(cliche' time) Here's your one chance, Fancy.... don't let me down..... BUAH!!!!! I'm so funny!....

I'll post the results of my quandry and what I did. Again. Thanks so much to both of you.... anything else you have to say will not be taken lightly... especially winning lottery numbers.... BUAH!!! I'm funny again!

Rich.

Now I'm going to build some code to move the records bac

 

[FancyPrairie]

Your tag property is not correct. It should be:

Where=tblCentralLocBoolean.Location_Num,String;"

Check out the documentation in the header of BuildWhere. I believe I indicate the valid options.

 

[CptCrusty1]

Fancy,

I tried STRING as well as TEXT, however, neither of them work. I'm not thinking its your code, it has to be something small and minor that I'm not aware of and you can't see since you're not sitting behind my computer. Eitherway. I rebuilt the paired list boxes using two tables, each one bound to the appropriate listbox. The queries seem to be working. Your method would have been shorter, but, it wasn't meant to be. Ginder's method actually seems to be working. Thank you so much for your help. I greatly greatly appreciate it.

Rich.