"Order by" issues when opening a form (1 Viewer)

[mkdrep]

I have created a form (see attached form.jpg) that is opened by a macro. I have set the "Order by" criteria to be:
"[specjobs].[Rev_Rept_Date] DESC" (as shown in SortOrder1.jpg)

This criteria opens to form correctly time and time again UNTIL I have sorted the form, when it already open, by a field other than [Rev-Rept-Date]. Unfortunately, every time I do that and then close the form, the next time I run the macro the form opens up using the last sort I made. (as shown in SortOrder2.jpg), in this case:

"[specjobs].[JobBidDate] DESC, [specjobs].[Rev_Rept_Date] DESC"

It appears to me that the LAST sort order I made on the form, when it was opened, in this case I sort the [JobBidDate] in descending order, is placed in front of the sort order I really want, which is to have the form sorted [Rev_Rept_Date] in descending order.

How can I stop the [Order By] setting I want from being changed even after I have changed the sort order which I want the form viewed in a particular instance. In other words, I want to be able to sort the form on different fields when I have opened it, however, when I close and re-open the form, I want the [Order by] to always be:
"[specjobs].[Rev_Rept_Date] DESC"

Thank you in advance for your help.... Mark

 

[RuralGuy]

What line of code are you using to close the form?

 

[JHB]

How do you close the form, are you using a button with any code behind it or how?

 

[mkdrep]

What line of code are you using to close the form?

Please open the (sortOrder1.jpg) I attached and that will show you the "Order By" code I put in the "Order by" field of this forms Property Box....

 

[RuralGuy]

That shows the OrderBy but *how* are you closing the form?

 

[mkdrep]

That shows the OrderBy but *how* are you closing the form?

Sorry, I didn't understand your first question....here is the code used to close the form:

Private Sub CloseJobs_Click()
On Error GoTo Err_CloseJobs_Click

DoCmd.Close
Exit_CloseJobs_Click:
Exit Sub
Err_CloseJobs_Click:
MsgBox Err.Description
Resume Exit_CloseJobs_Click

End Sub

 

[RuralGuy]

Try replacing
DoCmd.Close
...with...
DoCmd.Close acForm, Me.Name, acSaveNo

 

[JHB]

What happens if you close the form by hitting the [X] in right corner of your form?

 

[sneuberg]

I don't think this should be happening. Usually when I've encounter this sort of problem it's been because I inadvertently went to design view and then saved the form, forgetting that the property wasn't in the state I wanted. Anyway, whatever, what you can do to fix this is put the following in the form load event

Me.OrderBy = "[specjobs].[Rev_Rept_Date] DESC"

 

[mkdrep]

What happens if you close the form by hitting the [X] in right corner of your form?

Same thing I describe above....it reopens using the last Sort I performed on the form............

 

[mkdrep]

I don't think this should be happening. Usually when I've encounter this sort of problem it's been because I inadvertently went to design view and then saved the form, forgetting that the property wasn't in the state I wanted. Anyway, whatever, what you can do to fix this is put the following in the form load event

Me.OrderBy = "[specjobs].[Rev_Rept_Date] DESC"

Please excuse my ignorance as I am learning as I go along.....

Here is the code I put in the Event Tab/On Load
Job Quote Form10-2005-MKD.OrderBy=[specjobs].[Rev_Rept_Date] DESC

I have attached the error code I am getting.....I guess I don't understand what "Me.Orderby" represents....

I've attached (2) (.jpgs) for your review

Thanks again for your help

 

[sneuberg]

Sorry I should have explained that better. First "Me" is short hand for the full form reference of the form. You can use in in the form's module instead of Forms!Job Quote Form10-2005-MKD and I think its better as the intelisence will tell you if you got it right.

Now for the specific instructions.

1. Go to the Forms properties On Load event as shown in your attachment Load_Orderby
2. Erase the stuff you typed in there
3. Click on the three dots ... on the right
4. On the Choose Builder form choose Code Builder and click ok
5. On the line after Private Sub Form_Load() type: Me.OrderBy = "[specjobs].[Rev_Rept_Date] DESC"
6. Switch back to Object View (Shift F7) and save the form

 

[mkdrep]

Sorry I should have explained that better. First "Me" is short hand for the full form reference of the form. You can use in in the form's module instead of Forms!Job Quote Form10-2005-MKD and I think its better as the intelisence will tell you if you got it right.

Now for the specific instructions.

1. Go to the Forms properties On Load event as shown in your attachment Load_Orderby
2. Erase the stuff you typed in there
3. Click on the three dots ... on the right
4. On the Choose Builder form choose Code Builder and click ok
5. On the line after Private Sub Form_Load() type: Me.OrderBy = "[specjobs].[Rev_Rept_Date] DESC"
6. Switch back to Object View (Shift F7) and save the form

It worked! Thank you very much for your all your help and your patience!