Sorting form after new data has been entered

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form where new user information is input daily. It is sorted by
last name. Is there a way that when I get to the end of a record it will
insert the new data appropriately alphabetized? For instance, last name Ryan
- how do I get it with the other Ryan's in the form?
 
Are you using a query as the RecordSource of the form and does it have an
OrderBy clause? If so then a Me.Requery will resort the records but it also
takes you back to the 1st record. You will need to make your own
arrangements to get back to the same record.
Save the record ID
Requery
then FindFirst on the ID and set the Bookmark
 
I have a form where new user information is input daily. It is sorted by
last name. Is there a way that when I get to the end of a record it will
insert the new data appropriately alphabetized? For instance, last name Ryan
- how do I get it with the other Ryan's in the form?
Click to expand...

First off... you're not entering data into the form. You're entering data
*THROUGH* the form into a Table.

What you can do is base the form, not directly on the Table (which is an
unordered bag of records), but on a Query sorting the records in the desired
answer - e.g. select the LastName and FirstName fields and set the sort order
to Ascending on both. When you close and open the form, or requery it, you'll
see the records appearing in the desired order.

John W. Vinson [MVP]
 
Requerying the form in its AfterInsert event procedure will do it, but the
record pointer will be moved back to the first record in the sort order. To
stay at the newly inserted record you'll need to grab the value of the
primary key to a variable before requerying the form, and then navigate back
to the record after requerying the form by finding it in a clone of the
form's recordset and then synchronizing the form's Bookmark with that of the
clone, so, if the key is called CustomerID and is a number data type, the
code for the form's AfterInsert event procedure would be:

Dim rst As Object
Dim lngID As Long

lngID = Me.CustomerID

Me.Requery

Set rst = Me.Recordset.Clone

With rst
.FindFirst "CustomerID = " & lngID
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

If the key is a text data type you'd need to use a variable of String data
type:

Dim strID As String

strID = Me.CustomerID

and wrap its value in quotes characters like so:

.FindFirst "CustomerID = """ & strID & """"

Ken Sheridan
Stafford, England
 
Back
Top