Requery SQL Computed Field

  • Thread starter Thread starter newsmail
  • Start date Start date
N

newsmail

I have created a computed field in my SQL table and I'm using MSAccess as my
front end.

When I update my form with new values the values change, but not my computed
field in the form

I have used the requery command , this works , but it changes my record
pointer to the 1st record
in the table. I want to form to remain at the current position when I
requery

Don't know If I'm doing this correctly or not or if there is another simpler
way..

Please Help.

Thanks.
 
I assume by 'SQL table' you mean the result table of a query, not a base
table. The values in the underlying base table(s) won't be changed until you
save the form's current record, either explicitly or by moving to another
record or closing the form, so the computed column in the query won't be
updated to reflect the unsaved changes in the controls on the form.
Requerying the form saves the current record of course. You could try saving
the current record with:

RunCommand acCmdSaveRecord

or with:

Me.Dirty = False

If necessary you could then refresh the form with Me.Refresh; this doesn't
move the record pointer to the start of the form's recordset. However, why
not simply have a computed control on the form rather than a computed column
in the query, e.g. if you have columns NetPrice and TaxRate (as a fractional
number) for instance an unbound GrossPrice text box on the form would have a
ControlSource of:

=[NetPrice] * (1 + [TaxRate])

Ken Sheridan
Stafford, England
 
Ken,
Thanks for pointing me in the right direction.

I have recently migrated my Acces tables to SQL Server, so I din't know if
the computer field on the
table level in SQL is the most efficient way to go. Prior to this migration
I've always had the computed field
in the form itself as you've stated

I guess my critiera should be - Make it simple

Thanks


Ken Sheridan said:
I assume by 'SQL table' you mean the result table of a query, not a base
table. The values in the underlying base table(s) won't be changed until
you
save the form's current record, either explicitly or by moving to another
record or closing the form, so the computed column in the query won't be
updated to reflect the unsaved changes in the controls on the form.
Requerying the form saves the current record of course. You could try
saving
the current record with:

RunCommand acCmdSaveRecord

or with:

Me.Dirty = False

If necessary you could then refresh the form with Me.Refresh; this doesn't
move the record pointer to the start of the form's recordset. However,
why
not simply have a computed control on the form rather than a computed
column
in the query, e.g. if you have columns NetPrice and TaxRate (as a
fractional
number) for instance an unbound GrossPrice text box on the form would have
a
ControlSource of:

=[NetPrice] * (1 + [TaxRate])

Ken Sheridan
Stafford, England

newsmail said:
I have created a computed field in my SQL table and I'm using MSAccess as
my
front end.

When I update my form with new values the values change, but not my
computed
field in the form

I have used the requery command , this works , but it changes my record
pointer to the 1st record
in the table. I want to form to remain at the current position when I
requery

Don't know If I'm doing this correctly or not or if there is another
simpler
way..

Please Help.

Thanks.
Click to expand...
Click to expand...
 
Newsmail,
You might do this: Before calling Requery, save the value of the primary
key column into a variable. Then, Requery the form and navigate to the
correct record as if you had used a "search" feature.

Here's a web page with sample code:
http://www.tek-tips.com/faqs.cfm?fid=4398

Good luck,
Mattias

From: newsmail, on 2/5/2007 12:43 PM:
 
Back
Top