Requery SQL Computed Field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

newsmail - 05 Feb 2007 17:43 GMT

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.

Reply to this Message

Ken Sheridan - 05 Feb 2007 19:13 GMT

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

> I have created a computed field in my SQL table and I'm using MSAccess as my
> front end.
[quoted text clipped - 13 lines]
>
> Thanks.

Reply to this Message

Swordsman - 05 Feb 2007 20:10 GMT

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

>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
[quoted text clipped - 48 lines]
>>
>> Thanks.

Reply to this Message

Mattias Jonsson - 07 Feb 2007 12:42 GMT

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:

> I have created a computed field in my SQL table and I'm using MSAccess as my
> front end.
[quoted text clipped - 13 lines]
>
> Thanks.

Reply to this Message