var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Manipulating a list box
I'm confused again. I am trying to manipulate a listbox based on SQL statements. I thought as a start I would use the one that is there now and working. The code I have written is as follows:
Private Sub MakeList()
Dim strSql As String
If Me.txtCategory.Column(0) = 4 Then
strSql = "SELECT tblPoolData.ID, tblPoolData.PoolID, " & _
"tblPoolData.Area, tblPoolData.Volume, tblPoolData.TargetTemp AS Temp, " & _
"tblChlorineDonour.ChlorinationType, tblPoolData.Sanitiser, " & _
"tblFilterType.FilterType, tblHeatSource.HeatSource, " & _
"tblPoolData.MaintenanceClient, tblPoolData.OperationsClient" & _
"FROM tblSanitiser RIGHT JOIN " & _
"(tblHeatSource RIGHT JOIN (tblFilterType RIGHT JOIN (tblChlorineDonour RIGHT JOIN tblPoolData ON tblChlorineDonour.ChlorinationID = tblPoolData.ChlorinationType) " & _
"ON tblFilterType.FilterTypeID = tblPoolData.FilterType) " & _
"ON tblHeatSource.HeatID = tblPoolData.HeatSource) " & _
"ON tblSanitiser.SanitiserID = tblPoolData.Sanitiser;"
Me.lstPools.RowSourceType = "Table/Query"
Me.lstPools.RowSource = strSql
As far as I can tell it is bug free and if I understand help and Microsoft correctly it should run. But nothing happens, it is definitely being called and running as I have stepped through on debug, to no avail?
Does the query that you build in STRSQL return any records if you run the sql-statement in the query builder?
Yes the SQL statement is a direct copy of a query that was previously populating the list box.
and you are sure that
"If Me.txtCategory.Column(0) = 4 Then"
Thanks I added the requery to the bottom of the If statement just after setting the rowsource and I still do not get any return value in the list box.
Before anyone asks previously the list box was working with this exact SQL statement. All I have done so far is copy the SQL statement into the code, delete it as the rowsource from the property box. All other properties at this time remain the same as it was when the query was the row source. In other words this should work.
Ya, a .requery shouldn't be neccessary. Setting the rowsource implicitically (oh boy, spelling is hard) requeries.
Is the data in those tables filled at runtime, if so when are you calling the function? Maybe it's a timing prob.
Right now, there does not appear to be anything is this query which is "dynamic."
So, if you simply copy and past the SQL string in to the comboboxe's RowSource and you set the RowSourceType to "Table/Query," does the combobox work.
What is it about this query that you are going to make "dynamic?"
Since the RowSourceType does not change because the query is "dynamic," just set it in the combobox properties and don't fool with it in code.
Have you put this SQL in to a named query and set the RowSource to that named query. Does it work then?
Thanks for the help. I managed to fix the problem, which was a space after one line I had missed.
Now I can fool with this and make the List box dynamic.