Filter Using Multiple / Multi-Column List Box

Sep 17, 2012

I'm not familiar with listbox yet and i want to filter my form using it.

I have two separate listboxes which display "category" & "type" data from the same table.

Here is the situation i wanted for my listbox.

1. Select one data from "category" listbox.
2. Then it will automatically filter data from "type" listbox or it will list all related "type" data corresponding to "category" data.
3. Then select one data from "type" listbox and it will filter all related data on the form/sub-form.

Is it possible to do that way? Can i do it on multicolumn listbox instead of using 2 listbox?

View Replies


ADVERTISEMENT

Forms :: List Boxes - Single Column Versus Multi Column

Apr 3, 2013

How do I select the first column of a multi-column list box (called "List1") for a query.

A single column list box works fine.

Code:

SELECT Tble_Employee.Emp_No, [forms]![attendee_form]![list1] AS SelectedCourse
FROM Tble_Employee;

View 6 Replies View Related

Use Multi-select List Box To Filter A Report With Two List Boxes

Nov 20, 2013

Allen Browne's "Use a multi-select list box to filter a report" solution, in particularly with two multi-select list boxes? The code works fine for me for either box so long as I code it for one box alone. Combining the two into one code results in a type mismatch error. I'm trying to use the code to pass the contents of both multi-select boxes as Where conditions to a report. Both boxes are based on number fields. To try to isolate the problem, I've removed Allen's setDescription and OpenArgs conditions. We're unfortunately still on Access 2003 as the company desires to squeeze every dime by using until end-of-life next year.

Code:
Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the list box.
Dim varItem As Variant 'Selected items

[Code] .....

View 14 Replies View Related

Multi-column List Box

Apr 19, 2006

Hi there,

I'm sure I'm not being dim, but I really can't figure how to do this.

I have a list box with two columns. One column (bound) is a name, the other is a unique ID. I need to transfer both the Name and the ID to a second list box (again, two columns).

I can get it to do this with single selections, but not multiple ones, and this is what I need.

Any ideas?

Cheers

View 1 Replies View Related

Multi-column List Box

Oct 1, 2004

I have a list box derived from a two column SQL select. When I try to use the user selected element I only get the first column data value.

In debugging I have tried ...

Ret = MsgBox(Me.List6.Value, vbOKCancel)

and...

Ret = MsgBox([Forms]![MeetingStatus]![List6], vbOKCancel)

Both of which return the selected row, first column value only.

I can find no help on how to return the content of the second column or the selection as a whole.

View 1 Replies View Related

Multi List Box Filter

Jun 7, 2007

I appreciate your help in advance. I am new to most of this and I need a little assistance. I have searched the web and found some articles etc... I am just not sure how to apply it to mine because I am unfamiliar with some of the wording etc.. I understand a very little of what people are saying but I can't quite put it all together.

I have a form called "reportfilterfrm" in this form I have 3 list box's. On 2 of the list box's multi select is selected to "None" and I have them bound to the criteria field in my query and that works perfect. The 3rd list box multi select is set to "Simple" and I am not sure how to get the query to refrence this.

The query is called "reportfilterqry"

listbox #3 is called [opponent] I would like it to be the criteria for [opponent] in the "reportfilterqry" It is a text field.

Thanks again for your help. I really appreciate it!

View 8 Replies View Related

Forms :: How To Filter A Multi-valued List Box Field

Nov 30, 2013

The main form has textbox & a Listbox with which I filtered a datasheet inside a sub form. Everything worked fine only the listbox [Discipline] is not working !! it cuase Run-time error: 3831. The multi-valued field "[Category]" cannot be used in a WHERE or HAVING clause.So how do I filter a multivalued Listbox field [Discipline] ?

Private Sub cmdSearch_Click()
'On erorr GoTo errr
Me.tblFLM_subform1.Form.RecordSource = "SELECT * FROM tblFLM " & BuildFilter
Me.tblFLM_subform1.Requery
Exit Sub
errr:
MsgBox Err.Description
End Sub

[code]....

View 3 Replies View Related

Forms :: Multi-Select List Boxes To Filter Subform

Oct 6, 2014

I have 7 multi-select list boxes, which I want the user to be able to select multiple items and have it filter a subform by what is selected. The subform will not be visible.

Here is the code I tried for filtering my subform (which is throwing an error when I call it).

Private Function MasterSearch()
On Error GoTo Error_MasterSearch

Dim StrgSQL As String
Dim WhereClause As String
StrgSQL = "SELECT * FROM MasterTbl"

[Code] ...

The above code was to test just 1 of the 7 listboxes. When I call it on click for the command button, it throws an error about the "Call MasterSearch".

View 1 Replies View Related

Queries :: Multi-Select List Box As Filter For Query Field

May 16, 2013

On [Form1] I have a Multi-Select Listbox[List1] which shows Job Numbers [WBS] (The job numbers displaying are a result of a separate query filtered by the Fiscal Year combobox). When I click [CMD1] I want [Query1] results to only be selected [WBS] from the form.What is the best way to code this? I'm a beginner when it comes to VBA.

View 12 Replies View Related

Modules & VBA :: Multi Select List Boxes With Multiple Columns In Access 2013

Oct 22, 2014

I have a listbox set to Multiselect property of Simple. The listbox is populated by using a table. There are 4 columns in the listbox

Code:
1 3/23/2014 4/5/2014 2014
2 4/6/2014 4/19/2014 2014
3 4/20/2014 5/3/2014 2014

The columns are PayPeriod, StartDate, EndDate, FiscalYear

What I want to be able to do is highlight a chunk of dates and have the first selected StartDate and the last selected EndDate populate two hidden text boxes so I can use them for my queries/reports.

I've tried a couple different ways. Each time what happens is it only uses the last item I have selected in it's calculations.

Code:
Dim ItemIndex As Variant
For Each ItemIndex In Me.lstPayPeriods.ItemsSelected
If Me.lstPayPeriods.Selected(ItemIndex) And Me.lstPayPeriods.Selected(ItemIndex - 1) = False Then
Date1.SetFocus
Date1.Text = Me.lstPayPeriods.Column(2, Me.lstPayPeriods.ListIndex)
End If
Next

In this example I tried to have it go through each Item of the listbox. I wanted to check to see if the current row was selected and the row before it wasn't. That way I could determine it was the first item selected in the group of selected items. It would always only use the last item I had selected.

Code:
Dim CurrentRow As Integer
Dim FirstDate As Date
For CurrentRow = 0 To Me.lstPayPeriods.ListCount - 1
If Me.lstPayPeriods.Selected(CurrentRow) Then
Date2.SetFocus

[Code] ....

I tried to do something similar with this code. Again, it only uses the last item I have selected.

View 2 Replies View Related

Multiple Text Search On One Column To Filter Records?

Mar 6, 2013

I have a column that has a bunch of keywords they are separated by comma... so for each row of that column it will have a few different keywords example: lake superior, river, mountain, lake wollongong

I know its a bit of a nono with databases to have columns with comma separated text.. well so i read somewhere anyway but the document i have been handed to work has hundreds of rows in this column with up to 14 keyword entries.

I have a form that searches through Item's names based on 2 keyword boxes.

Here's the criteria in my query:

Is Null Or Like "*" & [Forms]![frmItemView]![SearchPhrase1] & "*" And Like "*" & [Forms]![frmItemView]![SearchPhrase2] & "*"

This will show all records when both keywords are blank, and filter records using the 2 keywords otherwise.

I have a form with two text boxes and have set the correct values as outlined in Evans post. I then have the query set to run via a button. I run it but it will only give back records for the entry i have put in the 1st text box. This would work well for me otherwise... mine is like this:

Is NULL or Like "*" & [Forms]![Searchtable]![Key1] & "*" And Like "*" & [Forms]![Searchtable]![Key2] & "*"

Searchtable being my search form
key1 being my first text box entry
key2 being my second text box entry

View 1 Replies View Related

Forms :: Single Column Combobox To List Values From Multiple Fields

Apr 8, 2013

I have a data base with names, email IDs, phone numbers of 5 types(example quality, security, IT,finance etc) of person from ten different project teams. I created a basic form for the end user to enter values and created fields for each of the person's name, email ID etc based on the personal type( liek quality, IT, finance etc) on each team. The background table contains records of almost 200 people now.

All of these people will be trained for one task and some will be trained for multiple tasks. I know how to make the combo box allow multiple values/selections but, i cannot list out all 200 personal names in the combobox's rowsource/value list. Is it possible to combine all different name fields and list out all personal(of one team), so that the end user can just select multiple names for that team who have been trained and so on. i need to set the rowsource of the combobox to the values of all personal from one team so the team leader can just go select who has been trained and who has not.

View 1 Replies View Related

Access Query To Filter Out List Based On Multiple Row Criteria From Another Table

May 10, 2014

I have an access database in which I have a table A and table B. Table A has a list of 200 website URLs. Table B has one column ID and another criteria.

I want to create a query to filter websites list which does not have values or characters from table b.

I have these values in table B that I want to be filtered out or not shown in my URL Select Query

.org
.gov
.du
.pk
.dk

I would keep on adding more criteria into this so criteria table so adding new criteria into table B should not disturb our filtering.

Below is what I have tried but in vain and it says atmost you can atmost one criteria row in sub query

SELECT tableA.WEB_ADDRESS
FROM tableA
WHERE ((([tableA].[wEB_ADDRESS] Not Like '*'+(SELECT * FROM tableB)+'*')=True));

View 9 Replies View Related

General :: Access Query To Filter Out List Based On Multiple Row Criteria From Another Table

May 10, 2014

I have an access database in which I have a table A and table B. Table A has a list of 200 website URLs. Table B has one column ID and another criteria.

I want to create a query to filter websites list which does not have values or characters from table b.

I have these values in table B that I want to be filtered out or not shown in my URL Select Query

.org
.gov
.du
.pk
.dk

I would keep on adding more criteria into this so criteria table so adding new criteria into table B should not disturb our filtering.

SELECT tableA.WEB_ADDRESS
FROM tableA
WHERE ((([tableA].[wEB_ADDRESS] Not Like '*'+(SELECT * FROM tableB)+'*')=True));

View 3 Replies View Related

Multi-column Combo Box

Sep 15, 2005

I have a combo box with 5 columns. When I click it I want to display columns 1 to 4, together with the headings. But after making my selection, I want column 5 stored in the field.

How can I do that? Currently it is storing column 1.

Thanks,

Dave

View 2 Replies View Related

Multi Column Form

Oct 29, 2005

How can I get a form to have multiple colums.

View 2 Replies View Related

Multi Column Report

Feb 15, 2005

I have a reporting need that I believe (hope) is not unique

My customer needs to print out information about a customer that is compused of information specific to the customer (keyed on customer ID) and sets of information about their purchases in different categories (food, cars, clothes and pets). Each of these categories has different identifying characteristics. (These are represented by subReports on the report).

No problem printing out the report looking like this

-------------------------------------------------------------------
Customer Name - Mike Lester
Custoner Address - 101 Main St.,
AnyTown,CO 11111

Food
Beets 10 lbs $1.25 Green
Lettuce 1 lbs $ .78 Green
Bananas 20 lbs $ .33 Yellow

Cars
Mustang 1966 $23,888
VW 1955 $222
VW 2002 $23,000
Cadillac 2004 $44,999

Clothes
Belts 5
Shirts 9
Socks 11
Pants 5
Hats 1

Pets
Goldfish 2 Fish
Beagle 1 Dog
Great Dane 3 Dog
Cocateil 2 Bird
Macaw 1 Bird
------------------------------------------------------------------------

The problem is the customer wants the report to use the entire page. I tried positioning the subReports in two columns (left and right) but the problem is based on the number of items in each subReport the report ends up looking dumb and wasting paper (also it doesn't look very professional)

What I want to happen is the report to print as many subReports as it can on the left column (without leaving any hanging orphans) then start printing then next subreport at the beginning of the right column. See example below: (Sorry about the ~ Symbol being used to represent a space, for some reason the text box wouldn't save my formatting)

-------------------------------------------------------------------------
Customer Name - Mike Lester ~~~~~~~~Clothes
Custoner Address - 101 Main St., ~~~~~~~Belts 5
AnyTown,CO 11111 ~~~~~~~~~~~~~~~~~~~~~~~Shirts 9
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Socks 11
Food ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Pants 5
Beets 10 lbs $1.25 Green~~~~~~~~~~~~~~~~Hats 1
Lettuce 1 lbs $ .78 Green
Bananas 20 lbs $ .33 Yellow ~~~~~~~~Pets
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Goldfish 2 Fish
Cars ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Beagle 1 Dog
Mustang 1966 $23,888 ~~~~~~~~~~~~~~~~~~~Great Dane 3 Dog
VW 1955 $222 ~~~~~~~~~~~~~~~~~~~~~~~~~~~Cocateil 2 Bird
VW 2002 $23,000 ~~~~~~~~~~~~~~~~~~~~~~~~Macaw 1 Bird
Cadillac 2004 $44,999
------------------------------------------------------------------------

This is probably the longest question of the day. Does anyone have any
ideas or examples. I am hoping that this doesn't involve any massive VBA
or using Word. There are 200 other reports, and the customer doesn't currently use Word.
The entire application is 100% Access.

View 2 Replies View Related

Created List Box With One Column - Display All Characters Of List Item

Jan 2, 2014

I have created listbox with one column (contains one column only), now i would like to display all the characters of list item (want scroll bar to listbox).

How do i display all text of list item, I have already fixed Column Widths to max length (22";0.1"). However when scrolling to right, it is going to next blank column of list box, which is created only to change Column Widths property.

View 12 Replies View Related

Multi-Criteria Filter

Jan 3, 2008

Good morning everyone,

I use this code to filter a subform with multiple criteria.

Private Sub Filter_Click()
Dim strWhere As String
If Not IsNull(Me.Coordinator) Then
'Create Predicate
strWhere = strWhere & " AND " & "Orders.[EmployeeID] = " & Me.Coordinator & ""
End If
If Not IsNull(Me.Customer) Then
'Create Predicate
strWhere = strWhere & " AND " & "Orders.[CustomerID] = " & Me.Customer & ""
End If

If Not IsNull(Me.Supplier) Then
'Create Predicate
strWhere = strWhere & " AND " & "Orders.[SupplierID] = " & Me.Supplier & ""
End If
Me.Track_All_Orders.Form.Filter = strWhere
Me.Track_All_Orders.Form.FilterOn = True
End Sub

This Code works great, however i want to use it to filter my report as well, so I put strWhere as a global var and i wrote this code to generate the report with the same filters criteria as the subform:

Private Sub cmdGenerateReport_Click()
Dim stDocName As String
stDocName = "Statement"
DoCmd.OpenReport stDocName, acPreview, , strWhere
End Sub

Nothing happen when i click on cmdGenerateReport!! any clue?

Million thanks in Advance,
Best Regards,

View 2 Replies View Related

Query To List Multiple Records In A List From Multiple Queries

Jul 11, 2013

I have a DB where you there's 5 tables all linked together by one project ID

tables below

Project , Staff, Asset, allowances, travel, mark up

What I can do is create a new project, then add records to each of the other tables on what different items I require,

i.e. I create a new project - called project one, in the project table I create a record stating, name, time scale, client and location, then I add different records to each of the other tables on what I require all linked to the same project ID. (probably not explained that too well)

Now I want to create a query that lists all the requirements one after the other this will make it easier to create reports and to calculate costing's.

At the moment I have made 5 different queries listing all the data, then have one report containing 5 sub reports to display the data, no this does work.

View 7 Replies View Related

Queries :: Get SQL From Data On Multi Column Listbox

Oct 22, 2013

I wish to generate a new query consisting rows that I have selected in a multi colum list box. May I know if it is possible to work? I am totally lost now

View 10 Replies View Related

Formatting Date In Multi-Column Combo Box

Apr 14, 2015

I have combo boxes that contain attorney names and phone numbers. The attorney name is what is filled into the field when a selection is made from the combo box into the form. The phone number is just there for the occasions that the person managing the data in the database needs to call that attorney. Just a quicker way instead of having to go to another form to find the phone number. I created a query to combine the first and last name to one field (FullName) and this is where the combo box gets it's info.

When I run the query, the phone number is shown formatted as I want it to ((###)###-####), but in the combo box in the form, the phone number has no format to it. What I need to do to get the phone number to show up formatted in the combo box list?

View 2 Replies View Related

Filter Query By Multi Select Listbox

Jul 27, 2006

Dear All,

I am just starting out in Access Development. I have a database that runs a logistics department. This was working fine until the users wanted a little more tweaking.

The report prints out the vehicles with all corresponding drops. This comes out all on sheet.

Is there a way that I can create a form to filter the report via a multi select listbox and print each drop selected on a seperate sheet of paper.

I am using Access 2003

Many thanks for any help or source code given

View 3 Replies View Related

Reports :: Filter A Report By Using A Multi-select Box

Oct 3, 2013

I'm using the following code to filter a report by using a multi-select box.

Code:
Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click

Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant

[code]....

I have a report that contains a subreport. The report is simply an image of a word document. The subreport will contain the actual data.When I open the subreport, the IN query works perfectly. When I open the main report, I only get the first record listed in the IN query. I have the master/child links set properly.

View 14 Replies View Related

Forms :: Multi Field Filter On A Form?

Mar 16, 2015

I have a filter on a form : There is a text box (txtSearchP) where the user will enter their search term then click button to apply the filter:

Private Sub cmdSearchP_Click()
' Filter by Programme Description
Me.Filter = [Programme_Desc] Like "*" & Me.txtSearchP & "*"
Me.FilterOn = True
Me.Requery
End Sub

This works fine but actually I want to search two fields for the text entered in txtSearchP [Programme_Desc] OR [Programme]

I've tried this but it doesn't work:

Private Sub cmdSearchP_Click()
' Filter by Programme Code and Description
Me.Filter = [Programme_Desc] Like "*" & Me.txtSearchP & "*" OR [Programme] Like "*" & Me.txtSearchP & "*"
Me.FilterOn = True
Me.Requery
End Sub

I know I could give the users two text boxes and two Buttons one for each and this would work but I don't want the form to be cluttered.

View 4 Replies View Related

Multi-Column Combo-box Won't Bresk Down In Text Boxes

Aug 16, 2005

My form's 10 column combo-box (based on a query) won't break into text cells. Some of the columns contain numbers, some test, some phone #s. All columns show when I click the Combo-box in Form View. Access Help File said to put:
=Forms!Customers!CompanyName.Column(1) in the ControlSource for the text cell. Tried that and was returned #Name? for any of the fields in which I tried it.
My expression was:
=frmSpartanStudentBehaviorLog!Last_FirstUC.Column (3)
The other properties in combo-box are set correctly: ColumnCount=10, Column Widths are correct.

I also tried the expression:
=Last_FirstUC.Column(2) but got the same result #Name?

I'm baffled. As a very new user, I'll appreciate any assistance. Thanks.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved