none
How do move a record to the top of a Datasheet Form?

    Question

  • Hello,

    I have a subform, that displays in datasheet view.  The datasheet displays a list of records from a SQL view.

    On the datasheet I have a Job Number field (along with other fields).  When the Job Number is double-clicked, another form is then opened that contains detailed information about that Job.  When that form is closed, I would like to move the selected Job Number to the top of the datasheet,  followed by jobs that are listed after.

    How would I do that?  Thanks so much for your help!

    smsemail

     

    Wednesday, February 27, 2013 10:53 PM

Answers

  • Hi,

    do you want to really move the record itself to the top? If so, you need some criteria to sort the data. For example, you can create a query with a parameter [myID] (which is PK of last modified record) for your subform:

    SELECT MyTable.*, CBool(MyTable.ID=[myID]) AS ChosenOne
    FROM MyTable
    ORDER BY CBool(MyTable.ID=[myID]), MyTableID;

    And before closing the form change the value of parameter in the query and requery the subform. Or make this parameter be a textbox on the main form and just change its value before closing. Just change [myID] to Forms!MyForm!MyTextBox.

    If you in fact want to move record selector to a particular record to help users find the last modified record, just use .FindFirst method of your subform's recordset.


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru


    Thursday, February 28, 2013 8:55 AM
  • On the datasheet I have a Job Number field (along with other fields).  When the Job Number is double-clicked, another form is then opened that contains detailed information about that Job.  When that form is closed, I would like to move the selected Job Number to the top of the datasheet,  followed by jobs that are listed after.

    How would I do that?  Thanks so much for your help!

    Hi smsemail,

    Just thinking loudly.

    I you have a DateTime field in the Job record (if not, then add one), you can fill that field with with the current DateTime on reading. After returning to the datasheet form, you can do a re-sort descending on that field.

    Imb.

    Thursday, February 28, 2013 8:30 AM

All replies

  • On the datasheet I have a Job Number field (along with other fields).  When the Job Number is double-clicked, another form is then opened that contains detailed information about that Job.  When that form is closed, I would like to move the selected Job Number to the top of the datasheet,  followed by jobs that are listed after.

    How would I do that?  Thanks so much for your help!

    Hi smsemail,

    Just thinking loudly.

    I you have a DateTime field in the Job record (if not, then add one), you can fill that field with with the current DateTime on reading. After returning to the datasheet form, you can do a re-sort descending on that field.

    Imb.

    Thursday, February 28, 2013 8:30 AM
  • Hi,

    do you want to really move the record itself to the top? If so, you need some criteria to sort the data. For example, you can create a query with a parameter [myID] (which is PK of last modified record) for your subform:

    SELECT MyTable.*, CBool(MyTable.ID=[myID]) AS ChosenOne
    FROM MyTable
    ORDER BY CBool(MyTable.ID=[myID]), MyTableID;

    And before closing the form change the value of parameter in the query and requery the subform. Or make this parameter be a textbox on the main form and just change its value before closing. Just change [myID] to Forms!MyForm!MyTextBox.

    If you in fact want to move record selector to a particular record to help users find the last modified record, just use .FindFirst method of your subform's recordset.


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru


    Thursday, February 28, 2013 8:55 AM