Forms :: Using A Query Within A Form?
Jul 24, 2013
I have a form that has a text box in the header and when the user enters the farmer Code into the text box the relevant record is displayed.
I am using the following criteria in the query within my Form Cotton12:
Like [Forms]![Cotton12].[2012]
Now I have 2 tables Table Cotton12 and Table Cotton11. They contain a database of farmers, from the year 2012 and 2011. Sometimes the data is the same sometimes it is not. For example Farmers are given farmer codes, however due to incorrect capturing of data different farmers have been given different codes throught over the years.
What I would like to do is in the same form, have the user enter in a farmer code and data from the 2012 table come up and the data from 2011 table come up so they can be compared and if that code is not in one of the tables the fields relating to that table stay blank.
For E.g. the form might look like this.
2012 Farmer Name Acreage Yield Estimate 2011 Farmer Name Acreage Yield Estimate
View Replies
ADVERTISEMENT
Oct 19, 2004
What is the best way to impliment a query in a form so that the user can view the query records, and have the option to print or save the selected record using command buttons?
I tried subforms but I could not get the command buttons to work in the subform after it went into the form, it wanted to print the entire form instead of the selected record from the subform.
So in a nutshell I have 3-4 queries that are built, and I want to have them show up on my form in a format that the user can scroll through the results and select a single record of the results and then print or save that individual record from the form, if such a thing is possible.
Thanks in advance
Todd
View 1 Replies
View Related
Jul 23, 2015
I have a form which will be used as the basis to print a label.
It is bound to a query and when I open the form I pass over a 'where' condition to return 1 record. I then use the query to produce a report/label.
What I want to do is to update the form/query without updating the underlying tables to the query.
View 14 Replies
View Related
Aug 17, 2014
I have a big table, EquipmentDetails and separately I have job plans.Job plans can have many pieces of equipment. I store the relationship in EquipmentDetails_JobPlanDetails, but am feeling frustrated as I can't seem to get Access to build the SQL query from this in the way I want.
I am generating a List Box in a form which is populated from a query.The query calls on a table which lists ItemID from EquipmentDetails and JobID and in the current form (where the list box is) I want to pick up and display the ItemIDs associated with that JobID. The current JobID reaches my form correctly (I've proven this by displaying it in a text box) from the previous form.
The problem I have is that I can't seem to get the SQL query to only get ItemIDs that are linked to the current JobID. No matter what I try, it either gets all of the ones in that table, or I can't compose one.What kind of SQL do I need to only grab the ones relating to the current JobID please?I've tried building it in the design view but it says that it can't do it because the outer joins are ambiguous.
View 2 Replies
View Related
Jul 28, 2013
How can I display the count value from a query on a subform for the related/selected record?
I've tried a DLookUp without success (the text box remains empty):
=DLookUp("[CountOfMethodID]","Methods count","[MethodsRungVisitID] = Form![Form4b]")
Here's my table/query/form info:
Query is called "Methods Count"
Field of that query which I want displayed on the sub form is "CountOfMethodID"
Sub form is "Quarters and peals sub"
Main form is "Form4b"
Both form and sub are parent/child linked
There is a Relationship between the VisitID field in the "Visit Dates" table and the MethodsRungVisitID field of the "Methods Rung" table.
View 1 Replies
View Related
Nov 9, 2013
I'm trying to perform a task that I'm absolutely certain is simple to do.. if you know how to do it. I'm trying to add three check boxes in the form header to affect the query related to the form.Each is a simple Boolean, and simply want to apply a "WHERE col1 = val1, col2 = val2, col3 = val3". Like I said, very simple.
It seemed like I could just use a Macro in the click event of the check boxes, and use a SetFilter within the macro that applies a Where Condition = [Shipped] = [chkShipped].
This... almost works. When I click the checkbox it asks for the value (it doesn't know how to process [chkShipped], which is the name of the corresponding checkbox). So that's broken, but if I provide 0 or -1, then I get the expected result.I would like a 3-state checkbox, but I can't even get this working. I can tell I'm either close, or in the entirely wrong direction.
View 2 Replies
View Related
Nov 20, 2013
I have a subform which is controlled by a query eg . Fname&" "& Lname as Name, order_number. and i have form which have combobox.
When form opens the query works fine but when i search value order_number in combo box it gives the result but with ?Name in Name column.
I know i can do this with Filter method, and rewriting the query, but i want to use it by passing the value in query of subform.
View 3 Replies
View Related
Mar 31, 2013
I've done a query with my equipment table for duplicates company names to get a number for my customer form for total number of equipment.
how can I get the number of duplicates for each company to be put into total number of equipment in my customer form?
View 6 Replies
View Related
Feb 9, 2014
I currently have this form (combobox) that filters using one piece of criteria.
Private Sub cboSelectPress_AfterUpdate()
'Moves to Press field and
'finds records where "press" matches whatever is selected in the combo box
DoCmd.ShowAllRecords
Me!Press.SetFocus
DoCmd.FindRecord Me!cboSelectPress
End Sub
"Press" is a machine number. How can I add more combo boxes to filter additional information from my query. Like "room" or "product" or "date?" What happens if the user leaves one of the boxes blank?
My fields are
"Date"- date the product was ran
"press"- the press it was ran on
"product"- the name of the product
"shift"- the shift it was ran on
I want the user to be able to select ALL or none of these fields for results.
I am using Access 2013
View 2 Replies
View Related
Dec 24, 2014
I have created a form with buttons which runs queries. The parameters I need for the query are being passed through just as I wish and the relevant query is run correctly. However, if I then press the button on the form with different parameters I get the results from the previous query. I can only solve this by closing the query before pressing the button. Is there an easy way to close the query to prevent this from happening. In case this is relevant, the only data which I am returning from the query is stored in the windows clipboard.
View 6 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
Apr 5, 2014
I have the following code that is intended to use the three fields obtained from a record on a continuous form and display the details of the record on a single form. I have not used the findfirst method because I am having enormous problems with it. Instead I am defining a query based on the recordsource of the continuous form. This sql query gives one record as result and that record is to be used as recordsource by the single form.
Code:
Public Sub SboxSelectForm(ForName, ParName, QryName)
On Error GoTo NotFound
Debug.Print "SelectForm ForName="; ForName; " ParName="; ParName; " QryName="; QryName
Dim ResultQy As String, F As Form, P As Form
Dim db As DAO.Database
Dim Prsc As DAO.Recordset, Frsc As DAO.Recordset
[code]....
View 5 Replies
View Related
Apr 27, 2014
I am having a problem with my append query on my student form.When I add in the details on the form and run the append query it throws back a dialogue box saying Enter parameter value...
View 2 Replies
View Related
May 1, 2014
1) I Created a button that imports data into a table (Hours)
2) I created a query that finds duplicates in that table
3)I created a form put the button on said form
4) I dragged my query from explorer window onto form
5) I click button, code runs to import data into table however query on form doesnt update, I have to close and then re-open the form, obviously this is no good.
View 1 Replies
View Related
May 14, 2015
I filter my table with a query - where the Invoiced Date is NULL. In the vba ADD RECORDS code I may INSERT new records which should then also appear - but are not showing up. The records ARE added to the table, but don't show up unless I close the form and reload it and then the query filter picks up the added records.
View 5 Replies
View Related
Aug 21, 2013
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?
View 6 Replies
View Related
Mar 19, 2013
Display data from Query into Form..I am attaching the database dbtest.zip which contains a table, couple of queries and form.I have done what ever settings I could after going thru some tutorials / discussion forums online, but to no avail.
All that I want is, when I open the form "frmtest" in "form view", and select CS in Combo 8, the corresponding fields in the form viz Item name, Sl No and System Name should get updated on the form. I want to view it in "Single Form" format.
View 1 Replies
View Related
Sep 29, 2014
I am using Office 2007 and have a form that is linked to a query, also I have a sub form linked to the same query. I cant understand why both forms say in their properties that they are linked to a query but when I try and add a text box to show customer ID it tries to show data from a table
View 1 Replies
View Related
Jul 8, 2015
I'm working on a db for work. On my Nav form, I have two tabs- each tab has cmd btns that open up different search or data entry forms. I also have a few reports that can be opened based on a query I created.
The last thing I want to add is a control/button that opens the query wizard so the user doesn't have to navigate to the "Create" tab of access. The reason behind this is that one user may not know how/where to find the query/report wizard.
Is there some VBA code or Macro I can create to add this functionality to a button ("Create New Report" or "Create New Query".)
I'm using Access 2010.
View 5 Replies
View Related
Nov 6, 2013
Using 2010.Would like to open a new form as a popup to display the results of a query. Trying to avoid eliminating objects in my DB, so not wanting to create Query Objects, but just creating the SQL on the fly.
The purpose of opening the popup form is to display the results of the Query based on a Button selection that the user chooses. Not sure if I can pass the Query from Form to Form, or if the called Form should run the Query. The Query output is a single record which is a Memo field, which is why I want to open the new form to display the result. The Memo text can be a page long or more.
I am not getting any errors and can open the form. The problem is that I am not getting the Query results displayed on the new form. The code below is from my first form that has the buttons. The popup form is Results_frm.
Code:
Private Sub InScope_bt_Click()
Set dbs = CurrentDb
strSQL = "SELECT In_Scope FROM Project_Scope_Deliverables"
Debug.Print strSQL
ResultType = "I"
DoCmd.OpenForm "Results_frm"
Forms!Results_frm.RecordSource = strSQL
End Sub
View 11 Replies
View Related
May 12, 2015
I have a form that displays employee details. I've also created a query that calculates the current age of all employees and updates automatically. I've also created a subform that displays the query data.
My question is, how do I get the query data to display on my main form? Can I do it directly from the query or do I have to use the subform? There are multiple columns on the form and I want to add it as a new column that displays the same as the rest but continues to update automatically.
View 9 Replies
View Related
Feb 23, 2015
I want to use a variable from a form to a query as a criterion. but how?
View 1 Replies
View Related
Sep 28, 2014
I have a form that has a check box on it. For some reason the check box is not being checked when an update query is run prior to load. I issued the 'Me.Requery' command after the update query is run (via 'On Load' and 'On Open' in the 'Event' property . . . neither worked) of the form. Is this the correct way to refresh a form after an update query is run ?
View 8 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
Oct 23, 2013
I have a form with combo boxes that works beautifully, but I've been asked to add another feature to it. It requires adding a button that runs a query and displays the query results on the screen.The query code is:
Code:
SELECT DISTINCT Product.MSDS
FROM Product INNER JOIN tblStoreProducts ON Product.[ProductKey] = tblStoreProducts.[ProductKey]
WHERE (((tblStoreProducts.MaxUnits)<>0) AND (([Product.HazardKey])<>79))
GROUP BY Product.MSDS, tblStoreProducts.StoreKey
HAVING (((Product.MSDS) Is Not Null))
ORDER BY Product.MSDS;
One of the existing buttons on the form has this code behind it:
Code:
' btnHMIS_Click
Private Sub btnHMIS_Click()
On Error GoTo Err
If IsNull(Me.cboCompany) Then
[code]....
As you can see, the button is able to pass the parameters (which Company, and which Store within the company) to the report.
Code:
' btnMSDSSheetsPrint_Click
Private Sub btnMSDSSheetsPrint_Click()
On Error GoTo btnMSDSSheetsPrint_Click_Err
[code]...
How do I pass the StoreKey information into the query? Is it my query that's wrong?
View 2 Replies
View Related
Mar 10, 2014
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
View 3 Replies
View Related
