Using Multi Select List Boxes

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

---

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

---

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

---

Query Selected Fields From A Multi-select List Box
I'd like to return - preferrably through a query but a report works as well - selected table fields. I currently have a form that selects certain records from the table based on a couple multi-select list boxes. However, I'd also like to be able to select which fields are queried/reported from the table. I can make a list box containing the field values of my table but I'm not sure where to go from there. Thanks in advance for any help.

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

---

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

---

Select Values In Multi Select Listbox
i have a multiselect listbox in my form.
The multiselectlistbox contains the names of different persons from tblUsers.
it's allready possible to write the id's of the names to another table (tblPresent).

But what I can't manage to do is re-select the values in another multiselect listbox. This multiselectlistbox is located on my editform.
I can display the values using a valuelistbox, but i need to see the non-selected items too..

hope someone can help me out

View Replies !   View Related

---

Forms +List Boxes +Text Boxes With Formulas
I have a form with a List Box (List BoxA) that returns a value from a query. I then have a Text Box (Text BoxB) that uses the value from List BoxB and multiplies it by let's say 2. It works giving me the correct value but only becomes visible after I click inside List BoxA. How can I make it visible as soon as the form is opened? I tried refreshing the form data, didn't work and I tried a requery macro which didn't work either.

Thanks,

Lester

View Replies !   View Related

---

Multi Combo Boxes, Which One Runs
I have 6 combo boxes on a form, which call a report based on a query. The idea is that the user can query data 6 different way. How do I/ or the user know which one is actually running first. Would the order left to right make a difference? (None have sort ascending or descending)

View Replies !   View Related

---

List Boxes And Combo Boxes
Hi everyone,
I have been making progress with customizing a
MS Access program, but one major problem is that
I have been trying to make a List Box or Combo Box
that I can use to enter data in the TABLE, but I
find that I get a pull-down list that has the list of
values from only the parameter that is primary key.
More specifically, the program is set up as follows:
I have two tables in this program: One that is called
"invoices" and one that is called "items". In both of
these tables, there is one common parameter, which
is "Item Number". Item number is the primary key,
and I used the "relationships" function to tie this
parameter to itself between the two tables.
I was successful in setting up a list-box for the
"Item Number", but when I try to set up a list-box
for another parameter that is supposed to display
a person's initials, the pull-down list displays the list
of item numbers instead of the list of people's initials.
In fact, I don't know if there is an extra step I need to
take so that the database stores a list of people's initials.
Instead, I just fill in the initials in the field for each
record for which the "Item Number" is the primary key.
How can I get the list-box to pull down a selection of
different people's initials, or in other words how can I get
all the people's initials to be stored so that the list of
initials can be looked up. Just so you know, I have tried
different choices of entries in the "Lookup" tab in the
Design mode of Tables, including Display Control,
Row Source Type, Row Source, and Bound Column,
but the outcome is that the only parameter that I
get get in the pull-down list is the primary key, which
is Item Number.
I appreciate any help you can offer in explaining how
to correct this.
On a separate note, one of the parameters is "Date", and
on the reports, I'm trying to figure out how to filter a
specific date range so that I can limit each report to a
specific month. Please advise me on this procedure as well.
Thanks.

View Replies !   View Related

---

Multi Select Box's
I am looking to see if anyone can help i have been able to get the multi select function part of my form working but i need another filter in the query for a date but it keeps deleting this part of the query everytime i run the report what i need to be able to do is get all jobs against a specific leadcraft that are in the past and also 31 days into the future.

Paul

here is the sql so far, thanks to those who helped already.

Private Sub preview_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("FAelecplan")
If Me!Leadcraftlist.ItemsSelected.Count > 0 Then
For Each varItem In Me!Leadcraftlist.ItemsSelected
strCriteria = strCriteria & "MAXIMO_V_WORKORDERS_FA.LEADCRAFT = " & Chr(34) _
& Me!Leadcraftlist.ItemData(varItem) & Chr(34) & "OR "
Next varItem
strCriteria = Left(strCriteria, Len(strCriteria) - 3)
Else
strCriteria = "MAXIMO_V_WORKORDERS_FA.LEADCRAFT Like '*'"
End If
strSQL = "SELECT * FROM MAXIMO_V_WORKORDERS_FA " & _
"WHERE " & strCriteria & ";"
qdf.SQL = strSQL
Dim stDocName As String
stDocName = "elecplan"
DoCmd.OpenReport stDocName, acPreview
On Error GoTo Err_preview_Click
Exit_preview_Click:
Exit Sub

Err_preview_Click:
MsgBox Err.Description
Resume Exit_preview_Click
End Sub

View Replies !   View Related

---

Multi-line Scrollable Text Boxes??
Hi All,

I'm a noobie with Access, and on my form which displays Account information, I also want to display the multiple Addresses this account can have. I can't find any type of scrollable text box (something to look like a spreadsheet), and I know it's not a list box I need to use.

How do I create this and what is it called? I tried using a data grid but I dont think that is it either :confused:

Also, my scrolling text icon on the toolbox is greyed out, not sure why.

Please help!

Thanks,

Mike

View Replies !   View Related

---