Forms :: Multi Select List Boxes And Parameter Queries

Apr 16, 2013

I am creating an absenteeism database which has the following tables:

tblEmp - PK - auto number, EmpId, First Name, Last name
tblFunction - PK - FID (autonumber) has departments
tblTL - PK TLID (autonumber) has list of team leaders
tblRelated - pK - Id (auto), EmpId, TLID, FId as long integeres
tblCodes - CodeId (auto), list of absenteeism codes like late, mia, etc
EmpLeave - pk - autonumber, empid, codeid, hours, date of absenteeism

I have a Qry_Master which just joins all the information together as it gets updated on a monthly basis

Now, I am trying to create a form where the user has option to select one or more tls and one or more codes and when they hit the button, it should come up with all emps that have those codes and report to the team leader selected.

In my form, I have made both my list boxes as multiselect and i have Qry_frm that is a parameter query but when i run the button nothing happens and i cant seem to figure it out. I have attached the sample database to this thread.

View Replies


ADVERTISEMENT

Using Multi Select List Boxes To Run Queries

Apr 21, 2006

I have a simple question that I know will have a very complex answer


You have two sets of tables with identical structures. The first set contains data that is a model for the second set. The users run queries that append data to the second set of tables

Here is an example of the structure
TblSourceOne
SourceOneID
SourceOneData

TblSouceTwo
SourceTwoID
SourceOneID
SourceTwoData

TblSouceDetails
SourceDetailsID
SourceTwoID
SourceDetailsData

As you can see TblSourceTwo contains a foreign key from TblSorceOne and TblSorceDetails contains one from TblSourceTwo

The main table for the second set

TblTargetOne
TargetOneID
SourceOneID
TargetOneData

The user adds a record to the main table (TblTargetOne) then uses an append query to add all of the applicable records from TblSourceTwo to TbleTargetTwo

TblTargetTwo
TargetTwoID
TargetOneID
TargetTwoData

So now the user has a data model in TblSourceTwo which has been “copied” to TblTargetTwo

Next for each record in TblSourceTwo that has been copied to TblTargetTwo that has daughter records in TblSourceDetails another query is executed to copy all of the pertinate records to the TblTargetDetails

TblTargetDetails
TargetDetailsID
TargetTwoID
TargetDetailsData

Now the user has completely copied all of the necessary record for both table.

Finely the question

How can I use a multiselect list box to achieve this in one operation? I don’t care if it includes running multiple queries but it needs to appear as one single operation to the user.

View 2 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

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

General :: How To Multi-select For Cascading List Boxes

Sep 4, 2013

I am making a form at work for a coworker with cascading combo/list boxes where she can select a Customer, then Platform Description, then Period, then Year. I used VBA code for this and it's working perfectly - for single selection. I want to update this so she can select multiple Platform Descriptions and/or Periods and it will give all the applicable info.

I can use or a way to set up a query, etc. so that multi-select will work for me? Unfortunately, just changing the formatting to multiselect makes the listbox just blank white, so I'm assuming it's going to be more complicated.

(Here's the code I currently have for the cascading combo/list boxes, if you need it. SD0039DA_T is the table name where my listboxes are pulling info from.)

Code:
Option Compare Database

Private Sub Form_Load()
On Error Resume Next
CustomerCB.SetFocus
PlatformDescriptionL.Enabled = False
PeriodL.Enabled = False
YearCB.Enabled = False
End Sub

[code]...

View 3 Replies View Related

Modules & VBA :: Multi-select List Box Items To Pass Into Text Boxes

Oct 16, 2014

I have an access project that I am working on and need to be able to select multiple items from a listbox and have the exact selections appear in a textbox on the same form. I have looked around and have not been able to find any code that works.

I have tried:

Me.user2 = Me.slct_auditor.Column(0, 1)
Me.user3 = Me.slct_auditor.Column(0, 2)
Me.user4 = Me.slct_auditor.Column(0, 3)
Me.user5 = Me.slct_auditor.Column(0, 4)
Me.user6 = Me.slct_auditor.Column(0, 5)
Me.user7 = Me.slct_auditor.Column(0, 6)
Me.user8 = Me.slct_auditor.Column(0, 7)

but when skipping the first item in the listbox it is still passed as into the textbox.

View 4 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

Queries :: Multi-parameter Select Query

Jan 12, 2015

I created a form that has 3 multiple drop-down selections that is supposed to select records from [TableName_1] based on those selections. I initially created the query using the query designer with actual "hard coded" selections to make sure it worked. When it gave me the expected records, I changed the criteria from the "hard coded" option to the input form's control reference [Forms]![FormName]![ComboName]. I did this one at a time and tested after each change.

After the first change, I got exactly what I expected.

After the second change, it seemed okay. (note: I put the criteria on the first criteria line in the query designer, not on the second line)

After the third change, I get the dreaded: The expression is typed incorrectly or is too complex to be evaluated....

Here is the SQL:

SELECT M_Lending_Institution.InstitutionName, M_Lending_Institution.GeoRegionID, M_Lending_Institution.SpecialtyID, M_Lending_Institution.SBA
FROM (SELECT M_Lending_Institution.InstitutionName, M_Lending_Institution.GeoRegionID, M_Lending_Institution.SpecialtyID, M_Lending_Institution.SBA FROM M_Lending_Institution) AS LenderSearchQuery
WHERE (((LenderSearchQuery.GeoRegionID.Value)=[Forms]![LoanSearch]![CmbPrefGeo]) AND ((LenderSearchQuery.SpecialtyID)=[Forms]![LoanSearch]![CmbSpecialtyArea]) AND ((LenderSearchQuery.SBA)=[Forms]![LoanSearch]![CmbSBA]));

And once this is sorted out, yes I want to be able to allow for all records to be returned if any of those fields is NULL. I've read a LOT of posts around this subject, but I can't seem to find anything that answers my question without jumping into advanced VBA code.

View 14 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

Forms :: Multi Select List Box In A Form

Mar 6, 2013

I have a form which contains a multi select list box of team leaders. Unbound getting source from tl table. Based on one or more selection from user I want to run query that brings up those team leaders. When I have multi select to none in list box and select one to the query works fine. When I change it to simple or expanded it shows nothing. I have query criteria to get data from form list box.

View 1 Replies View Related

Forms :: Multi List To Select Reports Send By Email

Mar 13, 2015

I am trying to create a Multi list to select reports to send by email. I am able to select one report to send but when I change the list box to multi select it doesn't work.

The codes I have been using are;

Private Sub Command2_Click()
On Error GoTo ErrorHandler
Dim sAddr As String, sSubj As String, sFor As String
If IsNull(ReportList) Then Exit Sub

[Code] ....

View 4 Replies View Related

Modules & VBA :: Programmatically Find And Select Item In Multi Select List Box

Apr 23, 2015

I have a multi slect list box (simple) and I need to find and select an item using vba - e.g., the bound column is the ID field and I need to select a specific ID (which will be different each time) as opposed to selecting the 100th record for example. How do I do this?

View 2 Replies View Related

List Box One Click Select/deselect With Multi Select

Aug 28, 2004

Hi,
is there any (reasonably simple) way to select or deselect multiple items from the List Box with individual clicks without using Ctrl key. Eg first click on an item would select it leaving all other items as they are, subsequent click on the already selected item would deselect it etc. I hope this is not too confusing and I would appreciate some help.
Thanks!

View 1 Replies View Related

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

Multi-Select List Box

Jun 10, 2005

Hi,
This is my first posting on this forum, and I would greatly appreciate any help with this issue...

I have a form that is used for entering information into tables. I would like to be able to select multiple options from a list on a form and have it saved into a table. Any ideas? Is this even possible :confused:

Here are some paths I followed:
The table has a List Box field, type Text, that gets values for the list items from another table. On the form, I have changed the Multi-Select property of the List Box to "Extended" so that the user can select multiple items from the list. But when I select items from the List Box, the table is not getting updated with the selected items.

Another way I tried doing this: The table has a List Box field that has no values (I'm not looking up values from another table). On the form, I set the Row Source of the List Box equal to the query that gets the list values from a table. I then set the Multi-Select property to "Extended". When I select multiple items from the List Box, the table doesn't get updated.

FYI: The control source for the list box on the form is pointing to the field in the table.


Thanks,
dbnewbie

View 4 Replies View Related

Multi-Select List Box

Jun 6, 2006

Good morning all...

I have two questions regarding a multi-select list box.

First, is there any way to have the list box include multi-line items...or which will scroll beyond the right border of the box?

Second, I have a multi-select list box which is populated with an ID # column and a Description column. The user can select as many items from the list box as needed and, as the list box item is clicked, the ID # is added to a memo field on the form, with each ID # delineated with a coma. Is it possible to separate the memo field back out so that each item is identified individually (for report purposes)?

Thanx so much for your help with this...you guys/gals are simply the best!

Karen

View 1 Replies View Related

Multi List Select Problem

Sep 29, 2005

I have a dialog box where the users can filter a report by various combo boxes that works well. I'm trying to add a multiselect lis box option for one of the combo boxes and have added code which I've also used before. Together the code is looping through and opening the report without a problem, except it's not filtering by the options selected in the list box. Here's what I've got:

If Left(Me![cboSupplier].Column(0), 1) = "*" Or Right(Me![cboSupplier].Column(0), 1) = "*" Then
where = where & " AND [qssupp] Like " + Me![QSSupp].Column(0)
Else
where = where & " AND [qssupp]=" + Me![cboSupplier].Column(0)
End If

If Left(Me![cboPartNo].Column(0), 1) = "*" Or Right(Me![cboPartNo].Column(0), 1) = "*" Then
where = where & " AND [partID] Like " + Me![PartID].Column(0)
Else
where = where & " AND [PartID]=" + Me![cboPartNo].Column(0)
End If

For Each varItem In cboStatus.ItemsSelected
If strCondition = "" Then
strCondition = "[odstatusid] = " & cboStatus.Column(0, varItem)
Else
strCondition = strCondition & " OR [odstatusid] = " & cboStatus.Column(0, varItem)
End If
Next varItem

Set QD = db.CreateQueryDef("Dynamic_Query", _
"Select * from qryWeeklyOrderStatusRpt " & (" where " + Mid(where, 6) & ";"))
If (DCount("*", "Dynamic_Query") = 0) Then
MsgBox "There are no records to print"
Exit Sub
End If

Can anybody see where I'm going wrong?

View 4 Replies View Related

Multi Select List Box Formatting

Jan 30, 2006

I am using a Multi Select List Box to display a list of names from a table, in order to select e-mail recipients. The names are in two fields: [Main]![First Name] and [Main]![Surname].

I have done this by using the following code for the rowsource:

SELECT DISTINCTROW Main![e-mail], Main![First Name], Main!Surname FROM Main;

This gives a rather undesired effect, in that shorter first names have a large gap before the surname. In addition, there is a dull separating line between the two columns, thus:

Jane | Smith
Bartholomew | Simpson

My questions:

Is it possible to arrange the names so they appear in one column, with a space between them? (i.e. they would appear as written)
If not, can I get rid of the separating line?

Many thanks in advance if you can help!

View 4 Replies View Related

General :: Multi Select List Box

Dec 22, 2014

I use access to gather information on testing that is conducted at my company. In the database I designed I set up a multiselect list box for when the people who conducted the test can check off the test equipment that they used. I did this because at anytime any number of different items can be used. Though I don't think we have ever used more then 10 test items. The reason for tracking the items that were used is if they, when out for yearly calibration, come back out of spec we can look at what tests used that equipment. As I further develop my system, I am starting to ask for more information from the requesters. I would like them to be able to check off what individual items are in the item being tested. Creating that list isn't the hard part for me.

My question comes in here. I know that using a multiselect list box is frowned upon as they can be sloppy to deal with when looking to extract data. Being as I am looking to expand my database, I was looking for what to do. I would still like to give the option of a multiselect list box, but should I create more fields and have the items used put into these new fields? Should I do the same for equipment that is used for the testing. If not and the list box is ok, how best to go through all the records to gather what ones used the specified equipment?

View 2 Replies View Related

Open Report From Multi-select List Box

Jul 22, 2015

I am running MS Access 2010 on Windows XP and my access skills are limited.

So I have a db with tables, forms and reports and would like to give my users the option of opening a filtered report (from a form).

The form (frmSelectStatus) and report (rptStatus) have been created and both open correctly by normal selection from the 'All Access Objects' side bar.

frmSelectStatus has a multi-select list box (lstStatus) and a command button (cmdStatus). When I click cmdStatus i get the correct report opening but it is all status records and not filtered by the selection made in lstStatus - and i know this is a result of not referencing 'lstStatus' in the code. i also have a query (qryStatus) which does not contain anything.

My 'on-click' code for cmdStatus is

Private Sub cmdSelect_Click()
DoCmd.OpenQuery "qryStatus", acViewNormal, acEdit
DoCmd.OpenReport "rptStatus", acViewReport
DoCmd.Close acQuery, "qryStatus"
End Sub

I know I need to reference 'lstStatus' but am not sure where or how to do that in the code.

View 1 Replies View Related

Updating A Table From A Multi-select List Box

Mar 26, 2012

I am trying to make a simple database where the data entered in a form will update to a table. My issue is, one of the fields is manufacturing location where I would like the user to be able to enter multiple locations and then have those locations update the table where the record is stored. I've been able to set up a list box with multi-select but am stuck at getting the table to update with the choices made from the list box selection.

View 2 Replies View Related

Assign Query Output To Multi Select List Box

May 10, 2005

Morning - I have been searching a bit but to no avail. I have a query (qryTopTenList) set up in a database that selects the Top Ten "Classes" from a table with a key field of "insclass".

I have a multi select list box (LstInsClass) that allows end users to select multiple insurance classes. What I would like to do is set up a standard button that when clicked, will loop through all of the insclass in the query "qryTopTenList" and then select those same insurance classes in my multi select list box. So the end result is that 10 records in the List Box would be selected.

This is helpful b/c it prevents the need to go through the entire list box to select the top ten classes.

Thx in advance for any insight. I appreciate it.

Rob

View 1 Replies View Related

Multi-select List Record Creation Code

Aug 5, 2005

I have a multi-select list box for selecting which faculty members apply to a project. The faculty table and project table are linked in a many-to-many relationship. I have the following code which should create entries in the link table:

Private Sub Command5_Click()
Dim varItm As Variant

rs.Open "tblFacultyLink", CurrentProject.Connection, adOpenDynamic, adLockOptimistic

For Each varItm In lstFaculty.ItemsSelected
rs.AddNew
rs!FacultyID = Me.lstFaculty
rs!EntryID = Me.EntryID
rs.Update
Next varItm

rs.Close
Set rs = Nothing
End Sub

It successfully creates new records and enters the EntryID and LinkID (autonumber). However, FacultyID is always left blank. lstFaculty is the unbound list box which has three columns from the faculty table and is bound to FacultyID. Any ideas on why FacultyID isn't created in the link table (I get no error messages)?

Also, any ideas on how to prevent duplicate links being created every time the button is pushed? I was planning on having it first run a delete query for that EntryID in the link table so that it replaces the old links and any that have now been unselected are no longer linked. Comments on that idea?

Thanks again to everyone on the forums for your help.

View 4 Replies View Related

General :: How To Order Values In Multi-Select List Box

Jun 4, 2013

Is there a way to order the values in a multi-select list box:

1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20 appears in the listbox as:

1,10,11,12,13,14,15,16,17,18,19,20,2,3,4,5,6,7,8,9 . Is there a way to have it appear as it does originally?

View 1 Replies View Related

Using A Multi Select List Box To Pass Parameters To A Query

Apr 24, 2015

I have created a search form which I would like to use to run a query (so the data is in a spreadsheet form and I can export it).

In this search form I have a multi select list box (simple) that list the states in the US. I need to be able to pass 1 or more states as a search criteria at the same time. Also I need it to pull ALL states if there is nothing selected. The search form has a bunch of fields on it that won't all be used.

I know once you select more than one thing from a list you can't reference it directly, is there a way to accomplish what I want to do?

View 1 Replies View Related

Multi-select List Box Selections For A Record And Re-selecting It?

Feb 1, 2013

So I have a multiselect listbox. The list that it shows depends on what is selected in a separate combo box.I want to be able to select multiple items from the listbox, and then be able to leave the record, and when I come back to it, to still have those items highlighted. Right now in my form, as I go through records, if I highlighted items 1,2 and 3 on a record, those first three lines will stay highlighted as I move through different records, rather than changing to what had been selected for each record.

I've already stored the actual selected values in a subtable. I just want to have it highlighted again so that you can see what was previously selected.

View 2 Replies View Related







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