Filtering a list box in a form seperate from the form source code

[Steve]

I have a list box on my form that comes from a seperate query than the forms source code and I would like to use an option group for the user to decide what to filter. Any help would be greatly appreciated.

 

[Graham R Seach]

Steve,

Just create a procedure to change the ListBox's RowSource property, based on
the option selected in the Option Group.

Private Sub myOptionGroup_AfterUpdate()
Dim sSQL As String

Select Case Me!myOptionGroup.Value
Case 1: sSQL = "SELECT........."
Case 2: sSQL = "SELECT........."
Case 3: sSQL = "SELECT........."
End Select

Me!lstMyList.RowSource = sSQL
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

I have a list box on my form that comes from a seperate query than the

forms source code and I would like to use an option group for the user to
decide what to filter. Any help would be greatly appreciated.

 

[Steve]

Graham,
I tried what you were saying and here is my code:

Dim sSQL As String

Select Case Me!SawFilter.Value
Case 1: sSQL = "SELECT tblIssueReceive.Saw_ID, [Mill_ID] & " " & [Bandsaw_Type] AS Expr1, tblIssueReceive.Date_Issued, tblIssueReceive.Date_Returned FROM tblSawInventory INNER JOIN tblIssueReceive ON tblSawInventory.Saw_ID=tblIssueReceive.Saw_ID WHERE (((tblIssueReceive.Date_Issued) Is Not Null) AND ((tblIssueReceive.Date_Returned) Is Null) AND ((tblIssueReceive.Saw_ID) like "BH*")) ORDER BY tblIssueReceive.Saw_ID"

End Select

Me!ListSaw.RowSource = sSQL

But I keep getting a complie error saying expected end of statement and it highlights from my &" "& to the end of the line. Do you have any ideas to what I am doing wrong?

Thank you in advance.
Steve

 

[Graham R Seach]

Steve,

Yep, that looks fine.

Just to make you aware though; you can use line-continuation characters to
make the code more readable.

A line-continuation character is an underscore, which tells Access that the
following line is part of the same line. You have to use it in conjunction
with the & character; for example:

sSQL = "SELECT tblissueReceive.Saw_ID, [Mill_ID] & [Bandsaw_Type] AS Expr1 "
& _
"FROM tblsawinventory " & _
"INNER JOIN tblIssueReceive " & _
"ON tblSawInventory.Saw_ID = tblIssueReceive.Saw_ID " & _
"WHERE(((tblIssueReceive.Date_Issued) Is Not Null) " & _
"AND ((tblIssueReceive.Date_Returned) Is Null) " & _
"AND ((tblIssueReceive.Saw_ID) LIKE "

You're better to check Help, but I think you can use up to 20
line-continuations at a time.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

Graham,
After playing with it a little more, I came up with this code and it worked:

Dim sSQL As String

sSQL = "Select tblissueReceive.Saw_ID, [Mill_ID]"
sSQL = sSQL & " & [Bandsaw_Type] AS Expr1"
sSQL = sSQL & " From tblsawinventory Inner Join tblIssueReceive ON

tblSawInventory.Saw_ID = tblIssueReceive.Saw_ID"

sSQL = sSQL & " WHERE(((tblIssueReceive.Date_Issued) Is Not Null)

AND ((tblIssueReceive.Date_Returned) Is Null)"

sSQL = sSQL & " AND ((tblIssueReceive.Saw_ID) like "

Select Case Me!SawFilter.Value
Case 1: sSQL = sSQL & Chr$(34) & "BH*" & Chr$(34) & ")) ORDER BY tblIssueReceive.Saw_ID"


Thank you for helping me out. Would you say this was the correct way to do it?

Steve

Graham,
I tried what you were saying and here is my code:

Dim sSQL As String

Select Case Me!SawFilter.Value
Case 1: sSQL = "SELECT tblIssueReceive.Saw_ID, [Mill_ID] & " " &

[Bandsaw_Type] AS Expr1, tblIssueReceive.Date_Issued,
tblIssueReceive.Date_Returned FROM tblSawInventory INNER JOIN
tblIssueReceive ON tblSawInventory.Saw_ID=tblIssueReceive.Saw_ID WHERE
(((tblIssueReceive.Date_Issued) Is Not Null) AND
((tblIssueReceive.Date_Returned) Is Null) AND ((tblIssueReceive.Saw_ID) like
"BH*")) ORDER BY tblIssueReceive.Saw_ID"it highlights from my &" "& to the end of the line. Do you have any ideas
to what I am doing wrong?