Re: Near Suicidal!

Tech-Archive recommends: Fix windows errors by optimizing your registry

"RobUCSD" <RobUCSD@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:23E38730-2038-4749-AE1D-3F181E24EDE9@xxxxxxxxxxxxx
I'm loosing it here guys. For 2 days I've been trying to get my list
box to filter by pressing a button on the form where the list
resides. By pressing on the button I want to pass a parameter (or
criteria?) to the the query the list's rowSource is based on. My
source table has a field called fldChargeType that holds codes for
each type of charge. The codes are SA, SAD, SD, ADM, PA, PD, and PAD.
These codes have meaning, i.e., SA=Supply Ablation, SAD=Supply
Ablation Device, SD=Supply Device, and so on. I need to pass one or
more of these variables to the query that my list is based on so that
the user is presented with a sub-set of the 187 different charges I
have in my source table.

When I go into the query and manually enter in "SA" OR "SAD" (for
example) into the fldChargeType's criteria row (in query builder) and
run the query I get the correct results. Below is the SQL statement
version of this query.

SELECT tblChargeMaster.fldChargeMasterNo, tblChargeMaster.[fldsvc cd
+ scd], tblChargeMaster.fldAlternateDescription,
tblChargeMaster.[fldCPT4 commercial],
tblChargeMaster.fldChargeTypeCode
FROM tblChargeMaster
WHERE (((tblChargeMaster.fldChargeTypeCode)="SA")) OR
(((tblChargeMaster.fldChargeTypeCode)="SAD"))
ORDER BY tblChargeMaster.fldAlternateDescription;

So the above works to get the list to filter, but I have to go in and
set the criteria manually.

I've been trying versions of the code below (as provided by the
discussion group's responses) to filter the list thru the command
button on the form, but to no avail. I have posted this code several
times to the discussion group and the outcome is always the same.
When I click the command button the list goes blank.

I guess I'm not declaring my variables correctly or at all. I'm a VBA
rookie and I'm not sure how to properly declare them. Could somebody
please look at the code below and try and help?

Thanks, Rob
*******************************************************
Private Sub cboFilterSupplies_Click()

Me.lstChargeMaster.RowSource = "SELECT * FROM tblChargeMaster WHERE
fldChargeTypeCode " = "SA"""

End Sub

The above is on one line, it wraps here do to the question form.

Your code is incorrectly formed. Try this:

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

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

Quantcast