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 RepliesI 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 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
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
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
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] .....
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.
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.
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] .....
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] .....
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!
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
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 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
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] ....
I have created a form that contains data from two tables. Each drop down on the form, for each table, contains multipl values. I want to be able to make multiple selections from each down (usually this is holding down the ctrl key and making multiple selections). However; this method does not work in Access. Is there a way to select multiple values at one time in Access similar to holding down the ctrl key?
View 2 Replies View RelatedI'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 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 RelatedHi,
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!
Hello,
I've got this multiple select listbox which writes data into a textbox:
Private Sub List2_AfterUpdate()
Dim Cursisten As String
Dim ctl As Control
Dim Itm As Variant
Set ctl = Me.List2
For Each Itm In ctl.ItemsSelected
If Len(Cursisten) = 0 Then
Cursisten = ctl.ItemData(Itm)
Else
Cursisten = Cursisten & "," & ctl.ItemData(Itm)
End If
Next Itm
Me.txtCursisten = Cursisten
End Sub
And I've got a SELECT ALL button to select all records in the listbox:
Private Sub cmdSelectAll_Click()
On Error GoTo Err_cmdSelectAll_Click
Dim i As Integer
If cmdSelectAll.Caption = "Alles Selecteren" Then
For i = 0 To Me.List2.ListCount
Me.List2.Selected(i) = True
Next i
cmdSelectAll.Caption = "Alles De-Selecteren"
Else
For i = 0 To Me.List2.ListCount
Me.List2.Selected(i) = False
Next i
cmdSelectAll.Caption = "Alles Selecteren"
End If
Exit_cmdSelectAll_Click:
Exit Sub
Err_cmdSelectAll_Click:
MsgBox Err.Description
Resume Exit_cmdSelectAll_Click
End Sub
The only thing is that when I use the SELECT ALL button, the function List2_Afterupdate doesn't work anymore. There must be a simple solution but I just can't figure it out. Can anyone please help me?
Tnx a lot!
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 RelatedI am looking to see if anyone can help i have been able to get the multi select function part of my form working but i need another filter in the query for a date but it keeps deleting this part of the query everytime i run the report what i need to be able to do is get all jobs against a specific leadcraft that are in the past and also 31 days into the future.
Paul
here is the sql so far, thanks to those who helped already.
Private Sub preview_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("FAelecplan")
If Me!Leadcraftlist.ItemsSelected.Count > 0 Then
For Each varItem In Me!Leadcraftlist.ItemsSelected
strCriteria = strCriteria & "MAXIMO_V_WORKORDERS_FA.LEADCRAFT = " & Chr(34) _
& Me!Leadcraftlist.ItemData(varItem) & Chr(34) & "OR "
Next varItem
strCriteria = Left(strCriteria, Len(strCriteria) - 3)
Else
strCriteria = "MAXIMO_V_WORKORDERS_FA.LEADCRAFT Like '*'"
End If
strSQL = "SELECT * FROM MAXIMO_V_WORKORDERS_FA " & _
"WHERE " & strCriteria & ";"
qdf.SQL = strSQL
Dim stDocName As String
stDocName = "elecplan"
DoCmd.OpenReport stDocName, acPreview
On Error GoTo Err_preview_Click
Exit_preview_Click:
Exit Sub
Err_preview_Click:
MsgBox Err.Description
Resume Exit_preview_Click
End Sub
I've been trying to determine what recordsource to apply to a listbox to get the following results for its three columns:
Column1 Column2 Column3
AddressID Address Owner
The data is being pulled from two tables:
tblAddress
AddressID (pk)
HouseNum
Street
Apt
tblOwner
OwnerID (pk)
AddressID (fk)
LastName
FirstName
Current
Problem: I need each record from the Address Table to be shown with the most current Owner information from the related table. The most current Owner Information record is identified by a checkmark in the 'Current' field.
I've tried the following queries:
This query gives me duplicate addresses for each instance of owners:
SELECT DISTINCTROW [AddressID], [HouseNum] & ' ' & [Street] & ' ' & [Apt] AS Address, tblOwner.LastName AS Owner FROM tblAddress LEFT JOIN tblOwner ON tblOwner.AddressID=tblAddress.AddressID;
This query gives me only addresses that have a current owner, the problem is, sometimes a house won't have any related owner record in the owner table:
SELECT DISTINCTROW [AddressID], [HouseNum] & ' ' & [Street] & ' ' & [Apt] AS Address, tblOwner.LastName AS Owner FROM tblAddress LEFT JOIN tblOwner ON tblOwner.AddressID=tblAddress.AddressID WHERE tblOwner.Current = True;
Can this even be done with a query? This is driving me nuts. Help!
Scott
I have a form where a user can select from a listbox (pulled from a database) and "Add product" - it then goes into a Textbox, each taking a new line when "Add product" button pressed. Then when they press "Add Customer" those details along with other entered details gets stored in my Customer Database..
The textbox store multi-line entries in the "ProductName" field heading in database.
Is there anyway i can use a List instead of the textbox? Adding individual entries to one certain customer? I have an EDIT facility aswell so would be easier to have a "Remove Product" button instead of manually backspacing a whole product entry from another textbox (containing all products, on new lines).
I have multiple codes assigned to records in a table. I want to be able to say IIf [code] = "FMIX",[qty]*2.2046/7.1, IIf [code] = "Liqd", [qty]*2.2046 and everything else can just equal [qty]. I have tried this statment Expr1: IIf([family-code]="FMIX",[SumOfqty-on-hand]*2.2046 IIf([family-code]="LIQD",<[SumOfqty-on-hand]*2.2046>,[SumOfqty-on-hand]),[SumOfqty-on-hand]) and it doesn't work. Can anyone tell me what is wrong?
View 2 Replies View RelatedHi,
This is my first posting on this forum, and I would greatly appreciate any help with this issue...
I have a form that is used for entering information into tables. I would like to be able to select multiple options from a list on a form and have it saved into a table. Any ideas? Is this even possible :confused:
Here are some paths I followed:
The table has a List Box field, type Text, that gets values for the list items from another table. On the form, I have changed the Multi-Select property of the List Box to "Extended" so that the user can select multiple items from the list. But when I select items from the List Box, the table is not getting updated with the selected items.
Another way I tried doing this: The table has a List Box field that has no values (I'm not looking up values from another table). On the form, I set the Row Source of the List Box equal to the query that gets the list values from a table. I then set the Multi-Select property to "Extended". When I select multiple items from the List Box, the table doesn't get updated.
FYI: The control source for the list box on the form is pointing to the field in the table.
Thanks,
dbnewbie