Change Query Based On Form Fields
Dec 19, 2006
I am trying to come up with a simple way for users to query the database and pull back entries relating to the (potentially vague) data they input.
I know how to run a query based upon data input in a form ("=[Forms]![FormName]![FieldName]"), and I know I can do this several times in one query.
However, if one of the fields is blank, the query doesn't return anything.
Is there a way to use only one query, but still allow the user to leave search criteria blank? The only thing I can think of is by using a very convoluted VB script and creating a query for each possible combination of criteria.
It's not an error-checking issue, either, as entering info into all of the fields isn't required.
For reference, the searchable fields are:
Agent Name, Beginning Date, Ending Date (intended for a date range), Request Type, and District Name.
A user could then search for any requests made by an Agent; any requests by an agent within a date range; any requests made by anyone in a date range; ad nauseum.
There are quite a few combinations, there, and I'd rather not create a query for each one (or code the VB conditionals to choose the right one).
I hope I'm clear in what I'm trying to accomplish :o
Thanks in advance!
View Replies
ADVERTISEMENT
May 30, 2014
I have a form where customer data is entered. one field serves as a criteria for a query. If, for example, A is selected in the form the query uses the criteria "A" in the X field f query. However, if B is selected in the form, the query uses the criteria "B" in Y field of query.
I can easily do this by setting up two queries and having criteria A run in X field of query and similarly for B. However, it seems as if there should be a way for me to have one query and simply use the criteria in a different field.So, is there VBA that will update a given query's SQL to use one criteria in one case and another critiera in another case?
View 1 Replies
View Related
Oct 29, 2013
I need a query's field to change to what is selected in a combo box.
Example,
I select product 1 in the combo box on a continuous form, after the update there is a requery. The form now displays the data from product 1.
I have tried putting [forms]![form1]![combo1] into the field box of the query. But this only makes the form display "product 1" in every row, it does not take any data from the table.
View 3 Replies
View Related
Feb 6, 2014
I am new to MS Access, and am not sure if what I want to do is even possible. It is my understanding that comparing subsequent cells within a field in a database cannot be done- so I thought I'd see if there is a way to go around it.
I have a dataset for pedestrian activity, with over 3 million rows and 40 columns - too big for excel to handle.
I need to sort the entire dataset by 2 fields, following which I need to search down the field containing my pedestrian ids (numbered 1, 2, 3... till approx 10000), and when my ped id changes from one to the next, I need to check the value in a field showing the ped location, and if that matches with the ped's previous location, I need to copy out a cell corresponding to the previous cell's time stamp. If it doesn't match, I need to copy out another time stamp from another field.
View 1 Replies
View Related
Nov 6, 2013
Is it possible to add a field (i.e., variable) to a query (or SQL programming) based on a form selection?For instance, if I use a form to allow an end-user to specify which fields they wish to include in the query (essentially creating a UI for the query builder), how would I go about creating the query/SQL or updating the query/SQL?
View 3 Replies
View Related
Nov 22, 2013
I have a subform which is a continuous form displaying a single combobox of trade names so there may be up to 4 or 5.i want to click on a button on the main form which will open a form based on a query that will display all personnel matching all or 1 of the trades in the subform?
View 2 Replies
View Related
Jul 2, 2005
Greetings! This forum has been most helpful to a rusty Access user.
I'm creating a student group score entry form, having problems with creating the form I want. Say for example we have the following:
1. Three groups: A,B, and C.
2. Group A has 5 students, Group B has 3, and Group C has 20.
I want a form where I can select group A, and a list of the students will populate and enable me to enter scores for all of them on the same page. This will save time not having to re-enter the information for so many students. If anyone has a clue of what I'm talking about or is willing to lend me aid, I would appreciate it.
Cheers.
View 4 Replies
View Related
Feb 19, 2007
Newbie here. Just trying to make a characteristic (e.g., color) of a button change based upon contents of a form field. Can anyone please adivise. All help is greatly appreciated.
View 3 Replies
View Related
Jun 30, 2015
I have a form with a combobox, textbox, a button and a listbox.
Currently the button checks if the textbox is empty and if it is, it runs a SQL query to select data using the combobox value in the where clause, else it selects data using the textbox value in the where clause
I need to be able to type or select values in both controls and use those values in the SQL query as a where.
View 6 Replies
View Related
Jun 28, 2015
I have a query that creates counts of fields based on the data in other fields, basically it tells me that in a table there are two entries with value ABC????? and three of DEF????? , the query works perfectly.
When I create a form to display this data and base the form on the Query I keep getting a message box asking for the ID (key field) from the base table.
If I type * in the box (to denote all values) and press enter I get the results expected.
View 4 Replies
View Related
Mar 6, 2008
Just wondering... is this possible.
I have a database with 200 clients. If a user pulls up a record and the customer's insurance has expired, is it possible to change the background color of the form so that this automatically alerts the user that this customer needs to be contacted and the record needs to be updated?
Or are there any other suggestions that may serve the same purpose?
View 1 Replies
View Related
Nov 8, 2013
Is there a way of using a text field value as the heading for query column?
ie change...
Column header: FieldInATable
to
[form1].[field2]: FieldInATable?
View 1 Replies
View Related
Jun 21, 2015
I have been looking some information on changing image based on form combo box selection on form.
I manage to do case by case but i need it in a simple code because their will be many employees just to avoid adding case by case code for each one.
Private Sub Emp_IDCombo_AfterUpdate()
Select Case Emp_IDCombo.Value
Case "AM-001"
Imageholder.Picture = "C:UsersAMGDesktopam-001.jpg"
Case "AM-002"
Imageholder.Picture = "C:UsersAMGOne DocumentsHR & Admin DatabaseEmployee Picturesam-002.jpg"
End Select
I have employees table where all images location is saved in text field and i have a combo box on form which is employee id.
Tables relationship
Employees_table [PK] to Contracts_table [FK] via field name {emp_ID}
Fields Name
Combo Box name on form Emp_IDCombo and row source is SELECT Employees_table.Emp_ID, Employees_table.EmployeeName, Employees_table.Emp_Pics FROM Employees_table;
Text field is located in employees_table called [Emp_Pic] for images location.
View 10 Replies
View Related
Jun 12, 2014
I have two combo boxes in the form header and command buttons in the form detail. The combos allow the user to select either a customer or a prospect, then the command buttons open forms that only show records pertaining to the customer or prospect selected. Combos are "4Custcbo" and "4Proscbo".
1.How do I change which command buttons are available depending on whether the user has selected a customer or a prospect? Do I use two different subforms or is there a better way?
2.How do I hide the other combo box once the user has selected either a customer or a prospect?
View 3 Replies
View Related
Jun 24, 2014
I would like to change the background color of a field that is the result of a Unique Values query. I am trying to get a list of invoices where all the line items are approved. I can't seem to get it to work the way I want because if even one invoice line item is approved it will show up as approved.
Is there a way to change the background color of the invoice field to red if ANY of the Approved line items are = False
View 7 Replies
View Related
Jun 3, 2015
I have a dashboard-style form that has a list box with tasks connected to a query with line items of those tasks.
I have another query that will only show the line item selected from the list box. This means the query will only have one line item.
I want to have a command button below the list box that, when clicked, will modify the solo item in the second query to change a yes/no field from no to yes.
How can I accomplish this? Also, how can I make a button that deletes the selected line item?
View 3 Replies
View Related
May 5, 2013
I need to come up with a way to derive field X (see below) in a query.
For each change in field A, Set X=1
For each change in field B, X=X+1
The below table shows what the query results should look like.
A
X
B
[code]....
View 2 Replies
View Related
Nov 12, 2014
I have a little "y2k issue".
My DB has many (!) forms that are based on Crosstabs queries. The headers are years, and therefore my formfields has controlsources like 2014, 2015 and so on.
Example:
The table it comes from looks something like this:
tblProject
Project: Myproject
Value: 1000
EndYear: 2014
The crosstab then takes the EndYear as a coloumn header. Then my forms use these fields, now named 2014, 2015, 2016 .... and give me a nice looking table showing the accumulated "end" for multiple projects.
The problem is when the year changes after 31/12, all fields must manually be changed. This means I have to manually change 2014 to 2015, 2015 to 2016 and so on in all of my formsfields controllsource.
I have played around to find a way to change the source of the field, and hoped I could say something like =year(now()) and for the next year say =year(now())+1 but this only returns that year as a value, and not as a controlsource...
The query functions nicely (ive changed the system time, and it has no issues with this structure), but the forms source is still in the previous year, and returns and error (of course).
When the year changes past new years eve, so will the headers (fieldname) change... but how to make the form change fieldsource?
View 3 Replies
View Related
Apr 11, 2014
I have a subform that displays doubles in a textbox as fixed with 4 decimal places as default. I have been trying to change the number of decimal places based on the value in a textbox on the main form. I have tried this:
Code:
Forms![Main Form]![Holder_subform].Form![Final PCT].[DecimalPlaces] = CByte(Forms![Main Form].Numdecs)
where [Final PCT] is the textbox on the subform containing the decimal number and Numdecs is the textbox on the main form containing the number of decimals I want to use. I want to implement this Form_Current sub.
View 2 Replies
View Related
Aug 17, 2005
I have a query with the fields employee_name, shiftname, shiftdate and have set it up so that 2 input messages boxes popup allowing the user to input a shiftname (a,b,c) then a shiftdate. from this query i have created a form, but instead of having 2 message boxes popup on screen before the form is loaded is it possible to have 2 input fields on the form (one for shift date & one for shiftname) that allows the user to enter into these fileds whenever they wish provided the form is open and all the records bellow change matching the employee name with the corresponding shiftname & date?, any advice would be great.
View 4 Replies
View Related
Mar 20, 2013
I have a form which uses a parameter based query to present an individual senior doctor with a list of names for of individual juniors to provide an assessment report on. When the first form opens the user enters their RespondentID.
Once senior has decided which names to comment on I have another form which has the questions to be answered which is opened by clicking a button on the first form.
How can I pass a parameter from the first form to the second so that only the records relevant to that senior doctor are displayed? The underlying table has 60 senior doctors and 20 junior doctors. The senior doctor is identified by the field RespondentID in the first form. I've tried putting a WhereClause in the FormOpen command but I still get a dialogue box asking for the parameter RespondentID when the second form is opened.
View 2 Replies
View Related
Jun 13, 2006
I am very new at Microsoft Access. I have two forms, A and B, that are filled out by two different users. Form A gets filled out first than form B gets filled out. Each has a name box as well as a birthdate box and a few other similar text fields. However, each form also has a few distinctive fields. I was wondering how I could input the common information in Form A to Form B so the user of Form B does not have to spend time retyping the name and birthdates again. In other words, I want to synchronize the similar records between the two forms. I would like the values to appear in a table as well. I would greatly appreciate it if someone could help me with the visual basic code. Thank You.
View 1 Replies
View Related
May 31, 2006
:confused:
I am trying to help someone with a complex problem (so it seems to me) but I will first ask about what should be a simple thing....
First goal: to COUNT the number of times a TYPE of visit is made.
There are several different TYPEs but only interested in tracking 2 of them.
When a crosstab query is created - if one of the 2 parameters are not "met", a blank is returned. I have been reading posts about using NZ and IIf IsNull, etc to get past that - but none of them make any sense to me and the Access help suggestions do not work. Hope someone can make it clear with this information: (can't give more specifics to keep privacy intact)
The SQL was written by Access not by me. :)
Here is an example of the Crosstab SQL (which is using a previous query):
TRANSFORM Count([qryTest2.TYPE]) AS CountOfTYPE
SELECT qryTest2.CID
FROM qryTest2
GROUP BY qryTest2.CID
PIVOT qryTest2.TYPE;
-----------
qryTest2 SQL: (Grouping by to remove dups)
SELECT DISTINCTROW tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE
FROM tblM LEFT JOIN tblC ON tblM.[M#] = tblC.[M#]
GROUP BY tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE
HAVING (((tblC.TYPE)="Out" Or (tblC.TYPE)="In"))
ORDER BY tblM.CID, tblM.LNAME, tblM.FNAME;
Thanks for you time! :)
View 1 Replies
View Related
Jun 2, 2014
using access 2010. I have two tables, an example of their structure is in the attached PDF, and in text below. I want to be able to join/ answer question between the tables like the one outlined below.
Reference Table: A table with the most likely pet based on income and location
Header: Country, State, Income Min, Income Max, Pet
Record: US , NY , 1 , 5 , Cat
Record: US , NY , 5 , 9 , Dog
Question Table: A table with income and location
Header: Country, State, Income
Record: US , NY , 5
What type of pet will the people in the question table have?
Answer: Header: Country, State, Income, Pet
Record: US , NY , 5 , Cat
The logic works like this:
Question Country = Reference Country
Question State = Reference State
Question Income >= Reference Income Min AND Question Income <=Reference Income Max
Obviously I am not working with cats and dogs but this is my table structure.
View 3 Replies
View Related
Mar 20, 2013
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 Related
Aug 21, 2005
Hi all,
I am endeavouring to filter a form based on the position of an individual.
I would like the users to be able to select their position and then any record where there is a match in the "Primary Responsibility" field or the "Secondary Responsibility " field will be displayed.
I have an underlying query that is populated by a combo box on an unbound form.
This has worked when generating reports for individuals but I cannot get the required result when using the form.
Thanking anyone in advance who can assist me with this.
Regards,
Rod
View 2 Replies
View Related
