Refresh form on any update

[gg]

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 via AccessMonster.com]

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

 

[Marshall Barton]

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.

 

[gg]

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.

 

[ruralguy via AccessMonster.com]

Glad we could help.

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

 

[gg]

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]

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.

 

[gg]

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?

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.

 

[John W. Vinson]

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?

 

[gg]

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]

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!

 

[gg]

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

 

[gg]

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