Forms :: Adjusting Query In Form

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 Replies


ADVERTISEMENT

Problem With Adjusting Tables/database

Aug 1, 2006

Looking for advice on how to make changes required in my database designed to track health wellness. Here is a breakdown of what I have now:

Membership Query built off membership table that isolates one employer group and creates age off birthdates that will change as the months go by.

Category table that gives up to 9 different items included in the wellness program along with the criteria. Shown as A through I with corresponding age and time restrictions.

Compliance Log – results come from compliance input form that updates each member’s data relative to the wellness items that are listed. Based on restrictions in place for their age group, we have been indicating them as either compliant or non-compliant.

Scorecard – built of some of membership query details, including the group # and the age of the person. Contains a subform that is built off the compliance table that lists the Wellness Category, Time Frame, Criteria, Compliant (yes, no, na, unknown) and complaint date of activity.

Everything was perfect until one of the larger employers now wants the scorecard to be more specific. In other words, under criteria, wants specifics spelled out according to gender and age. Previously we were showing “to be completed within time frame”. Even though everyone’s scorecard showed the same category/criteria information, we internally applied the age/gender rules based on our data input. Now they want the criteria to reflect the rules applicable for each member only on his/her scorecard. Example: Health Maintenance exam, age 19-39, every 12 months (if member within that age group) or 40+, every 12 months etc.

I have struggled most of last night trying to figure out different ways to make this happen in an automated way, and I am at a loss as to how to reconstruct some of my work to allow for this. I am looking for some ideas here. For starters, I did expand the category table to include the different gender-age groups but cannot figure out how to get this related to my members, their changing age, and the scorecard.

I am attaching a brief copy of my DB with hopes that someone can help me. We need to send scorecards out on Friday and short doing lots of manual things; I am not sure where to go with this.
:confused:

View 1 Replies View Related

Reports :: Automatically Adjusting Height Of A Control

Aug 18, 2014

I have a report that prints lab tests. One section has 4 columns that are framed. Only the first control (STest) expands to more than one line. The Can Grow works on this control and subsequently the section expands. I need a code that expands the height of the other three controls to match that of STest. I am using office 2010.

View 11 Replies View Related

Adjusting Line Spacing In Access 2010?

Oct 20, 2011

I can't find anything in "access reports 2010" which allows me to adjust the line spacing. The default spacing is "huge", yet line spacing is set at 0cm.

Quick note: line spacing is the distance between detail lines.

View 5 Replies View Related

Query In Forms Question (printing A Single Selected Query Record From A Form?)

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

Forms :: Update Form / Query Without Updating Underlying Tables To Query

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

Forms :: Using ID Value Passed To Form From Previous Form In Query

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

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 5 Replies View Related

Forms :: Display Value From Query On Sub Form

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

Forms :: Query From Form To Subform?

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

Forms :: Query From Table To Form?

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

Forms :: Filtering A Query Using A Form

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

Forms :: Closing A Query From A Form

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

Forms :: Field Of A Form In A Query?

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

Forms :: Continuous Form With Query

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

Forms :: Append Query For Form

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

Forms :: Sub-query Not Updating On Form

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

Forms :: Form Filtered By A Query

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

Forms :: Toggle On Form To Query Between Two Criteria?

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

Forms :: Display Data From Query Into Form

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

Forms :: Query Linked Form Won't Allow To Add Text Box

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

Forms :: Query / Report Wizard On Nav Form?

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

Forms :: Opening New Form For Query Output?

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

Forms :: Display Query Data In A Form

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

Forms :: Using A Variable From A Form To A Query As A Criterion?

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

Forms :: Refresh Form After Update Query

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







Copyrights 2005-15 www.BigResource.com, All rights reserved