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.
I am creating a a text box where the user enters a text then clicks an option from the option that is used as the criteria for the search e.g. Last Name, Phone , address then a command button wil run a query.
I have successfully created a dynamic search form, which filters a listbox as the user is typing into a textbox.However, I am now trying to make the search more basic, which I cannot do. I want the listbox to be blank, and only display results where the number in the textbox matches one of the fields in the listbox.
Code:
Sub SearchFor() 'Create a string (text) variable Dim vSearchString As String 'Populate the string variable with the text entered in the Text Box SearchFor vSearchString = SearchForProp.Text
So I created a search form which filters my results. Apparently I don't have enough posts to link to the source of the code though. The code is from this website, /forums/showthread.php?t=188663
I then wanted to allow users to double click a result in the listbox to take them to the form which contains details about that record, however the listbox doesn't point to the correct record.
-When nothing is specified in the search field, all records are shown in the listbox, but double clicking on the first record takes me to a form that has no information. Clicking the second record takes me to the Detail Form of the first record. -When information is put into the search field, the results are filtered and only a few display, however clicking the first result again takes me to a blank form while the second result takes me to the first record of the unfiltered list. There is no way to reach the Detail Form for the last result of the listbox. -I tried right clicking the results in the listbox and copy pasting the information. The first result gave me a "0", the second a "1", and so on and so forth. This was the same whether or not the information was filtered or not. -I tried switching to a combo box and got the same results.
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
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?
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?
hi i just signed up to this site and need some assisstance from someone
i need to create a form in access 97 that enables me to search for a keyword on a given search area, for example the cd/dvd rom drive for files and folders
I'm currently trying to build in access a replica of an atrocious search function in excel.
I have a list of data quite simply in 5 columns and i want to filter through this data about (10000 rows).
My form has 5 data points.
The first is Product Name this is a string (i've looked up a lot of codes to search strings and even partial strings but no one seems to have done what i need).
- Basically i need it to search for any / multiple parts of the string entered. - for example if someone enters apple trees june i need it to look for cells containing those three words in any order, even conjoined for example "appletreejune" would still return or "apples on a tree in june". - This is attached to a single col called Product Name.
Based on this search i need it to look for data in a col called mark type (which is selected by a drop down)
Then by Market Context (also a drop down)
Then by a start and an end date, however, only one of the values (start or end) needs to be between the start and the end dates listed in the start and end date columns in the table.
On the access form I have designed , I need to give optional search option to the users. I have the following search options on the form (screenshot attached):
From Date To Date Port Vessel Voyage ---------------------
The query is as follows:
SELECT dbo_VESSEL.VESSEL_NAME, dbo_VESSEL.VESSEL_CD, dbo_VESSEL.VOYAGE_NUM, dbo_VESSEL.PORT_CD, dbo_VESSEL.DEPART_ACTUAL_DT, dbo_VESSEL.DIVISION_CD FROM dbo_VESSEL WHERE ( (dbo_VESSEL.VESSEL_CD Like [Forms]![VESSDAT].[Form]![txtvessel]) OR (dbo_VESSEL.VOYAGE_NUM Like [Forms]![VESSDAT].[Form]![txtvoyage]) OR (dbo_VESSEL.PORT_CD Like [Forms]![VESSDAT].[Form]![txtport]) OR (dbo_VESSEL.DEPART_ACTUAL_DT BETWEEN [Forms]![VESSDAT].[Form]![txtfromdept] And [Forms]![VESSDAT].[Form]![txttodept])); ----------------------
However, the form still prompts me to enter values for all the boxes and still doesnt show the correct data.
Cant seem to work this one out. I have quite a complex search form. The underlying query displays the results in a list box on the same form.
So far I have used the following expression for all the fields on my form (whether text or integer values): Like "*" & [Forms]![Frm_FrmSearch]![AssetName] & "*"
This appeared to work correctly. However, now my Asset Management System is storing a number of Equipment Type's. As one of the query criteria is Equipment Type ID it means that selecting PC (1) also displays the details for Printer (11), Scanner (12) etc......
I know why it does this (because these numbers start with a 1 and I am using a like expression). However I cannot seem to get it working.
The equipment type value is present in every record so I dont think I can use =FormValue or FormValue Is Null. I did try: =[Forms]![Frm_FrmSearch]![EquipmentType] Or Like "*" & [Forms]![Frm_FrmSearch]![EquipmentType] & "*"
but it seemed to skip the first parameter and still displayed printers etc. as before.
I have a feeling this may be easier than I expect however I am at a standstill.
I have a Query that is called from an unbound list box when data is typed into one or all three unbound txtBoxes "txtLastName" ,"txtFirstName", and "txtVIN" the query populates the listbox almost as it should..
The purpose is to identify duplicate entries based on three critera, last name, first name and VIN with the VIN bieng an execption meaning that if the VIN does not match I still want the matched first and last names to remain in view..
When I open the form where the list and text boxes are all records show in the listbox and as I begin to type the last name all records that do not match that critera are dropped, the same goes for the first name this works great. Once I get to the VIN however if there is no match I loose all three and the listbox is empty.
Is there a way to maintain matched names in the list view eventhough there is no match for the VIN?
Below is the code I am using in the Query Design, it is the same for all three fields Lastname, FirstName and VIN.
Like "*" & [Forms]![frm NewOrderVINVerify]![VinToFindFen] & "*"
So I'm working on something for housing. Each house has a "Property Reference". This property reference links to all other information on the property.
There's two tables, Referral (For a tenant, with the Property Reference as a foreign key) and PropertyInfo (Holding all the property information)
Basically I want to save users as much input time as possible, so I'd prefer if they could just enter the property reference for a person, and that populates the rest of the table.
I'm currently using DLookup on the main form where it displays the information related to the property reference, obviously it's not actually being saved into any fields.
Will this method be okay if I would want to search the records by the address on the main form?
I have a multivalue criteria inside a listbox that I need to use to filter several data out of a table by using a query. The multivalue textfield is the 3rd column of the listbox (eg. John Jonson,Tom Boost,Kim Moore). When I select a row inside the listbox, I want all the adressess of the people that are mentioned in the 3rd column when I click on a button. How do I make this criteria for a query to get the information I wanted?
Hi all I have a query linked to a report that prints a worksheet specific to a individual work item. This report/query picks up the Work_ID value on a form. I have 2 other forms displaying the same work with different amounts of detail. Rather than create a new report/query to run from each form, I am trying to use the one query/report from each form. The problem is that I cannot get Access to recognise the Work_ID value from the other forms. I have tried the following:
In the Work_ID criteria field building an SQL statement as below [Forms]![frmVCRUpdate]![Work_ID] Or [Forms]![frmVCRShort]![Work_ID] Or [Forms]![frmVCRLong]![Work_ID] - This does not work, it keeps asking for the frmVCRUpdate Work_ID value when I try to run the query from the other forms Adding 2 extra Work_ID Values to the query and on the 2nd and 3rd criteria lines specifying that it look for the Work_ID value from the other forms but I get the error above.
Any suggestions on how I can make this work would be appreciated, I'm not sure what else to do. Craig
having trouble using a text box on a form to set the search criteria for a particular field within a query.
Ideally i would like to enter the criteria in a textr box then click a button which sets the criteria and opens the query results in a report,
I have designed the query but cant which works if you go into the design and enter the criteria. the problem lies with getting the text box on teh form to set teh criteria.
If i use the expression builder to set the criteria to the same value of the "text" within the "text box" on said form the following happens,
If i open the query itself it opens a small window and displays the "expression" that i entered in the criteia box, above the data entry. the query does work this way but dont understand why the expression is displayed??? The text box on the form also becomes locked, not allowing data to be entered.
I have tried to link the query direct to the text box. also tried creating a table which has data entered via form then linking the query criteria to a field in the table.
Could some one point me in the right direction please
Hi there, I have a search form with a text box (unassigned at the moment). The search I want to conduct, filters the product number by the users search string with the use of * as a wildcard option.
I also have a Query with all the product numbers listed, no criteria set. How can I link the the search string in the search form to the query so when the search button is pressed, it will filter the results.
I have a problem printing a Subform that uses multiple criteria(in textboxes) as filters.
The search portion of the form works fine. The problem is I have created a report based on the subform and am using the following code to open/filter the report
Code: Private Sub PrintBtn_Click() Dim strCriterion As String Dim strMsg As String, strTitle As String
I currently have a database which contains various information for part returns. Among this information is contained a parts tracking number, VIN Number, and Date Code. I want to have a small dialogue box (Form) where a user can enter a tracking number, (or a date code or VIN if tracking number unavailable) to search for a record containing that tracking number. I have already created a replica of my new part entry form to view parts in read only mode only.how to create small form to search for record based off criteria then open the replica form i have made to that record?
I am attempting my first access database which tracks blood components in our medical facility. When a component is issued to a patient there is a button to push which links to a report that runs a query. The user must enter the unique key for the component at a promt, which ensures the report generated will be for that component only. My question is if there is a way to cpture and use that key automatically when pressing the button. If I need to clarify something, please let me know--I may not have the Access developer lingo down yet...
I have an access 2007 database connect to sql server 2008.I am running a pass though query to search between two dates (this query has been fine for years)
If I now run any search using parameters from 26th March 2014 to date - the query takes 10+ minutes to run.If I then change the date to 25th March 2014 to date - it runs in a nano second.I have not changed the back tables and I have not changed the format the data is saved in.
1. I created a form with some search-fields which are related to a query. Then I added a Subform in which I put some more Search criteria (So that I can easily hide and unhide those additional searchfields). It sounds strange but is necessary ;-). Now I related those searchfields in the subform to the same query. When I run that query a window pops up that I should put in a value in all those searchfields which are in the subform. But I told Access that it should display all rows, if there is no value in those searchfields. Just as I did it with the Searchcriteria in the Main form. Do I have to do something special, when I have a query which is related to two Forms?
2. I want a searchfield to search in three different columns. Usually the value will just be found in one of those columns. As the Table I search is very long and has many searchfields and multiple of those will relate to more than one column, is there an easy way to do it in VBA? As I did it by using the "or" field when designing a query, but this seems very slow and unstable.
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.
I have a list box called "product list box" based on a query called "searchqry", i also have another listbox called "type list box" , how do i get the type list box to only show "types" based on the section in products list box?
