Apply a filter every time data is entered in datasheet view?

[Guest]

I have applied a filter (to sort on multiple fields) in the data base. I want
Access to apply the filter (re-sort) every time a new record is added in the
datasheet view. HOW?

 

[John Vinson]

I have applied a filter (to sort on multiple fields) in the data base. I want
Access to apply the filter (re-sort) every time a new record is added in the
datasheet view. HOW?

You cannot sort "a data base". Jargon alert: in Access, a "Database"
is the .mdb file, a container for multiple Tables, Forms, Reports,
Queries and other objects.

I'd suggest that you not use the table datasheet at all. It's too
limiting. Instead, use a Form (which could be a datasheet view form if
you wish); it should be based on a Query sorting the records in the
order you desire. Put code in its AfterInsert event

Private Sub Form_AfterInsert()
Me.Requery
End Sub

Note that this will get timeconsuming when the table grows large, and
that it will always jump back to the first record when you requery.
You can put additional code

DoCmd.GoToRecord acForm, Me.Name, acLastRecord

to jump to the last record, but it will appear at the top of the
screen.

John W. Vinson[MVP]

 

[Guest]

JP,
A table or query maintains the current sort as part of the layout. If you
want the datasheet to open in a specific sort and retain it, you sort the
datasheet, close the datasheet and answer YES to save the changes in the
layout.
The default sort is by primary key.
Access coders encourage others to use forms and reports for data access and
not table or query entry.
You can use a form set to datasheet view based on a query with your sort
pattern. This form would retain the sort sequence you desire.
To do this:
Use the New Form option for a FormWizard and select your table or query,
choose the Datasheet option for layout, and in the properties, open the data
source and code the sort..

 

[Guest]

This worked AWESOME! Thank you! I have a follow up question. In my form how
to I set the focus (I believe that's the term) to the newly entered record
after the Requery?

 

[John Vinson]

This worked AWESOME! Thank you! I have a follow up question. In my form how
to I set the focus (I believe that's the term) to the newly entered record
after the Requery?

Like I said:

You can put additional code

DoCmd.GoToRecord acForm, Me.Name, acLastRecord

to jump to the last record

John W. Vinson[MVP]

 

[Guest]

Sorry, I'm a novice at this. I tried your recommendation. I inserted the
command "DoCmd.GoToRecord acForm, Me.Name, acLastRecord" right before End
Sub. When executed I get an error message " Run-time error: 2105 You can't go
to the specified record". What am I doing wrong?

 

[John Vinson]

Sorry, I'm a novice at this. I tried your recommendation. I inserted the
command "DoCmd.GoToRecord acForm, Me.Name, acLastRecord" right before End
Sub. When executed I get an error message " Run-time error: 2105 You can't go
to the specified record". What am I doing wrong?

It should be acNewRecord (to go to the new record) or acLast (to go to
the last record).

John W. Vinson[MVP]

 

[Guest]

OK, got the syntax correct, I can get it to go to the first or last record in
the table, what I am trying to do is after a new record is inserted and the
is sorted (Requery) I want to go to that newly inserted record. Where ever it
is in the table. Is that possible. BTW, I REALLY appreciate your help and
hope I'm not a real PAIN!

 

[John Vinson]

OK, got the syntax correct, I can get it to go to the first or last record in
the table, what I am trying to do is after a new record is inserted and the
is sorted (Requery) I want to go to that newly inserted record. Where ever it
is in the table. Is that possible. BTW, I REALLY appreciate your help and
hope I'm not a real PAIN!

That's a bit more complex. You'll need to note the Primary Key value
of the record after inserting it, and find that record in the form's
Recordsource; then set the form's bookmark to the recordsource's
bookmark.


John W. Vinson[MVP]

 

[simon white]

That's a bit more complex. You'll need to note the Primary Key value
of the record after inserting it, and find that record in the form's
Recordsource; then set the form's bookmark to the recordsource's
bookmark.


John W. Vinson[MVP]