Sort key not sorting

[lmcc007]

When I try to sort the CompanyName field, it does nothing. All the other fields will sort like it suppose to.

Also, CompanyName field is a subform on the main form.

Any suggestions on how to get this to work?

Below is my table structure:

tblCompanies
CompanyID dbLong PrimaryKey Indexed
Industry dbText
CompanySize dbText
Address dbText
City dbText
State dbText
PostalCode dbText
DateEntered dbDate

tblCompanyNames
CompanyNameID dbLong PrimaryKey Indexed
CompanyID dbLong ForiegnKey Indexed
CompanyName dbText
DefaultNameToUse dbBoolean


Name: tblCompaniestblCompanyNames
Table: tblCompanies
Foreign Table: tblCompanyNames
PK: CompanyID FK:CompanyID

 

[MajP]

There are multiple ways to sort, a query, code, manual, etc. Can you describe in more detail how you are trying to sort?

 

[lmcc007]

First, it was set up like this:

1. Main form with button to click to sort.
2. Opens frmSort
3. frmSort has: 1) an unbound combo box with Row Source: CompanyID and CompanyName; 2) Option Group with 2 values: Ascending Descending.

Below is the code:

On Error GoTo ErrorHandler

If cboSortBy.ListIndex > -1 And Not IsNull(cboSortBy.Value) Then
Forms!fmainCompany.OrderBy = Replace(cboSortBy.Value, " ", "") & IIf(optSortOrder.Value = 2, " DESC", " ASC")
Forms!fmainCompany.OrderByOn = True
End If

DoCmd.GoToRecord , , acLast
DoCmd.GoToRecord , , acFirst


ExitErrorHandler:
Exit Sub

ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
Resume ExitErrorHandler

CompanyID sorts correctly, but CompanyName does not sort properly.


After trying various things, we created this dummy database to try to see where the problem is.

With this database, we clicked the Microsoft built-in sort button on the Sort & Filter ribbon. The CompanyName field will not sort but all the other fields will sort.

 

[dhookom]

Does your form actually have [CompanyName] in its record source?

What is the record source of fmainCompany?

Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

The record source for fmainCompany is tblCompanies.

 

[dhookom]

There is no CompanyName in tblCompanies so there is no way you can sort by it.

Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

CompanyName is a subform on Company form joined by CompanyID.

 

[dhookom]

Are you attempting to sort the subform records or the main form records?

Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

Subform (CompanyName)--CompanyName is a subform on the main form. CompanyID is on the main form.

 

[lmcc007]

DELETE/IGNORE THIS THREAD, PLEASE! I GOT WHAT I WAS TRYING TO DO.