Using A Form To Set A Query Field
Mar 8, 2006
Any help or advice on what to search on would be appreciated please
I have a table with the stocklevels of all my shops Fields (PLU, Stocklevel_1 to StockLevel_6..)
Currently I have a form with 3 category levels which set the criteria in a query so I can gereate a list of 50 random PLU's as long as the query already has which Stocklevel to look at (ps the stocklevels table is linked to the Stock details stable via the PLU field)
Rather than creating multiple queries can I include another combo box or a option group with a list of branches which will set which field to get info from? Any suggestions on how this is done?
Thanks:confused:
View Replies
ADVERTISEMENT
Apr 25, 2013
I have 3 peices of data that I am working with: Group number, plans and benefit elections. On my form I have fields for the group number and plan and 2 queries. When I enter a group number the first query displays a list of plan descriptions for that group. From there I can enter the plan description into the form's plan field and that runs the second query to give me a list of valid elections for that plan. This all works fine but I want to make the plan selection a little less tedious. What I would like is to be able to double click the query field housing the plan description and have it copied to the form's plan field. I could use the ID instead of the plan description but I work in a production environment and very key stroke counts so I would really like to have a simple double click process.
Is this even possible? If so how would I set that up? I tried using the double click on event macro builder but it does not seem to have this kind of option.
My form contains fields for group number and Plan. The same form houses 2 querys, one that pulls plans based on the group number and the other to pull elections based on the plan field (not the plan query). Would like to double click a plan within the plan query and have that description populated into the form's plan field.
View 6 Replies
View Related
Aug 17, 2006
I have sequence field in query and want it show in form.
Please help me. Thanks
View 1 Replies
View Related
Dec 12, 2013
I have a Form that has a subform and in that there is a subform that runs a query. I need to take the value of in one of the fields returned in that query and transfer it to a field in my original subform.
View 4 Replies
View Related
Nov 22, 2005
I have a list box in a form that controls the query results which will display a name when a certain region is selected. I would like for this to be incorporated into my form so when the user selects the Eastern region for example, John Smith will automatically be returned in the filed on the form since he is the person corresponding to this region. What is the best way to do this and also have the query filed linked to the same table my form is linked with? Thanks.
View 4 Replies
View Related
Jun 21, 2005
:confused: I've tried everything I can possibly think of to get this to work. I've also looked at other threads and I still couldn't get this to work. The code for the list box in my form is Forms!Display_Engineering_Jobs_Report_Inputs!Repor t_Column1. I am trying to set the value in this list box as the input for the field on my query. This value also corresponds to a heading within a table I have. The information under the heading in this table is what I am trying to show in the query. Please help! Also, I'm pretty new to Access, so explain it as if you were writing a page in "Access for Dummies." Thanks!
View 9 Replies
View Related
Jul 24, 2005
Hi,
My situation is hard to explain but i will give it a shot. What i have is a db with employees and its being used to track training sessions. These employees are split into 6 teams and those teams are split into 7 teams each.
I have written two queries, first asks the user what team to display. This data is used in the second query. The second query asks for what training item we are searching for. The result of the 2nd query is displayed in a form. Displaying of the information on a form is important. This way the user can verify that the members on this list are the ones he is updating. Now I wrote a third query that works off of the 2nd and updates the datecompleted field with todays date.
Problem is when i post a button on the form to show all these employees complete it referes back to the first query and asks all the filtering questions again.
I would like to know if there is any way to run an update query from a form that has already been filtered. Or if there is a way to reference these records in a new query so as to avoid all the filtereing again
hope i explained it well
Thanks
Ricky
View 7 Replies
View Related
Aug 20, 2005
I have been looking at some of the solutions to the running sum problem.
The queries I use cannot use the primary key as there are gaps as some filtering has been done with an unmatched query to give me my sample dataset.
From this dataset I have a list of courses and the number of learning hours per each course. This is the field I would like to perform the running sum on.
Its a parameter query and the intention is to email the query the the various schools so no form was necessary.
The solution on microsofts web site wants me to use a form and I dont understand how it works anyway.
Is there a code solution that can use a recordset that doesnt rely on forms?
At present I am reliant on the date which does work but not where a student does two courses on the same day. The forums advice was to use the autonumber but there are gaps in that so that doesnt work. can I generate an autonumber on the fly as an expression and use that in the sub query method?
Fields:
[Date][Person Id][Course Code]
[Course Name][GLH][Running Total][UpliftCode]
[50%][Rule Broken]
what I have so far:
SELECT Pro.Date, Pro.[Person Id], Pro.[Course Code], Pro.[Course Name], Pro.GLH, (Select Sum(Pro1.[GLH]) FROM [QryAllEnrolmentsWithoutMatchingQryWithdrawls] Pro1 WHERE Pro1.[Date]<=Pro.[Date]) AS [Running Total], Pro.UpliftCode, [Running Total]/2 AS [50%], IIf([UpliftCode]="99","No",IIf([GLH]>[50%],"N","Y")) AS [Rule Broken]
FROM QryAllEnrolmentsWithoutMatchingQryWithdrawls AS Pro
ORDER BY Pro.Date;
kind regards in advance
View 1 Replies
View Related
Mar 10, 2006
Hey All,
I have a question regarding referencing a form using dates between.
Currently I have it setup as:
Between [Start Date] And [End Date]
on the query.
I want to reference a form, which i figured would be like this
Between [Forms]![frmSBReports]![txtStartDate] And [Forms]![frmSBReports]![txtEndDate]
But this doesnt work! :confused: What did I do wrong here? Any help as always is greatly appreciated!
Thanks!
Mateo
View 1 Replies
View Related
Mar 6, 2005
I have created a form from a query. I have then at a later date added a new field to the query. I have returned to the form and attempted to add it to the form but it doesn't show the new field within the control source options. Does anyone know why? Do I really have to start all over again with my form?
Thanks
Just1
View 3 Replies
View Related
Apr 21, 2005
Simple set up,
I have a Sub form titled Training that has the the following two fields:
[Department],[Jobs]
The jobs is a pull down select box that contains all of the job titles for the entire plant. What i would like to happen is for the user to be forced to select the appropriate department first and then use the [Department] selected to feed the criteria to the query that the [Jobs] field is located in.
In addition i have been un-able to get the fields to be locked until the [Department] field is entered.
I have tried the following unsuccessfully:
Jobs Query Criteria:
=[Foms]![Training]![Department]
View 1 Replies
View Related
Jul 17, 2005
Hi All
Ok I have a simple problem. I want to entry value of a field as criteria of a query. Ie. I have a field on a form which is EmplyeeCategores. It's simple to use this as a a filter in a query by simply putting Forms![frmMailer2]![EmployeeCategories] in the criteria of the EmployeeCategories column in the query if there was only 1 criteria.
What I mean is, if the value of the field in form was A1 for example, it'll work and the query will bring up all emplyees who are categorised as A1. But I want users to be able to put multiple codes in the form, such as A1 or A2 or C4, and the query to bring up all the employees that fit into all 3 of these categories,
I hope this makes sense. Any help will be much appreciated.
Many thanks
Oz
View 3 Replies
View Related
Dec 1, 2005
How can I get my form filed to return the results of a query that I have? For example I have my query set to use the month and year that are in my form in a cbo to get the desired results for Balance Growth. I want this Balance Growth result to show in my form field after the year is updated in my cbo. Right now in my Balance growth field on my form I have it linked to the query but it has the #Name? in it. Thanks for any help.
View 2 Replies
View Related
Dec 9, 2005
I have a form that is linked to a table. I would like to have a field on my form that has a control source of a query I have. There are cbos on my form that control the output of the query so I would like for the query to run after all 3 cbos have the data selected and then this form field linked to the query to update. The 2 problems are:
1. How to link this field to the query
2. How to get this field to update after the cbos have been selected.
View 1 Replies
View Related
Aug 5, 2006
Is the a way of changing the fields selected in a query by a form.
I have a form showing how many people need to do each module. Each textbox is populuted by a count from the table.
The idea is simply to avoid having 26 queries based on each module. Is there a way to have the field name changed via the form.
I have used the on dbl click event to pass the field name to a hidden field on the record. I just need to pass the data in the hidden field to the select query.
View 2 Replies
View Related
Aug 19, 2013
I want to make a query([query1]) based on a table([table1]) and a field from the [form1] then I want to make another field in [form1] and bound it to [query1] all the fields are number (and double in [table1])
View 1 Replies
View Related
Jul 11, 2012
Can a form change the field on a query?
Right now one of the tables in my query has multiple years across the top (as the fields), 2010, 2011, 2012, 2013. Each year contains different rates that get applied to labor hours.
I would like to know if there is a way to have a form change the field in my query. I have attached a word document showing print screens of what I mean.
View 1 Replies
View Related
Apr 26, 2006
I tried to word the title as accurately as possible.
What I'm trying to build is a Form where they can select data in different fields to limit (filter) the list. In my query criteria I want to read the form, if there's a value use THAT in the criteria (for that field) otherwise ignore it.
I've tried isnulls etc. If I put a value (like City) it properly returns all the records with that city, however if I put nothing the "ISNULL" returns a value that the criteria doesn't match i.e. I get nothing.
There must be a way to tell a query to NOT use any criteria if an object (form!field) is blank...?
View 2 Replies
View Related
Nov 8, 2006
Hello,
I am running a append query from a button.
In the query, I have set the OrderID criteria to:
[Forms]![sfrmOrders]![txtOrderID]
Whenever I try to run the query from the open form & subform, an Access 'Enter Parameter Value' window pops up.
I would like to use this Criteria for many different features in my db (SendObject, etc.), but I cannot find how to set the OrderID based on the current form.
If anyone can see what I am doing wrong, I would appreciate your help!
Thanks!
View 9 Replies
View Related
Dec 21, 2007
Hi-
I have an Access DB with several different forms all relating to one master table.
Each record in the table can be resolved=yes or resolved=no.
Right now each user must click the resolved box to resolve the entry and then enter the date.
I have created a button that will run the update query below.
UPDATE [TBL Master] SET [TBL Master].[Resolved] = Yes, [TBL Master].[Date Completed] = Now()
WHERE ((([TBL Master].[Primary key])=[forms]![frm name]![primary key]))
WITH OWNERACCESS OPTION;
The query allows me to update the record on that form efficiently. However, I do not want to create one query for each form (30 plus) for this.
Is there a way to use the form that was used when the button was clicked without naming the form specifically?
View 4 Replies
View Related
Feb 21, 2008
If anybody could shine some light it would be much appreciated.
I have 3 tables:
Task table---*Task ID, Task(txt), Start(date/Time), finish(date/Time)
Tasking table---*TaskingID, TaskID, EmployeeID
employee table---*EmployeeID, Name(txt)
The employee and task table are joined to the tasking table. This is so i can have a task which has many employees and an employee with many tasks. What i'm try to do is create a form which starts a task with a subform to add employees, but with combo box which filters employees with tasks which over-lap the task in focus(start and finish fields) but still showing employees which do not yet have any tasks. the start and finish fields are hh:nn dd/mm/yy.
If anybody can help i would be very grateful
Jim
View 3 Replies
View Related
Jun 15, 2006
Hi
I have a Form called Products. Each product is uniquely identified with a primary key called [ProductID].
Products contains a calculated field called [UnitsOnHand]. [UnitsOnHand] gets its values from a subform called ProductTransaction that exists within the Products Form.
ProductTransaction contains a field callled [Received] . At the moment [UnitsOnHand] will add up all the values it finds on the [Received] field to derive the [UnitsOnHand] value for that product at any particular time ie =Sum(nz([Received])) on [UnitsOnHand] .
I also have a Query AutoSell that outputs a value for each [ProductID].
How can I use these values for each [ProductID] generated by the Query to substract from the values on the [UnitsOnHand] calculated field on the Product form.
ViRi
View 1 Replies
View Related
Oct 18, 2006
hello.. i have 2 tables
CUSTOMER TABLE
customerid
customername
customerphone
BOOKING TABLE
bookingid
bookingdate
customerid
At the moment i have 2 forms.. one customer form based on the customer table and one booking form based on the booking table.
at the moment, my user enters a customer record using the customer form.. they then save and close the customer form..
they then open the booking form.. and in the customer id field they enter the customers id.. this way, they successfully register that customer to that booking..
what i want to do is allow the user to enter the customer details and the booking details in one form..
i assume i would have to create a third form based on a query..
if i include all the fields from both tables in this query.. and then make a form based on this query, how can i make the following 2 things happen..?
1. when the third form is opened and customer details are entered... a new customer record will be added to the customer table (with a new customerid)..
2. this id will then need to be auto placed into the foreign key customer id field in the booking table.
do you get me?
View 3 Replies
View Related
Feb 21, 2008
If anybody could shine some light it would be much appreciated.
I have 3 tables:
Task table---*Task ID, Task(txt), Start(date/Time), finish(date/Time)
Tasking table---*TaskingID, TaskID, EmployeeID
employee table---*EmployeeID, Name(txt)
The employee and task table are joined to the tasking table. This is so i can have a task which has many employees and an employee with many tasks. What i'm try to do is create a form which starts a task with a subform to add employees, but with combo box which filters employees with tasks which over-lap the task in focus(start and finish fields) but still showing employees which do not yet have any tasks. the start and finish fields are hh:nn dd/mm/yy.
If anybody can help i would be very grateful
Jim
View 3 Replies
View Related
Oct 24, 2013
I've inherited a database that has a table with a large number of Yes/No fields. Apart from normalizing the database, I was wondering if the following was possible:
ContractTable has Yes/No Fields for rights granted for each order (television, dvd, internet etc).
Using a form, the user selects the specific right they'd like to run a report on - selecting this right from a drop-down menu, this is labelled "RightsCombo21" on the form.
When the button is clicked, the right selected is now the "Field" section of the query.
The SQL query right now looks like this (shows all rights sold for the territory):
WHERE ((([Contract Table].[Contract Cancelled])=False) AND (([Contract Table].[Contract End Date])>Now()) AND (([Contract Table].[Contract Type])="License Agreement") AND ((ContractIDAndCountry.CountryName)=[Forms]![TerritorySpecificRightsChooser]![TerritoryCombo7]));
I'd like to add something to this WHERE statement:
AND (([Contract Table].RightsCombo21)=True)
Not sure if it's a syntax issue, or I'm attempting something that isn't possible.
View 4 Replies
View Related
Mar 3, 2014
I have a database I use for tracking IT problems (tickets). The form I use has a field to select who the ticket was opened by. To select this, I created a query that shows only active contacts (users) and set the opened by field to use this query to select the user. It works great except for the fact that once a contact is no longer an active user their name on the opened by field is blank and no longer shows on any old tickets they had. Their name is still associated with the ticket though. I know this because I have the opened by field set so when it loses focus it opens the contact record (form) of the user so that we can update their data. So I find it weird that the opened by field on my old historical tickets wont show the name of a user who is no longer with us but the field itself knows who it was and brings up the contact form for the non active user after lost focus.
View 1 Replies
View Related
