Sort form by combo box

[insanity]

I have a continuous form with primary key of the table in a combo box. The combo box has three columns and I would like to be able to sort the form based on the data in the 2nd column of the combo box, rather than the Primary Key.

I have a Option Group on the footer on the form to enable to sort on other fields by a SQL query, which "reloads" the .recordsource. The other two sort options are based on data in text boxes so no problems, however the combo box is giving me grief.

Does anyone please know how to do this please?

Thanks

Peter.

 

[dcx693]

What have you tried with accessing the second column of the combo box? Have you tried using the .Column(x) property? I've never tried that in a SQL statement myself, but I think it might work. Something like "ORDER BY " & Me.combo.Column(1) & ";"

 

[insanity]

Thanks for the advise.

I tried the above, but unfortuately didn't work. It came up with a parameter input box and when I go to the debug screen the SQL becomes: "SELECT * FROM tblValidUpload WHERE Uploaded = False ORDER BY ER560;". ER560 is the value, not the name of the field.

This really has me puzzled. I thought of putting a hidden textbox that did a lookup however, the textbox returns the same value for all records on the continous form and no good for sorting.

Any further suggestions and i'm really puzzled and thanks for your help dcx693.

Thanks.

Peter

 

[dcx693]

ER560 is the value, not the name of the field

Then what is the name of the control you are trying to sort on? And does that control contain numeric or text information?

 

[insanity]

I've just realised I didn't explain myself properly.

I have the following in the code:

    Select Case Me.fmeSortBy
    Case 1 'cost centre & combobox
        varSortBy = Me.cboEstab.Column(1)
    Case 2 'period & textbox
        varSortBy = "Period"
    Case 3 'year & textbox
        varSortBy = "Year"
    End Select

    strSQL = "SELECT * FROM tblValidUpload WHERE Uploaded = False ORDER BY " & varSortBy & ";"
    frm.RecordSource = strSQL

There are three fields in the combobox, linked from "tblSchool": Estab (PK) col0 width0, CostCentre col1, SchoolName col2. Estab is the linked field and C/C is a looked up field.

The other fields (Case 2 & 3) are part of "tblValidUpload", populate a textbox and sort OK.

Does this help and is it possible? I really appreciate your help, thanks so far!

Peter

 

[dcx693]

Hmm...now looking at this, I wonder if it will be possible.

The ORDER BY clause requires a field name that exists as a field in the SQL SELECT string. If you're taking something from a combo box, the selected value must equal a field name and not a list of values that you'd like to sort on.

Why use a combo box in this manner anyway? Besides, why not just set Case 1 to be equal to the field name that you'd like to sort on?

 

[insanity]

As it turns out, I was making is much harder than it needed to be. What I did was an INNER JOIN on "tblSchools.Estab" on "tblValidUpload.Estab" to include the header CostCentre instead as Estab wasn't actually needed on this form.

Thanks for your help! The last one was the trigger in my head to simplify it!

The SQL code is much longer (have to select each field instead of *" but it worked!

THANKS again for all your help!

Peter

 

[dcx693]

Glad I was able to help, even if indirectly.