MVP's; Please Help

[Guest]

I'm sorry for reposting but no one's been able to help me with my struggle.

With the code below I want to filter my lstBox to narrow down the records
the user sees in the list. I try to accomplish with a command button
(cboFilterSupplies) on the form where the list resides, with the code below.
Problem is, when cboFilterSupplies is clicked, the lstBox goes blank. I've
double checked everything but it still doesn't work. I've even gone into the
rowsource query and manually set the criteria to "SA" OR "SAD", and it works
there. Just not with the code below.

Any help would be greatly appreciated. Rob
*******************************************************
Private Sub cboFilterSupplies_Click()

Me.lstChargeMaster.RowSource = "SELECT * FROM table WHERE fldChargeTypeCode
= '" & "SA" & "'"

End Sub

 

[Guest]

RobUCSD,

I'm not an MVP, however, I don't mind trying to help.

You do not indicate whether "SA" a variable or just a string value?

If it is a string variable, then the sql statement should be modified as
follows:

Me.lstChargeMaster.RowSource = "SELECT * FROM table WHERE fldChargeTypeCode
'" & SA & "'"

If "SA" is actually a string value, try:
Me.lstChargeMaster.RowSource = "SELECT * FROM table WHERE fldChargeTypeCode
= "SA"

 

[Albert D. Kallal]

Me.lstChargeMaster.RowSource = "SELECT * FROM table WHERE
fldChargeTypeCode
= '" & "SA" & "'"

Looking at the above...I assume you hard coded this for testing...just to
get this working...

the above should be:

"SELECT * FROM table WHERE fldChargeTypeCode = 'SA' "


I left out the

me.lstchargeMaster.rowSource =

part, so my sample does not word wrap...

Try the above....

Once we get the above working, then we can move onward to a non hardcode
parameter value.....

So, the whoel thing, on one line would be:


me.lstchargeMaster.rowSource = "SELECT * FROM table WHERE
fldChargeTypeCode = 'SA' "

(but, it wraps in this post).

 

[Albert D. Kallal]

Ok...

we had:

"SELECT * FROM table WHERE fldChargeTypeCode = 'SA' "

In the above, I mentined this is "hard" coded.

eg:

"SELECT * FROM table WHERE city = 'Edmonton' "

So, clearly in the above, our Edmnton, or 'SA' is a fixed hard value, and
not a varable. Rmemeber we are just making up a sql stametn here, and that
sql is siimply plain text. So, we can build up that string in pieces.

The problem here is that likely the sql for the listbox does not like the
"*" (asterisk) to specially ALL columns from the table.

Your sql that you "stuff" into the list box MUST match the column count you
set in the listbox (so, I guess my first question is what does the CURRENT
sql for the lsitbox that works look like? Is the list box 1 column, 2
columns....how many is it?

Many listboxes are often two columns:

id ChargeTypeCode
1 SA
2 PA

So, in fact, while the listbox displays "text" values, often the listbox
actually *returns* a number type value. So, when the form loads..the listbox
seems to work...so, what is the sql for that list box now? (you need to post
that sql used).

Once you posted the current sql that works for the listbox.......then we can
write the code to "stuff" the equalling sql into the listbox via code....