Refresh form on any update

I have a form that has displ;ays about 50 fields.  About 12 of those fields, 
when updated, can change the one dlooikup control on the same form.  I can 
code a refresh form for the onUpate event for each field to update the 
dlookup, but iis there a way to do it just once if any field is updated on 
the form. 

I tried using a join instead of the dlookup but then can't update the form 
manually. 

 Minor, I know, but this same type of thing has come up for me elsewhere.
 I am using A2007 with w7.   

Thanks in advance
0
Utf
1/18/2010 5:37:01 AM
access.formscoding 7493 articles. 0 followers. Follow

12 Replies
823 Views

Similar Articles

[PageSpeed] 22

Is there anything in this link that would help you create an updateable query
for your form?
http://allenbrowne.com/ser-61.html

gg wrote:
>I have a form that has displ;ays about 50 fields.  About 12 of those fields, 
>when updated, can change the one dlooikup control on the same form.  I can 
>code a refresh form for the onUpate event for each field to update the 
>dlookup, but iis there a way to do it just once if any field is updated on 
>the form. 
>
>I tried using a join instead of the dlookup but then can't update the form 
>manually. 
>
> Minor, I know, but this same type of thing has come up for me elsewhere.
> I am using A2007 with w7.   
>
>Thanks in advance

-- 
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201001/1

0
ruralguy
1/18/2010 3:04:33 PM
gg wrote:

>I have a form that has displ;ays about 50 fields.  About 12 of those fields, 
>when updated, can change the one dlooikup control on the same form.  I can 
>code a refresh form for the onUpate event for each field to update the 
>dlookup, but iis there a way to do it just once if any field is updated on 
>the form. 
>
>I tried using a join instead of the dlookup but then can't update the form 
>manually. 
>
> Minor, I know, but this same type of thing has come up for me elsewhere.
> I am using A2007 with w7.   


If the DLookup text box expression references the 12 bound
fields, it should recalculate auromatically.  If you are
doing somthing else, please provide details.

-- 
Marsh
MVP [MS Access]
0
Marshall
1/18/2010 5:37:18 PM
The list on the recommended site did it-I modified two tables to have so that 
I could  link indexed fields and can now update the query.  Thanks for quick 
and accurate reply, much appreciated.

"gg" wrote:

> I have a form that has displ;ays about 50 fields.  About 12 of those fields, 
> when updated, can change the one dlooikup control on the same form.  I can 
> code a refresh form for the onUpate event for each field to update the 
> dlookup, but iis there a way to do it just once if any field is updated on 
> the form. 
> 
> I tried using a join instead of the dlookup but then can't update the form 
> manually. 
> 
>  Minor, I know, but this same type of thing has come up for me elsewhere.
>  I am using A2007 with w7.   
> 
> Thanks in advance
0
Utf
1/18/2010 7:47:01 PM
Glad we could help.

gg wrote:
>The list on the recommended site did it-I modified two tables to have so that 
>I could  link indexed fields and can now update the query.  Thanks for quick 
>and accurate reply, much appreciated.
>
>> I have a form that has displ;ays about 50 fields.  About 12 of those fields, 
>> when updated, can change the one dlooikup control on the same form.  I can 
>[quoted text clipped - 9 lines]
>> 
>> Thanks in advance

-- 
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via http://www.accessmonster.com

0
ruralguy
1/18/2010 8:01:18 PM
Thanks for looking into this.

The data source for my form is a query.  The dlookup value is a calculated 
value in another another query.  This other query uses the same table as its  
primary data source but links to other tables to get more parameters needed 
to calculate the dlookup value. I can't join the two queries because the form 
becomes unupdateable.  With help from other reply, I made the "other" query 
updatebable, but am struggling to make the joined query to query updateable. 

From yours and the other reply, I guess that there is no way to do a refresh 
from updating any field on a form.  That is, the code has to be written on  
the update event for the paticular?


"Marshall Barton" wrote:

> gg wrote:
> 
> >I have a form that has displ;ays about 50 fields.  About 12 of those fields, 
> >when updated, can change the one dlooikup control on the same form.  I can 
> >code a refresh form for the onUpate event for each field to update the 
> >dlookup, but iis there a way to do it just once if any field is updated on 
> >the form. 
> >
> >I tried using a join instead of the dlookup but then can't update the form 
> >manually. 
> >
> > Minor, I know, but this same type of thing has come up for me elsewhere.
> > I am using A2007 with w7.   
> 
> 
> If the DLookup text box expression references the 12 bound
> fields, it should recalculate auromatically.  If you are
> doing somthing else, please provide details.
> 
> -- 
> Marsh
> MVP [MS Access]
> .
> 
0
Utf
1/18/2010 8:07:01 PM
gg wrote:
>From yours and the other reply, I guess that there is no way to do a refresh 
>from updating any field on a form.  That is, the code has to be written on  
>the update event for the paticular?


No, there isn't.  The standard way to do that is using:
	Me.Dirty = False		'save record with new value
	Me.[calculated text box].Requery	rerun the calculation
in all the related bound controls.

-- 
Marsh
MVP [MS Access]
0
Marshall
1/18/2010 9:21:53 PM
I like this as it should do what I want.  However, I tried it and it doesn't 
seem to work. A form refresh is still requiered to update the dlookup control 
after i update the bound field.  Someting to do with the way my queries are 
structured?

"Marshall Barton" wrote:

> gg wrote:
> >From yours and the other reply, I guess that there is no way to do a refresh 
> >from updating any field on a form.  That is, the code has to be written on  
> >the update event for the paticular?
> 
> 
> No, there isn't.  The standard way to do that is using:
> 	Me.Dirty = False		'save record with new value
> 	Me.[calculated text box].Requery	rerun the calculation
> in all the related bound controls.
> 
> -- 
> Marsh
> MVP [MS Access]
> .
> 
0
Utf
1/19/2010 1:16:01 AM
On Mon, 18 Jan 2010 17:16:01 -0800, gg <gg@discussions.microsoft.com> wrote:

>Someting to do with the way my queries are 
>structured?

Quite possibly... but we can't see them from here. Care to post the query and
any code that might be relevant?
-- 

             John W. Vinson [MVP]
0
John
1/19/2010 1:39:46 AM
John-The only code I have is what a previous reply recommended which was 
me.dirty=false then dlookupControl.requery.  Activated from the onDirty event 
of the form.

How do I post a query on this site?
Basically the structure is this:
Table1 is the main source table for two queries.  

Query1 sources Table1 linked to several tables one_many. Certain calcs are 
made and displayed on form1.  query1 is manually updateable

Query1 is the source for form1

Query2 also uses Table1 as the main table with links to several other tables 
one-many. It is now updateable manually. A calculated value in query2 needs 
to be displayed on form1.  

If I join the two queries using the primary field in table1 as a join, the 
new query is not updateable.  

I tried to make just one big query instead of two, but becomes updateable 
and really don't want to do it anyway, just way too many things to keep 
straight.

So, I show the value of interest on  form1 by  using dlookup of the control 
in query2. I would like it to auto update the dlookup  whenever any field is 
changed on form1.  Right now, I include a refresh button on the form.

I admit that by now, I could have installed an on update refresh command for 
every unbound field on the form, but hate mysteries.  Thanks for interest.




"John W. Vinson" wrote:

> On Mon, 18 Jan 2010 17:16:01 -0800, gg <gg@discussions.microsoft.com> wrote:
> 
> >Someting to do with the way my queries are 
> >structured?
> 
> Quite possibly... but we can't see them from here. Care to post the query and
> any code that might be relevant?
> -- 
> 
>              John W. Vinson [MVP]
> .
> 
0
Utf
1/19/2010 8:21:01 PM
On Tue, 19 Jan 2010 12:21:01 -0800, gg <gg@discussions.microsoft.com> wrote:

>How do I post a query on this site?

Open the query in design view. Select View... SQL from the menu, or use the
leftmost tool in the toolbar as a dropdown to get into SQL view. 

Copy and paste the SQL text to a message here. It may look cryptic but trust
me, there's folks here who can read it easily... and it would be an
interesting learning exercise to do so yourself!
-- 

             John W. Vinson [MVP]
0
John
1/19/2010 8:38:07 PM
OK
The qry for the form source is 
SELECT TblBoatData.IDboat, TblPersonalData.LNAME, TblBoatData.BOAT_NAME, 
TblBoatData.LOA
FROM TblBoatData LEFT JOIN TblPersonalData ON TblBoatData.Id = 
TblPersonalData.ID;

The query that creates the dlookup field is:
SELECT TblBoatData.IDboat, TblBoatData.BOAT_NAME, [loa]*[profilef]*10 AS 
Tester, TblBoatData.LOA, T_DW_ProfileFactor.profilef
FROM (TblBoatData LEFT JOIN T_DW_BallastFactor ON TblBoatData.BALAST_MOV = 
T_DW_BallastFactor.IDballast) LEFT JOIN T_DW_ProfileFactor ON 
TblBoatData.Profile = T_DW_ProfileFactor.IDprofile;

These are highly reduced versions of the real queries, but structure is 
identical and  they act exactly the same way as the bigger real version.  
Both are updateable, but a new query based on the two above, joined on 
IDboat, is not updateable
Again, I appreciate your interest very much.
George

"John W. Vinson" wrote:

> On Tue, 19 Jan 2010 12:21:01 -0800, gg <gg@discussions.microsoft.com> wrote:
> 
> >How do I post a query on this site?
> 
> Open the query in design view. Select View... SQL from the menu, or use the
> leftmost tool in the toolbar as a dropdown to get into SQL view. 
> 
> Copy and paste the SQL text to a message here. It may look cryptic but trust
> me, there's folks here who can read it easily... and it would be an
> interesting learning exercise to do so yourself!
> -- 
> 
>              John W. Vinson [MVP]
> .
> 
0
Utf
1/20/2010 6:07:01 AM
John-I posted in a separate reply, so maybe didn't get to you.  Here are the 
queries:

The qry for the form source is 
SELECT TblBoatData.IDboat, TblPersonalData.LNAME, TblBoatData.BOAT_NAME, 
TblBoatData.LOA 
FROM TblBoatData LEFT JOIN TblPersonalData ON TblBoatData.Id = 
TblPersonalData.ID; 

The query that creates the dlookup field ("tester") is: 
SELECT TblBoatData.IDboat, TblBoatData.BOAT_NAME, [loa]*[profilef]*10 AS 
Tester, TblBoatData.LOA, T_DW_ProfileFactor.profilef 
FROM (TblBoatData LEFT JOIN T_DW_BallastFactor ON TblBoatData.BALAST_MOV = 
T_DW_BallastFactor.IDballast) LEFT JOIN T_DW_ProfileFactor ON 
TblBoatData.Profile = T_DW_ProfileFactor.IDprofile; 

These are highly reduced versions of the real queries, but structure is 
identical and they act exactly the same way as the bigger real version. 
Both are updateable, but a new query based on the two above, joined on 
IDboat, is not updateable.
Again, I appreciate your interest very much. 
George 

"John W. Vinson" wrote:

> On Tue, 19 Jan 2010 12:21:01 -0800, gg <gg@discussions.microsoft.com> wrote:
> 
> >How do I post a query on this site?
> 
> Open the query in design view. Select View... SQL from the menu, or use the
> leftmost tool in the toolbar as a dropdown to get into SQL view. 
> 
> Copy and paste the SQL text to a message here. It may look cryptic but trust
> me, there's folks here who can read it easily... and it would be an
> interesting learning exercise to do so yourself!
> -- 
> 
>              John W. Vinson [MVP]
> .
> 
0
Utf
1/23/2010 1:53:01 AM
Reply:

Similar Artilces:

Sending Information with Outlook 2003 Custom Forms
I have created a custom message form that is used to transfer information between two parties. I have used a combination of controls (text boxes, check boxes, option buttons, combo boxes) for page 2 of the message that will be used to enter all the information to be transfered. Ideally what would happen is that Party A would partially complete the form and send it to Party B. Party B would then complete the necessary information and return the form to Party A. When information is entered, the form is sent, and the receiver opens the message, all of the controls are blank. There is no...

Best way to update links
When we open a file, it will ask us if we want to update links and it will ask "yes or no"? If you answer yes, it will say "not enough memory" u"nable to update external links". It does appear to be updating the information. Should I have any concerns as a result of the warning? Is this a result of a slow PC or the network? Would the better approach be to answer "no" and update the links manually? Thanks for your help in advance! ...

how do i refresh or sync all?
I am trying to force my mail to refresh, or to look for new mail much like Outlook Express does when you click on "Sync All". Maybe I shouldn't be concerned with this. Does Outlook deliver mail as soon as it is processed by the server or is it sent at preset intervals set by the administrator? Les perhaps you mean Send/Receive ? Still learning wrote: > I am trying to force my mail to refresh, or to look for new mail much like > Outlook Express does when you click on "Sync All". Maybe I shouldn't be > concerned with this. Does Outlook de...

Opening the form
Hi Guys, I am created a consult form, is linked to a query, but when the form is open it shows a data already, I would like to have the form empty no data at all and when the use call the record, shows it, another thing is that I want to create a cancel command button and what I want to do is clear all data from the form. I will appreciate any help high quality Soccer jerseys NBA Jersey tracksuit and jackets, GHD hairstraightener supplier from www.willpa.com Are you a Retail businessman who bother by the purchase price? China Cheapest TOP wholesale website can help yo...

Need to close mulitple forms
Hello, I need help writing code to close two forms at the click of a button. I have the below code but it keeps erroring out. I want to close a form called "frm_AddtlToolsCasesUIBAll" and the current form that I am on/the form that has the button to close both forms. How should I rewrite this? Private Sub Command87_Click() Dim frm_theOtherForm As Form Set frm_theOtherForm = Forms![frm_AddtlToolsCasesUIBAll].Form frm_theOtherForm.Close Set frm_theOtherForm = Nothing DoCmd.Close End Sub Your help is greatly appreciated. Thanks. Iram Setting a refer...

Refresh some not Refresh All
Hello. I have a workbook with 10 sheets all of which have external data that needs to be refreshed periodically. Most of the time I only need to refresh 8 of the sheets. Is there a way to refresh just those 8 without having to refresh each one separately? Thanks in advance. You can use a macro to do it. It would go something like this... Sub RefreshSome() Dim wks As Worksheet Dim qt As QueryTable For Each wks In Worksheets Select Case wks.Name Case "Sheet1", "sheet2" Case Else For Each qt In wks.Que...

Ive added new fields to my transaction form and cant delete them
I've forgotten how I added them, any ideas? In microsoft.public.money, fpdave wrote: >I've forgotten how I added them, any ideas? Delete the Classification. What program are you using? That did it, much apreciated. I did searches for field, column, etc. On Fri, 22 Oct 2004 18:07:19 GMT, via_newsgroup@please.tnx(Cal Learner-- MVP) wrote: >In microsoft.public.money, fpdave wrote: > >>I've forgotten how I added them, any ideas? > >Delete the Classification. What program are you using? ...

sending an email form.
Hi There I'm not sure if this is possible. When I complete a piece of work I want to send a standard email asking people to mark the work out of a given criteria. I want to make this as easy as possible for people to do. I was wondering how easy it would be to create a form of some kind that would enable the recipient to just fill in a few boxes and hit a button to return it. Any help with this would be greatly appreciated. Thanks in advance Jamie ...

Forms / Threading / Timers
Hi all I have some trouble with Threading in Forms application. - Standard System.Windows.Forms.Form - OnShown I create a new thread - in this new thread I create a new System.Timers.Timer - On Timer.Elapsed I do some SQL database actions There are no form invoke within the thread/timer and also the form does nothing but always when Timer.Elapsed the form is blocked ... How can this be done ?? Do I have to create a AppDomain get real MultiThreading ?? Thanks and best regards Frank Uray Frank Uray wrote: > Hi all > > I have some trouble with Threading in For...

GALMod32.exe does not update
Dear Friends, Recently i provided my users with GALMod32.exe to update their personal details on the GAL. What noticed later was that some users were able to update all the available fields while for some users all the fields did not get synchronised with Exchange GAL. and for few users not a single field got update in their user properties. Does anyone have any idea aboout this strange behaviour of exchange.I let my users update the gal twice to thrice time but no luck. Really looking forward to anyone's help. Regards BurhanM Does your Exchange Org consist of more than one s...

not updating quotes for ticker QQQQ?
For the past six weeks, at least, using Money 2004 I have to manually go in each day and add the closing price for the NASDAQ 100 Trust (symbol = QQQQ). Is there a reason the program won't pick up the daily quote on this holding? The bad pricing gives me an erroneous portfolio value each day. Not a good thing. hwyhiker The Woodlands, TX In microsoft.public.money, hwyhiker wrote: >For the past six weeks, at least, using Money 2004 I have to manually go in >each day and add the closing price for the NASDAQ 100 Trust (symbol = QQQQ). >Is there a reason the program won't ...

Survey form for Outlook?
I'm part if the IT support for the Hospice I work at. We are thinking of doing a survey on "How are we doing?". I'm thinking it would be neat if we could make it an Outlook (we are running Outlook 202 and Exchange 5.5) form that the user could respond, fill in and send back. Any thoughts on any existing forms/programs that would let us do that? -- Regards, Hank Arnold ...

Refresh 1 subform from another
I am trying to refresh a subform from another subform and I cannot seem to make it work. I have a button on subform 2 that needs to have subform1 refresh. I used Me.subform1.mainform.form.refresh and I get an error that says Method or Data Member not found. Anyone know what I did wrong? I am thinking I should use ! instead of . but I don't know what the difference is between them. Any help? -- Thanks As Always Rip Ripper wrote: >I am trying to refresh a subform from another subform and I cannot seem to >make it work. I have a button on subform 2 that needs to have su...

Display Count of records from subform on Main form
I need to show a count of records for doc on main form. Doc info is on main form and list of pts is on subform. I have entered an unbound text box on the main form and have tried DCount, ABSSum and Count all I get is 0. This is what I have entered: =DCount([ID]) or ABS(Sum([myfrom].[ID]) and =Count([ID]). Thank you in advance for any help you can provide. Thank you. Try it this way: Create a query with the needed table. Add the id-field to the grid. Total it by Count. Save the query and on the form you place an unbound field. In VBA you can do a Dlookup for the one field in the q...

VWD2008 will not refresh HTML file
I am trying to use the showmodaldialog function to display help files for my website. But when I make modifications to the HTML file, a previous version of that file is displayed from within the VWD environment, even if I just want to debug that file. I have restarted VWD, but the old version is still displayed. When I double-click that same file outside the VWD environment, the modified version is displayed, so I know the changes are being saved. I have searched the entire hard drive and there is no other file with the same name. ...

Update Rollup 3 won't install properly
Hi, After running lots of updates on SBS 2008 server, I finally was offered the Update Rollup 3 to alleviate the problem of my Win7 box not being able to connect. However, I am getting an error when trying to install the patch. After about 40%, the install fails with Code 6BA. Also, the "New Updates are available" baloon pops up in the system tray, offering to install it again. I've tried restarting the server, but can't seem to install this update. Any suggestions would be appreciated. Geordie GeordieB wrote: > Hi, > > After running lots of u...

Cells WON'T stop refreshing automatically
I have a workbook with many worksheets and links that I need to publish externally with links locked so that users won't get the "The workbook you opened contains automatic links..." message. I have gone into <Tools> <Options> <Calculation>, unchecked the <Update Remote References> checkbox, and saved the workbook; however, each time I reopen the saved workbook, the <Update Remote References> checkbox has been rechecked by some mischevious gremlin, and I invariably get the "The workbook you opened contains automatic links..." mess...

Refreshing
Hi !! I ve done a OCX to view certain type of images. The OCX has a dialog in it with buttons. This OCX has been loaded into a html page. When multiple OCX are being placed in the html page I find the images that are displayed flickers. I use invalidate to refresh but refreshing is very fast. What should I do now to make smooth refreshing. Thanx Sudharsan Double buffer the image. Create a memory bitmap, copy to it, the BitBlt to your OCX's HDC. This may increase CPU usage a little. "Sudharsan" <Sudharsan@discussions.microsoft.com> wrote in message news:A72BC9BB-...

Popup form does not appear for some users
Access 2003: I have a popup form (Popup=Yes, Modal=No) that appears when I open it on my PC, but does not appear when another user opens it on her PC. I have connected remotely to her PC, can open the form in Design mode, but when I switch to Form View, I can no longer see it. I have checked IE popup blocker, but ironically my popup blocker is on, and her popup blocker is off. Any thoughts? ...

Floating form/window?
Hello all. Is there a way to have a "floating" form on an Excel spreadsheet? What I mean is to have a form (with controls) that always is available no matter what worksheet the user clicks on. For my particular project, there will be about 50 worksheets in this one project. What I want is to have a form (or window) that is always available so that the user can add a new member whenever they wish. I thought about using a command button, but then I don't know how to make that button appear on each worksheet. The new member would always be added to the active worksheet (or th...

Clipart Woe -- how to refresh location
How can I refresh the path to the clipart gallery for the clipart files? I have moved the clipart from a network location to the local harddrive and rebuild the artgal50.mmc file, but the gallery still looks to the network location?? Help is greatly appreciated, because searching the MS resources is drivining me nuts Nigel Nigel, they are coming to take you away, take you away. If you hear a knock on the front door, do not open it as it may be the fine gentlemen with a while jacket with straps on. Nigel, you are driving us nuts not telling us what version of Windows and Publisher you...

User form
Is there any way to modify the User Form? I want to add a field to the form that is already in the schema...thx The System User form is not editable. There is a way to make it customizable, that is to change the customizable column in the entity table of the _metabase database. This will allow you to publish customizations for this entity. But I would strongly advise not to use this, since it can break your entire CRM system. Also you will loose support from Microsoft. Rob Bakkers, Avanade Netherlands "mark@markhembree.com" wrote: > Is there any way to modify the User F...

Web Query Refresh problem
Hi I've set up a workbook that contains about 75 sheets. Each sheet contains a web query. I then have a summary sheet which summarises the actual part of information that I want from each web page. Everything works fine, most of the time. I've set up a little button to 'refresh all data'. However, sometimes the refresh doesnt seem to work on some of the sheets. It actually pulls data from a website which I've set up on a different sheet. If, however, you go to that individual sheet, and manually refresh that sheet, it pulls through the correct data from the web. Som...

forms...when i click "Edit read page"
i'm trying to work on some forms in outlook 2003 and it seems to hang up a lot. specially when i click on edit read page it hangs up and i have to close out outlook and reopen it. i have Sp1 installed....can anyone help please...Thanx ...

AJAX UpdatePanel does not refresh after calling Update()-Method
Hi, I'm new to AJAX and has a little Problem to get an UpdatePanel inside an CustomServerControl to work. My goal is to create an CustomServerControl with an (at designtime) unknown number of UpdatePanels, that can be updated seperatly. Since I'm stuck I create a litte more static testproject to analyse the problem but I ran out of ideas what I could been missing. I would be realy glad if someone can show me hwo to get the UpdatePanels working. I created an ServerControl-Project called ServerControlTest and an ASP WebApplication called AXAJTest The Default.asxp look...