Query Selected Fields From A Multi-select List Box

Multi Select Listbox To Pass Selected Values To A Query
All -

For the life of me I can't get the Multiselect Listbox to correctly pass along all of the item selections to a Query which a form is based on.

I've been up and down the forum, and I can't figure out what piece of code to use and how to use it successfully.

I've been able to get a string created using the example posted here (http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=47909) and it's in the format of "54,67,89,100" etc.

Public Function
Public Function fMultiSelect(ctlRef As ListBox) As Variant
Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In ctlRef.ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & ","
End If
Criteria = Criteria & Format(ctlRef.ItemData(i), "0000000")
Next i

fMultiSelect = Criteria
gMultiSelect = Criteria
End Function

Call:
Call fMultiSelect(Forms!frmPreSPIPComp!lstProjects)


I now need to pass that string to a Query. Once it's been passed to the query, I can open the report based on it.

Essentially I have a button that will perform the string creation, and I would then like to open a report. I want to base the report off of a query and then have the query use Criteria to dwindle down the report.

Am I missing something here?

The long explanation:

I have a single form that allows for the selection of the report. Once the report is selected, certain fields appear that allow for certain criteria to be selected (ie. Class Name, Multi-Select Class Name, Student Names, Multi select Student Names, Dates, etc.)

Once the report has been selected and the criteria set, a user hits a single button that runs the specific report.

Any better ideas of how to set this up. The reports will ultimately be basing their criteria on what the form has in all of it's unbound fields.

I also have a table that specifies the Display Name, actual Report Name for the button to figure out what report to run.

Bottom Line. I want to use a Listbox to filter a report. If I can use a query to base the report off of even better. I don't want to create the SQL in VBA.

Any ideas? Thanks!

View Replies !   View Related

---

Assign Query Output To Multi Select List Box
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 Replies !   View Related

---

Multiple Combo Boxes Instead Of Multi-select List For Query ... ?
I have a database to keep track of overtime. Via combo boxes, the form prompts the user for department, date range, and employee and returns the amount of overtime worked by that employee for that department in the given range. I'd like to put 5 combo boxes on the form and allow the user to select up to 5 employees, resulting in a report with each of the employees listed with their respective data. I have considered a multi-select list box, but since we have 300+ employees the ability to start typing a last name to select each employee seems a little more user-friendly. I do have a report that lists all employees in the selected department and date range, but want the ability to select up to 5 employees instead of look through a report/list with 300 names. I am new to Access, would like to know I'm proposing is the most feasible way to accomplish what I need and a general example of how the query would be set up. Thank you in advance for your time.
Ceidre

View Replies !   View Related

---

List Box One Click Select/deselect With Multi Select
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 Replies !   View Related

---

Multi-Select List Box
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 Replies !   View Related

---

Multi-Select List Box
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 Replies !   View Related

---

Multi List Select Problem
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 Replies !   View Related

---

Using Multi Select List Boxes
i have been looking at threads on this subject, and i cannot see the answers to my problem,

i have a subform containing a list box (list box1) of values relating to the critical value of the subfrm

what i want to do is have the ability to change the records contained in the list box1
currently to do this, i have created a pop up form with a list box (list box2) containing all the potential values of (list box1),
on closing the pop up form, there will be append and delete queries to change the records in listbox 1 and other related tables, this i have managed to work out already

my problems are 2.

how do i highlight the records in listbox 2, that are already contained in listbox 1

on closing the pop up form, how do i update the listbox 1 on the subform with the amended records

as ever, i apologise for the lack of techincal terminology and appreciate any help in advance.

View Replies !   View Related

---

Multi Select List Box Formatting
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 Replies !   View Related

---

Two Multi Select List Boxes
I have two multi select list boxes that I want to filter a query. I can curently filter the query with selection from one list box. I would also like the query to appear in a subform and the email address that the query out puts to appear in a text box.

Here is my curent code:

On Error GoTo Err_cmdClerkship_Click
Dim MyDB As DAO.Database
Dim qdef As DAO.QueryDef
Dim i As Integer
Dim strSQL As String
Dim strWhere As String
Dim strIN As String
Dim flgSelectAll As Boolean
Dim varItem As Variant

Set MyDB = CurrentDb()

strSQL = "SELECT * FROM tblClerkship"

'Build the IN string by looping through the listbox
For i = 0 To lstrole.ListCount - 1
If lstclerkship.Selected(i) Then
If lstclerkship.Column(0, i) = "All" Then
flgSelectAll = True
End If
strIN = strIN & "'" & lstclerkship.Column(0, i) & "',"
End If
Next i

'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE [strClerkship] in (" & Left(strIN, Len(strIN) - 1) & ")"

'If "All" was selected in the listbox, don't add the WHERE condition
If Not flgSelectAll Then
strSQL = strSQL & strWhere
End If

MyDB.QueryDefs.Delete "qryMaster"
Set qdef = MyDB.CreateQueryDef("qryMaster", strSQL)

'Open the query, built using the IN clause to set the criteria
DoCmd.OpenQuery "qryMaster", acViewNormal
'DoCmd.OpenForm "qryMaster", acNormal

Thanks

View Replies !   View Related

---

Using Multi Select List Boxes To Run Queries
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 Replies !   View Related

---

Multi-select List Record Creation Code
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 Replies !   View Related

---

Filter Query By Multi Select Listbox
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 Replies !   View Related

---

Can I Dynamically Choose Which Fields Are Being Selected In A Query?
Is it possible to have a form designate which FIELDS to include in a selection query? I know that I can determine criteria and such, but i want the user to be able to generate a report based on the query select a, b, c from blah, or if they wish, just select a, b from blah...

View Replies !   View Related

---

Query Help - ID's Multi Fields
Hi I Like to know how to aquire the correct results:

Table:
ID - Personalinfo - location - records - date
102 - Dave Blogs - Bristol - Queen - 12.12.03
103 - Brian Smith - Plymouth - SKA - 12.11.03
102 - Dave Blogs - Bristol - Madona - 12.12.03

By selecting the ID I want to show all listings for that ID.

ie:
102
Dave Blogs
Bristol
Queen 12.12.03
Madona 12.12.03

Im going round in circles

View Replies !   View Related

---