Forms :: Combo Box Query - Form That Searches A Table To Show Certain Criteria
Jul 21, 2014
I have created a form that searches a table to show certain criteria. I am having trouble with a part where the table uses a checkbox and I am trying to use a combo box on the search form to return the results based on is it checked? Yes/No or both. When I test it I keep getting Runtime error 3075 missing operator.
The sql I am using is below and when I try and see where it is going wrong it highlights the line.
Code : Me.Filter = strWhere
Code:
Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "#dd/mm/yyyy#"
Is it possible in access 2010 to create a a query that only shows a text box if a combo box criteria is met?
Example: On a form there is a combobox (Result) that can either be negative or positive. If the value is Negative then a query is already setup that populates a mailmerge with some text. If Results=Positive can a query be created that will show the textbox (Data). It only needs to show if the positive criteria is met.
I have a search form with blank fields tied to a table, four criteria search boxes, and a button to take the input from the search boxes, search the table, and populate the results on the form's blank fields. As of now, it works as long as all four criteria boxes aren't null.I used filters to achieve this, and here's the code that works as long as all four boxes are not empty. (My criteria boxes are as follows: a textbox called "Keyword" and three combo boxes called HRCombo, BuildingCombo, and RoomCombo, and the fields they're tied to are as follows: "Item Description" "HR Holder" "Building" "Room") My first line "Me.Filter = ..." was broken up to make it easier to view.
I need it to be able to do the search no matter which combination of criteria boxes have input. Someone recommended using if statements to do the following: Create four strings, one for each criteria box. Use 4 if statements to check if the box is null - if it is null, assign an asterisk to its string, and if its not null, assign the value I used for the above Me.Filter statement to each box's string. Then, use Me.Filter and concatenate the four strings at the end. Here's the code I used for this, and, with my limited knowledge, I can't get it to work.
Code:
Dim StrA as String, StrB as String, StrC as String, StrD as String If Me.Keyword is null then StrA = "*" else StrA = [Item Description] Like " & Chr(34) & Me.Keyword & "*" & Chr(34) End If
I created a navigation form on which I put a form call [frmAnimal Setup].
I then placed one combo box on the Navigation frm Header. I have bound it to its source and it actually queries the tbl and show the right info. However when I select one, it will not let me.
In addition, [frmanimal setup] will not allow me to select a breed although it does query the tbl and shows the right information.
I have created a combo box which lists companys, when i click on a certain company it brings all records for that company but underneath the last record it shows all the other records on the form.
How do i get it to only show the records for that company only.
I'm looking to select name of a hospital from "HospName" from a list box. If no, then at least to write the name and the rest I'll get it associated with the name.
Then I need to know if I can put this in a form so the form asks same criteria?
I've been looking around and have found some posts that pointed me in the right direction, I just can't get it to work. What I have is 37 excel files of competitor cross references. There are 2 columns in each excel file, our number and their number. I have inserted them as a linked table in the db. What I want to do is create a form that has a field for every part number and make all of those fields a search field. That way they can type in any number and get all numbers back. I have created a query but once I get past 4 linked excel sheets then I get errors about a type mismatch in expression. Also I can't get the form fields referencing the query to show up when I open it. If I leave the query at 2 or 3 fields and use [Forms]![CrossRef]![txtItemNumber] in the criteria of our number in the query, it works. I get a window that pops up when I just open the query asking for a number, I type it in and the query returns the number and competitor numbers. Am I doing this the hard way?? Thanks for any ideas...
I am having an issue trying to show all records when I load my form. It loads correctly when I don't have a record source for the main form. However, the combobox filters will not work. My goal is to have users be able to use the datasheet, subdatasheet and combobox filters. I can get the form to work just fine when I link the Main form and subform, but when the form opens it is filtered on the first record. I have been successful with this approach when using other forms, but they didn't require a subdatasheet.
Is there anyway I can have the comboboxes to filter yet be able to show all records until the user selects filters? Is this possible?I finally got my Manager to agree to use a database instead of Excel files based on this form setup and I need to make as "user friendly" as possible (look and function like a spreadsheet) I attached some pics of the form along with the code for the filter.
I can't figure out what I am doing wrong... I want the query criteria to be defined by a combo box in the form. The user will select the criteria on the combo box, then click a button to run the query, however the query keeps coming up blank.
[Forms]![UserReview]![Staff] is what I have entered in the criteria for the field in the query. If I simply type in "casbds1" as the criteria, which is one of the choices in the combo box, it returns with the data I want, but I just can't get it to work with the combo box. Any ideas what I am doing wrong?
I am building a tracking database where we would be able to track information which field are null data and report them to our Administration to fill the null data. For this I have created a form name "Search" and I have a combo box control on that form which is bound to "Table = employee" and its ROW SOURCE TYPE="Field List", I would like to able to query records where the selected value in this combo box is null through out the table.
For example if I select "Telephone" from this combo box dropdown, I would like the query to show all the records where the "Telephone" is null, how to set the criteria in query to take the combo box value as "Field Name" and then compare it with the Field/Column in the table and show the null values.
I have a form with a combo box to be used for navigating to records (created using the wizard), based on a query with these fields: 1 - Last Name (Sorted Ascending) 2 - First name (Sorted Ascending) 3 - Company 4 - CustomerID (PK)
The combo box only partially works, it pulls up a list, but here is the problem:
If the name comes up 'Hartman, BillyBob', and there is only one Hartman, then all is well.
But if you have several Hartmans, say:
Hartman, Ace Hartman, Bob Hartman, Stan
and say you try to select Bob, it always goes to the first Hartman in the list (in this case Ace).
It is a pain in the ace, what am I doing wrong? Thanks for any help...
I am trying to make a form where the user can check/uncheck query criteria via several check boxes. The idea is that the user can start with many criteria and then deselect criteria if the search does not return enough results.
I have been setting up several queries and thought I would combine them in a "Master Query", since I thought it may be easier to deal with each criterion and the respective switch this way.
Lets say we run a hairdresser.
I have a field in the form that allows me to select clients. This is also used in the query. Works fine. Now to the hard part.
Example 1:
Each customer has a budget to spend on haircuts. Each hairdresser offers haircuts from $x to $y. The query should return all hairdressers that are appropriate for the budget of the selected customer. There should be a yes/no button on the form to ignore or use this criterion.
Example 2 (this completely threw me off):
Each customer has a set of preferred services from a table (e.g. cutting,washing, coloring). Each hairdresser offers certain services also based on this table (e.g. cutting,washing, coloring, drying). The customer and hairdresser table use the services table and a multi combo box to select the services. The query should return only hairdressers that offer some or none of the services wanted by the client.
Again, there should be a services yes/no button on the form to either ignore or include this criterion.
To clarify, the hard part for me is the query. I am fine with setting up the tables and the form. Just not sure how to implement something like "IF (ServicesCheckBox = -1, 'then use service criterion', 'ignore service criterion')".
I have a combo box on my form that passes criteria to a simple select query. There are four possible selections to make from the combo box. For some reason, when I select the first option on the list the query runs perfectly. However, if I select the second, third or fourth option from the combo box, the query returns no records, even though I know there are records in my table which should be returned.
I'm building a simple access database to be used in a factory. It's pretty much there now, in the sense that it does all it needs to do, but some things feel a little "clunky". One of these things is the page where new products are created.
When a product is created the user has to specify what raw materials it's made up of and that works great, there is a continuous sub form that lets the user just add a row for every raw material. Having it as a continuous form makes sense as there can be lots of rows or just a few, so it's flexible.
Currently though the labour/time on that page works in the same way. But it feels a bit silly as there are only 3 sorts of labour (at least currently, they might add a couple more later). The labour types are..
- Build - Finishing - Handling
My question is, how would I go about turning the existing continuous row-by-row subform for labour into a single form that just listed those three types of labour (read from my labour type table)? So that it just shows a row that asks for quantity of each available labour type.
Not all products have all types of labour but it would be fine to write a 0 in the labour table for (for example) Finishing against a product that for example didn't need Finishing Labour. Obviously with the current continuous form approach no row would be written for Finishing if the user didn't select that.
I guess I could hardcode a single form to show the three current types, but ideally it would be flexible, coping with new labour types when the users find they need to add them.
I have set up a database that stores actions (i.e jobs). In the table; two of the fields are...'required completion date' and 'actual completion date'. I wish to lookup, by using a query, all of the open actions (those which havent yet been complete (i.e the 'actual completion date' is null)) and then later on all those which are overdue (i.e the 'actual completion date' is null And the 'required completion date' <today....this being the criteria for an overdue action).
However, I have used a form which has a combo box which contains the values open and overdue. When a selection has been made I want a form to display with the results depending on the selection that has been made. I am capable of creating a form based on a query, but am unsure of how to construct the query with the correct criteria based on the option that is selected from the form.
I have an append query that appends records to a table, and I have a form based on that table.
Users will click a button that will run the append query and then open a form for users to fill in remaining empty cells. How can I filter the form to show only the newly appended records?
When I run a query, whenever the criteria is not met, I want the column header for the row to not show up in the query.
I am running a select query and I have 10 items on the query, I select what I want via check box and then hit OK to run the query. I want the fields that are not checked to not show up in the query at all. Right now I still get the column header, any way to get rid of it. Thanks a lot for any help.
I've two tables, QA (Quality Assurance) and Instruments. I'm using form to add data to QA.
There's a field in QA, named InstrumentUsed. The criteria is that InstrmentUsed should only accept value when 'status' field in Instruments table shows 'working'. If status is 'faulty' or 'need repair', it should pop up the related error message and cursor stays on the same field.
I've not starting learning VBA yet, so I'm trying to make use of Macro's.
How to use data from another table as a validation criteria for a field in form?
Pops up a MessageBox and stays on the same field, on the form, unless error is resolved?
I want to show or hide a box on a continuous form. If there is a date in the ShopOut field i want the label to show up , if not then it will not appear.
I am trying
If IsNull(Me!ShopOut) Then Me.StillinShop.Visible = True Else Me.StillinShop.Visible = False End If
however, it shows the box whether there is a date in ShopOut on not. Is it possible to do this in a continous form or do i need somekind of me.record#.ShopOut or something.
I created a search form. It has 6 unbound text boxes and 2 combo boxes in the header. Users can select values from the combo boxes and/or enter names in the other text boxes. These values all go into a filter on my Main table and the filtered results show up in the detail section. That used to work fine.
Now, I've been trying to convert the filter into a query and show the query results in the detail section instead. (Why? Because of the ever-changing business requirements, of course!)
For some reason, the detail section went blank. All white. When I change the Data Entry property to No, it fixes that.
However, the text boxes for entering the search criteria will not accept any values anymore. It's like they are disabled.
When I change Data Entry property to Yes, I can enter text into the text boxes again. But the detail section blanks out again.
I am attempting to create a toggle on a form that would instruct a query which criteria to use.
Specifically they are date criteria. I want to be able to toggle between evaluating on a start basis verse a ship basis.
If the toggle is set to 'Start Date', the query would use the start date as its criteria. If the toggle is set to 'Ship Date" it would use the ship date.
The other complication is that within the query, "Start Date" and "Ship Date" are two separate fields always contained in the query, so how do I write the query so that it only applies criteria to the appropriate field based on the toggle setting?
I am working on a warehouse database and i have run into a small problem. I am using access 2010 and 2007 depending on location.
I have attached a picture of my database relation ship so you can see where i am coming from. What I am trying to do is make a main form called products and use that as the sours of the information that my queries look for and then have linked pages to my main form showing the results of that.
Example of the above would be as follows
Product on main form lets say tennis ball red then on my link forms show stock plus location and quantity
On another linked form product on main form and orders for that product and qty
I have several (about 10-15) queries that I have designed to run monthly reports. Various pieces of the criteria for each query need to change regularly. For example, in design view, I currently have a 'Where' field for each of our eight products. Where 1= include the product in the query analysis. Where 0= do not include the product in the query analysis. We would like to have a form that has entry boxes for each product where the person doing the analysis could type '1' or '0' and then the query could update the criteria entered before running.
We also need dates to update via the form. I currently have [Current Period End Date] and [Prior Period End Date] pop up boxes on each query that allow the person running them to enter their desired dates. But again, my boss wants to limit their involvement with the actual queries. Plus, you end up entering the dates for all 10-15 queries, which is a hassle. We would like to have entry boxes for the desired dates in the form and have the queries pull date criteria from the form.
If we can pull query criteria directly from a form, is it possible to create a table from a form and then pull the query criteria from the table?
I've created a farm database, with a form (frmSearch) that will allow user to filter data. The form comprises of combo box and list box etc... for the user to input their own criteria. The subform below has a datasource. The data source is based on a query (qContractionSearch) which is basically a parameter query with 3 tables. The problem is, however, that it won't work with 3 tables... but will work if data source comprises just one table.
See the farm database attachment...and go to frmSearch...then go to Contraction tab. (The Cattle tab filter works fine-it only has a single-table datasource).i have a requery macro which runs whenever the user clicks 'search'.
