Form Combo Box Drop Down Sorting (1 Viewer)

ldbryant55 · Oct 18, 2018

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?

Ranman256 · Oct 18, 2018

you have them as STRINGS.
you should set the data type to NUMBER. then it will sort correctly.

ldbryant55 · Oct 18, 2018

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

Minty · Oct 18, 2018

If they are really numbers then they should be stored as numbers. So change the datatype in the table.

ldbryant55 · Oct 18, 2018

I do have the field classified as a number in the table. What can I change to help this situation? Thanks!

Minty · Oct 18, 2018

What's the combo rowsource, there must be something changing it to make it think it's a text field.

ldbryant55 · Oct 18, 2018

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.

Gasman · Oct 18, 2018

Has that happened since you added the UNION clause?
Why is it even there?

ldbryant55 · Oct 18, 2018

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 · Oct 18, 2018

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

Gasman · Oct 18, 2018

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?

ldbryant55 · Oct 18, 2018

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.

Gasman · Oct 18, 2018

Try setting it to Null

ldbryant55 said:
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 · Oct 18, 2018

Thanks! Where can I do that?

JHB · Oct 18, 2018

That should do it:

Code:

[COLOR=Red][COLOR=Black]Me![cboProjectID] =[/COLOR] [B]Null [/B][/COLOR]

Minty · Oct 19, 2018

By using the Union with the "*" Access will convert the resultant results to Text as * can't be a number, hence the sorting issue.

Form Combo Box Drop Down Sorting (1 Viewer)

ldbryant55

New member

Ranman256

Well-known member

ldbryant55

New member

Minty

AWF VIP

ldbryant55

New member

Minty

AWF VIP

ldbryant55

New member

Gasman

Enthusiastic Amateur

ldbryant55

New member

ldbryant55

New member

Gasman

Enthusiastic Amateur

ldbryant55

New member

Gasman

Enthusiastic Amateur

ldbryant55

New member

JHB

Have been here a while

Minty

AWF VIP

Similar threads

Users who are viewing this thread