Order of Records in Combo Box problem

[Gibson]

I have a combo box on a form. It contains the line numbers for a specific
invoice. the control source is aa query from a table. I want to view the
line numbers in Descending order(Highest number to smallest number). When I
run the query alone it looks fine. When I use the combo box on the form the
order of the invoices is mixed up. For example, top to bottom in the combo
box will be 2,3,4,1. Is there a way to have them appear in the combo box the
same way they appear when I run the query independently, descending order?

 

[Dirk Goldgar]

I have a combo box on a form. It contains the line numbers for a
specific invoice. the control source is aa query from a table. I want
to view the line numbers in Descending order(Highest number to
smallest number). When I run the query alone it looks fine. When I
use the combo box on the form the order of the invoices is mixed up.
For example, top to bottom in the combo box will be 2,3,4,1. Is there
a way to have them appear in the combo box the same way they appear
when I run the query independently, descending order?

What is actually in the RowSource property of the combo box? Is it a
table name, a query name, or an SQL statement? If it's either of the
latter two, what is the SQL?

 

[Guest]

Set the RowSourceType for the combo box to be a Table/Query and then set the
RowSource to a query that has an 'ORDER BY'.

Good Luck!

 

[Gibson]

Thanks for the response! To answer your question a query is referenced in
the row source. The SQL is listed below. As you can see the query has two
tables. I want to display the invoice numbers in descending orrd. Thanks

SELECT tbl.LineNum, tbl1Hts.File, tbl1Hts.Inv, tbl7Hts.Hts1, tbl1Hts.Value
FROM tb17Hts LEFT JOIN Tb2 ON (tbl1.LineNum = Tb2.LineNum) AND (tbl1.Inv =
Tbl2.Inv)
AND (tbl1.File = Tb2.File)
WHERE (((tbl1.File)=[Forms]![frm1]![File]) AND
((tbl1.Inv)=[Forms]![frm1]![Inv]));

 

[Dirk Goldgar]

Thanks for the response! To answer your question a query is
referenced in the row source. The SQL is listed below. As you can
see the query has two tables. I want to display the invoice numbers
in descending orrd. Thanks

SELECT tbl.LineNum, tbl1Hts.File, tbl1Hts.Inv, tbl7Hts.Hts1,
tbl1Hts.Value FROM tb17Hts LEFT JOIN Tb2 ON (tbl1.LineNum =
Tb2.LineNum) AND (tbl1.Inv = Tbl2.Inv)
AND (tbl1.File = Tb2.File)
WHERE (((tbl1.File)=[Forms]![frm1]![File]) AND
((tbl1.Inv)=[Forms]![frm1]![Inv]));

That query doesn't contain any ORDER BY clause, so the order in which
the records are returned is going to be arbitrary. I'm puzzled, though,
as that SQL (as posted) refers to all sorts of different table names
that aren't included in the query. I think you must have typed it into
your message by hand and made a lot of typos. Next time you have a
question about SQL, copy and paste the SQL directly from the query,
*maybe* inserting line breaks and formatting to make it more readable,
but not changing the text in any way.

Whatever the correct SQL of the query is, you're going to want to add an
ORDER BY clause at the end of it. The last part of the SQL will look
somthing like this:

WHERE (((tbl1.File)=[Forms]![frm1]![File])
AND ((tbl1.Inv)=[Forms]![frm1]![Inv]))
ORDER BY LineNum DESC;