I'm wondering is it possible to create a multiple parameter query which will return results even if you leave some of the parameters blank ?. I'm trying to set up a Form which will allow users to select parameters using combo boxes but at the moment you need to fill them all in or you'll get no results...
View RepliesI am totally new to Visual Basic.I have dependent cascading combo boxes setup. The hierarchy goes:
Segment
Family
Class
Brick
Now, I need the value in the Brick field to create a varying number of other cascading combo boxes. They are the Brick Attributes. The brick attributes are the labels for the combo boxes and the brick attribute values would be the values you can chose from within the combo boxes. Depending on which Brick you chose, there are a varying number of Brick Attributes, and thus a varying number of needed combo boxes to pop up.
I'm trying to build an database for aircraft operators. I've got the basic tables structure and relationships but I'm stuck on building an search form to filter records by user input.I've got following controls on my form (unbound):
1. AircraftType (combo box) from tblAircrafts
2. CompanyName (combo box) from tblListOfAircraftsOperators
3. TeailNumber (text box) from tblAircraftOperators
4. AirportNameSearch (combo box) from tblAirports
5. PassengersNumber (text box) from tblAircraftOperators
6. ManufactureYear (text box) from tblAircraftOperators
7. SourceSearch (combo box) from tblInfoSource
8. CountrySearch (combo box) from tblCountry
9. CategorySearch (combo box) from tblAircraftCategory
10. EamilToOperator (text box) from tblAircraftOperators
11. InteriorPhoto (Bound object frame) from tblAircraftOperators
12. ExteriorPhot (bound object frame) from AircraftOperators
I need to enable users to search for aircrafts based on those criteria. As I mentioned I'm new to Access and I don't have any advanced coding skills. I have a query build to perform the search and this is the code I've managed to write so far:
SELECT AircraftOperators.RegistrationNumber, AircraftOperators.PassengersNumber, AircraftOperators.ManufactureYear, AircraftOperators.EmailToOperator, AircraftOperators.ExteriorPhoto, AircraftOperators.InteriorPhoto, tblListOfAircraftOperators.OpratorName, tblAircrafts.AircraftType
FROM tblAircrafts INNER JOIN (tblAirports INNER JOIN (AircraftOperators INNER JOIN tblListOfAircraftOperators ON AircraftOperators.CompanyName =
[code]....
Attached I have a database that I've been working on which has a form called "frmCriteriaSearch". It is based off of the qryCriteriaListBoxUpdate query. I am trying to get the listbox in the second tab of the results section to work. It queries fine for the checkboxes, but I cannot get the comboboxes to affect the query (unless a checkbox has already been selected)
View 6 Replies View Relatedhave a query that works fine when I have to select one parameter, however I don't know how to select multi parameters...In this query I would like to able to select as well as the specific 'POSTITION' value Also 'BASE' and all those parameters that have a check box empty or full.
Code:
SELECT tblCrewMember.StaffNumber, tblCrewMember.Surname, tblCrewMember.Name, tblCrewMember.Position, tblCrewMember.Base, tblCrewMember.Nationality, tblCrewMember.StartingDate, tblCrewMember.Resined, tblCrewMember.ResinedDate, tblCrewMember.Birthday, tblCrewMember.IDCrewMember, [GroupBy] AS Expr1
FROM tblCrewMember
WHERE ((([GroupBy])=[Position]))
ORDER BY tblCrewMember.StaffNumber;
I have re-written this question as I think I managed to cause confusion!
(I really do need help!) My problem involves three tables. The first table is NAMES with an auto-number key field called NamesID.
The second table is a list of Dance Classes, called CLASSES again with an auto-number key field called ClassesID.
The third table is a list of MEMBERS in each dance class.
Obviously, the MEMBERS can belong to many CLASSES, and each of the CLASSES can consist of many MEMBERS.
My aim is to add a new record in MEMBERS by using a ComboBox containing records from the NAMES table.
On the form, I have a combo-box which shows me the list of CLASSES
(ComboClasses). I click on the class.
I then click on the NAMES combo-box, click on a name and want it to appear in the table of MEMBERS for the class shown in the CLASSES combo-box.
The problem
I am selecting the name using “ Select * From NAMES where Names.NamesID=[Forms]![FormName]![ComboNames] (supplied by supersubra) but how do I get the ClassesID into MEMBERS record.
At the moment, if I add a name that member appears in every class, or I have to manually insert the ClassID.
Philip
Hi,
I currently have a select query that has a where statement to select a value depending on the value of a combo box in a form.
WHERE (((tblSampleSubmission.SubmissionNumber)=[Forms]![frmReportPreview]![SubNumber]))
What I am wondering is there any way that the where statement can be for another form depending on what form is currently active by using an "or" statement?
Basically what I am trying to do is make the db as easy to use as possible and not have to type in the value again.
I have a user form with six different dropdown boxes. I would like to create a query that gets its criteria from users selecting values from one or more of these boxes.
I have tried:
[forms]![frmName]![comboboxname] or [forms]![formName]![comboboxname] is null
in the corresponding query fields and get a "query is too complex" error message, even when only selecting one criteria.
What am I doing wrong?
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.
View 5 Replies View RelatedI am fairly new to Access and I would like to create a form to allow users to create their own query. I would like to allow users to select multiple fields (perhaps with checkboxes?) from all possible fields in a table to return either all data from that field or narrow their search by inputting certain criteria or choosing from a drop down into a text box. Is this possible in Access and any detailed specifics on how to achieve this?
View 3 Replies View RelatedI have a simple question that I know will have a very complex answer
You have two sets of tables with identical structures. The first set contains data that is a model for the second set. The users run queries that append data to the second set of tables
Here is an example of the structure
TblSourceOne
SourceOneID
SourceOneData
TblSouceTwo
SourceTwoID
SourceOneID
SourceTwoData
TblSouceDetails
SourceDetailsID
SourceTwoID
SourceDetailsData
As you can see TblSourceTwo contains a foreign key from TblSorceOne and TblSorceDetails contains one from TblSourceTwo
The main table for the second set
TblTargetOne
TargetOneID
SourceOneID
TargetOneData
The user adds a record to the main table (TblTargetOne) then uses an append query to add all of the applicable records from TblSourceTwo to TbleTargetTwo
TblTargetTwo
TargetTwoID
TargetOneID
TargetTwoData
So now the user has a data model in TblSourceTwo which has been “copied” to TblTargetTwo
Next for each record in TblSourceTwo that has been copied to TblTargetTwo that has daughter records in TblSourceDetails another query is executed to copy all of the pertinate records to the TblTargetDetails
TblTargetDetails
TargetDetailsID
TargetTwoID
TargetDetailsData
Now the user has completely copied all of the necessary record for both table.
Finely the question
How can I use a multiselect list box to achieve this in one operation? I don’t care if it includes running multiple queries but it needs to appear as one single operation to the user.
I have a table and in that table I have multiple columns one being check boxes and another is the description. There are others but these are the relevant ones. I filter under description for HR Payroll but this description is not uniform. After HR Payroll it could vary so the description is not always the same. I want to select all check boxes under this description and was told use a query no clue how to do that though.
View 3 Replies View RelatedI am trying to make a search option in my form header. Right now I have two unbound combo boxes (CboAccountsfilter and cboCourseName) that I can use to filter my records. Currently, I can use the drop down for CboAccountsfilter and a list of accounts will appear. When I select one, the corresponding Course Names will appear in cboCourseName. This works fine...Code below. I would like to take the filtering a step farther and add checkboxes to filter the data. I my form, there currently exist several check boxes (yes/no)...(Priority, Rep Top Target, Manager Top Target, ect). I would like to have the option to use a check box to filter. I.E if I had a checkbox in my header called PriorityFilter, if checked it would only bring up those records that met the two combo boxes criteria and was a priority.
Below is the code I have so far...it doesnt have anything for the checkbox because I am at a lost of how to get started.
Private Sub CboAccountsfilter_Change()
Me.Requery
Me.cboCourseName.Requery
Me.Check178.Requery
End Sub
[code]...
I have done this before and can't remember how I did it and I can't fogure out how to do it. I have a form with textboxes that are for displaying info to the users. I want to lock them so that users can not click on them or high light the fields. So basically the user can only click on fields I want them to.
View 4 Replies View RelatedI am creating an absenteeism database which has the following tables:
tblEmp - PK - auto number, EmpId, First Name, Last name
tblFunction - PK - FID (autonumber) has departments
tblTL - PK TLID (autonumber) has list of team leaders
tblRelated - pK - Id (auto), EmpId, TLID, FId as long integeres
tblCodes - CodeId (auto), list of absenteeism codes like late, mia, etc
EmpLeave - pk - autonumber, empid, codeid, hours, date of absenteeism
I have a Qry_Master which just joins all the information together as it gets updated on a monthly basis
Now, I am trying to create a form where the user has option to select one or more tls and one or more codes and when they hit the button, it should come up with all emps that have those codes and report to the team leader selected.
In my form, I have made both my list boxes as multiselect and i have Qry_frm that is a parameter query but when i run the button nothing happens and i cant seem to figure it out. I have attached the sample database to this thread.
I have a report that gets its data from a query. I need the query to run before the report based on criteria based from two combo boxes on a form.
View 3 Replies View RelatedI am trying to set up a listbox with an option to select multiple items (I have done this and tested it with debug.print and it seems to work). I am then building a filter statement with VBA. I want to then use a button to add this statement to the filter in a subform with (a datasheet design), and then requery it.
My code below seems to be working in part. But I am getting all the items at times. Seems to work consistenly when selecting one item only, but I can't see anything wrong with my 'OR' statements when I debug.print.
Private Sub Command176_Click()
Dim i As Integer
Dim strFilter As String
Dim blnFirst As Boolean
i = 0
If Me.List163.ItemsSelected.Count = 0 Then
[Code] .....
howdy all, ive never touched Access until 2 days ago so my experience is
sorely lacking but here is my question:
i want to create a form with a combo box from which a selection is
made (data in the combo box is simply a field list from the same table the query is searching, but
my stumbling block is that i want to include the query in the
same form as the combo box and have it dynamically updates based on the
selection in the combo box.
however for the life of me i cant get the query to update based on the
input (using [Forms]![Form]![Combo1] as the criteria in the query) i have set this criteria in the
CustomerID field of the query (which is also the primary key of the table)
I have a form that has a combo box that contains every query in my database. This worked fine until i started having to create cascading queries to return the data I want. How can i populate my combo box with only certain queries in my database.
This is the code I was using to put all queries into my combobox.
FROM MSysObjects
WHERE MSysObjects.Name NOT LIKE "~*" AND
MSysObjects.Type = 5
ORDER BY MSysObjects.Name;
Upon clicking a button or clickin the query in the list, I want the query to run......So the main question is how to I include certain queries in my combo box but not all of them? I do not know SQL or any programming, so as specific as possible will help immensely. Thank you everybody.
Let me see if I can explain this.
I have two tables: TblValues and TblLOC.
TblValues is a table of stactic values where I have listed about nine field names with values under each that usually will not change. This way if there is a change to one of the fields later, I can just add it to the table.
Then there is tblLOC. Here I have similar field names that matches each field in tblValues where I can populate them in my form. I have several fields defined as combo boxes. In the properties for each field defined as a combo box, for row source type has Table/Query selected and for the row source I have the following code in the table, which is an example for the field named Profit Code in TblLOC.
SELECT tblValues.ID, tblValues.Pro1Code
FROM tblValues
WHERE (((tblValues.Pro1Code) Is Not Null));
Then I have my form where I have referenced my fields from the tblLOC. In the properties of each combo box, under the tab labeled DATA, the control source has the name of the the combo box field that is in tblLOC, in this case Profit Code.
Everything works fine. The user selects a value from the form and tblLOC is updated. I can create queries without criteria with no problem. However, in a query if I try to add any criteria, even as simple as using "like" to identify a value in a field of the table that has been populated by the combo box, I will get no results. However, if I use the same functionality in a column where a combo box is not used to populate the field, I get the results that I expect.
Here is an example of the SQL in the query that does not work for me that is applied against a field that is a combo box.
SELECT tblLettersOfCredit.[Profit Code]
FROM tblLettersOfCredit
WHERE (((tblLettersOfCredit.[Profit Code]) Like "Misc"));
If I use the same functionality with a field that is not populated by one of the combo boxes then everything works fine.
I am hoping that there's a simple solution for this, maybe syntax due to the use of a combo box or maybe a property that I need to set... So far I have been unsuccessful in finding the answer. If anyone has any ideas on this one, I would certainly appreciate it. Thanks :)
I have 2 unbound combo box's on a form 1 called cboclient1 and the another called cboclient2. would like to be able to filter field name client name twice by cboclient1 and cboclient2 here my sql from my query
Code:
WHERE (((Assets.Client)=Forms![report gen]!cboclient1)) Or (((Forms![report gen]!Cboclient1) Is Null));
This works perfectly for cboclient1 problem comes when I try and add cboclient2
I have tried
Code:
WHERE (((Assets.Client)=Forms![report gen]!cboclient1)) Or (((Forms![report gen]!Cboclient1) Is Null))AND(((Assets.Client)=Forms![report gen]!cboclient2)) Or (((Forms![report gen]!Cboclient2) Is Null));[/
This doesn't work at all....
Good Afternoon,
I am trying to create a form where a user will enter in a value into a text field. Afterwards, when the user clicks "Enter", a query will run and will LOOK FOR THE VALUE THAT WAS ENTERED INTO THE TEXT FIELD. i.e.
User enters their address into the field and clicks the enter button.
Afterwards, a query will run like
select * from customers where address = @address <== the value the user entered into the text field. This is where the mystery lies. How do you pass values?????
Thanks,
Nervous Jervous
So I have this form with a combo box which successfully displays a range of items from which I would like to select from - but unfortunately I cannot. I cannot select anything from the combo box.
I have checked the form and table to ensure I have edit rights - which I do.
The form is based on a query - attached is a picture of the relationships within the query.
I assume I have created a non updateable query but this is only an assumption.
I am lost about the best course of action to take. Is there a way to identify the problem?
Do I just cut my loses and recreate the functionality somehow?
I have a form where I am trying to use 4 combo boxes(nomenclature,BPN,vendor, and reference) to filter a list box containing part numbers. The way I have it set up right now is in the listbox it is searching for each field and then in the criteria section i have [Forms]![myform]![respectedFieldsCombo].
This works when selections are made from the combo boxes but when one is blank (not being used to filter) then I assume it passes null for that value and the listbox doesn't return anything. I have tried to make it so the listbox ignores null values but im still having this problem.
For example: If i only have a selection for the vendor combo box then i want the list box to show all respective part numbers for that vendor, where nomenclature or any of the other fields are irrelevant. I also want to be able to stack these filters upon every new combo box selection.
I am working on a database where law clerks input the tasks that they accomplish each day. They use a form called "TaskDetails" to input all of the information related to their daily tasks via combo boxes. The problem I have is: often times the clerks work on the same task more than once and I would like them to be able to continue tasks already in progress - using notes to indicate what particular portion they completed that day.
In order to do this I would like to have a combo box that is based on a query of all of the tasks the clerks have completed. When they select a Task Name from the combo box, I would like all other combo boxes on the page to auto-populate based on the selection.The query that my combo box is based on includes all relevant fields.
When I click on the "Task Name" combo box I am presented with all of the records related to tasks. The fields/columns are divisionofwork, subcategory, workassignedby. I have combo boxes for all of these fields on the same page, bound to the table of tasks. Ideally, a clerk makes a selection from the "Task Name" combo box, and the subsequent 3 combo boxes will reference their relevant column in this selection and auto-populate.
I am creating a query that should filter records of events based on multiple fields. The filters should work with any combination of field criteria, but only two of the four field are working properly, as follows:
1. a text box for searching with event name (free text) - this is working;
2. a combo box to filter events by country name - this is working;
3. a combo box to filter events by event's keyword (category) - this is NOT working;
4. a combo box to filter events by year - this is NOT working