Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Aug 2003
    Location
    amsterdam, Netherlands
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    requery and sorting (access 2000)

    hi

    I have a question and hope anyone can help me out with this:

    I have a form based on three tables that shows data sorted descending on a certain field ( the main table key field)
    this way every time you open the form the last added record in the main table is displayed.

    (also I use a button for a requery that people can use every time a record has been added and the form was not closed.)
    this all works fine.

    but when i use the standard access "sort" buttons that come in the toolbar it will sort the record according to the selected field
    but after that I can't get the records in that form to be sorted anymore according to the query that the form is based on.

    so my question is: 1. what is the relationship between the sort buttons and the qeury that you build your form on and
    2. how can I keep using the sort button but also have my form sorted according to the basic query using "requery"

    Thanks ahead.

    Diederik

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: requery and sorting (access 2000)

    The basic relationship between the sort buttons on the toolbar and the query is that the sort changes the order of the recordset based on the field you choose. When you do that in a form, it sets a property on the form called Order By and that property is set in the form. The property will contain something like:
    myQuery.mySortField DESC
    if you choose to sort in descending order. So if you need to requery, you should clear that property before you do the requery. However, I'm also curious why you need to requery - bound forms in general display the record as soon as it is added. Hope this clears things up some.
    Wendell

  3. #3
    Lounger
    Join Date
    Aug 2003
    Location
    amsterdam, Netherlands
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: requery and sorting (access 2000)

    Hi Wendell,


    Thanks, I found what you said about the added sortingorder in the properties of the form.
    but how can I have access remove this (using some code or macro or so)

    and The reason I have that requery is because this form is an overview form.
    when a user adds a record he/she does that in an other form. that way in the overview form it doesn't show the added record rightaway.
    but requery helps.

    Greetings

    Diederik

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: requery and sorting (access 2000)

    If the button is on the form you want to requery use this code :
    Me.OrderBy = ""
    Me.Requery

    If the button is on another forms use :
    Forms.FormName.OrderBy = ""
    Forms.FormName.Requery
    Francois

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: requery and sorting (access 2000)

    Interactively: Records | Remove Filter/Sort

    In code:

    <code>Me.OrderBy = ""</code>

    if the code is in the module of the form itself; if you call it from another form, use

    <code>Forms!FormName.OrderBy = ""</code>

    where FormName is the name of the form whose sort order you want to clear.

  6. #6
    Lounger
    Join Date
    Aug 2003
    Location
    amsterdam, Netherlands
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: requery and sorting (access 2000)

    Guys,

    Thanks!

    So simple and so easy.
    (and so much relief!)

    Greetings

    Diederik

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •