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 RepliesAllen 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] .....
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".
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.
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]....
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
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.
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
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?
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 RelatedHi,
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!
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.
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.
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!
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
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
I currently have a database of customer information and form set up with a MultiList box based on the regions those companies are based. You select the region(s) you require from the list box, click a button and it runs a query.
However I now want to be able to filter the results further using a combo box with business sectors in also.
The code used for the multilist box is as follows (I don't totally understand all the code if I'm honest, but it works )
Private Sub Command2_Click()
Const cstrQuery As String = "CAMPQRY"
Dim strNames As String
Dim strSelect As String
Dim StrWhere As String
Dim varItm As Variant
[Code] ....
I'm using pbauldy's code to filter a report by a multi-select listbox. The only issue is..how do I include more (7 to be exact) listboxes to the code? It seems the OpenReport vba is only allowing 1 where clause?
Code:
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant
'add selected values to string
Set ctl = Me.VP_ListBox
[Code] .....
I have a form that contains three multi-select combo boxes, called Market, Readiness, and Role. I am trying to use the user-selected criteria from these boxes as parameters for my query, which is linked to a personnel report.
I have been attempting (without success) to modify code to gather all the selections in each box and then open the updated report. The code below uses the combo box called "Market" and a report called "Succession".
Private Sub Run_Report_Click()
Dim StrWhere As String
Dim varItem As Variant
If Me.Market.ItemsSelected.Count > 0 Then
[Code] .....
Questions:
1. This code is missing something, since when I click my event button to run it, a box pops up to ask for the parameter I already gave in the combo box.
2. What is the syntax to add in the other combo box selections?
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?
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!
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?
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?
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 RelatedI 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.
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