Results 1 to 10 of 10
  1. #1
    PerioDoc is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Oct 2019
    Posts
    4

    Alphabetizing combo box

    Click image for larger version. 

Name:	Access Table.JPG 
Views:	33 
Size:	29.1 KB 
ID:	40015

    Please understand that my knowledge of access forms is very limited. That said, my issue is this table (see picture) which I have linked to a combo box within a form. The problem arises when I add additional fields and though I am able to alphabetize my inputs, I can't seem to get them to display in the same way when using the drop-down in the form. What's the best way to resolve this issue?

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    What is the problem? Are manufacturers in your combo displayed without any order specified (i.e. ordered by ID), or ordered by Field1? Or doesn't the new manufacturer display in combo?

    You set RowSource property of combo as 'SELECT ID, Field1 FROM Manufacturer ORDER BY Field1 ASC', BoundColumn property as 1, ColumnCount property as 2, and ColumnWidth property as '0, 2,5'.

    In case you use the combo to select the value of field in active record, you set ControlSource property to this field. In case you use the combo to search a record, or to determine an unbound value, you leave ControlSource property empty.

    When you did add new manufacturer when your form was activated, then the new manufacturer to be displayed you need to requery the combobox in form after the manufacturer was added - usually the AfterUpdate event for form where appending was done is used for this.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    To add to Avrils comments. In databases (not just Access) data is stored randomly so you need to apply a sort to get the result you want (which is why First and Last produces unreliable results). Applying a sort in a table only affects the view when you open that table directly, it does not affect anything else that references that table such as a query, form or report.

  4. #4
    PerioDoc is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Oct 2019
    Posts
    4
    Thank you for your reply. The problem is that I don't understand how to alphabetically order my manufacturer in the table and have it display in like manner when I select that in my form combobox (see picture)Click image for larger version. 

Name:	ImplantLog2.jpg 
Views:	19 
Size:	137.3 KB 
ID:	40880

    You might have provided a solution in your reply as well though my brain is slow to register what I'm actually supposed to do.

  5. #5
    PerioDoc is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Oct 2019
    Posts
    4
    Here is the property sheet for the combo boxClick image for larger version. 

Name:	ImplantLog3.JPG 
Views:	19 
Size:	41.8 KB 
ID:	40881

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    To repeat points already made, you don't sort the records in the table. To do this, you use a query or a SQL statement.
    Arvil told you exactly what you need to do using a SQL statement in post #2.
    Change the row source to that and your combo will be sorted as you want
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    Join Date
    Apr 2017
    Posts
    1,673
    The property RowSouurce for Combo14 must be like 'SELECT ManufcturerID, Manufacturer FROM Manufacturer ORDER BY 2 ASC'. On Format tab of Property Sheet, set proper values for ColumnCount (2) and ColumWidths (0;2.5).

    Ignore the order any entries are displayed in tables. And hide Objects Pane (the left one where all tables, queries, forms, reports, etc. are listed) from users - to avoid a lot of sorrows in future. Users start with opening form, and for data viewing and entering only forms and reports must be used.

    To display records in form sorted, use RecordSource of form like 'SELET * FROM YourTable ORDER BY SortingField ASC', or enter sorting field name as form's OrderBy property, and set OrderByOnLoad = 'Yes'.

  8. #8
    PerioDoc is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Oct 2019
    Posts
    4
    Quote Originally Posted by ArviLaanemets View Post
    The property RowSouurce for Combo14 must be like 'SELECT ManufcturerID, Manufacturer FROM Manufacturer ORDER BY 2 ASC'. On Format tab of Property Sheet, set proper values for ColumnCount (2) and ColumWidths (0;2.5).

    Ignore the order any entries are displayed in tables. And hide Objects Pane (the left one where all tables, queries, forms, reports, etc. are listed) from users - to avoid a lot of sorrows in future. Users start with opening form, and for data viewing and entering only forms and reports must be used.

    To display records in form sorted, use RecordSource of form like 'SELET * FROM YourTable ORDER BY SortingField ASC', or enter sorting field name as form's OrderBy property, and set OrderByOnLoad = 'Yes'.
    Ok, almost there. It's now showing a alphabetized drop down though when I select the 'manufacturer' of interest, it doesn't want to populate the entry into the form field. Additionally, I no longer see my previous entries. Click image for larger version. 

Name:	ImplantLog4.JPG 
Views:	18 
Size:	239.4 KB 
ID:	40882

  9. #9
    Join Date
    Apr 2017
    Posts
    1,673


    Check those 2 properties (delimiters may depend on Window's regional settings)

    Edit: I tried to paste image from Snipping Tool above directly (didn't want to litter my hard disk with odd images). It didn't work!
    Attached Thumbnails Attached Thumbnails ComboPropertiesCapture.JPG  
    Last edited by ArviLaanemets; 02-13-2020 at 12:57 AM.

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    it may be something to do with your field name being the same as the table name (manufacturer). Suggest change one of them

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 01-29-2016, 08:52 PM
  2. Replies: 11
    Last Post: 09-03-2015, 11:12 AM
  3. Replies: 2
    Last Post: 10-21-2014, 07:57 AM
  4. Replies: 3
    Last Post: 07-03-2013, 10:38 AM
  5. Alphabetizing form data
    By Sarge, USMC in forum Forms
    Replies: 3
    Last Post: 01-02-2013, 08:18 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums