The user can select multiple items (non-sequential) on the listbox, say items 1 and 35313. I'd like the listbox to scroll (meaning display) to the currrently selected item, so that the user can see it while being processed.
I've seen stuff about property ListIndex, which does exactly that, but it's only useful when you have just ONE item selected (in a multiselect listbox apparently it deselects the rest of the items, so no point in using it, I guess, unless there's more to it!!).
So the key here is how to do the scrolling/display.
The code to loop through the listbox and process each selected item is attached:
Code:
Dim vItem As Variant
Dim Content as String
For Each vItem In Me.lst.ItemsSelected
'scroll to selected item ???
'processing of the selected item
Content = Me.lst.Column(1, vItem) 'copy the content to do sth with it
Me.lst.Selected(vItem) = False 'unselect the current item
Next vItem 'go to next selected item
how to scroll to the currently selected item? Mind that I need to go through the list of selected items to process them for further use (i.e, I need them selected).
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?
I'm using the following code to clear selected records from a listbox after a button is pressed:
Code:
For i = 0 To lstProducts.ListCount - 1 lstProducts.Selected(i) = False Next
The code works great except that it leaves the listbox scrolled all the way to the bottom. What is the best way to scroll back up to the top of the listbox without selecting another record?
Does anyone know how to get the columns of a listbox selected item and store it to a string? There is no multi-selection so it should be easier. I tried s$ = Me.list1.selected but it keeps giving me an error
I am having trouble with a If statement. I am trying to save a record into a table when an item is Selected (highlighted blue) in a list box. Keep in mind that my listbox is set to multiple selections. I have the following code but sometimes it works and sometimes it doest.
The reason why I am trying to get this to work is because when a item is selected I will add it to the table and when an item is deselected I will delete it from a table. See my code and pictures I have attached :
Private Sub list_audits_Click() Dim strsql As String Dim list As String Dim id As String id = Me.User_ID.Value
Show me the selected item in a Listbox without looping in the whole list, because my Listbox is multiselect and I want only the item that selected recently.
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've been using the following code successfully in Access 2003 & now I need to migrate to Access 2010. The purpose of the code is to use the items that the user selects in the list box to build the criteria of a query. Access 2010 keeps giving me a syntax error when I try to run the query & I don't know why:
My code is:
On Error GoTo Err_Command151_Click
' Declare variables Dim db As DAO.Database Dim qdf As DAO.QueryDef Dim varItem As Variant Dim strCriteria As String Dim strSQL As String
[Code] .....
The syntax error I get in Access 2010 is:
Syntax Error in query expression 'SELECT * FROM qryContractListSummarybyDateContract3TYPEBREAK WHERE qryContractListSummarybyDateContract3TYPEBREAK.Rep ortableName IN('Adbri Masonry NSW');'
I would like to use a listbox set to multiselect to add records to a junction table. I've been using code to accomplish this with checkboxes (love how it looks and works) but after moving my tables to Office 365 as the backend, linkedto a local frontend, sql does not like this particular set up, and I do not have the time or knowledge to sort out why. So what I need is a step by step to look at the many, in this case possible roles a contact can have, and choose one or more, which then creates a record in the junction table with the contact id and role id.
I would prefer to not use a combobox on a continuous form because every time a user goes to select roles he would have to scroll through all the choices for each separate role.
I have added a scroll list to my access form and although I can see the item I need to pick in the drop down list when I manually start typing it, the scroll box doesn't bring the value up and states that the value I have selected is not in the list (when it clearly is !!)
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!)
I have a listbox which is populated by a query from a table.
I also have a sub which allows users to quickly select multiple items from the listbox (based on a pre-defined criteria) rather than scrolling through and selecting them manually / individually
The sub works fine but when it is finished, the listbox is automatically scrolled to the bottom (or, rather, to the last selected item)
I would like to have the listbox scroll back to the very top once all of the appropriate items have been selected.
(I could loop through the listbox items in reverse - or 'bottom-up' - in the code but that would only have the desired effect if the topmost item was selected. I want it scrolled to the top regardless of whether the topmost item is selected or not).
My form utilizes five list boxes. The user must choose from each of the five list boxes in order to filter out a specific product from a products table.Every time the user makes a selection, the next listbox is filtered, and set focus to. Each listbox often contains more than 1 choice within it, however, there are a few cases where there is only 1 choice to be made.
How can i determine with code when a particular listbox only has 1 choice so that I can have the choice be made automatically and then set the focus to the next listbox in the series?
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.
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?
I'm using Access 2010 and have a form that is basically the last step in generating a report (which will be a printable invoice).
I am facing major problems when it comes to removing line items that the user may have accidentally clicked. I have a checkbox field embedded in the table (tblLineItems) which is set up as binary. I have created a form from tblLineItems, and have in turn embedded that as a subform on the invoice generating form.
When I select line items, I have them populating a two column listbox that shows the user what they have selected before actually opening the report. The code I am using is on the checkbox click event in the subform:
Code: Private Sub InvoiceY_N Click() Dim InvLineTotal As Currency If InvoiceY_N.Value = True Then
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?
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?
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
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!"
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
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
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?
