Automatically Update Value In The Subform From Crossstab Query

Oct 10, 2006

Hello,

I have a main form which has 2 subforms. The main form is the name of the Student. The first subform is the data entry form. It has 2 fields. One for the Trimester - a drop down which has "1, 2, 3" listed. And the second field is the benchmarks (again drop down). Now the second subform is a more elaborate way of viewing (only for viewing purposes) which standards have been chosen for which trimester. Now this form's control source is a Crossstab query, which updates the Trimester field for each of the benchmarks for the particular student.

Now my problem is I am trying to update this subform which is for viewing purposes only, automatically, so that the moment a benchmark is chosen on the other subform, the [trimester] field automatcially gets updated on this form. All the benchmarks are listed on this form (view form) and a field which is the trimester field. Data entry is not allowed in this form. So the information has to get updated automatically. It does when I close and open the form, but thats not how it should work.

I tried everything I thought possible (requery, refresh, a button to click) but couldn't get the form to update.

Please help! I would really appreciate if you could help me find a solution.

Thanks in advance

A

View Replies


ADVERTISEMENT

Modules & VBA :: Subform Event To Update Automatically A Field In Form

Mar 18, 2015

I have a form and a subform

Which method should I use if I need to update automaticaly a field in form based on an event in subform ?

Just to be more clear, have a look in the below example.

In first print screen, student "Johnson Nick" has not completed all tests in TestA subform, the form field "DateCompleteA" is blanc.

Once a student completes all tests in Period A, I need the form field DateCompleteA to show the date that he completed the last one. (18/3/2015).

If student has not yet completed all tests in TestA subform the Form field DateCompleteA must be blanc.

Student has not completed "literature"yet, DateCompletA field is blanc

Student completed ALL his test, subform is updated, Form field DateCompletA is updated with the maximum date.

View 3 Replies View Related

Query To Automatically Update Report

Oct 26, 2011

I have created a parameter query that uses a combo box form. The people who will be using it when completed do not want to see the results in query form. Is there a way to have the query update a form or report without having to create and reformat each time?

View 13 Replies View Related

Queries :: Possible To Have Query Update Only On Demand And Not Automatically?

Aug 13, 2015

Is it possible to have query update only on demand and not automatically? I have a big database and some are best run overnight.

View 12 Replies View Related

Queries :: Creating Field Names In A Query That Will Automatically Update Each Month

May 10, 2013

I am hoping to create a field name in a query that will change every month. Right now the filed name is qryTechQuintileMonth-7.am_quintile. this designates that the data is for October 2012. There are 6 other fields named similarly for Nov 2012 through April 2013.

Is there a way to name these fields with the proper month-year (mmm-yy) so they automatically update each month?

View 4 Replies View Related

Queries :: Append Query - Automatically Update Tables Depending On Value Of Yes / No Field

Feb 25, 2014

I am giving two tables and I need to create a macro that automatically updates these tables depending on the value of a Yes/No field. If it's No, it's in the 1st table TableOne, if it's Yes it automatically updates to TableTwo.

So, the best way I saw to go about is to set up an append query and then create a macro that runs it

So my tables have the values FirstName, LastName and isValid (more but keeping it short)

So for my append query, I put TableTwo in the pop up I get. Then, where it asks for the field I put it

Field:FirstName
Table:TableOne
AppendTo:[TableTwo].[FirstName]
Criteria:[TableOne].[isValid] = 1

I do this for all (it was autocompleted except the Criteria field). I tried to keep Criteria with data only for isValid but that didn't work. I wrote it for all the field names, still didn't work. Whenever I click run it says it'll append 0 rows.

View 5 Replies View Related

Forms :: Automatically Updating Fields In A Subform From Another Subform

Feb 11, 2014

I am having trouble figuring out the method to automatically update some fields in SubForm from 2 other SubForms.I have attached 2 pics, the first GradeEntry1 shows what the tblTopic_Class_Grade form looks like after I manually enter everything into it. GradeEntry2 is what the form looks like when I fill out the Form starting at the top.

I'd like the tblTopics_Class_Grade form auto-populate the TrainingClassID (it currently does this), TopicClassID, StudentID, TrainingTopicID based off the entry from the above forms.My end goal is that I need to have a grade for each student on each training topic for each class. Like:

Class1-Student1-Topic1-GradeX
Class1-Student1-Topic2-GradeX
Class1-Student2-Topic1-GradeX
Class1-Student2-Topic1-GradeX

View 6 Replies View Related

Query In Subform Wants To Update

Oct 26, 2006

After a scary recovery I have a new problem: Intially I changed the 'Data Entry' property on a subform, in a multi-tab form :eek: My tabs all dissappeared. (My Bad!:o )
Now that I have recovered I get a message when I close the form, asking do I want to save the changes to the design of the query.
This only seems to happen when I have read a second or third etc record that displays a different set of records in the subform.
This is a HR mdb with leave records in the subform.
Any help would be appreciated.
PS I thought I had posted this 2 hrs earlier, but can't seem to find it. Hoping this isn't a second posting ...:confused:

.... added an image of error

View 1 Replies View Related

How Do I Update A Subform Based On A Query ?

Sep 14, 2006

I would like the subform in the lower left corner to be blank when
I open the program. It is populated from my tblClients table.
How it works in general is you select "x" number of items from the
list box...hit the search button and the results go through a query
and then show up in the subform.
everything works ok but, like I stated I would like the subform to
be blank when the program is first started up.
It seems to be retaining information from the last start up in the
subform box.

Thanks for your help...it's coming along great so far...
see attached doc...

View 4 Replies View Related

How Do I Update A Subform Fields From A Single Query?

Feb 20, 2005

I have a subform with two fields whose record source is a Query ("subquery")

Code in the query is as follows:

"SELECT Sum(capital) AS cq1
FROM capital_act WHERE (((capital_act.month)='jan' Or (capital_act.month)='feb' Or (capital_act.month)='mar') And ((capital_act.projectid)=forms!frmplan!projectid)) ;"


This query updates the field cq1 in my subform. I want to add 3 other fields for the 2nd, 3rd and 4th quarters. For instance the second quarter field cq2 field would have the capital_act.month as 'apr' or 'may' or 'jun' and so on...


cq1 value gets updated fine. I dont know how to add the fields cq2, cq3, and cq4 on the same subform whose record source would be the same query ("subquery").

I added a second SQL statement in the same query ("subquery") for cq2 but access does not let me add it.

Can somone point me in the right direction please?

Thanks.

View 11 Replies View Related

Queries :: Unable To Update Subform After Re-query?

Jun 28, 2013

I have a form that displays an updatable subform. The main form has search fields and a search button that when clicked will reset the record source of the subform and then do a refresh. The problem is that after the search the subform stops becomes locked. Here is the code:-

Me!sbfDepartmentSub.Form.RecordSource = MySQL
Me.Refresh

I have tried Me.Requery also.

View 10 Replies View Related

General :: Trying To Get Event To Update Subform / Query With Vba

Mar 28, 2014

I have a query in a subform on the main form. I have a search box that updates the subform/query as you type something (using the On Change event). You then click on the record you want which transfers the information to the appropriate text boxes (one of these txt boxes is the clientID I talk about below) located next to the search box.

I have a Contacts subform/query much like the serarch box I created and I am using a txt box (on the main frm) clientID (which I get from the above process) to filter the contacts.Now when I pass the ID to the txt box on the main form I am having trouble getting a event to trigger and update the subform/query correctly.

I am using VBA to create a simulated Click action which seems to work but is not updating the Contact subform/query, it is just resetting the subform/query. If I manually click on the txtbox with a ID in there all works wonderfully. I have attached the database. I made the clientID and btn next to it visible(this would not be visible normally).I just realised I left a button on the main form next to the clientID txt box just ignore that and click on a client then the clientID txt box to see how it updates the contacts subform..

View 5 Replies View Related

Forms :: Update Query From Subform On Navigation Form

Jun 17, 2013

I have a navigation form, a navigation subform, and then a subform. When I click the save button on the navigation subform, i need the data entered in two fields on the subform to save in a table. the subform is based on a query. This is what I currently have on the the button's onClick event:

Code:
Update data_tbl
Set [data_tbl].[Approved] = [Forms]![Navigationfrm]![Navigation subform].Form![Entry_subfrm].[Form]![CboAppd]
[data_tbl].[Comments] = [Forms]![Navigationfrm]![Navigation subform].Form![Entry_subfrm].[Form]![txtComments]
WHERE [data_tbl].[ID] = [Forms]![Navigationfrm]![Navigation subform].Form![Entry_subfrm].[Form]![ID]

View 3 Replies View Related

Update Subform With Query Results In Main Form

Jul 17, 2012

I created a main form containing list boxes and a subform. What I want to do is to select items in the list boxes. Then, by clicking "Search" button, the data filtered by the selected items will be shown in the subform. But the subform cannot work and show the query results. I think I miss some VBA codes.

Complaint Database_William.zip

View 1 Replies View Related

Automatically Update Field

Sep 12, 2006

I am currently handling an insurance operation. I have 5 Sales Executives (SE) who receives certain percentage of commission for each sold insurance policy. SE receives their respective commission on a pro-rata basis. Meaning, if they give 4 equal monthly payment scheme to their clients, they will also receive their commission -- 4 times.

Example:
SE Commission for one sold policy is: 100.00. (Granted SE gives 4-month-term, SE will be receiving 25.00 monthly, upon cleared payment)

On my 2 tables lies the following fields:
[Table1]
SECom1
SECom2
SECom3
SECom4

[Table2]
CustPayment1
CustPayment2
CustPayment3
CustPayment4

Is it possible to automatically update Table1.SECom2 based on the figure on Table1.SECom1, once Table2.CustPayment2 is updated?

Thank you!

Sheila

View 2 Replies View Related

Update Contact Details Automatically

Nov 20, 2007

Dear all, please excuse the fact that this is not an explicit problem I have, but more of a general query.

I have an Access database (2003) with a load of contact details in it. Including various fields for each contact, such as their email, services they offer, where they are, main contact name etc.

Some of these contact details will be out of date and I want to make sure that all the contact details are up-to-date.

I am in the process of setting up a mail merge with a Word document which will output to Word their relevant details, and this can then be e-mailed to each of the contacts with them to update and e-mail back again. Someone would then manually update each of the contact details.

This is quite a time intensive way of doing it, and I wondered is there a way of automating the process? I don't have the time to put the whole of their details on a website so they can update it over the web, but is there a way that access could possibly read the return Word document to automate updating their details? Or is there another easier way of doing it, without using word and using something else such as Excel or something.

If anyone has any experience in doing this kind of thing I would be really glad of some tips.

Many thanks

View 9 Replies View Related

Automatically Update To Today's Date

Oct 24, 2006

Hi

I have a table which has a date field in it, where the default value is Now().

How do I get this field to automatically update in the table to "today's date"?

Thanks

Maria

View 12 Replies View Related

Update Automatically A List Control

Jan 5, 2005

hello all
i have a problem, i have a form bounded to a query
that displays the books infos, in this form i have a list control
that displays the list of authos based on the code of the book
the problem is when i have more than one record in the opened
form ie more than one book and i move to the second record
the list doesnt change and displays the authors of the first book
when the form first opened
anyone has an idea how can the list be updated automatically when
i move between records
thanks a lot

View 6 Replies View Related

Forms :: Automatically Update Unbound Box

Jul 28, 2014

why my vba code wouldn't work.I have a scorecard form, and what I wanted to do on a form was that if a user enters a number in a bound box (to a table), it would automatically update an unbound box. So I coded it as this.

Private Sub SupplierCorrectiveActionRequests_Enter()
If [Request] = 0 Then [Text30] = 100
Elseif [Request] = 1 Then [Text30] = 75
Elseif [Request] = 2 Then [Text30] = 50
Elseif [Request] = 3 Then [Text30] = 25
Elseif [Request] >= 4 Then [Text30] = 0
End Sub

But it throws an error that there cannot be Then if there are no ifs, weird.Another problem I had is that I'm using a continuous form, and whenever I scroll down there seems to be a visual bug,is there any way to do that without changing the theme?

View 5 Replies View Related

Modules & VBA :: Date Update Automatically

Mar 19, 2014

In my Access form, users update 4 checkboxs when their work is completed, when this 4 boxed checked then the 5th checkbox will check automatically using afterupdate. Now i have added the 6 the column with date completed, here i want the date to be updated automatically when the 5th column is checked automatically. how to do it?

View 10 Replies View Related

Modules & VBA :: Automatically Update Time

Jan 25, 2015

I have a form fmrClasses and what I'm trying to do is write a code that as soon as the user types a beginning time will update the end time to 3 hours later for example If user enters 9:00AM for beginning time the end time will be set to 12:00 pm.

View 9 Replies View Related

Automatically Update A Field When A Box Is Checked

Jul 22, 2014

I'm fairly new to using Access for any serious purposes. I'm trying to replicate some database functionality I setup in Salesforce and just trying to clear few core concepts in Access.

I'm messing around in the Contact Management template and I'm wondering how I can make it so when so when a yes/no checkbox field (called "Active") is ticked a date/time field (called "Last Date") is automatically set to today's date + 60 days.

In Salesforce I would create a Workflow Action triggered by an if Active = true statement, with a Field Update something like TODAY() + 60.

I can tell I need to create an After Insert/After Update Data Macro but can't quite figure out what to put in.

View 11 Replies View Related

How To Automatically Update A Table Field From A Form?

Feb 19, 2006

hi there
I am having some difficulty with using Access (aren't we all?) ;-(
I have a table with a the following fields: START_DATE, END_DATE, RENTAL_PERIOD, DAILY_COST & TOTAL_COST.

What I am tring to achieve is that when someone fills in the START_DATE, END_DATE & DAILY_COST fields (which update the table behind the form) that the form will take the END from the START and put the result into the RENTAL_PERIOD field and then multiply this by the DAILY_COST value to automatically fill in and display the TOTAL_COST field.

It must be simple but I just can't get it to work and it's stopping me from sleeping :mad: I have found myself doing Access in my dreams which is not good!!

Any tips much appreciated - Thanks
Mike

PS I have written 2 queries which will calculate this for me but dont seem to be able to action the queries from within the form (if that helps!)

View 3 Replies View Related

Update A Date/Time Field Automatically

Oct 24, 2005

Most likely a newb question, but how can i just update the date/time field to the current date, as opposed to constantly entering in the current date?

Thanks

View 1 Replies View Related

Making Controls On Form Update Automatically

Aug 3, 2006

I have a couple of drop down menus on a form i have designed which have values which are dependent on another drop down.

So, for example, when i select USA in country, the US$, appears in my currency box but i have to select US$ from the drop down when i want it to appear automatically.

Any ideas on how to do this?

Cheers

Also, i have a calcualtion attached to a text box:

[Text87] = (1 / [List93]) * [Combo85]

However, the calculation will not be performed, even on click, until [List93] has been clicked on. [List93] is a constant value and is a hidden field so how can i make access perform the calculation without clickin [List93]

Many thanks in advance

Cheers again

View 2 Replies View Related

Modules & VBA :: Update Range For Graph Automatically

Mar 17, 2015

I am trying to update the range for the graph automatically and I'm calling this from Access because my data exists in Access. I've attached a sample file.what I am trying to do:

1) click on the button
2) button will update the usedrange in "trending" tab
3) have the graph's data range based on the usedrange.

Code:

Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
xlApp.Workbooks.Open "C:Documents and SettingsgjiaDesktopmetric_DEV.xlsx", True, False

[code]...

note: the columns will grow bigger to the right, which is dynamic, so I've used usedrange method, but the code fails on the usedrange line saying the object or method is not being supported...

View 3 Replies View Related







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