Why won't my "requery" command sort the results?

[Seth Schrock]

I have a form (frmEmployee) with a field called SystemID. I also have a query that finds all of the system IDs currently used and sorts them in numerical order. This query is the basis for a subform on form frmEmployee. The purpose of this subform is to be able to see what system IDs are being used so that a new system ID can be used when entering a new employee. In the SystemID field, I have an AfterUpdate event that requeries the subform so that it registers the new system ID or a changed system ID. My problem is that if I change a system ID from 20 to a non-existent 13, then the subform lists it as 12, 14, 15, 16, 17, 18, 19, 13, 21. If I then change it back to the original 20, then it shows 12, 20, 14, 15, 16, 17, 18, 19, 21. If I click on the refresh records button in the ribbon, then the sort works. Why doesn't the sort from the query work when the subform is requeried?

[NeoPa]

It looks like what you have works, but you're doing it in the wrong place. It's hard to say more without more info, but that should tell you where to look at least.

[Seth Schrock]

What is in the wrong place? My requery code?

[NeoPa]

I'm guessing so. There's so little info here it's hard to be sure, but the ordering of the results you've displayed is a bit of a clue.

[Mihail]

Try to refresh your form from VBA: FormName.Refresh (or Me.Refresh)

[NeoPa]

That won't help I'm afraid Mihail. Seth is already doing a .Requery, and that is a superset of .Refresh. IE. It does all that .Refresh does and more.

[Mihail]

Thank you, NeoPa. It is useeful to know.
Seth say that the Refresh button from the ribbon do the job. And he press this button, of course, AFTER he run the code. I think in terms cause-effect (not neccessary true).

[Seth Schrock]

I did try to do a .refresh command right after the .requery, but it said that the .refresh was not valid for the form. Maybe because it is based on a query.

On frmEmployee, I have two subforms: sfrmFobRegistration and sfrmUsedSystemIDs. The field SystemID is in sfrmFobRegistration. Currently, I have the .requery command in the AfterUpdate event of the sfrmFobRegistration.SystemID field. I just ran a test, and when I comment out the .requery command the result is the same as when I include the .requery command, except that when the .requery command is used, the form goes back to the first record. So it is as if the form is refreshing without running the query that is behind it, if that is possible. Here is the exact code that I'm using for the .requery command:

Forms!frmEmployee!sfrmUsedSystemIDs.Requery

I can't think of any other place to put the .requery command other than the AfterUpdate event because the form doesn't need requeried until the information would change which is when the SystemID field is updated. Is there a better way?

[NeoPa]

If it's in the wrong place Seth, you are going to include the code that handles the AfterUpdate event. The whole event procedure. That way we may have something to go on. I tried to drop a hint earlier about too little information. Perhaps I was too subtle.

[Seth Schrock]

Sorry, I tried to give you more information, but I didn't know what information you needed. Here is the whole AfterUpdate event:

Private Sub SystemID_AfterUpdate()
 
    Forms!frmEmployee!sfrmUsedSystemIDs.Requery
 
End Sub
 

Do you need anything else? I'll try to answer any question to the best of my ability, but I can't promise that I will know the answer :)

[NeoPa]

No Seth. That's plenty.

Put the code in the form's AfetrUpdate procedure. I'm not sure how well this will work, but give it a try (The results you report don't seem logical even when triggered at a time which is essentially correct, but let's see what happens when done in the proper place).

Private Sub Form_AfterUpdate()
    Me.{YourSubformControlNameHere}!sfrmUsedSystemIDs.Requery
End Sub

You may wish to set a flag in the SystemID_AfterUpdate() event procedure to notify the .Requery is required, but it should only be called when the record has actually updated in the table (which doesn't happen until the form is updated of course).

[Seth Schrock]

Okay, it worked. However, it doesn't do anything until the focus leaves the form. This is the obvious result because that is when the AfterUpdate event occurs. I just think that it would make more sense to only requery the subform when the data would change. In this case, the subform is requeried no matter what field is updated. I do think that it will work for my purpose, however.

What gets me is that it didn't work in the AfterUpdate event of the field. As long as the code is correct (I copied and pasted from what I already had) and the event is triggered, shouldn't the result be the same?

Oh, and what do you mean by setting a flag in the SystemID_AfterUpdate() event procedure to notify the .requery is required? I didn't get that part.

[NeoPa]

Ok Seth, think about exactly what has happened at the point of the SystemID_AfterUpdate(). Data is changed on the form in that control. The important point here is that nothing has changed in the table at this point. The form simply reflects a buffer of potential changes that have not yet been committed. Trying to check the table now would make sense only if the logic were wrong.

As for setting the flag in SystemID_AfterUpdate(), that just means that the code in Form_AfterUpdate() could be changed to run the .Requery code only when the changes on the form included a change to SystemID.

See below an example of what I'm trying to explain :

Option Compare Database
Option Explicit
 
Private blnIDChanged As Boolean
 
Private Sub Form_AfterUpdate()
    If blnIDChanged Then
        Me.{YourSubformControlNameHere}!sfrmUsedSystemIDs.Requery
        blnIDChanged = False
    End If
End Sub
 
Private Sub Form_Current()
    blnIDChanged = False
End Sub
 
Private Sub SystemID_AfterUpdate()
    blnIDChanged = True
End Sub

I hope that's a lot clearer now, but feel free to ask for further clarification if anything is still unclear.

[Seth Schrock]

Okay, that makes sense. I didn't think about the fact that the table wouldn't be updated.

And I got the setting the flag part too. I have never seen this done, but it does make sense. Seeing the code, I figured out what is happening. I've included it in my code. Is there a term for code that start with just a "Private" instead of "Private Sub"? I would like to learn more about it, but I don't know what it would be called to Google it.

Thanks for your help.

[NeoPa]

Private simply defines the scope of whatever is being declared. Private followed by a simple name (rather than Const; Sub; Function; etc) is a declaration of a variable. If you look up Private you'll see it specifies the scope of the item (whether that is a variable or procedure) which indicates where in your project it is recognised from. In this case the variable is private to that particular module, so it can be seen anywhere in any of the procedures within that module, but not seen by any other modules.

[Seth Schrock]

Okay. Thanks again.