Solved Storing the value of a Function (1 Viewer)

[ProgramRasta]

Hi All,

I am calling upon your expertise once more.

I have what may be a very quick question to answer.

I have a main form where the record source is run from a query - within this query there is a function that performs some calculations (the actual calculations are not bound to any field in a table).

When I filter by the calculations on the main form, the filter takes a considerable amount of time to filter the record.

My question is - is it possible to store these values on the main form and open a new form with these values (so that I can filter on these values) without having to do the calculations again?

I have provided some sample data - in the sample data, I would like to store the value of "Total" and create a new continuous form that links back to the main form without having to perform the calculations again.

Many Thanks

 

[arnelgp]

use Expression in your Query:

Total: Nz([number1, 0) + Nz([number2], 0)

 

[ProgramRasta]

use Expression in your Query:

Total: Nz([number1, 0) + Nz([number2], 0)

Thanks for reverting back to me.

This is sample data (the only sample database I had at hand) - the actual function is a lot more complex.

Is it possible to store this data in an array or a record source and then use these in a sub form?

I'm not technical enough to know how (if) it can be done.

Many Thanks

 

[ProgramRasta]

use Expression in your Query:

Total: Nz([number1, 0) + Nz([number2], 0)

I tried this method in the query design and entered a value to filter by it's still taking a considerable amount of time to filter (10 minutes later and the results still haven't returned). as in put Nz(paramater, 0) - not changed it to an expression.

to provide some info on the function - it calculated the difference between a number of dates. this information is then populated on the main form (which is continuous). the main form loads up quickly(ish) but when I try and filter it just takes a crazy amount of time.

Thanks

 

[CJ_London]

If your function contains domain functions it will be extremely slow. Especially for things like filtering or sorting as the entire dataset needs to be processed.

the main form loads up quickly(ish)

Does it in reality? forms will often load with the first few records and continue processing the rest of the recordset in the background. To check, make sure you have the record navigation bar showing and see how long it takes to populate with the number of records.

suggest explain what the function actually does and provide the code. Also some example data and the outcome required.
Have you looked at using the datediff function?
Sometimes functions are necessary, but sql can usually be used and will be the fastest way

 

[ProgramRasta]

If your function contains domain functions it will be extremely slow. Especially for things like filtering or sorting as the entire dataset needs to be processed.

Does it in reality? forms will often load with the first few records and continue processing the rest of the recordset in the background. To check, make sure you have the record navigation bar showing and see how long it takes to populate with the number of records.

suggest explain what the function actually does and provide the code. Also some example data and the outcome required.
Have you looked at using the datediff function?
Sometimes functions are necessary, but sql can usually be used and will be the fastest way

CJ, thank you for the reply.

I have worked around it by applying the function to an unbound text box. I know wish to filter the values either using a filter method or a combobox. I have tried the latter but I am not seeing the unbound textbox as a choice of column. any help would be greatly appreciated.

 

[CJ_London]

the filter works on the form recordset, not the form so you can't filter on an unbound control

 

[ProgramRasta]

thanks for the reply.

 

[CJ_London]

since it appears to be the function that is slowing things down, as suggested before explain what the function actually does and provide the code. Also some example data and the outcome required.

Also, did you check how long it takes to populate the form recordset per post #5?

Only other thing I can suggest if your form is loading quickly is rather than filtering your form, repopulate it using the filter as criteria. e.g. instead of

me.filter="somefield=" & mycontrol
me.filteron=true

try

me.recordsource="SELECT * FROM (" & me.recordsource & ") WHERE somefield=" & mycontrol

note if you are 'filter browsing', you'll need to keep a note of your original recordsource string otherwise you won't be able to undo.

 

[ProgramRasta]

the filter works on the form recordset, not the form so you can't filter on an unbound control

since it appears to be the function that is slowing things down, as suggested before explain what the function actually does and provide the code. Also some example data and the outcome required.

Also, did you check how long it takes to populate the form recordset per post #5?

Only other thing I can suggest if your form is loading quickly is rather than filtering your form, repopulate it using the filter as criteria. e.g. instead of

me.filter="somefield=" & mycontrol
me.filteron=true

try

me.recordsource="SELECT * FROM (" & me.recordsource & ") WHERE somefield=" & mycontrol

note if you are 'filter browsing', you'll need to keep a note of your original recordsource string otherwise you won't be able to undo.

Thanks for the reply once again.

As the record source in the combobox - is it possible to add a function to the SQL statement?

similar to SELECT Function name(parameter1, parameter2) as Name from Sometable where....

These are probably elementary questions but I literally have 20+ hours of Access experience.

Appreciate your time.

 

[Isaac]

One piece of advice. Something I see people doing that, in my opinion, makes it MUCH harder than it needs to be...there was a post here yesterday about dynamic combobox/listbox filtering that involved this when I looked into it. And that is, setting a row- or record-source in Design view, and then trying to dynamically add & remove to/from the literal string (like Me.Listbox.Rowsource=Me.Listbox.Rowsource & " , AND field1="criteria" & ":"), and then wondering why it's inordinately hard to have to indefinitely keep track of what that literal string is ...

The approach I would advise for a Form, Listbox, or Combobox that you already know is going to have some end-user, dynamic filtering activities is this. Create a query (QueryName) that produces the basic recordset with all necessary fields, and use that as the Recordsource or Rowsource in Design view. (This way everything is OK when the form or control first loads). Then in your code that dynamically filters, (let's say the AfterUpdate event of a combobox), code like Me.Recordsource="Select * from QueryName where fieldname=" & Me.Combobox1.Value". Anytime more filtering is done, it's just a fresh bit of code to select * from the starting query where...

Rather than coding to try to figure out what the row/recordsource is at a given moment and then literally adding to that string. Not that it can't be done that way, it's just the harder way to do it.

 

[ProgramRasta]

One piece of advice. Something I see people doing that, in my opinion, makes it MUCH harder than it needs to be...there was a post here yesterday about dynamic combobox/listbox filtering that involved this when I looked into it. And that is, setting a row- or record-source in Design view, and then trying to dynamically add & remove to/from the literal string (like Me.Listbox.Rowsource=Me.Listbox.Rowsource & " , AND field1="criteria" & ":"), and then wondering why it's inordinately hard to have to indefinitely keep track of what that literal string is ...

The approach I would advise for a Form, Listbox, or Combobox that you already know is going to have some end-user, dynamic filtering activities is this. Create a query (QueryName) that produces the basic recordset with all necessary fields, and use that as the Recordsource or Rowsource in Design view. (This way everything is OK when the form or control first loads). Then in your code that dynamically filters, (let's say the AfterUpdate event of a combobox), code like Me.Recordsource="Select * from QueryName where fieldname=" & Me.Combobox1.Value". Anytime more filtering is done, it's just a fresh bit of code to select * from the starting query where...

Rather than coding to try to figure out what the row/recordsource is at a given moment and then literally adding to that string. Not that it can't be done that way, it's just the harder way to do it.

thanks for the reply

 

[ProgramRasta]

One piece of advice. Something I see people doing that, in my opinion, makes it MUCH harder than it needs to be...there was a post here yesterday about dynamic combobox/listbox filtering that involved this when I looked into it. And that is, setting a row- or record-source in Design view, and then trying to dynamically add & remove to/from the literal string (like Me.Listbox.Rowsource=Me.Listbox.Rowsource & " , AND field1="criteria" & ":"), and then wondering why it's inordinately hard to have to indefinitely keep track of what that literal string is ...

The approach I would advise for a Form, Listbox, or Combobox that you already know is going to have some end-user, dynamic filtering activities is this. Create a query (QueryName) that produces the basic recordset with all necessary fields, and use that as the Recordsource or Rowsource in Design view. (This way everything is OK when the form or control first loads). Then in your code that dynamically filters, (let's say the AfterUpdate event of a combobox), code like Me.Recordsource="Select * from QueryName where fieldname=" & Me.Combobox1.Value". Anytime more filtering is done, it's just a fresh bit of code to select * from the starting query where...

Rather than coding to try to figure out what the row/recordsource is at a given moment and then literally adding to that string. Not that it can't be done that way, it's just the harder way to do it.

Is it possible to load up the parameters from the record source and then apply the function to these parameters in the after update event?

So Query A is the record source that contains parameters A and B, so using your example as

Me.RecordSource = "select " & Function(parameterA, ParameterB) & " from QueryA where fieldname=" & Me.Combobox1.Value

 

[CJ_London]

I've really lost track of what you are trying to do - initially it was about poor performance of filtering a form, then it seems to be a combobox and now about the afterupdate event (of what? form? combo? some other field?). So without a clear understanding of what you are trying to do, I don't think I can help.

 

[Isaac]

I am not sure but I think I understand you.
You can set the Recordsource to ANY valid SQL string (or name of a saved query or table).
Thus, yes: You could save a "faster" query (without the function that is slower) --as long as it has the column names that the form or control needs--in Design view. That would be what is utilized when the form first loads (presumably faster). Then in the filtering code, you could code something like:

Me.Recordsource="Select field1,field2,field3,Function([param],[param]) as field3 from queryname"

 

[ProgramRasta]

I am not sure but I think I understand you.
You can set the Recordsource to ANY valid SQL string (or name of a saved query or table).
Thus, yes: You could save a "faster" query (without the function that is slower) --as long as it has the column names that the form or control needs--in Design view. That would be what is utilized when the form first loads (presumably faster). Then in the filtering code, you could code something like:

Me.Recordsource="Select field1,field2,field3,Function([param],[param]) as field3 from queryname"

That is exactly what I'm looking for! Really appreciate your help with this.

Many thanks to all who contributed.