How to return the result of my query inside the table?

[aat]

Hi all,



So I finally managed to create a query that returns the rank of my record in the table based on a number.

Now, when I update my record using a form, when I update that number in particular, I would like an afterupdate event that returns in a field "rank" the result of that query based on the new number.

How can I do that?

Thank you,

Aat

[pdebaets]

It sounds like you need to base your form on query that includes all your table fields PLUS your calculated "rank" field. Then you just need to have a text box control on your form for the calculated "rank" field and it will be updated automatically when you update any data used in the calculation of that field.

[aat]

Hi Peter
Thanks for your reply
You are a little too fast for me. How do I based the form on the query?
I started by selecting, in the properties of my "rank" field, the source as being the calculated rank field in the query, but then on the form it returns #name?
Help!
Thank you
Aat

[pdebaets]

First, create a query that contains all the table fields plus your calculated field. The query SQL will look something like this:

Select MyTable.*, <some calculation goes here> as Rank from MyTable;

Then in form design view, set the form recordsource property to the name of your query.

[aat]

Hi, thanks for that.

So that means all the fields in my form will be generated from the query? But when I update the fields, is it input back onto my table?
I've got quite a lot of fields here including many that are populated by afterupdate events of other fields, how is this going to get affected if I put the query you mentioned as the record source of my form?

I appreciate your answer but if there was a way to use my ranking query to "clinically" update one field only without affecting the others, that would be great.

Thanks a lot

Aat

[Aragan]

1. Just create a form based on your table, not in the query
2. Add manually another textbox and set your query as its source
3. Add an afterUpdate event in the number textbox that says Me.RankFieldName.Requery

[aat]

Hi all,
Thanks for your replies.
I do not seem to getting it right.
My form field's data control source is now set at =[myquery]![queryrankcolumn].

The requery statement in the after-update event does not work as the field still shows "#name?" when I update the other field.

How is this supposed to work? Would the form automatically pick up the correct rank number from the query simply by looking up the name of the record and returning the corresponding rank number? Do I need to build relationships between my query and my form or my table?

BY the way, the ranking is based on a number that is itself updated by an after-update event. Basically this is the chain of events:

Field 1 updated > afterupdate event 1= update field 2 > field 2 is disabled and locked so update happens automatically without manual override possible.

I would like to have:
Field 1 updated >
afterupddate 1 > update field 2
afterupdate 2 > me.myrankfield.requery (which is based on field 2 being updated as well)

But my rank field (field 3) does not update and shows me #name?

Sorry I hope this is not confusing but I seem to be on a wrong slope here.

Thank you

Aat

[Aragan]

So when Field1 is updated then NumberField is updated and this makes RankField gets updated too??

You dont have to set up ur control source... but the data source, i dont know in english, i have access in italian... but is the second option..under the control source XD

Sou u 1st ave to create ur form linked to the table u r using, after that u set the data source of NumberField with the 1st update query, something like "SELECT Field1 some calculation blablablabla", then u create a new textbox and set its data source with the 2nd update query that will be something like " SELECT NumberField some calculation blablablabla", after that u set the AfterUpdate Event of Field1 as "Me.NumberField.Requery" and the AfterUpdate Event of NumberField as "Me.RankField.Requery"

[June7]

aat, can you provide the project for analysis? Attach to post. Run Compact & Repair first, zip if still large.

[aat]

Hi All
Thank you for your replies. As indicated I have trouble to input the result of the query inside my form following after-update events. I must get something wrong in the way I link my rank field.
At the invitation of June7 I attach a mock-up version that looks exactly like what I am trying to build. You will see very easily which field on my form does not get updated.
Any help in building that afterupdate event will be greatly appreciated!
Thank you all very much
Aat

[June7]

I tried to run an UPDATE query on table1 using Query1 Leverage RK field as source data, but get error message the 'operation must use an updateable query'. The subquery that performs the ranking makes the query (and any query in the sequence) not updateable.

Why would you save this rank value to a table? It is dynamic as rank changes with each new record. The calculation can be done as needed in query and displayed in report. Why do you need it on form?

If you really want on form, can do a DLookup in the form's RecordSource and then bind textbox to this constructed field.

Rank: DLookUp("[Leverage RK]","Query1","ID=" & [ID])

However, adding a new record requires refreshing the form and the refresh action sets focus on first record.

[aat]

Hi June7,
The form is in itself a great tool for my homework and each time I update a record, having a look at the relative performance of the record by way of updating its ranking on a limited number of fields gives indeed a dynamic and very useful analysis tool while I am updating the record. Running a separate report in comparison would be arduous and not very convenient.
I tried to link the rank as you said, if you can see in the attachment, but I still get an error message. How is the field supposed to update appropriately?
Thank you very much for your help so far.
Aat

[June7]

The DLookup criteria is on the ID fields. Need ID field in Query1.

[aat]

Hi June7

That's great. it works now!

Final one... the rk doesnt update in the table. Any chance i can have the info on the form reflected back into the table as well?

Thanks so much for the help so far.

Antoine

[June7]

I already stated that attempt to save the calculated rank to table fails. It is generated in the query and therefore, use the query as recordsource for form and report.

If you edit or add a record on the form, must requery the form to display the revised ranking. Can requery by clicking the Refresh button on ribbon or with VBA in some event of the form. The requery action will set focus to the first record on form.