Question Sort by ID (1 Viewer)

[sanmisds]

I have an 'Issues' form. And, I have noticed that issues list are displayed by 'Issue Owners' sort order. I would want it to display by 'Issue ID'. What do I need to do here? I appreciate your help on this and thanks in advance!!

 

[tarcona]

I would need to see an example because I dont really understand what you want to the extent to help you.

 

[tarcona]

But I think you might just need to go to the table that the form is based off of and sort the ID field as you want and then save the table and then open the form again....

 

[sanmisds]

The form is populated based on a query. Query is below:

SELECT Issues.ID, Issues.Title, [Contacts_Assigned to].[First Name] & " " & [Contacts_Assigned To].[Last Name] AS [Assigned To Name], [Contacts_Opened By].[First Name] & " " & [Contacts_Opened By].[Last Name] AS [Opened By Name], Issues.[Opened Date], Issues.Comment, Issues.[Due Date], [Contacts_Opened By].[E-mail Address] AS [Opened By E-Mail], [Contacts_Assigned To].[E-mail Address] AS [Assigned To E-Mail], Issues.Status, Issues.Category, Issues.Priority, Issues.[Assigned To], Issues.[Opened By], Issues.Monitor, Issues.[Project Name], Issues.[Status Date], Issues.InnCode, Issues.MAINT, Issues.TSC, Issues.[IHG Cert], Issues.Lenovo, Issues.SRG, Issues.Web, Issues.AIC, Issues.TEK, Issues.AR, Issues.TAAS, Issues.HD, Issues.EUS, Issues.CSC, Issues.NDSC, Issues.ePS, Issues.GCS, Issues.SDC, Issues.[IHG NHOP], Issues.[IHG FR], Issues.MICROS, Issues.[IHG HSS], Issues.[IHG Deploy], Issues.IBM, Issues.[Due Date], Issues.Comment, Issues.Priority, Issues.Category, Issues.TSS, Issues.[IHG HI RELAUNCH], Issues.[IBM HI RELAUNCH], Issues.[IHG HIB], Issues.[IBM HIB], Issues.TSC, Issues.SIP
FROM Contacts AS [Contacts_Opened By] INNER JOIN (Contacts AS [Contacts_Assigned To] INNER JOIN Issues ON [Contacts_Assigned To].ID = Issues.[Assigned To]) ON [Contacts_Opened By].ID = Issues.[Opened By]
WHERE (((Issues.Status)<>"Closed"))
ORDER BY Issues.ID, [Contacts_Assigned to].[First Name] & " " & [Contacts_Assigned To].[Last Name];

Even though, it says Order By Issues ID, some how I see in the form, it is coming up as by Assigned to name. Thanks for looking at it.

 

[tarcona]

Try this:

Me.OrderBy = "[Forms]![Issues]![ID]"
Me.OrderByOn = True

If that doesnt work just put in the field you wish to order by instead of [Forms]![Issues]![ID] so it would look like:

Me.OrderBy = "[fieldNameHERE]"
Me.OrderByOn = True

And put this in the OnOpen Event.

 

[sanmisds]

I apologize. I am not that familiar with it. So, do I put this code along with the SQL statements I have? Or, how about I add this code? sorry for being so ignorant.

 

[tarcona]

Ok. That is ok.
Open up your form in design view. Go to View > Properties. And go to the Event tab in the Properties menu and find On Open. Click on the white space and a ... box should be visible. Click on the ... and it will bring up another menu and choose "Code Builder". And that will bring up up in this white space that should be like:

Private Sub Form_Open(Cancel As Integer)

End Sub

And your cursor will be in between these two statements. Copy and paste the code I just gave you in between these statements. Then close the code view and go to Form View and see if the code works. If it does, it will sort the records by the ID field in ascending order.

 

[sanmisds]

Thank you very much. It worked. My code is as you suggested:

Private Sub Form_Open(Cancel As Integer)
Me.OrderBy = "[ID]"
Me.OrderByOn = True
End Sub

Now I learned something very important. Again, tarcona for walking me along. Appreciate it the most!! Have a great day!!

ps. I may have few more questions along the way until I finish up this database.

 

[tarcona]

Ok send me a PM if you have anymore questions. Good luck!

 

[sanmisds]

Thank you. I will. I appreciate it very much.