UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Sorting, Access 2016    
 
   
mike60smart
post Jan 21 2020, 08:10 AM
Post#1


UtterAccess VIP
Posts: 13,658
Joined: 6-June 05
From: Dunbar,Scotland


Hi Everyone

I have 2 Unbound Combobox's which are set as Cascading.

The 1st Combobox allows the user to select a Supplier and the 2nd Combobox shows all materieals for the selected Supplier.

The Row Source of the 2nd Combobox is as follows:-

CODE
SELECT tblMaterialsTable.MaterialID, tblMaterialsTable.SupplierID, tblMaterialsTable.ItemSKU, tblMaterialsTable.ItemDescription, tblMaterialsTable.ItemUnitPrice, tblMaterialsTable.MarkupTypeID
FROM tblMaterialsTable
ORDER BY tblMaterialsTable.ItemSKU;


When the user users the Combobox's the Column for ItemSKU is not sorting alphabetically?

Is there a method to resolves this?

Any help appreciated.

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
cheekybuddha
post Jan 21 2020, 08:16 AM
Post#2


UtterAccess Moderator
Posts: 12,285
Joined: 6-December 03
From: Telegraph Hill


Hi Mike,

The RowSource you posted doesn't seem to reference the first combobox, which makes me wonder whether you are [re-]setting it in code in the AfterUpdate event of the first.

If so, please post the code of that event. It may yield a clue.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
BruceM
post Jan 21 2020, 08:28 AM
Post#3


UtterAccess VIP
Posts: 8,027
Joined: 24-May 10
From: Downeast Maine


Is ItemSKU a lookup field, perchance?
Go to the top of the page
 
mike60smart
post Jan 21 2020, 09:36 AM
Post#4


UtterAccess VIP
Posts: 13,658
Joined: 6-June 05
From: Dunbar,Scotland


Hi David

Here if the After Update Event :-

CODE
Private Sub cboSupplier_AfterUpdate()

10        On Error GoTo cboSupplier_AfterUpdate_Error
      Dim sSource As String

20    sSource = "SELECT  tblMaterialsTable.MaterialID, tblMaterialsTable.SupplierID, tblMaterialsTable.ItemSKU, tblMaterialsTable.ItemDescription, tblMaterialsTable.ItemUnitPrice, tblMaterialsTable.MarkupTypeID " & _
      "FROM tblMaterialsTable " & "WHERE [SupplierID] = " & Me.cboSupplier

30    Me.cboMaterials.RowSource = sSource

          
40        On Error GoTo 0
50        Exit Sub

cboSupplier_AfterUpdate_Error:

60        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cboSupplier_AfterUpdate, line " & Erl & "."

End Sub

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
cheekybuddha
post Jan 21 2020, 10:34 AM
Post#5


UtterAccess Moderator
Posts: 12,285
Joined: 6-December 03
From: Telegraph Hill


My suspicion was right - you don't include and ORDER BY statement when setting the new RowSource.

Amend line 20 to:
CODE
' ...
20    sSource = "SELECT  MaterialID, SupplierID, ItemSKU, ItemDescription, ItemUnitPrice, MarkupTypeID " & _
      "FROM tblMaterialsTable " & _
      "WHERE [SupplierID] = " & Me.cboSupplier & " " & _
      "ORDER BY ItemSKU"
' ...


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
PhilS
post Jan 21 2020, 10:37 AM
Post#6



Posts: 679
Joined: 26-May 15
From: The middle of Germany


You need to add the ORDER BY clause from the first posted SQL to your code setting the dependent rowsource.

--------------------
A professional Access developer tool: Find and Replace for Access and VBA
Go to the top of the page
 
mike60smart
post Jan 21 2020, 11:46 AM
Post#7


UtterAccess VIP
Posts: 13,658
Joined: 6-June 05
From: Dunbar,Scotland


Hi David

As always spot on

Many thanks again.
cheers.gif

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
cheekybuddha
post Jan 21 2020, 11:49 AM
Post#8


UtterAccess Moderator
Posts: 12,285
Joined: 6-December 03
From: Telegraph Hill


I'm glad we could help, Mike! thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th February 2020 - 07:42 PM