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 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!
hi i apologize if this question has been answered before. i have looked at numerous others in the 'listbox' search box, but not had any success in adapting the answers to what i need.
i have a form A with a listbox of 1,400 rows
i then select maybe the 10th row down on the listbox, and
a new form B opens with information pertaining to the listbox row selected form A changes its visible value from true to false
i do my editing on form B
now what i would like to do is, on form B click a next button and for
form B to close form A to be visible again, and the next row (11th) on the list box be highlighted form B to open again but with pertaining to the next listbox row (11th) selected form A to not be visible
Here's a tough one that has been driving me crazy! (Probably easy, but I don't want to admit it!)
I have a form with a listbox called "lst_exclist". The recordsource for this listbox is the following query:
SELECT tbl_collexcludereasons.priority, tbl_collexcludereasons.excname, IIf(tbl_collexcludereasons.enabled=-1,'Enabled','Disabled') AS enabled, tbl_collexcludereasons.priority FROM tbl_collexcludereasons ORDER BY IIf(tbl_collexcludereasons.enabled=-1,'Enabled','Disabled') DESC , tbl_collexcludereasons.priority;
Which basically gives me this with dummy data: 2 PIPELINE ENABLED 3 HELLO ENABLED 5 GOODBYE ENABLED 1 BAD DISABLED 4 GOOD DISABLED 6 LAST DISABLED
I had two command buttons, one up arrow and one down arrow. The up arrow is supposed to move the selected record on the listbox up by exchanging the next lesser priority number with itself. The down arrow does the same. The DISABLED records are supposed to be ignored on the move up and move down procedures, meaning only ENABLED records are allowed to exchange priority numbers, and thus move up or down on the list. I tried the following code:
Me!lst_exclisthidden = Me!lst_exclist
DoCmd.SetWarnings False
Dim startingnumber As Integer Dim endingnumber As Integer Dim nametochange As String Dim getchangerst As DAO.Recordset
If IsNull(Me!lst_exclist.Column(0)) = True Then MsgBox "Please choose an entry on the above list to move.", vbCritical, "Error" Exit Sub Else End If
If Me!lst_exclist.Column(2) = "Disabled" Then MsgBox "There is no need to move a disabled selection, please enable the selection to change it's priority.", vbCritical, "Error" Exit Sub Else End If
If Me!lst_exclisthidden = acLast Then MsgBox "You cannot move the bottom selection on the list down, please choose another one.", vbCritical, "Error" Exit Sub Else End If
But it seems to loop when it gets the the area it is checking for numbers because the highest records is disabled, so it's ignored. How can I make this work? Please help!
I have a list box contains 10 times . I have UP and Down buttons to move item up and down. My VBA works only if i set listbox multiselect property to 'None'. For the multiselect=simple option it throws error like in valid use of null in this line of code :
sText = lbfNames.Column(0, iIndex)
My VBA
Code:
Private Sub cmdUP_Click() Dim sText As String Dim iIndex As Integer iIndex = lbfNames.ListIndex 'check: only proceed if there is a selected item
I have two listboxes on a form listbox1 and listbox2. listbox1 is populated by table1 which has 40 fields and 1000 records. listbox2 is populated by table2 which has identical structure to table1(same fields) but has no records.
For both listboxes Multi Select is set to simple. Rowsource is Select * statement from tables. Row Source Type is set to Table/Query.
I would like to select multiple records from listbox1 and add them to listbox2 and table2 or just table2. After selecting the records from listbox1 the user will click a button to add the records to table2. Also need to delete the values selected from table1 on same button click.
I have a list box bound to a table. 3 columns (ID, item, open). only item is visible. The box contains forms or reports that will open on double click. The column open contains the name of the form or report to open
Some forms and reports are a work in progress, hence, they are still listed in the list box but the open value is blank and of course won't run.
I'd like to change the colour of the list list box items that won't run.
I'm thinking maybe some kind of If.. isnull type statement...
As my title states, that is my problem! I'm building search criteria from a form and have multiple list boxes... I want to be able to determine whether or not a list box has items selected or not. I have tried as many things as i knew how to with no luck...
Also, i searched the forum but could not find what i was looking for -_-
I have a listbox control on my form which works in conjunction with my search field on the first form. I can add new items with FRM_ItemsAdd. I can delete records by pressing the delete button.
However now I want the ability to Edit items in the list. How can I tell access I want to edit a record?
I have two listbox (SearchResults5 has two columns) and list_asset_add (one column) both have Extended multi-selection active. I have this code that automatically moves the items between the two listbox (it runs after pushing a button):
Code: Sub CopiTo_Click() Dim Msg As String Dim i As Variant If SearchResults5.ListIndex = -1 Then Msg = "Nothing"
[Code] ....
This works quite well if I manually select the items that I want to move. Most of the times SearchResults5 has lots of elements so I have decided to create a "Select All" button to speed up the process, here the code:
Private Sub Command271_Click() Dim n As Integer With Me.SearchResults5 For n = 0 To .ListCount - 1 .Selected(n) = True Next n End With End Sub
When I use the "Select All" button and I try to move the items between the two listbox, the function does NOT work.
BUT if I manually select one or more items in the first listbox, then I clear the selection and finally I use the subroutine to move the items between the two listbox, then the it works well again. How to make it work properly.
I've designed access form with one listbox and added 'up' and 'down' buttons to move up and down the selected item in the listbox. i.e from its selected position to one item up when 'up' button clicked and same as one item down when down button is clicked. Any VBA code will do this functionality?
Hi all, greate site and i have been able to solve most problems by using the search box although this problem is doing my head in...!!
I have a db that records project numbers and their details. I am using a listbox to allow a user to multiselect Project Involvements Tasks(ie Documentation, Build etc) against a project number.
I am able to read the selections into a separtate table with two columns which is structured as:
as you can see I dont have a problem getting the Itemsselected into a table... the problem that i am having is getting them out again when the record is displayed - ie marking them as itemsselected.
I believe that the event would be onCurrent which would loop through this table pick up the project number and recorded invovements and mark them as selected in the listbox. if there is no invovement then the listbox would show no selections.
I am using this code to read the selections in
=========================== 'Records project involvements against project Public Function AddInvolvements(ctlRef As ListBox) As String On Error GoTo Err_AddInvolvements_Click
Dim i As Variant Dim dbs As DAO.Database Dim rs As DAO.Recordset Dim qd As DAO.QueryDef Dim strDelete As String
Set dbs = CurrentDb Set qd = dbs.QueryDefs!qInvolvement Set rs = qd.OpenRecordset
'Delete records where project number exists against an invovelment incase of involvement changes strDelete = "Delete Project_Involvement.ProjectNo " & _ "FROM Project_Involvement " & _ "WHERE (((Project_Involvement.ProjectNo)=[Forms]![Add_Project_Details]![ProjectNo]));"
For Each i In ctlRef.ItemsSelected rs.AddNew rs!InvolvementType = ctlRef.ItemData(i) rs!ProjectNo = Me.ProjectNo.Value rs.Update Next i Set rs = Nothing Set qd = Nothing
Exit_AddInvolvements_Click: Exit Function
Err_AddInvolvements_Click: Select Case Err.Number Case 3022 'ignore duplicate keys Resume Next Case Else MsgBox Err.Number & "-" & Err.Description Resume Exit_AddInvolvements_Click End Select
End Function ===================================
Any help would be much appreciated - also thanks to Pat Hartman for his excellent examples esp http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=54924
I have a combo box that inserts data into an unbound list box and table. This works great but I am having trouble with the deletion part. I want to be able to dbl click on the item in the item list and delete it from both the list box and table. Currently, my code is deleting ALL items, not just the one item I want to get rid of. Any ideas would be most appreciated :) This is what I have for the deletion code:
Private Sub List92_DblClick(Cancel As Integer) DoCmd.SetWarnings False sql2 = "delete from PROFILE_Industry where " sql2 = sql2 & "profile_id = " & Me!Profile_ID sql2 = sql2 & " And Industry_focus = '" & Me!List92.Value & "';" DoCmd.RunSQL (sql2) Me.Refresh DoCmd.SetWarnings True End Sub
I'm trying to setup a listbox so that multiple items may be selected and removed at once. The Listbox Multi Select property is currently set to "Extended" and I have the following code on the onclick event of a command button:
Code:
Private Sub Command12_Click() Dim i As Integer For i = List10.ListCount - 1 To 0 Step -1
[Code]....
I have tested selecting 4 or so records on the list box and then pushing the remove button but it seems that the only record that is removed is the selected record that is furthest down on the listbox. The other selected records become unselected and must be reselected in order to continue removing records.
In my form I have the listFunctions list box set to Multi Select "Extended" in the following code contains a line to execute a query based on the selected items in a listbox. but for some reason instead of only changing the selected items it is changing all items in the listbox. I stepped through the code and it is looping the correct number of times based on the amount selected but is still changing all.
eg.if I select 3 items from the list, it loops through the execute 3 times.but the total 6 items will change.
Code:
Private Sub cmdEdit_Click() Dim varItm As Variant Dim sSQL As String Dim ssSQL As String
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 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
i have a form that there is a list box inside that. after selection of items (usually 20 items) and right click the mouse on items it should open another pop up form,the problem is after right click selected items will be unselected except one item that there is mouse on that. how can i prevent list box from deselecting items after right click .
the code for mouse right click is like below:
Private Sub ItemList_MouseDown(Button As Integer, Shift As Integer, x As Single, y As Single) Const RIGHTBUTTON = 2 Dim udtPos As POINTAPI Dim frm As Access.Form If Button = RIGHTBUTTON Then Set mp = New [*clsMousePosition] GetCursorPos udtPos DoCmd.OpenForm "frmshortcut" DoCmd.MoveSize udtPos.x * mp.TwipsPerPixelX, udtPos.y * mp.TwipsPerPixelY Forms!frmshortcut!txtparameter = Me.ItemList.Value End If End Sub
I am trying to create a list box that passes to the database a value that is different from what shown in the drop down list. For instance, I want to show the users a drop down list with "Yes" and "No", but then storing into the database "1" when "Yes" is selected and "2" when "No" is selected.
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
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.
