Sort a form by any column of any combobox. No need to add more tables to a form record source to do complex sorts in Access. If there's a column, Access can use it to sort. You just have to know the trick ...
Lookup_ to Lookup_ControlName.ColumnName
preface ControName with Lookup and then a dot and then specify ColumnName: -- amazing how Access can do this when the values aren't in the form RecordSouce, but it can! Working on a video using this technique to FILTER ... still making sample database and doing more rigorous testing
00:00 Introduction -- sort a form by columns of combo boxes
00:04 you can sort a form by columns of combo boxes
00:29 cross-reference table only stores foreign keys.
00:36 Unique Index
00:48 cross-reference table.
01:02 design view of form
01:40 calculated control
01:54 Only one column of a combobox can be seen when the list doesn't show.
02:10 MainName is the Last name of a human or the company name of a company.
02:16 NameA is the first name of a human
02:27 SQL for the ContactID combobox
02:34 The first column what is being stored
02:41 ColumnWidths setting
02:59 Null propogation
03:17 calculated control is a is a great way to show another column in the combobox
03:36 ControlSource for the calculated control
03:50 column indexes start with zero
04:21 Form isn't sorted by anything in particular.
04:25 The first button sorts by LastFirst and then the order.
04:48 see what other Sort command buttons do
05:31 Let's look at the code and see how this was done.
05:59 there isn't anything called Lookup_ContactID!
06:16 preface control name with 'Lookup_'
06:41 Remember the SQL statement?
06:54 Click event to sort by Category -- remember, Category isn't in the RecordSource for this form
07:36 The secondary sort is then either by LastFirst or by FirstLast.
07:45 sort by columns in 2 different comboboxes
09:12 Sort in ascending or descending order
09:39 ok, let's try something harder… by the number of contacts in a category
09:42 what if you want to sort by the number of contacts in a category?
09:48 How would you do that?
09:50 calculate the number of contacts
10:06 RowSource Builder
10:29 Totals Query
10:38 change Group By to Count
11:04 it's not generally a good idea to name things starting with a number sign,
11:47 need ColumnHeads
11:54 ColumnHeads is Yes so the number has some meaning.
12:03 ColumnWidths
12:06 ListWidth
12:24 link on customizing the QAT so you can get my Quick Access Toolbar (QAT) if you want it.
12:43 don't have to show the calculated number to sort by it
13:06 manual transmission
13:14 They're lined up, change Left.
13:32 Copy and Paste
13:54 Change Width to 1 inch for everything in the selection
14:23 Align Top and then Align Left
15:11 Change the name to something logical -- CategoryID_Count
16:04 What if a category doesn't have any contacts? Change the JOIN to show all categories
16:18 VBA needs to know the name of the calculated column
"17:04 preface the control name with ""Lookup_"""
17:10 The column name is #Contacts and it has to have square brackets around it since it's such a bad name.
18:18 set OrderBy andOrderByOn
18:56 web page with the sample database so you can try this out for yourself
19:08 tutoring
links:
Download this sample database:
[ Ссылка ]
Customize QAT
video-- Quick Access Toolbar (QAT) -- Customize and Share (Import/Export), Microsoft Access
[ Ссылка ]
Download contact database samples:
[ Ссылка ]
free tools and code at [ Ссылка ]
~~~
Please share your comments, thank you!
kind regards,
crystal
Remote Training and Programming
Let's connect and build your application together!
[ Ссылка ]
email Training AT msaccessgurus.com
