Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-18-2018, 07:57 AM   #1
ldbryant55
Newly Registered User
 
Join Date: Oct 2018
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
ldbryant55 is on a distinguished road
Form Combo Box Drop Down Sorting

Hello, I'm stumped. I have a drop down combo box in a form that lists projects by number, pulled from a project number field in the base table. It used to list the numbers in order, 1, 2, 3, etc., all of a sudden, it is now listing them like this: 1, 10, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 11, 110, 111, 112, etc. (see the pattern?)

I've tried several solutions, including adding an SQL "ORDER BY ProjectNumber", reformatting the field in the original table, reformatting the field in the form, etc. Right now, the field in the table is set as an integer, and the field in the form is set to format "blank". Like I said, I've tried all kinds of combinations, (standard, fixed, general number, etc.), but it still sorts the same way. What can I do to fix this?

ldbryant55 is offline   Reply With Quote
Old 10-18-2018, 08:01 AM   #2
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 3,136
Thanks: 0
Thanked 683 Times in 668 Posts
Ranman256 will become famous soon enough Ranman256 will become famous soon enough
Re: Form Combo Box Drop Down Sorting

you have them as STRINGS.
you should set the data type to NUMBER. then it will sort correctly.
Ranman256 is offline   Reply With Quote
Old 10-18-2018, 08:05 AM   #3
ldbryant55
Newly Registered User
 
Join Date: Oct 2018
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
ldbryant55 is on a distinguished road
Re: Form Combo Box Drop Down Sorting

Thanks! How can I reset them? In the table? In the form?

ldbryant55 is offline   Reply With Quote
Old 10-18-2018, 08:24 AM   #4
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,696
Thanks: 138
Thanked 1,532 Times in 1,504 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Form Combo Box Drop Down Sorting

If they are really numbers then they should be stored as numbers. So change the datatype in the table.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 10-18-2018, 08:37 AM   #5
ldbryant55
Newly Registered User
 
Join Date: Oct 2018
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
ldbryant55 is on a distinguished road
Re: Form Combo Box Drop Down Sorting

I do have the field classified as a number in the table. What can I change to help this situation? Thanks!
ldbryant55 is offline   Reply With Quote
Old 10-18-2018, 08:41 AM   #6
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,696
Thanks: 138
Thanked 1,532 Times in 1,504 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Form Combo Box Drop Down Sorting

What's the combo rowsource, there must be something changing it to make it think it's a text field.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 10-18-2018, 08:52 AM   #7
ldbryant55
Newly Registered User
 
Join Date: Oct 2018
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
ldbryant55 is on a distinguished road
Re: Form Combo Box Drop Down Sorting

The rowsource has this: SELECT tblProject.ProjectID FROM tblProject UNION SELECT '*' FROM tblProject ORDER BY ProjectID

The table (tblProject) has the field (ProjectID), which is the number I'm trying to list in the drop down on the form. There are several drop downs on the form, for various categories.

ldbryant55 is offline   Reply With Quote
Old 10-18-2018, 09:34 AM   #8
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,591
Thanks: 308
Thanked 403 Times in 388 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Form Combo Box Drop Down Sorting

Has that happened since you added the UNION clause?
Why is it even there?
__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
Old 10-18-2018, 10:00 AM   #9
ldbryant55
Newly Registered User
 
Join Date: Oct 2018
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
ldbryant55 is on a distinguished road
Re: Form Combo Box Drop Down Sorting

Honestly, I can't remember. I developed this many years ago, and I want to say that union clause was something I had to add to get the form to work correctly, but I can't remember now. I can try removing it, and see if that makes a difference. What would you suggest?
ldbryant55 is offline   Reply With Quote
Old 10-18-2018, 10:21 AM   #10
ldbryant55
Newly Registered User
 
Join Date: Oct 2018
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
ldbryant55 is on a distinguished road
Re: Form Combo Box Drop Down Sorting

Ok, I removed the "Union Select" from the code, and it did fix the sorting issue, but caused a different issue. There is a "Reset" button on the form, that upon click will reset all of these combo box drop downs to their default text which is "*". When I try to use the reset button now, it gives me an error: "Runtime error 2113 Value you entered isn't valid for this field". When I then go to "Debug", it highlights the ProjectID line in the SQL command:

Private Sub cmdReset_Click()

Me![cboBuyer] = "*"
Me![cboPlant] = "*"
Me![cboSupplier] = "*"
Me![cboCommodity] = "*"
Me![cboFirstTier] = "*"
Me![cboProductLine] = "*"
Me![cboProjectBasis] = "*"
Me![cboProjectID] = "*"
Me![cboProbability] = "*"
Me![cboProjectType] = "*"
Me![cboPriority] = "*"
Me![cboComplete] = "*"

End Sub
ldbryant55 is offline   Reply With Quote
Old 10-18-2018, 11:16 AM   #11
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,591
Thanks: 308
Thanked 403 Times in 388 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Form Combo Box Drop Down Sorting

The ProjectID is not text though, it is a number?
I'm not sure what you should be setting it to, Null perhaps.?

Otherwise what has changed to make the ProjectID appear to be text?
__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
Old 10-18-2018, 12:08 PM   #12
ldbryant55
Newly Registered User
 
Join Date: Oct 2018
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
ldbryant55 is on a distinguished road
Re: Form Combo Box Drop Down Sorting

I don't know what changed, it just started listing that way one day recently. Yes, to answer your question, ProjectID is a number. Any suggestions would be greatly appreciated. Thanks.
ldbryant55 is offline   Reply With Quote
Old 10-18-2018, 01:05 PM   #13
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,591
Thanks: 308
Thanked 403 Times in 388 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Form Combo Box Drop Down Sorting

Try setting it to Null

Quote:
Originally Posted by ldbryant55 View Post
I don't know what changed, it just started listing that way one day recently. Yes, to answer your question, ProjectID is a number. Any suggestions would be greatly appreciated. Thanks.
__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
Old 10-18-2018, 01:15 PM   #14
ldbryant55
Newly Registered User
 
Join Date: Oct 2018
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
ldbryant55 is on a distinguished road
Re: Form Combo Box Drop Down Sorting

Thanks! Where can I do that?
ldbryant55 is offline   Reply With Quote
Old 10-18-2018, 02:07 PM   #15
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,429
Thanks: 2
Thanked 1,993 Times in 1,949 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Form Combo Box Drop Down Sorting

That should do it:
Code:
Me![cboProjectID] = Null 

__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Drop-Down in Form Values Sorting psyc0tic1 Forms 4 09-20-2017 05:51 AM
Sorting Combo Box lookup in a Form scottblk Forms 4 01-23-2013 07:51 AM
Sorting drop down lists from combo boxes brillig12 Forms 7 10-05-2009 11:51 AM
Sorting combo box drop downlist in form Douglas Forms 4 08-15-2002 10:28 AM
Sorting a combo box on a form StephenB Forms 2 05-07-2002 11:44 AM




All times are GMT -8. The time now is 12:03 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World