Full Version: Disply order for ComboBox
UtterAccess Forums > Microsoft® Access > Access Forms
lajwillis
I'm betting this will be a simple fix but I just can't seem to get this working. I have the code below in the After Update section for a combo box. It's working great, I'd just like to add the ORDER BY to get the results of cboOfficeContacts to be displayed in order of Last, then First.
I've tried inserting ORDER BY [Last], [First] in a couple of different spots but can't seem to get it to work.
Any suggestions?
Thanks so much.
Dim sOfficeContactsSource As String
sOfficeContactsSource = "SELECT [tblOfficeContacts].[office]," & _
" [tblOfficeContacts].[Last]," & _
" [tblofficeContacts].[First] " & _
"FROM tblOfficeContacts " & _
"WHERE [OfficeID] = " & Me.cboOffice.Value

Me.cboOfficeContacts.RowSource = sOfficeContactsSource
Me.cboOfficeContacts.Requery
fkegley
I believe ORDER By comes after WHERE (if I am wrong someone will tell me)
OfficeContactsSource = "SELECT [tblOfficeContacts].[office]," & _
" [tblOfficeContacts].[Last]," & _
" [tblofficeContacts].[First] " & _
"FROM tblOfficeContacts " & _
"WHERE [OfficeID] = " & Me.cboOffice.Value & _
" ORDER BY Last, First"
Jack Cowley
sOfficeContactsSource = "SELECT [tblOfficeContacts].[office]," & _
" [tblOfficeContacts].[Last]," & _
" [tblofficeContacts].[First] " & _
"FROM tblOfficeContacts " & _
"WHERE [OfficeID] = " & Me.cboOffice " &_
"ORDER BY tblOfficContacts.Last, tblOfficContacts.First;"
NTESTED AIR CODE!!!
hth,
Jack
lajwillis
I added the line as stated above and this causes cmoOfficeContacts to have no results.
Dim sOfficeContactsSource As String
sOfficeContactsSource = "SELECT [tblOfficeContacts].[office]," & _
" [tblOfficeContacts].[Last]," & _
" [tblofficeContacts].[First] " & _
"FROM tblOfficeContacts " & _
"WHERE [OfficeID] = " & Me.cboOffice.Value & _
"ORDER BY [Last],[First]"

Me.cboOfficeContacts.RowSource = sOfficeContactsSource
Me.cboOfficeContacts.Requery
Otake it back out and it works fine, just no display order to speak of. Any other thoughts?
Thanks,
lajwillis
Well, that didn't work either. Entering the ORDER BY anywhere seems to cause no return. I have also noticed another problem - the second combo box (cboOfficeContacts) displays the proper list of selections but it will only save the first option in the list. Even if I highlight another name, the first item in the last is what gets saved to the table.
Any thoughts on this subject too?
Thanks!
theDBguy
In your previous code, I noticed that you were missing a space. Try to modify your code and see if it makes a difference.
im sOfficeContactsSource As String
sOfficeContactsSource = "SELECT [tblOfficeContacts].[office]," & _
" [tblOfficeContacts].[Last]," & _
" [tblofficeContacts].[First] " & _
"FROM tblOfficeContacts " & _
"WHERE [OfficeID] = " & Me.cboOffice.Value & _
" ORDER BY [tblOfficeContacts].[Last], [tblOfficeContacts].[First]"
HTH.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.