sort in tabular form

[maxmangion]

i have a tabular form which sorts the records in descending order based on a field which contains numbers. This works fine! However, as soon as i change the value of a field (which the form is sorted by), the sort does not take effect until i close the form and then re-open it.

Is there a way in which the sort can happens instantly without having to close and re-open the form ?

Thanks!

 

[adam_fleck]

Requery on AfterUpdate?

You could try putting Me.Requery in the AfterUpdate event of the field you're using to sort your form.

In design view, right click on the field and choose Properties from the pop-up menu. The Properties box should appear. Click on the Events tab and look for After Update. Click in the line for After update, click on list button that apppears, and choose [Event Procedure], then click on the [...] button.

You should see a code segment. Change it so it looks like this:

Private Sub Sort_Field_AfterUpdate()

Me.Requery

End Sub

 

[maxmangion]

thx for your suggestion, actually i have already tried your idea and it works out, but in this approach i still encounter a couple of problems:

1) if for example i am editing a record sorted in the 87th place and with the new data it should be sorted in the 75th place, although this works, the focus get lost from the record and it moves always to the first record. ( i would prefer if the focus remains at the current record which i am editing).

2) the field which the form is sorted by is a calculated field based on three fields, therefore, i have to place the above suggested code in the three fields because if i place it in the calculated field, it does not work!

Any ideas on this pls ?

Thank You!

 

[Mike375]

Put a macro (or code) that first SetValues and unbound text box with the record ID you are altering and then sorts on the field and then does FindRecord which has the same ID as the entry in the unbound text box.

I have the macro GoToControl, which will be the ID field and then the FindRecord expression in the macro is

=[Forms]![YourFormName]![UnboundTextBoxName]

Mike

 

[Rich]

Dim rs As String
rs = Me.txtID
DoCmd.RunCommand acCmdSaveRecord
TrDate.SetFocus
DoCmd.RunCommand acCmdSortAscending
txtID.SetFocus
DoCmd.FindRecord rs
TrDate.SetFocus

 

[maxmangion]

Rich,

Your solution works to perfection, however, i would like to ask you if it is possible that it would work with calculated fields as well, because otherwise i do have to paste that code into the three fields which the calculated field is based on!

Thanks for any ideas!

 

[maxmangion]

anyone have a possible workaround for the above to set it to work for calculated fields too pls ?

Thanks!

 

[maxmangion]

any other ideas pls, because i am really stuck with this to get it to work on a calculated field, and i do not wish to repeat the code several controls ?

Thanks in advance!