Multi-MultiSelect With 'All'
Aug 27, 2005
Now i need some help here.
I adapted this code from somewhere on this forum. But iam having a problem with using the 'ALL' criteria.
It Opens a Report Dynamically from multiple selections in the two multiselect ListBoxes, but when i select 'All' and select any other item in another listbox
For Example(All in Names and Home1 in Homes) it gives me all the Results.
I have tried several criterias to solve it out but iam still failing.
Hope there will be help. Attached is a sample db in A2K and A97.
Thanks in Advance.
View Replies
ADVERTISEMENT
Sep 7, 2007
I have spent the last couple of days trying to figure out how to make this work.
I have three tables.
tblIntakeMain
[IntakeMainID]
tblIncidentDetails
[IncidentdeatailsID]
tblPersonnel
[PersonnelID]
On the main form I use subforms to link tblIncidentDetails and tblPersonnel to tblIntakeMain. Both subforms can, and do, have many entries. This all works fine. What is not working is the search form I am using.
I am using Gromits most excellent Search Form. The problem is when I create a query, qSearch, to bring together the three tables I get a multiple records which makes the searches very confusing and near useless. Is there anyway around this? Is there something I am missing? Is there another search method I could use that would work in a similar way as Gromits? Please help before the Prozac runs out and I lose my mind--what little it left.
View 5 Replies
View Related
Apr 10, 2008
Hi everyone. Apologies if this has come up before, but the search terms I've tried here and on google keep turning up the wrong information.
At work I manage a large database with many tables. It stores data for participants in a research study. Each table stores the data for a different test, so one participant may have multiple records. Primary keys for these tables are defined by a combination of the participant and date of test fields. (Everything is dependent upon a table that stores the static info for participants, so the database is normalized.)
I want to be able to make a table that lists target participants and dates, and then create a query that looks at this table and pulls all the available data from various tables for those individuals that was recorded within one year of the target dates.
I've successfully made queries that meet these criteria while pulling data from only one table. The problem I'm having is that when I try to pull from multiple tables, each with it's own date field that needs to be used as a criterion, I end up excluding almost all the data, because most of the target participants do not have all the requested data within the target dates.
I've tried being inclusive with my criteria (using ORs), but then I end up with tons of data that I don't want and I need to filter through it, which defeats the purpose of the query.
Any advice on handling this issue, or do I basically just need to create a separate query for each table?
I'm sorry if this is too vague, but it's illegal for me to upload any of my own dataset. I could probably come up with an example if it's helpful, though.
Thanks!
View 7 Replies
View Related
Mar 31, 2005
Hi all
I have an unbound form. To the left I have a list box with a list of employee names. To the right I have a bunch of text fields. Once I've entered the data, I click on a button and it adds a new record. It works perfectly provided I add one employee at a time. It gets painful when I have multiple employees and I'm entering the same data.
Is there a way I can multiselect employees from the listbox, fill in the data fields and add multiple records which just one click??
I'd appreciate any advice.
View 4 Replies
View Related
Dec 25, 2004
Merry X-mas everyone!!!
I'm new to this message board and I'm glad i came across it. I've been looking for a way to multiselct search using Combo boxes.Reading through the threads here i found one (Posted by SBaxter). I've use the SQL Statements and VB code for my search. It works but not like his for some reason. The problem i'm having is when you open the form it asks for criteria like a parameter Query does. You have to type it in before it loads, unlike his where you click what you want out of combo boxes. At first i thought it was something i did wrong with the query but i've noticed that in his DB if you have the form open and then open the query it doesn't act like parameter. It does if the form is close. With mine that doesn't matter i still have to type it in! So i'm guessing it's with how i set up the list box in my form. I've attached a copy for someone to look at.
Thank.
RichM
View 5 Replies
View Related
Dec 19, 2004
Hi all
I have a list box with a bunch of names in it that links to a subform on a master form. At the moment I click on a name and update details in the subform. It would be nice if I could multiselect names then update details all at once without having to individually change each one. I tried the multiselect property but I get index or relationship errors. I don't have any relationships (because I'm not adding new records... just updating) and I changed the indexes to NO. Didn't work. I tried!! Any idea how I might get this working??
I appreciate any advice.
Damon
View 13 Replies
View Related
Apr 19, 2005
I have a table, "people" which has all the main data stored. I have another table, "organizations" which I'd like to be able to use on a form to select which organizations people belong to. I created a button on my main form which opens another form on which a listbox with all the organizations shows. What I'd like to do is from the main form, click a button that brings up a new form. On the new form, I can select the affiliations. Then, when I close that form, a box on the main form shows what i've just selected. I got this to work with the listbox, but when I changed it to multi-select it stopped working. Help!! Thanks.
View 5 Replies
View Related
Jun 25, 2005
I want to be able to select multiple items from a listbox, click a button, and then print the data related to that item selected using a report.
In the report to print the data, I have added the criteria
[Forms]![Print Labels]![List2]
This is the name of the listbox. However, this doesn't print the items selected in the list box.
Do I have to do this through VBA, or can I accomplish this through a query in the report?
Thanks!
View 1 Replies
View Related
Mar 10, 2006
Does any one know of a way to toggle a multiselect listbox's property programmatically?
The One In The VBS Help doesn't work!
View 5 Replies
View Related
Feb 18, 2006
Hi, my problems are numerous!
I have a form with a number of multiselect listboxes. Their sources are individual tables such as YEARS, PARISHES, CLIENTS. The underlying data is stored in a separate table. I want users to be able to select none, one or some values from each listbox and run a report.
As simple listboxes with one selection permitted this worked fine. I know that my problem is in concantenating the values.
I have tried attaching some code to the OnClick function of a cmd button on the form. I found this code referred to a number of times here and have tried to adapt it but no luck. I have now reduced my form to one listbox to test stuff out- this is the code-
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Form!testform
Set ctl = frm!lboparish
strSQL = "Select * from StJamesAgric where [Parish]="
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR [Parish]="
Next varItem
'Trim the end of strSQL
strSQL=left$(strSQL,len(strSQL)-300)
The first error I get is "can't find field 'testform'"
My form is called testform
The listbox is called lboparish
The table with all the data is called StJamesAgric
What am I doing wrong???? And also, if this code EVER works, do I need to repeat it for each listbox or do I insert refernces to each list box within just one subcommand?
Any help greatly appreciated.
Stella
View 5 Replies
View Related
Jan 28, 2005
I was wondering whether it is possible to select multiple values in a list box on a form that can be stored in a table.
View 4 Replies
View Related
May 21, 2007
I have a multiSelect Listbox that has email recipients. I have a command button with Me.emaillist = Null, and after I click the command button the Listbox is still highlighted in black. What VBA command do I create to completely clear the multiSelect Listbox, including the highlighted selection?
losstww
View 1 Replies
View Related
Nov 8, 2013
I have a single-user application about to become multi-user.
There is a table, displayed in a form as datasheet, where we do lots of things with the selected items.
Code:
MyID MyItem IsSelected ....
1______ItemA__ Yes
2______ItemB__ Yes
3______ItemC__ No
....
The user checks the IsSelected (bound to a checkbox) for the items required for further processing, does the processing and starts over. The function of the column IsSelected is to hold the boolean signifying whether or not to process the record, and to bind to the checkbox in the form.
How to retain the user interface (i.,e. selecting using one or more checkboxes) if the database is to be used by more than one user?
View 4 Replies
View Related
Jun 29, 2007
I would like to be able to create a recipe (saved in its own table) by pulling items from a multiselect list box (from the raw materials table) but which allows me to add the same ingredient more than once.
I work for a chemical manufacturing company and we need to track the cost of goods by recipe (final product), by linking the ingredients (where the cost of the raws are associated) to the final prodcut. Several of our products need to have chemicals added in the beginning, but then again at the end of the procedure unlike normal kitchen recipes.
(I sure hope I don't have to learn VBA . . . I'm totally new at Access!)
Thanks in advance for your help.
View 3 Replies
View Related
Jan 26, 2005
I'm trying to create a database where a single "Classification" field is populated by selections made in a multiselect listbox and I can't figure out how to do this. Any help people can provide?
Table 1:
Name_ID <pk>
Name
Classification
Table 2:
Classification_ID <pk>
Classification
What I want to happen is click on a button next to the Classification field (text) and a popup form with a multi-select list loads (this part is easy, of course). The user can select as many classifications as they want, click the ok button and each item selected then goes back to the first form and populate in the Classification field (seperated by commas or semi-colons).
This possible?
View 3 Replies
View Related
Jan 27, 2005
Hi,
I have adapted code from ghudson's example on
'http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=52736
I have a subform called frmsJobPartsUsed, which contains a multiselect list box where the user can select multiply parts used for one job and click a save button, which saves the parts to rows on the same forms (see picture). The user then enters the number used and that number is taken away from the UnitsInStock.
This form is made up of the following two tables;
TblStore
PartNo
PartName
UnitsInStock
ReOrderLevel
Discountinued
Remark
tblPartsUsed
PartUsedID
JobDetailsID
PartNo
PartUsedNum
NumberUsed
The multiselect listbox is made form tblStore, PartNo, PartName and Discontinued = 0
This all works fine so far.
What is need to do is before the parts selected are saved to the table I want to run some code to check
If a part’s UnitsInStock is equal to 0 then Message box saying no stock left need to reorder. It won’t save it to the table.
Or else
If UnitsInStock is greater than 0 but less than or equal to ReOrderLevel
Message box saying Stock running low need to reorder asap.
I have this kind of working but it doesn’t seem to be finding the correct UnitsInStock for the part selected.
Here is the code;
Private Sub cmdAnswer_Click() 'SAVE BUTTON
On Error GoTo ErrMsg:
'Code adapted from ghudson's example on
'http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=52736
Dim myFrm As Form, myCtl As Control
Dim mySelection As Variant
Dim iSelected, iCount As Long
Dim myDB As DAO.Database
Dim myRst As DAO.Recordset
Dim myRstCount As DAO.Recordset
Set myDB = CurrentDb()
Set myRst = myDB.OpenRecordset("tblPartsUsed")
Set myFrm = Me
Set myCtl = Me.lstAnswers
iCount = 0
'Count number of selected records/items
For Each mySelection In myCtl.ItemsSelected
iCount = iCount + 1
Next mySelection
'Check if anything is slected
If iCount = 0 Then
MsgBox "There are no Parts selected..", _
vbInformation, "Nothing selected!"
Exit Sub
End If
StrSQLCount = "SELECT tblPartsUsed.JobDetailsID, Count(tblPartsUsed.PartNo) AS CountOfPartNo " & _
"FROM tblPartsUsed " & _
"GROUP BY tblPartsUsed.JobDetailsID " & _
"HAVING (((tblPartsUsed.JobDetailsID)=" & [Forms]![frmJobs]![JobDetailsID] & "));"
Set myRstCount = myDB.OpenRecordset(StrSQLCount, dbOpenSnapshot)
'SART OF MY CODE TO CHECK FOR UNITSINSTOCK
For Each mySelection In myCtl.ItemsSelected
If Me.UnitsInStock.Value = 0 Then
MsgBox "Out of Stock!" & Chr(13) & "Please returen to Orders or Store to Re-Order Stock. " & Chr(13) & " ", vbOKOnly + vbCritical, "Re-Order Stock"
myCtl.Selected(mySelection) = False
Else
If Me.UnitsInStock.Value > 0 And Me.UnitsInStock <= Me.ReOrderLevel.Value Then
MsgBox "The Store is running low on stock!!" & Chr(13) & " Please return to Orders or Store to re-order as soon as possible.", vbInformation, "Need to Re-Order Stock"
End If
End If
Next mySelection
'END
iCount = 0
'Go throught each selected 'record' (ItemsSelected) in listbox
For Each mySelection In myCtl.ItemsSelected
'Current count of selected items
iCount = iCount + 1
'Print value to Immediate Window
Debug.Print myCtl.ItemData(mySelection)
'Add answers
With myRst
.AddNew
.Fields("JobDetailsID") = Forms![frmJobs]![JobDetailsID]
.Fields("PartUsedNum") = iCount
.Fields("PartNo") = myCtl.ItemData(mySelection)
.Update
End With
Next mySelection
'Requery form
Me.Requery
ResumeHere:
Exit Sub
ErrMsg:
MsgBox "Error Number: " & Err.Number & _
"Error Description: " & Err.Description & _
"Error Source: " & Err.Source, vbCritical, "Error!"
Resume ResumeHere:
End Sub
Private Sub cmdUnselect_Click() 'UNSELECT BUTTON
On Error GoTo ErrMsg:
'Code adapted from ghudson's example on
'http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=52736
Dim myFrm As Form, myCtl As Control
Dim mySelection As Variant
Dim iSelected, iCount As Long
Set myFrm = Me
Set myCtl = Me.lstAnswers
'Count number of selected records/items
For Each mySelection In myCtl.ItemsSelected
iCount = iCount + 1
Next mySelection
If iCount = 0 Then
MsgBox "There are no selections to Un-Select..", _
vbInformation, "Nothing selected!"
End If
'Go throught each selected 'record' (ItemsSelected) in listbox
For Each mySelection In myCtl.ItemsSelected
Debug.Print myCtl.ItemData(mySelection)
myCtl.Selected(mySelection) = False
Next mySelection
ResumeHere:
Exit Sub
ErrMsg:
MsgBox "Error Number: " & Err.Number & _
"Error Description: " & Err.Description & _
"Error Source: " & Err.Source, vbCritical, "Error!"
Resume ResumeHere:
End Sub
Any help would be greatly appreciated.
Thanks in advance
Rita
View 1 Replies
View Related
Mar 23, 2005
Hello,
I've scoured the dozen archived threads that relate to my challenge, but I still can't quite overcome it.
I have an aggregate query that runs but returns no values.
This is how I've tried to set things up:
A user makes a multiple selection in a list box on a form.
The multiple selection gets strung into an SQL that I feed to a hidden control on the form.
Whatever is in the hidden control then becomes the Where clause that I reference in a Criteria cell in my query.
The query is run when the user presses a command button.
Can anybody tell what's wrong with my code? Thanks very much in advance!
Private Sub cmdTractSelect_Click()
On Error GoTo Err_cmdTractSelect_Click
'Declare variables
Dim db As Database
Dim tbl As TableDef
Dim fld As Field
Dim qry As QueryDef
Dim frm As Form
Dim ctl As Control
Dim varItem As Variant
Dim strSQL As String
'Assign values to the variables
Set db = CurrentDb()
Set tbl = db.TableDefs("City")
Set fld = tbl.Fields("Tract")
Set qry = db.QueryDefs("Param")
Set frm = Forms!frmTractSelect
Set ctl = frm!lstTract
strSQL = "[Tract]="
'Grab the list-box selection and string it into SQL
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " Or [Tract]="
Next varItem
'Make sure the user selected at least one tract
If Len(strSQL) = 0 Then
MsgBox "You didn't select anything" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If
'Trim the SQL
strSQL = Left$(strSQL, Len(strSQL) - 12)
'Insert the SQL into a hidden control that will feed_
'the criteria of the Tract field
txtWhere.Value = strSQL
'Run the query
DoCmd.OpenQuery ("Param")
Exit_cmdTractSelect_Click:
Exit Sub
Err_cmdTractSelect_Click:
MsgBox Err.Description
Resume Exit_cmdTractSelect_Click
End Sub
View 4 Replies
View Related
Apr 19, 2005
OK - I have seen the other posts where individuals are trying to select multiple items from a list box and have a field populate with the selections. I have not seen a clear explanation defining if this is possible.
Essentially, I want to be able to query on the field and search for multiple selections within that field. Any recommendations as to how this can be achieved?
My next question, is if the selections in a multiple instance field are separated by a comma or some other character what is the best method to query for multile responses. For example, if the following data is in the field 1,2,3 and I want to query on 1 or 2?
Regards,
PolarBear
View 3 Replies
View Related
Jul 13, 2006
I am using Access XP.
I have a table for experiments - tblexp.
Each experiment can have one or more projects within it. So I have a drop down list of the various projects in a separate table - tblprojref with fields project no (projno) and project title (protitle).
When I am setting up a new experiment I am entering the information into a form. Along with other information that populates the underlying table, I would like to have a multiselect list box which allows me to pick the projects linked to the experiment. These would then either populate the same tblexp or another. I can only achieve this if I am only selecting one project from the list.
How can I get the several items selected into different fields and linked to the one experiment? I am new to this and although have found various threads relating to this they include a lot of code which I don't understand. Is there a step by step easy way to achieve this?
View 2 Replies
View Related
Dec 12, 2006
Hi, i was wondering if it's possible in a listbox with the multiselect option set to extended simply click on a field and open a form with the data of this field..
example:
i've a listbox of my customers, (only first and last name)..i click on one of this and a new form with all the details'll open, so i can see and modify stuff...
with the multiselect set to none this works..but with the extended??
(btw..i wanna just click on one filed, but i need the extended multiselection on for other options..)
thx in advance
View 14 Replies
View Related
Oct 15, 2013
I have inherited a database that is used to track equipment that comes in for repair. One of the fields in this database is a memo field that lists the parts that are replaced on a piece of equipment. It has been requested that I leave this field as it is. However, I would like to regulate the data that is entered into to some extent. I need to ensure that there is a comma between every part number listed (there is another group that wants data from this database and they want to be able to separate this field out into individual fields). Is there a way to have Access look at the data in a memo field, find all the periods or ampersands, and replace them with commas?
The other thought I had was to provide the person filling in the database with a multi-select list box that lists all the available part numbers for a given piece of equipment. That person could select as many as she wants and when she was done, the database would take those values, put a comma between each one, and paste them in the memo field. Is that possible? What I have tried isn't working. I tried to get it to loop through the selected items and put them in the memo field, but I just end up with the last value selected instead of all of the items selected.
I don't know what to try next, Either the person entering the data is going to be upset because she has to type every part number in a different field, or the people receiving the data are going to be upset because there isn't a comma between each number.
View 5 Replies
View Related
Apr 16, 2015
My table "EmpTraining" will store 5 fields:ID (PK Autonumber )
EmpID (From Employees Table)
FleetName_ID ( From FleetList Table )
Engine1_ID (From EngineList Table )
Engine2_ID (From EngineList Table )
At the point of adding a record to EmpTraining table , EmpID and FleetName_ID and Engine1_ID are Required, Engine2_ID is optional.
I am having trouble generating a Report Filter that will use 3 Listboxes(multiselect) and be able to limit the report by what is selected.
My Listboxes filter:EmpBase (from Employees Table)
FleetName_ID ( From FleetList Table )
Engine1_ID (From EngineList Table )
I am trying to use the Third listbox to filter both Engine1_ID and Engine2_ID and I think this is my main problem.
On the Filter form that has the ListBoxes I use a button to apply the filter to the report, the code for this Onclick event is:
Code:
Private Sub btnApplyFilter_Click()
Dim varItem As Variant
Dim strBaseStation As String
Dim strFleetType As String
Dim strEngineType As String
[code]....
How can I also make it filter the Engine1_ID and Engine2_ID fields .?
Or Should I store the FleetName/Engine1/Engine2 in a different way?
View 3 Replies
View Related
May 22, 2014
Is it possible to have a multiselect listbox on a subform with a datasheet view or do I need a combobox in this situation?
View 4 Replies
View Related
Jul 24, 2015
I have created a multi field search form that have 2 textbox and 2 multiselect listbox(extended). How to make the search form query correctly? Below are the details.
Form
frmSearchForm
2 textbox and 2 multiselect listbox
Textbox 1 > txtFirstName
Textbox 2 > txtLastName
Listbox 1 > lboSports
Listbox 2 > lboSchool
[code]...
I also have a button that run the query qrySearchForm .how am I going to make the query run successfully with multiselect listbox ? I understand that there are a lot of examples of the vba code for multiselect but that is only for multiselect alone and not like my search form that combine textbox and multiselect listbox.
View 14 Replies
View Related
Jul 11, 2013
"I have 1 "main" access file and "Portable".
In the form of main I creat buttom to open and apped the table of other access file-portable.accdb- to the main table!"
I had a problem before about attachment field appending anj JHB solved that problem in this link.
"I have 1 "main" access file and "Portable".
In the form of main I creat buttom to open and apped the table of other access file-portable.accdb- to the main table!"
See that problem and download attachment of that topic.
But I want to append a table with multi select combo box. That combo box field has query from table "list" and i want to append this 2 table (asli & list) to a main database!!!
Attachment instruction:
1-solved pervious problem(OK)
2-problem with combobox query(has ERRROR)
View 14 Replies
View Related
Jul 31, 2013
I created a form with a ListBox and a Command Button. The users selects the values in the listbox and then click the button to create word documents. I've written VBA code to accomplish this. But it's not working properly. It opens multiple word documents but all for the same one.
Private Sub Command6_Click()
Dim appWord As Object
Dim varItem As Variant
Dim strPathToTemplateFile As String
Dim strPathToProspectiveFile As String
Dim strPreferredFileName As String
For Each varItem In Me.List0.ItemsSelected
[code].....
View 13 Replies
View Related
