Question Regarding Functions in Queries

[ItsRoland]

I've got a form based off a query. For simplicity's sake, lets call the form "Form1" and the query "Query1". One of the fields in Query1 is calculated by a function. This is working perfectly.



However..

If I don't scroll through certain records on Form1, it doesn't update the record on the table, i.e. if I open Form1 all the visible records will update, but all the records you need to scroll down to see do not update.

My question is: Is this supposed to happen, or is something not behaving correctly? Personally I think this is working properly, but I talked to someone with more access experience than myself and they seem to think otherwise. I tried searching around but couldn't find anything, could just be I wasn't phrasing my search correctly. Regardless, I just want to confirm if whats happening is normal functionality or not.

Thanks!

[JoeM]

If it is simply a "Select" query and not an "Action" query (like an "Add" or "Update" query), then it doesn't matter, since nothing is really being permanently updated anyway.
A calculated field in a "Select" query is real-time, and simply does the calculation when called. If other records are not being called, then the calculation is not being done to them at that point in time. But since nothing is permanently being altered, it shouldn't matter.

[Minty]

By default access doesn't "process" all the underlying records displayed on a form, it only pulls the first x number of records based around what the form can display.

Your description of it "not updating the table" is a little confusing, a function in a query wouldn't update a value unless you were running an update query.

[ItsRoland]

My apologies for the confusion.

Query1 is a Select Query, I forgot that the function itself is what updates the tables, not the query.

Therefore I imagine I would need to remove the update table functionality from the function and change the query to an update query to achieve the desired results, correct? (i.e. when Form1 is opened, it will update all the records)

Would changing Query1 from a select query to an Update query cause any issues with Form1 (The form that uses it as a RecordSource)?

[Minty]

You can't use an update query as a record source for a form, it doesn't return any records.

Your sequence of events needs to be on form open, run the update query.
The record source for the form then simply will query the table with the updated records.

[davegri]

If you want the function to update a value in the table, as the records are displayed in the form, the function should not be in the query but should be the datasource for the control (textbox) on the form.
OR
the form_current event could call the function:
sub Form_Current()
textbox1 = MyFunction
end sub

[ItsRoland]

Sorry for the delay in response


I was able to get what I wanted by simply creating an update query with the same information as the select query, and using that anytime I need to update the table with information.

Thanks for the help!