Forms :: Using ComboBox To Look-up A Multivalued Field And Select Items In Listbox?
Jul 21, 2014
I'm creating a Form called Pharmacy where I can select a [Diagnosis] from a combo box in the form. This combo box source references a table called tblDiagnosis where each Diagnosis also has a multi-valued field called [Indications].
The Pharmacy form also has a multi-select listbox with all possible Indications. I would like to write a VBA code such that when a Diagnosis is selected, the Indications for that Diagnosis are automatically selected/highlighted in the listbox on the form as well.
As such, by selecting a Diagnosis, all the indications attached to that Diagnosis will automatically be selected; however, if additional indications are needed, they can still be selected afterward.
I know that there is a function Me!Listbox.Selected(i) = True, where i is the row of the entry in the listbox. However, the i in the listbox does not correspond to the ID of the Indication. I think that if there is a way to select listbox items by name, that would be much more efficient.
The reason I have a listbox, is because this listbox of Indications then references another table called tblDrugs where all drugs that are approved for the selected indications selected are filtered and displayed.
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
I found a code which is work good with subform table for searching multivalued Listbox but it doesn't work with open report vba code. This is the code is used for filtering the subform and i need it for openreport command
If Me.cboDiscipline > "" Then varWhere = varWhere & "[Discipline].value = '" & Me.cboDiscipline & "'" End If
This the code (note: its for open filtered report)
Private Sub Toggle3_Click() Dim strReport As String Dim strDateField As String Dim lngLen As Long Dim strWhere As String Dim lngView As Long Const conJetDate = "#mm/dd/yyyy#"
I am trying to set up a listbox with an option to select multiple items (I have done this and tested it with debug.print and it seems to work). I am then building a filter statement with VBA. I want to then use a button to add this statement to the filter in a subform with (a datasheet design), and then requery it.
My code below seems to be working in part. But I am getting all the items at times. Seems to work consistenly when selecting one item only, but I can't see anything wrong with my 'OR' statements when I debug.print.
Private Sub Command176_Click() Dim i As Integer Dim strFilter As String Dim blnFirst As Boolean i = 0 If Me.List163.ItemsSelected.Count = 0 Then
In my quality control database QC techs will be entering in test results. Each quality control entry they put in will have a product and lot number it is related too.
My question is about making data entry easier for the QC techs. When two jobs are being run at once, the techs may have to alternate between which jobs they enter, meaning a different product and lot number. It would be frustrating for the QC techs to enter in a product code and lot number for every entry they make.
Can the lot number field be automatically populated when the tech selects a product? Basically, if the tech selects product A, can the last lot number value for product A automatically populate the field in the new entry?
Edit: Currently I have the "solution" of making two instances of the entry form with each lot number entered on the different one. This allows the QC Techs to use the CTRL+ ' to populate the last record, allowing them to quickly enter the product code, lot number, and date of the job they are working on.
I have a multi-select listbox (called PotentialItems) which behaves perfectly unless one of the fields has a null value in it, in which case I get an error 13, "Type mismatch" in the following code. It is valid business-wise for column 9 to be null.
Code: Dim varItem As Variant Dim dblTargetPrice As Double With Me.PotentialItems For Each varItem In .ItemsSelected If Not IsNull(varItem) Then dblTargetPrice = Nz(.Column(9, varItem), 0) '<----- Error here End If Next End With
I am using the selections made of the form to generate a query for the user.
I have a CITIES listbox that is populated with values from a stored query.
I would like to make it multi-select and populate a LOCATIONS list box and a NAMES list box based upon the CITIES that are selected.
I have the locations currently populated from a stored query that reads the City selection from the Form. It looks like this
Code:
SELECT DISTINCT (t_location.LOCATION) AS Expr1 FROM t_location INNER JOIN t_asset_master ON t_location.LOCATION_PHY_ID = t_asset_master.LOCATION WHERE (((t_location.CITY)=[Forms]![MasterQueryGenerator]![CityList]));
I also want multi-select so that is you can un-select all and get the results for all cities.
Here is my half thought approach.
Code:
Private Sub CityList_AfterUpdate() 'Dim LocQryStr As String 'Dim r As Integer 'Dim ctl9 As Control 'LocQryStr = "SELECT DISTINCT (t_location.LOCATION) " & _
[Code] ...
I intended to have the variable LocQryStr as the row source but I abandoned the idea of having multi-select when I saw that .Selected(I) never returned true. Its like the values aren't read in this subroutine.
I have a form with a textbox, listbox and a button.
my textbox is based on one of my tables, and when I enter a value (which is saved in my table) in my textbox and press enter, certain values in my table goes to my listbox, and my listbox will just additem whenever I do same thing in my textbox all overagain.
how can I subtract listbox.column(1) items in my table field "Item_Quantity" where my Listbox.column(0) is equal to my table field "Item_Description".
My Listbox column count property = 4 Row source type = value list
My form is a profile of a dog and contains a Dog_ID. The ComboBox is a list of dogs names and shows all the females I want it to show all the females less the female that it is being linked with.
I have a combox with the following query in its RowSource: SELECT [Error Codes].[ID], [Error Codes].[ErrorCode], [Error Codes].[Active] FROM [Error Codes] ORDER BY [Error Codes].[ErrorCode];
I now need to change the query's Rowsource via code and so far I have figured out that this works:
However, the table "Error Codes Missing" also has a field called "Active" which I need to take into account; If the error code is not active then it should not show up in the combobox.
I know how to do that in the original RowSource, but how do I do it with code?
I have a form with a listbox in which users should be able to move the items up and down. The listbox has 4 columns and multiple selection is enabled.
For a NON multiselect box I have it working, and also for a multiple selection box but in that case it works only for one row at a time.
For a NON multiselect listbox:
Code: cmdDown_Click() Dim i As Integer Dim t1 As String, t2 As String, t3 As String, t4 As String i = selectie.ListIndex t1 = Nz(selectie.Column(0, i)) t2 = Nz(selectie.Column(1, i))
[Code] ....
You can select an item and then press "Down" as many times as you want to put the items as "down" in the list as you want it to be.
Multiselect listbox
This code also works for a multipleselection listbox when one item is selected, however, after you press "down" the item is still selected (highlighted) as the code reselects is (last line) BUT the value of selectie.listindex apparently is set to "-1".
Pressing "Down" again generates an error (i = -1). I can evade the error by adding "if selectie.listindex <=0 then exit sub", but that doens't fix the problem that the only way I can manage to reset the listindex to the new "position" of the item is to click on it again and THEN press "Down".
To fix this and be able to press "down" multiple times listindex should be set to the new value.
Adding a "me.selectie.listindex = i -1" doens't work (error), it seems like this value is readonly.
I can't find a way to "simulate" a mousepressed selection and really set the listindex.
The other problem is of course that this code doens't support moving multiple items at once: listindex points to the last selected item, but only one.
So, I tried another piece of code to move multiple items, not using listindex (since that resets to -1), but I run into another problem
Code: Private Sub cmdDown_Click() Dim var As Variant Dim i As Integer Dim n As Integer Dim t1 As String, t2 As String, t3 As String, t4 As String n = Me.selectie.ItemsSelected.Count
[Code] ....
The problem with this is: I can select multiple items and press "Down", but the problem now is that the selection is lost as soon as the code removes the first item, and the 2nd loop skips the if selected(i) = true (nothing is selected anymore). The me.selectie.selected(i+1) = true doens't work since that would only reselect the first item after moving it.
The code "forgets" which items were selected and moves only one item...So I guess I need to put the indexnumbers in memory while moving the items.
I have been searching a lot, but can only find VB-solutions. In VB it's a lot simpeler using f.e. the .list property of a listbox, which is not available in MSAccess
The solution I'm thinking about is:
- set an array with the numbers of selected items - put indexnumbers of the selected items in the array (f.e. 3 and 4) - move items based on the indexnumers in the array - when moving an item update the indexnumber in the array (3>4, 4>5) - after moving all items reset the selection based on the array
It occurred to me then if I'm going to use an array anyway, I might as well load all items in an array, do the "resorting" and the reload the items in the list from the array. Might be more straightforward?
Btw...It seems VB has a simple solution to moving items: listbox.list(i) = listbox.list(i+1) or something like that moves an item. Even Excel seems to have this property but not MS Access!
I'm creating an employee audit database, and, in the audit form, the user (ie. supervisor) can select a number of items from a listbox. Each item selected corresponds to an error that the employee has made, and, as such, the employee's Audit Score has two points deducted for each item that is selected.
Incidentally, there are other, solitary elements to the form, but this particular listbox houses a collection of items that are related under a single category.
The score is displayed at the bottom of the form, and it needs to update in real-time.
The problems that I am encountering are that I am unable to count the number of items selected and then I am unable to multiply that count by 2 (the point-value of each item on the list.)
Dim i As Integer, c As String With Me.ListBox.Column(0) For i = 0 To Me.ListBox.ListCount - 1 c = c & Me.ListBox.ItemData(i) & ", " Next End With c = Left(c, Len(c) - 2)
This works great!! Now I am trying to concatenate the column 3 of the listbox.
Tried to use the code below (pointing to the second column) but it always returns the first column data.
'Dim i As Integer, d As String With Me.ListBox.Column(2) For i = 0 To Me.ListBox.ListCount - 1 d = d & Me.ListBox.ItemData(i) & ", " Next End With d = Left(d, Len(d) - 2)
ItemData does not allow pointing to my 3 column of the listbox.
I'm new to access. Hope someone can offer some help!
I have created a simple database to better organise some day-to-day office data management. For instance, I have a form (just) to record event attended by staff. The data would then be output as a brief report on overall office activities. Since it's event-oriented, staff attendance is just one complementary information. I went through most of the post re multi-select. I understand that it may not be a good idea to return values from multi-select list boxes into a text field, as it would be problematic for using data-analysis in the future.
In my case, I just need to have a text box, neatly (and easy for user, too) shows who attended. As suggested in one of the posting, I follow the instruction of http://support.microsoft.com/default.aspx?scid=kb;en-us;210203. But it doesn't seem working. I attach my databsae (sorry, it's very simple, as I want to test whether the multi-select work).
Your help would be greatly appr:confused: :confused: :confused: ciate!
Also, what about if I group option button, will it allow multiple return by the way?
I have created a form with a 2 column list box. This is populated using vba but when I open the form in form view I cannot seem to select any of the items.
I'm trying to create a right-click event on a listbox that will copy selected listbox item(s) to a temp table. So far, I've got this code to acknowledge the right click:
Code: Private Sub List0_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single) If Button = acRightButton Then MsgBox "You pressed the right button." End If End Sub
Problem is the selected item on the list box doesn't move until after the mouse down event so whatever code I would run would involve the wrong record(s).
I'm using Access 2000 and 2003. How to get the the correct record selected on mouse down, or point me to a working example of right-click functionality on a listbox.
I have a field in a table where it is a lookup is a combo box. So this makes the data type of the field a number when in reality it is a text. When I have this field show in a list box it shows the number instead of the text.
How to do a 'Delete from ListBox' . My listbox is populated, but I want to be able to put a button on the form, select a record from the listbox and press the button to delete that record from the list.
I should add that this is an unbound listbox so it needs to be removed from the listbox and the table that is populating it.
Listbox is List22 the table that populates it is 'tblShootingTasks'
Hopefully somebody can help me on this one. I searched the whole internet and access forums, but I didn't find the exact solution for my problem.
I've got a table with students, a table attendance, where I now only save the students who are absent, but I would like to save also the students who are PRESENT (at the same time). I've got a combobox where I filter the Class, which then updates a listbox with the students from that class. What I do now is select the students from the listbox and then press a save button and it saves the records to the table absence with STATUS: ABSENT.
I would like to save the NON selected students also in that table, but with PRESENT in the column STATUS.
I thought of making another listbox next to it, where after selecting the absent students, they wil apear and disappear in the PRESENT table so I can store all the information. But the only problem is that I can find this solution when the listbox is populated by a list of values instead by a table or query. And the other solution is to store the temporary data into 2 different tables, but that's not working for me because it's a multi user database and everything will be messed up.
Hope that someone can help me, I will be very happy.
I tried to post this yesterday but it didn't go through for some reasons.
I've attached my database that contains student, classes, and couple other tables. I also created couples of forms and I wanted to register a single student to multiple classes at once.
For example: There're 3 classes (Math, English, History) on 1/3/2006. Student A wants to register 2 classes, Math and English. I should be able to check those two and submit and I'm done.
Currently, I have to select Math and assigns to student A, then select English. That's not good at all. What if there're 10 classes in one day and a student just want to register 8 classes? I have to do eight times.
It'd be great if you could help me. Thank you in advance. Isabel
The combobox in the frmResellerOrders works correctly until I include it in the Navigation form frmMainMenu. Once I include it the navigation form it no longer lets me select a value. I have read numerous articles about using the Forms!YourMainFormName.YourSubformContainerName.Form.YourControlName
However, none of these seem to mention when and where to use it. Do I put it in the query that the form is linked with or do I need to put it in my form somewhere? I have been trying to fix this off and on for over a week now. It is one of the last parts of my database that I need to get finished by Monday!
I have attached a copy of part of my database with some dummy data.
