Referring To Bound Column In Multi Select Listbox
Dec 12, 2012
So I have a list box that lists organizations. I recently changed the list box type to extended multi select. On the same form, I have a button that opens a new form where the user can input contacts for each organization. When the list box was not multi select, the expression [forms]![media]!
[List30] made the default value of one of the fields in my contact form the bound column from the selection in the list box. However, now that the list box is multi select, the contact input form does not seem to be able to get the value from the bound column in the list box. When multi select is turned on, is the bound column stored differently.
To even get the contact input button to work, I had to change the code from:
Private Sub Command40_Click()
On Error GoTo Err_Command40_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Contactsqry"
stLinkCriteria = "[Organization ID]=" & Me.List30
[Code] .....
View Replies
ADVERTISEMENT
Feb 6, 2015
I have two listboxes. One for Activities and the other for Organizations.
I set the Org listbox to be multi-select so I could run a loop on that listbox to join multiple Orgs to a single Activity. That part works well.
To do so, I am using the bound column (which is the pkey value) from the multi select listbox, and on the single select listbox I'm using the ListboxName.value to gather the pkey for the "1" side of this 1:Many series of inserts.
Now... I want to use one of the other-than-bound-column value from the multi-select listbox, but I don't know how. When setting a value I only know of the use of :
Code:
ListBoxOrganizations.ItemData(varItem)
and I don't know how to do the equivalent of
Code:
ListBoxOrganizations.ItemData(varItem's column(2).value)
yes, I just made that up, but I hope you get the point. Meaning, as the loop cruises the ItemsSelected on the multiselect I'd like to use other than the bound column when setting variables.
I've tried using the column property to then cruise to the proper record in the listbox, e.g. :
[code]
txtCollaborationDesc = "Collaborate " & ListBoxOrganizations.Column(2, varItem) & " with " & listboxActivities.Column(2)
[code]
But this seems to return the column(2) value of the first record loaded into the list box, as if the varItem piece is being ignored. I believe the loop is working properly, as when the inserts are happening correctly with the bound column of the multi-select list is correctly present as an Fkey in the resulting child records.
I just can't get any other column's value for some reason.
MS Access 2010 and this is an accdb.
View 3 Replies
View Related
Jun 10, 2015
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.
View 5 Replies
View Related
Aug 10, 2005
Hi
I've created a listbox and am using the following code
stWhat1 = "": stCriteria1 = ","
For Each vItm1 In Me!LstArchive.ItemsSelected
stWhat1 = stWhat1 & Me![LstArchive].Column(0, vItm1)
stWhat1 = stWhat1 & stCriteria1
Next vItm1
Me!txtCriteria1 = CStr(Left$(stWhat1, Len(stWhat1) - Len(stCriteria1)))
The problem is that my bound column is a text field. I'm really struggling on where to put my quotations so that I get the following with the In function
In('00638','00639').
Any help or pointing in right direction would be very much appreciated
Carrie
View 3 Replies
View Related
May 12, 2014
I have a list box bound to a query. If the list box retrieves two records, I am not able to select only one of them. If I click, it gets both records selected ( highlighted ). Is there any way I can select only one record?
View 8 Replies
View Related
Aug 19, 2005
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 1 Replies
View Related
Jul 19, 2005
hello!
i'm trying to select more than one value in a listbox based on a comma-seperated string in a textbox. it seems to be doing what it's supposed to do, except it's only selecting the last value it loops through. it doesn't seem to remember the others. there's probably an easier way to do this than how i did it, but here is the code:
Code:Dim lst As ListBoxDim lngCount As LongDim strSelection As StringDim strNewSelection As StringDim intLen As IntegerDim lngLen As LongstrNewSelection = ""strSelection = Me.Text19.ValueWhile InStr(1, strSelection, ",") <> 0strSelection = Trim(strSelection)lngLen = Len(strSelection)intLen = InStr(1, strSelection, ",")strNewSelection = Left(strSelection, intLen - 1)Set lst = Me!List0lst.RowSource = lst.RowSourceFor lngCount = 0 To lst.ListCount - 1 If lst.Column(0, lngCount) = strNewSelection Then lst.Selected(lngCount) = True Exit For End IfNext lngCount strSelection = Right(strSelection, lngLen - intLen)Wend
any bright ideas???
thanks,
*j
View 3 Replies
View Related
Oct 22, 2013
I wish to generate a new query consisting rows that I have selected in a multi colum list box. May I know if it is possible to work? I am totally lost now
View 10 Replies
View Related
Jul 27, 2006
Dear All,
I am just starting out in Access Development. I have a database that runs a logistics department. This was working fine until the users wanted a little more tweaking.
The report prints out the vehicles with all corresponding drops. This comes out all on sheet.
Is there a way that I can create a form to filter the report via a multi select listbox and print each drop selected on a seperate sheet of paper.
I am using Access 2003
Many thanks for any help or source code given
View 3 Replies
View Related
Jun 25, 2014
I am having a form that has a listbox, which brings up a lot of records, and the user may choose multiple records, but when user wants to scroll down the page to multi select, he cant do it with the ctrl keyboard button, when he press ctrl the page jumps back up, is there any workaround for that?
View 14 Replies
View Related
Nov 26, 2014
I recently have ontained some responsibilies from a coworker who has left the company.
I have frmEdgeReport which uses ratio buttons to populate the
multiselect listbox .. this all works.. now i need to add date ranges and
Everything I do seems to screw up what I already have working..
I have added two textboxes - txtStartDate and txtEndDate.
Currently I am getting error when i put the SELECT statement in its own query that says "Invalid SQL Statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', OR 'UPDATE'."
and when I just run my form I am getting an error that says "Error 3142 - Characters found after end of SQL statement"
this is what i have so far :
Private Sub SetReport()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the list box.
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
[Code] .....
View 1 Replies
View Related
Sep 24, 2014
1. I have created a parameter query in access 2010. This parameter is on only one field.
2. I have created a multi-select list box in a form so that users can select one of more items.
I want to pass the selected items as parameters to the save access query.
View 1 Replies
View Related
Oct 10, 2014
I'm using pbauldy's code to filter a report by a multi-select listbox. The only issue is..how do I include more (7 to be exact) listboxes to the code? It seems the OpenReport vba is only allowing 1 where clause?
Code:
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant
'add selected values to string
Set ctl = Me.VP_ListBox
[Code] .....
View 7 Replies
View Related
Jan 30, 2008
All -
For the life of me I can't get the Multiselect Listbox to correctly pass along all of the item selections to a Query which a form is based on.
I've been up and down the forum, and I can't figure out what piece of code to use and how to use it successfully.
I've been able to get a string created using the example posted here (http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=47909) and it's in the format of "54,67,89,100" etc.
Public Function
Public Function fMultiSelect(ctlRef As ListBox) As Variant
Dim Criteria As String
Dim i As Variant
' Build criteria string from selected items in list box.
Criteria = ""
For Each i In ctlRef.ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & ","
End If
Criteria = Criteria & Format(ctlRef.ItemData(i), "0000000")
Next i
fMultiSelect = Criteria
gMultiSelect = Criteria
End Function
Call:
Call fMultiSelect(Forms!frmPreSPIPComp!lstProjects)
I now need to pass that string to a Query. Once it's been passed to the query, I can open the report based on it.
Essentially I have a button that will perform the string creation, and I would then like to open a report. I want to base the report off of a query and then have the query use Criteria to dwindle down the report.
Am I missing something here?
The long explanation:
I have a single form that allows for the selection of the report. Once the report is selected, certain fields appear that allow for certain criteria to be selected (ie. Class Name, Multi-Select Class Name, Student Names, Multi select Student Names, Dates, etc.)
Once the report has been selected and the criteria set, a user hits a single button that runs the specific report.
Any better ideas of how to set this up. The reports will ultimately be basing their criteria on what the form has in all of it's unbound fields.
I also have a table that specifies the Display Name, actual Report Name for the button to figure out what report to run.
Bottom Line. I want to use a Listbox to filter a report. If I can use a query to base the report off of even better. I don't want to create the SQL in VBA.
Any ideas? Thanks!
View 5 Replies
View Related
Apr 7, 2013
I have a multi select listbox that I rebuild as I index thru some products in another listbox. This listbox then gives me the ingredients I can use. Works well, but the ghost selections in the listbox stay hilighted even after the rebuild.
I can select and de-select at will if there are actual items in the list box, but the ghosts remain.
Here is the code to clear the listbox
Dim _
ctlSource As Control, _
varSelected As Variant, _
intListCount As Integer
Set ctlSource = Forms.frm_select_UPC_LoinGrade!lstBox_ColdStorage
For intListCount = 0 To ctlSource.ListCount
ctlSource.Selected(intListCount) = False
Next intListCount
Forms.frm_select_UPC_LoinGrade.Refresh
Forms.frm_select_UPC_LoinGrade.Repaint
View 3 Replies
View Related
Jun 15, 2014
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
View 5 Replies
View Related
Jul 1, 2013
I am currently trying to make a form which will generate a report for the users. Currently the form has 3 combo boxes where users select which fields they want displayed from the table in the report. They also have sorting options next to them (Asc or Desc). I am now trying to put in a multi select list box under each combo box to allow users to filter the report to their liking. Currently I am trying to pull distinct values from the specific field of the table and display them in the listbox for users to select.
I have tried using vba to set me.filter1.rowsource = SELECT DISTINCT me.combo1 FROM EVAP_Database, but this doesnt seem to work. I have also tried a few other codes and still no luck.
Table: EVAP Database
Combo: Combo1
Filter Listbox: Filter1
View 6 Replies
View Related
Aug 22, 2014
I am attempting to filter records using a multi-select listbox, but all records are being returned. Here is my code.
Private Sub btnKeyboxCount_Click()
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
[Code] ....
View 1 Replies
View Related
Oct 24, 2013
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');'
View 12 Replies
View Related
Jun 5, 2014
I have a update statement as follows
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE [PP TBL] SET [PP TBL].[GTIN] = '" & [UPC QRY]![PALLET GTIN] & "' " & _
"WHERE ((([PP TBL].[PP ID])='" & [Forms]![PP Edit FRM]![ID] & "'));"
DoCmd.SetWarnings True
Access is telling me it can't find the record and from what I have tested it seems to be the Update line, not the where line. I am basing the set portion as equals a query - could this be causing the problem? Or can code be based on a query?
View 4 Replies
View Related
Mar 21, 2005
I have multiple users entering data on bound forms. I have three required fields, all in the first 3 fields of the form. My question is, can I program something to where Access will update the record, after the 3rd required field, so that other users are not having to wait to enter there new record?
View 1 Replies
View Related
Mar 21, 2005
I have multiple users entering data on bound forms. I have three required fields, all in the first 3 fields of the form. My question is, can I program something to where Access will update the record, after the 3rd required field, so that other users are not having to wait to enter there new record?
View 14 Replies
View Related
Apr 3, 2013
How do I select the first column of a multi-column list box (called "List1") for a query.
A single column list box works fine.
Code:
SELECT Tble_Employee.Emp_No, [forms]![attendee_form]![list1] AS SelectedCourse
FROM Tble_Employee;
View 6 Replies
View Related
Apr 23, 2015
I have a multi slect list box (simple) and I need to find and select an item using vba - e.g., the bound column is the ID field and I need to select a specific ID (which will be different each time) as opposed to selecting the 100th record for example. How do I do this?
View 2 Replies
View Related
Aug 16, 2006
I have a form with several fields bound to a table, and also a listbox of records that are not bound to any table. What I want to do is to be able to double-click a record in my listbox and have the record show up on my form in a particular bound field, and also become a part of the underlying table's record.
Is it possible to do this? If it is, how would I do it?
Thanks in advance to all who are willing to help!
View 4 Replies
View Related
Aug 28, 2004
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 1 Replies
View Related