problem - new rowsource clears my listbox

[GONSALVR via AccessMonster.com]

Hello All,

I'm having a problem with resorting a listbox. I have a combo-box where I
want my users to select come choices to sort by: eg: "Supplier Name", "Data
Souce" etc.

When the form is first loaded it is sorted by "Supplier Name"

On the change event of the combo-box I have the following code. However when
I run the statement:

Me.SupplierNameList.RowSource = strSQL

The form reloads and my listbox is empty with one row but all blank column
values. BTW I'm using a single select listbox with no Control Source

Thanks for your help.


**********************************************************************************************************

Private Sub ctl_SuppSortBy_Change()
Dim strSQL As String,

strSQL = "SELECT tbl_UniqueSupplierNameFinal.SupplierName,
tbl_UniqueSupplierNameFinal.DataSource,"
strSQL = strSQL + "tbl_UniqueSupplierNameFinal.EightA,
tbl_UniqueSupplierNameFinal.AustinTetraYN, tbl_UniqueSupplierNameFinal.WBES,"
strSQL = strSQL + "tbl_UniqueSupplierNameFinal.VETS,
tbl_UniqueSupplierNameFinal.UpdatedBy, tbl_UniqueSupplierNameFinal.
ModifiedDate"

If ctl_SuppSortBy.Value = "Data Source" Then
strSQL = strSQL + "FROM tbl_UniqueSupplierNameFinal ORDER BY
tbl_UniqueSupplierNameFinal.DataSource;"
Else
strSQL = strSQL + "FROM tbl_UniqueSupplierNameFinal ORDER BY
tbl_UniqueSupplierNameFinal.SupplierName;"
End If


Me.SupplierNameList.RowSource = strSQL

End Sub

 

[Andrew Tapp]

What is strSQL set to.

e.g. can you give an example of the dymanic sql that's created in the
variable strSQL. Have you been
able to run this as a query, and has it produced the results you expect?

 

[GONSALVR via AccessMonster.com]

Thank you for the quick reply Andrew

Yes I ran the SQL statement as a query and I got the expected results in the
datasheet view

Here's the complete SQL:

SELECT tbl_UniqueSupplierNameFinal.SupplierName, tbl_UniqueSupplierNameFinal.
DataSource, tbl_UniqueSupplierNameFinal.ModifiedDate FROM
tbl_UniqueSupplierNameFinal ORDER BY tbl_UniqueSupplierNameFinal.DataSource;

What is strSQL set to.

e.g. can you give an example of the dymanic sql that's created in the
variable strSQL. Have you been
able to run this as a query, and has it produced the results you expect?

Hello All,

[quoted text clipped - 38 lines]

End Sub

 

[Andrew Tapp]

Sorry, didn't see the code at the bottom of your original message.

In the If statement try using Me!ctl_SuppSortBy instead of
ctl_SuppSortBy.Value and try using Me!SupplierNameList.RowSource = strSQL
instead of Me.SupplierNameList.RowSource = strSQL

Thank you for the quick reply Andrew

Yes I ran the SQL statement as a query and I got the expected results in the
datasheet view

Here's the complete SQL:

SELECT tbl_UniqueSupplierNameFinal.SupplierName, tbl_UniqueSupplierNameFinal.
DataSource, tbl_UniqueSupplierNameFinal.ModifiedDate FROM
tbl_UniqueSupplierNameFinal ORDER BY tbl_UniqueSupplierNameFinal.DataSource;

What is strSQL set to.

e.g. can you give an example of the dymanic sql that's created in the
variable strSQL. Have you been
able to run this as a query, and has it produced the results you expect?

Hello All,

[quoted text clipped - 38 lines]

End Sub

 

[Andrew Tapp]

Just noticed as well that there's no space before the "FROM" statement.
Should be strSQL = strSQL & " FROM...". Also i would use & instead of a +.

Sorry, didn't see the code at the bottom of your original message.

In the If statement try using Me!ctl_SuppSortBy instead of
ctl_SuppSortBy.Value and try using Me!SupplierNameList.RowSource = strSQL
instead of Me.SupplierNameList.RowSource = strSQL

Thank you for the quick reply Andrew

Yes I ran the SQL statement as a query and I got the expected results in the
datasheet view

Here's the complete SQL:

SELECT tbl_UniqueSupplierNameFinal.SupplierName, tbl_UniqueSupplierNameFinal.
DataSource, tbl_UniqueSupplierNameFinal.ModifiedDate FROM
tbl_UniqueSupplierNameFinal ORDER BY tbl_UniqueSupplierNameFinal.DataSource;

What is strSQL set to.

e.g. can you give an example of the dymanic sql that's created in the
variable strSQL. Have you been
able to run this as a query, and has it produced the results you expect?

Hello All,

[quoted text clipped - 38 lines]

End Sub

 

[GONSALVR via AccessMonster.com]

Andrew,

The space before the FROM that was it. THANK YOU!!! THANK YOU!!! You're
the man.

Ray Gonsalves

Just noticed as well that there's no space before the "FROM" statement.
Should be strSQL = strSQL & " FROM...". Also i would use & instead of a +.

Sorry, didn't see the code at the bottom of your original message.

[quoted text clipped - 24 lines]