Dynamic listbox SQL code

[Guest]

I need to streamline some SQL code. Here's what I envision.
- Create a generic (static) function such as ListboxRowSource()
- Have several OnClick events... each of them may have an IF/ELSE statement
or several CASE statements... in this case I used the IF/THEN approach
- Given a specific OnClick event and its matching IF criteria, I'd call the
SQL from the ListBoxRowSource function and tag on the proper ORDER criteria

Currently, the way I wrote the code, I get a syntax error. It indicates
the error is: "Compile error. Expected array."

What am I missing?

Tom



++++++++++++++
Function ListboxRowSource() as string
dim ssql as string

ssql = "SELECT Field1, Field2 FROM TableName WHERE..." & vbcrlf
ListBoxRowSource = ssql

End Function
++++++++++++++
Private Sub Control_A_Click()
Dim ssql As String
If Me.Control_A.Caption = "A"
Me.Listbox1.rowsource = ListboxRowSource() + "ORDER BY
Field1"
Else
Me.Listbox1.rowsource = ListboxRowSource() + "ORDER BY
Field2"
End If
End Sub
++++++++++++++
Private Sub Control_B_Click()
Dim ssql As String
If Me.Control_B.Caption = "B"
Me.Listbox1.rowsource = ListboxRowSource() + "ORDER BY
Field3"
Else
Me.Listbox1.rowsource = ListboxRowSource() + "ORDER BY
Field4"
End If
End Sub
++++++++++++++

 

[Guest]

I am not sure from you post exactly what you are trying to do, but here is an
example that I use to build Where criteria an SQL statement.

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

Also, here is one I use to construct an Order By clause. In this case, the
user selects specific fields with check boxes. When a box is checked, it
passes the field name to this function to add it to the string.

Private Function ComeToOrder(ByVal strField As String) As String
On Error GoTo ComeToOrder_Error

If Len(strOrder) > 0 Then
strOrder = strOrder & ", "
End If
ComeToOrder = strOrder & strField

ComeToOrder_Exit:

On Error Resume Next
Exit Function

ComeToOrder_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure ComeToOrder of VBA Document
Report_rptUPOProductOfferingX"
GoTo ComeToOrder_Exit

End Function

 

[Jeff L]

I think a better way to do this would be to declare a constant ssql and
use it that way. At the very top of your code, where it says Option
Compare Database, right below this line put
Const ssql = "SELECT Field1, Field2 FROM TableName WHERE..." & vbcrlf
Then your other statements would change to:
Private Sub Control_A_Click()

If Me.Control_A.Caption = "A"
Me.Listbox1.rowsource = ssql & " ORDER BY Field1"
Else
Me.Listbox1.rowsource = ssql & " ORDER BY Field2"
End If
End Sub

Same thing with B.

Hope that helps!

 

[Guest]

Klatuu:

hmh, I had hoped that my example would provide sufficient information.
Given your example code, it doesn't appear that way though.

Allow me to recap:
- I have a form that has several OnClick events
- Each OnClick event has an IF/ELSE statement
- If "IF" then sort in ASC order... if "ELSE" then sort in DESC order
- Depending which OnClick event was clicked, I sort in ASC order by e.g.
Field_1

So, since my relatively lengthy SQL code is identical EXCEPT the "ORDER BY
FIELDNAME", I had hoped to put all SQL into a function and then simply add
the appropriate ORDER BY given the selected event the underlying matching
order criteria.

That "concatenated SQL code" is then used as the rowsource for my listbox.

Makes more sense?

Tom

 

[Guest]

Okay.
Here is another snippet I use in that same form to build a row source for a
list box. This form has 6 cascading list boxes. The Order By is not an
issue for me, but how I am handling the assembly of the SQL for the rowsource
may give you an idea

.RowSource = "SELECT DISTINCT MActivity " & _
"FROM quniUPOLists " & _
strWhere & _
"ORDER BY MActivity;"

So once you have your Order put together in a string, you can use it like I
jse strWhere to create the rowsource.

 

[Guest]

....we're still not communicating...

Sorry, but that's NOT what I've asked to solve. 8(

Tom

 

[Guest]

It is the answer.
I'm sorry you don't understand it.

 

[Guest]

Jeff,

thanks for chipping in... I'm glad to see that my request made sense to you.

Ok, I've done the following:
- added the CONST on top of the VBA
- include the ssql into one OnClick event for testing

This is what it looks like so far:

***********
Option Compare Database
Option Explicit

Const ssql = "SELECT tblIssues.TrackNoIDpk, tblIssues.OriginatingAgency,
tblIssues.AssignedAgency, [00_tblIssueGroup].IssueGroup,
[00_tblIssueSubGroup].IssueSubGroup, tblIssues.Issue, tblIssues.Source,
IIf([WatchlistPAR]='1','WL','PAR') AS WLPAR, tblActions.ActionRequired " & _
"FROM ((00_tblIssueSubGroup INNER JOIN (00_tblIssueGroup INNER JOIN
tblIssues ON [00_tblIssueGroup].IssueGroupIDpk = tblIssues.IssueGroupIDfk) ON
[00_tblIssueSubGroup].IssueSubGroupIDpk = tblIssues.IssueSubGroupIDfk) INNER
JOIN tblJunction ON tblIssues.TrackNoIDpk = tblJunction.TrackNoIDfk) INNER
JOIN tblActions ON tblJunction.JunctionIDpk = tblActions.JunctionIDfk " & _
"WHERE (((tblIssues.TrackNoIDpk) Like '*' &
[Forms]![frmKeywordSearch]![txtSearch2] & '*')) " & _
"OR (((tblIssues.OriginatingAgency) Like '*' &
[Forms]![frmKeywordSearch]![txtSearch2] & '*')) " & _
"OR (((tblIssues.AssignedAgency) Like '*' &
[Forms]![frmKeywordSearch]![txtSearch2] & '*')) " & _
"OR ((([00_tblIssueGroup].IssueGroup) Like '*' &
[Forms]![frmKeywordSearch]![txtSearch2] & '*')) " & _
"OR ((([00_tblIssueSubGroup].IssueSubGroup) Like '*' &
[Forms]![frmKeywordSearch]![txtSearch2] & '*')) " & _
"OR (((tblIssues.Issue) Like '*' & [Forms]![frmKeywordSearch]![txtSearch2] &
'*')) " & _
"OR (((tblIssues.Source) Like '*' & [Forms]![frmKeywordSearch]![txtSearch2]
& '*')) " & _
"OR (((IIf([WatchlistPAR]='1','WL','PAR')) Like '*' &
[Forms]![frmKeywordSearch]![txtSearch2] & '*')) " & _
"OR (((tblActions.ActionRequired) Like '*' &
[Forms]![frmKeywordSearch]![txtSearch2] & '*')) "

***********

Private Sub lblTrackNo_Click()

Dim ssql As String

If Me.lblTrackNo.Caption = "Track No " & Me.lblDn.Caption Then
Me.lblTrackNo.Caption = "Track No " & Me.LblUp.Caption
Me.lstBox.RowSource = ssql & " ORDER BY tblActions.TrackNoIDpk DESC;"

Else
Me.lblTrackNo.Caption = "Track No " & Me.lblDn.Caption
Me.lstBox.RowSource = ssql & " ORDER BY tblActions.TrackNoIDpk;"
End If

Me.lstBox.Requery
Me!lstBox.Selected(0) = True

End Sub

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

When I now click on lblTrackNo, my listbox is empty. Is there some small
syntax error that doesn't necessarily cause "blowing up" but results in an
incorrect SQL... ergo a blank listbox?

Thanks,
Tom

 

[Guest]

Klatuu...

respectfully disagree... as far as I'm concerned, that would require writing
the SQL several times.

Please check out Jeff's response... while his response is still not working,
he proposed the CONST (as I need to have it written).

Again, I do appreciate your help in this matter. But it ain't working for
me. Sorry.

 

[Guest]

.... also, I don't have "6 cascading" listboxes... I only have one listbox
that needs changing rowsources depending on the selected action.

Tom

 

[Guest]

It was my intent to give you some ideas, not spoon feed you. It appeared
from your post you had the basic idea under control, but needed some ideas on
how to put it all together.

I think Jeff's idea will work quite well. As Order By is always the last
part of an SQL statement, it is only a matter of how you put the Order By
string together. That is what I had to offer.

Good Luck.

 

[Guest]

Thanks, I did appreciate it.

It was my intent to give you some ideas, not spoon feed you. It appeared
from your post you had the basic idea under control, but needed some ideas on
how to put it all together.

I think Jeff's idea will work quite well. As Order By is always the last
part of an SQL statement, it is only a matter of how you put the Order By
string together. That is what I had to offer.

Good Luck.

 

[Jeff L]

In Private Sub lblTrackNo_Click(), you have ssql declared again. I'm
thinking you are probably having an issue there. If you are still
having trouble, put
debug.print ssql in your code, run it then in your code window, hit
CTRL+G and your ssql statement should appear in the window. Make sure
it looks ok and fix whatever needs fixing in ssql.

Jeff,

thanks for chipping in... I'm glad to see that my request made sense to you.

Ok, I've done the following:
- added the CONST on top of the VBA
- include the ssql into one OnClick event for testing

This is what it looks like so far:

***********
Option Compare Database
Option Explicit

Const ssql = "SELECT tblIssues.TrackNoIDpk, tblIssues.OriginatingAgency,
tblIssues.AssignedAgency, [00_tblIssueGroup].IssueGroup,
[00_tblIssueSubGroup].IssueSubGroup, tblIssues.Issue, tblIssues.Source,
IIf([WatchlistPAR]='1','WL','PAR') AS WLPAR, tblActions.ActionRequired " & _
"FROM ((00_tblIssueSubGroup INNER JOIN (00_tblIssueGroup INNER JOIN
tblIssues ON [00_tblIssueGroup].IssueGroupIDpk = tblIssues.IssueGroupIDfk) ON
[00_tblIssueSubGroup].IssueSubGroupIDpk = tblIssues.IssueSubGroupIDfk) INNER
JOIN tblJunction ON tblIssues.TrackNoIDpk = tblJunction.TrackNoIDfk) INNER
JOIN tblActions ON tblJunction.JunctionIDpk = tblActions.JunctionIDfk " & _
"WHERE (((tblIssues.TrackNoIDpk) Like '*' &
[Forms]![frmKeywordSearch]![txtSearch2] & '*')) " & _
"OR (((tblIssues.OriginatingAgency) Like '*' &
[Forms]![frmKeywordSearch]![txtSearch2] & '*')) " & _
"OR (((tblIssues.AssignedAgency) Like '*' &
[Forms]![frmKeywordSearch]![txtSearch2] & '*')) " & _
"OR ((([00_tblIssueGroup].IssueGroup) Like '*' &
[Forms]![frmKeywordSearch]![txtSearch2] & '*')) " & _
"OR ((([00_tblIssueSubGroup].IssueSubGroup) Like '*' &
[Forms]![frmKeywordSearch]![txtSearch2] & '*')) " & _
"OR (((tblIssues.Issue) Like '*' & [Forms]![frmKeywordSearch]![txtSearch2] &
'*')) " & _
"OR (((tblIssues.Source) Like '*' & [Forms]![frmKeywordSearch]![txtSearch2]
& '*')) " & _
"OR (((IIf([WatchlistPAR]='1','WL','PAR')) Like '*' &
[Forms]![frmKeywordSearch]![txtSearch2] & '*')) " & _
"OR (((tblActions.ActionRequired) Like '*' &
[Forms]![frmKeywordSearch]![txtSearch2] & '*')) "

***********

Private Sub lblTrackNo_Click()

Dim ssql As String

If Me.lblTrackNo.Caption = "Track No " & Me.lblDn.Caption Then
Me.lblTrackNo.Caption = "Track No " & Me.LblUp.Caption
Me.lstBox.RowSource = ssql & " ORDER BY tblActions.TrackNoIDpk DESC;"

Else
Me.lblTrackNo.Caption = "Track No " & Me.lblDn.Caption
Me.lstBox.RowSource = ssql & " ORDER BY tblActions.TrackNoIDpk;"
End If

Me.lstBox.Requery
Me!lstBox.Selected(0) = True

End Sub

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

When I now click on lblTrackNo, my listbox is empty. Is there some small
syntax error that doesn't necessarily cause "blowing up" but results in an
incorrect SQL... ergo a blank listbox?

Thanks,
Tom

I think a better way to do this would be to declare a constant ssql and
use it that way. At the very top of your code, where it says Option
Compare Database, right below this line put
Const ssql = "SELECT Field1, Field2 FROM TableName WHERE..." & vbcrlf
Then your other statements would change to:
Private Sub Control_A_Click()

If Me.Control_A.Caption = "A"
Me.Listbox1.rowsource = ssql & " ORDER BY Field1"
Else
Me.Listbox1.rowsource = ssql & " ORDER BY Field2"
End If
End Sub

Same thing with B.

Hope that helps!