I have a form which contains a listbox. It is a multi-select listbox. And I have a button on the form which runs a report. But I want the report to only show the selected records from the listbox. The report gets its records from an underlying query. But how do I make the selected records on the listbox become the query criteria?
View RepliesHello buddies :D, do you have any idea how to make this work?
To select data that falls within this criteria of date range between cboDate and cboDate2 (fields on my form). The date in [tblJobDetails]![timeIn] come in this format "08/17/06 10:24 AM", but the cboDate/cboDate2 (takes in date only e.g 08/17/06) what i am after is to evaluate specific hard coded time in addition to the date entered, i.e. even tho, i haven't entered time on the cboDate/cboDate2, I want specific time hard coded where e.g If i select a date range of 08/17/06 and 08/18/06 on my cboDate and cboDate2 it should really be evaluating: 08/17/06 8:00 AM to 08/18/06 8:00 AM.
This is the criteria i curentlly have on my query in design view tha works perfect in selecting date only.
([tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Or [tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Is Null) And ([tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Or [tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Is Null)
How can I incorporate 8:00am to 8:00am into my cboDate and cboDate2. What can i do to make this happen? Your kindness will be greatly appreciated http://www.naijaryders.com/forums/images/smilies/thankyou.gif
I have button on my form that uses a selected record in a listbox (non multi-select) to run code that updates a table. The code itself works great so long as there is a record selected in the listbox. If no record is selected an error is thrown. I'd prefer the end user not to ever see that error. If the end user clicks the button and no records are selected, I'd like for nothing to happen at all. I've tried several variations of checking the listbox with an if / then statement to see if records are selected and if so to exit sub, but they all throw errors when no record is selected in the listbox.
Code:
If IsNull(Me!lstHeatTreatments) Then
Exit Sub
End If
[Code].....
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?
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 want to delete certain records based on the selected date. However, I come across with this is error - Run time error '3464' (Data type mismatch in criteria expression).This part is highlighted in yellow. I even used the debug.print to test out if the sql statement is executed properly.
Code:
DoCmd.RunSQL DelSummarySQL
Here is my full code
Code:
Private Sub cmd_Delete_Click()
Dim DelSummarySQL As String
Dim StartRange As Date
[code]...
Access 2007-10
Listbox created: List62 (I know I need to rename it, but for now)
Multi-select: Extended
Row Source Type: Field List
Row Source: qryFieldList
Open Query: qrySelectedFields (I added primary key to first column, just to have at least one destination field).
Goal: to select multiple fields within listbox, click on command button to open query "qrySelectedFields" with selected fields from list box.This is the code I have on the command_click:
Dim varItem As Variant
Dim strSQL As String
If Me.List62.ItemsSelected.Count = 0 Then
MsgBox "Please select one or more fields.", vbExclamation, "Healthcare REIT"
Me.List62.SetFocus
End If
[code]....
It does absolutely nothing - doesn't add the fields to "qrySelectedFields", doesn't open the query, notta.
Okay then, after much trouble and confusion, I finally realized I need to use an Extended listbox in order to allow for multiple items to be selected from a list on my form (rather than the evil multiple selection combobox!).
However, now I am trying to figure out how to make one listbox (IndustryClassification) only be visible if the item "Industry" is selected in another listbox (TypeOfBusiness). Coding I can use for this in the AfterUpdate event of the listbox?
I need to create a query that has multiple criteria selected from a form For example. I will always have the customer name but then the user might also choose any one or more of the following, start date, end date, number etc
I have tried to create a select query and added the customer form element to the criteria but I am not sure how to do the rest because it will always have at least one more criteria but I am not sure which one and it may have up to 5 more criteria.
Hi there. I have a problem with queries that i am creating on the fly with access. My setup is this:
A table called Order which has various fields including a field called Date Taken. The format of this is Date/Time with the format d/m/yyyy.
A form called frmMultiSelect which has a listbox called lstdates and a command button called cmdOpenQuery.
in lstdates the row source is: SELECT DISTINCT Order.[Date Taken] FROM [Order] UNION select "......ALL......" from [Order];
And in the button onClick event i have the following code (adapted from a web source):
Private Sub cmdOpenQuery_Click()
On Error GoTo Err_cmdOpenQuery_Click
Dim MyDB As DAO.Database
Dim qdef As DAO.QueryDef
Dim i As Integer
Dim strSQL As String
Dim strWhere As String
Dim strIN As String
Dim flgSelectAll As Boolean
Dim varItem As Variant
Set MyDB = CurrentDb()
strSQL = "SELECT * FROM [Order]"
'Build the IN string by looping through the listbox
For i = 0 To lstDates.ListCount - 1
If lstDates.Selected(i) Then
If lstDates.Column(0, i) = "......ALL......" Then
flgSelectAll = True
End If
strIN = strIN & "#" & lstDates.Column(0, i) & "#,"
End If
Next i
'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE [Date Taken]=" & Left(strIN, Len(strIN) - 1)
'If "All" was selected in the listbox, don't add the WHERE condition
If Not flgSelectAll Then
strSQL = strSQL & strWhere
End If
MyDB.QueryDefs.Delete "qryCompanyCounties"
Set qdef = MyDB.CreateQueryDef("qryCompanyCounties", strSQL)
'Open the query, built using the IN clause to set the criteria
DoCmd.OpenQuery "qryCompanyCounties", acViewNormal
'Clear listbox selection after running query
For Each varItem In Me.lstDates.ItemsSelected
Me.lstDates.Selected(varItem) = False
Next varItem
Exit_cmdOpenQuery_Click:
Exit Sub
Err_cmdOpenQuery_Click:
If Err.Number = 5 Then
MsgBox "You must make a selection(s) from the list", , "Selection Required !"
Resume Exit_cmdOpenQuery_Click
Else
'Write out the error and exit the sub
MsgBox Err.Description
Resume Exit_cmdOpenQuery_Click
End If
End Sub
When i open the form and look at the list box the dates are there along with the ......ALL...... however the dates whose day is less than 10 are displayed with the leading 0 e.g 04/12/2005.
When i click the run query command button on one of these dates the query is opened blank. the sql code output is:
SELECT *
FROM [Order]
WHERE [Date Taken]=#04/12/2005#;
If it was changed to 4/12/2005 it works but i dont know why access is putting in this leading 0.
Please help me!! I can send the database if needed
Thanks very much
I have a search form that uses several comboboxes, textboxes and checkboxes that are used as criteria in a query. You enter in the relevant information, hit search and a report opens based on the filtered query.
What I would like to do is change one of these combo boxes to a listbox and use the multiple selection as the query criteria. I know it's not as simple as just putting the listbox as a criteria in the query, and I've also tried many different variations on the varItem and strWhere code.
having used a query to generate data from a set of records where i can select the criteria (they are two letter abrv and are in a single field in the table but there may be multiple criteria). As I would like others to do it without using the query my first thought was a form with a series of check boxes tick for inclusion in the query or not. As i have built the query am I looking at changing that or taking it all into VB and using some code to pull the relavant 'ticks' across............ and how the hell would i do that......
many thanks in anticipation......
having used a query to generate data from a set of records where i can select the criteria (they are two letter abrv and are in a single field in the table but there may be multiple criteria). As I would like others to do it without using the query my first thought was a form with a series of check boxes tick for inclusion in the query or not. As i have built the query am I looking at changing that or taking it all into VB and using some code to pull the relevant 'ticks' across............ and how the hell would i do that......
many thanks in anticipation......
I have a listbox that uses a query to reference a value in the current form. The problem is for some reason Access is trying to fill this listbox before even showing the form, so I get prompted to manually enter the query criteria when it should be picking it up from the form.
This is bizarre because it's only just started doing it. I've removed the listbox and created it again, but no help. I have listboxes such as this all over the place, but this particular one has just started playing up! Why?
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 a query that selects records based on certain flags in each record. I can view the record in datasheet view, but I need to be able to edit the records selected by the query using another form. Is there any way to automate this process?
View 14 Replies View RelatedHow can I get a Query Criteria To Select All Records or specific records in query design section.
I have a table that shows many departments with credit card transactions. I like to run a query to see specific department, or have an option to see all the departments when the query is run.
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
any help would be very much appreciated
Hey all,
I have a problem with my update query (SQL),
The situation where it is used:
The SQL code is as a command on a button on a form. It is used to take the value in the text box(Name: Restock) and update it to the Quantity field in the table. (tblStock)
The problem is that the Update, updates all the records changing all the values in the Column (Quantity) to the value that is in the text box.
I need it to update the record which is displayed in the form not all the records.
Here is the SQL at the minute.
DoCmd.RunSQL ("UPDATE tblStock SET Quantity = (Restock) WHERE ItemID = [ItemID]")
Any help would be great. Thanks in advance
Hello.
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 -_-
Thanks for any and all aide!
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:
ProjectNo - InvolvementType
123 - Testing
123 - Build
123 - Documentation
456 - Build
789 - Testing
789 - Documentation
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]));"
DoCmd.SetWarnings False
DoCmd.RunSQL strDelete
DoCmd.SetWarnings True
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
Regards
Robert
Hi all,
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
Thanks alot
Yet another question.
I have a listbox on a form that show all Files for a particular volunteer. I have an "Edit File" button below the listbox to be able to edit that particular file. Is there a way about opening that form to that specific record without using a query?
Thanks
ScrmingWhisprs
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
[Code] ....
I have an unbound listbox with 3 columns. Call it PeopleBx. How can I address the item in the first row and first column. How can I address the first column in the selected row and how can I address the item in row 3 and column 2. I tried with
Code:
Me.PeopleBx.Selected
and other possible variations but failed.
From a search form example I got from this forum I made the following:
A listBox which shows results as you type, using a query.
This listBox has 1 column, I need it that way.
What I need is to show the information from the selected item of the listBox on a textBox, getting this info from the results of the query.
I found 2 answers which I don't like:
1- =DLookUp("[Column]","[Query]") as source of textBox
This shows only information from the first row. So if you select the second result from the listBox, it still shows the first row.
2- =SearchResults.column(int) as source of textbox
It does not work, because listBox has only 1 column.