Error When Sorting

[jim may]

I have a Form where I can ADD RECORDS -- the Form is based on a Query (Source a Single Table) -- I need for the records to be automatically sorted on the field LastName

When I add a New Record, afterwards this same new record "BAKER" appears at the end, After "ZORRO"

If I click in the LastName Control of My Form and Click the A-Z Sort Icon in the Ribbon, an error message pops up saying:

The specified field [queryMembers].[LastName] could refer to more than one table listed in the From clause of your SQL statement.

If I click OK, -- I then check and "BAKER" is correctly sorted as I move through the records; and "ZORRO" is now last as it shold be...

I opened the queryMembers and Baker is properly sorted;

I opened the TableMembers and Baker is still at the END of the Records;
What can I do to simplify this for my user IN THE FUTURE?

TIA,

Jim

 

[alansidman]

In your query, do you have the sort for the Last Name field set to ascending? That may do it. Another thought. Put a me.requery in the after update event for the Last Name field on the form.

Alan

 

[jim may]

Yes In my Query I have the Sort Line LastName-Ascending, FirstName-Ascending...

I just added a Me.Requery line to my AfterUpdate against my Lastname Control.
I Selected Add a Record
As soon as I entered BAKER and pressed Enter
The sort took place... The new record form didn't stay on the screen long enough for me to complete the other fields before taking me back to my first Record...

Thanks,

Jim

 

[alansidman]

Jim;
Put the me.requery in the After Update for the last field or on closing the form. It probably really doesn't belong in the after update for the last name field.

Alan

 

[jim may]

"or on closing the form" -- thanks, but is there another option. I don't want to have to close the form (to have the afterupdate fire), as the user likely wants to remain in the Form and perform other related work...

Jim

 

[alansidman]

Jim;
I was working off the top of my head. You are right. I would try after scouting the events for a Form to put it in the AfterUpdate event for the form. This should fire when you move off the current record and on to a new record or to actually close the form.

A question for you on how you add new records. After entering your data on a new record, do you have a command button to open another new blank record, or do you use something else. My reason for asking is if you use a command button, then you could put the me.requery as the first line of code on the command button, but you would also have to put it on any close form button you also have so that it would fire when you close the form as well.

I hope this helps to get you where you need to be.
Alan

 

[jim may]

Finding the "AfterUpdate event at the form level" is pretty difficult... I see the On Current event which, of course fires on entering a different record. I went to Barnes and Noble, looked at 3 different Access books for how to do this, but NO LUCK !!

Access is a difficult product it seems

Just Prior to adding a new record I click on a command button attached to this code:

Private Sub Command120_Click()

On Error GoTo Command120_Click_Err

    On Error Resume Next
    DoCmd.GoToRecord , "", acNewRec
    ChurchName.SetFocus
    If (MacroError <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
    End If


Command120_Click_Exit:
    Exit Sub

Command120_Click_Err:
    MsgBox Error$
    Resume Command120_Click_Exit

End Sub

 

[xenou]

Finding the "AfterUpdate event at the form level" is pretty difficult... I see the On Current event which, of course fires on entering a different record. I went to Barnes and Noble, looked at 3 different Access books for how to do this, but NO LUCK !!

There's a drop down on the menu bar to show what is currently selected ... you should be able to pick "Form" from it (or any part of the form, or any control). In Access 2007/2007 this is at the top of the "property sheet".

Or ... just click in the top left corner of the form in design view to select the "form". Then view form properties and events (this is my usual).

Or ... in the Visual Basic editor click in some white space then choose Form from the left drop down. This is useful if you're in VB already. The right drop down will list events.

The main thing is to realize that controls have events and the form has events and some of these events have the same name. Fighting with Access -- I've done it all. Odd beast. Certain things are best in control events (before/after) and certain things are best in form events (current/before/after). If you requery the form you should get a new sorted form. This won't have any affect on the table. Always assume tables aren't sorted in any way. Although you can sort a table's view when you open it for viewing, it's still not the way the records are "really" stored. You can wimp out and code your updates, but it's generally faster and easier to maintain if you learn these events and their "quirks".

Useful reading (to be familiar with all the events and their order of occurence:
http://office.microsoft.com/en-us/access-help/order-of-events-for-database-objects-HP005186761.aspx

ξ

 

[jim may]

xenow,
Many Thanks for jumping in here. Your explanation is CLEAR; I'm familiar enough to know how to get to the Form level Design and did so, I entered in the Afterupdate
event the single line Me.Requery. I tested by adding a new member. After adding and moving to another existing record my just-added record is still at the end of the file
(record 507 of 507). But now, at least when I click inside the LastName Field, and go to the A-Z sort Icon, I DO NOT GET THE ERROR-TYPE MESSAGE as Before And I do now see my just-added record in its proper place (105 of 507). Is there a wat to add a couple more lines of code to my Forms Afterupdate event code to have the system automatically do this Sort - A-Z (Ascending) for the user, so they don't have to use the Sort Icon? Thanks So Much for your help. I'm closer to the GOAL-LINE!!

Jim

 

[xenou]

I entered in the Afterupdate
event the single line Me.Requery.

It may sound like an obvious question but
1) does the form query source have an order by clause?
2) does the form itself have a order by clause?

I prefer to use sorted query sources and not mess around with the form properties for ordering records (for no particular reason except consistency).

As far as I know, if the form is requeried it should retrieve records anew in whatever sorting order is determined by the form (or lack thereof). This would be an interesting case if you're finding this isn't so.

By the way, it sometimes helps, during testing, to make sure the events are firing (a good way to learn when they fire too):

TheNameOfMyForm_AfterUpdate()
    [COLOR="RoyalBlue"]msgbox "After Update"[/COLOR]
    Me.Requery
End Sub