Help with creating variables/routine

[Guest]

The below is used in the click event to filter available charges from a
lstBox. I've discorverd that I need to change the criteria for this query
based on the type of visit, i.e., Clinic, Device, Ablation, Cardioversion,
etc. I can get this info from the field, fldVisitType. My problem is that I'm
very inexperienced at declaring variables in code.

Any help would be greatly appreciated. Thank, Robert

Me.lstChargeMaster.RowSource = "SELECT * FROM tblChargeMaster " & _
"WHERE fldChargeTypeCode IN('PA', 'PAD') " & "ORDER BY
fldAlternateDescription"

 

[Guest]

Hi Rob,

Use something like this:

Me.lstChargeMaster.RowSource = "SELECT * FROM tblChargeMaster " & _
"WHERE fldChargeTypeCode IN('" & me.fldVisitType & "') " & "ORDER BY
fldAlternateDescription"

As an aside, it's a bad idea to have your controls on your form having the
same name as fields in your table - you can end up with circular reference
problems. Try adding txt or dte or cbo or whatever to the front of the
control name.

Damian.

 

[Guest]

Thanks for response. I was hoping to get something like:
If fldVisitType = Ablation (or some variable) Then fldTypeCode= PA (or some
variable)

so would it look like

Dim fldVisitType AS String
Dim fldTypeCode As String

How could I set up the diferent values for the variables? Thanks for your
help.

Thanks Robert

 

[Guest]

No worries Rob...

You could use a select case statement like this:

Dim fldTypeCode As String

select case me.fldVisitType
case "Ablation"
fldTypeCode = "AB"

case "Soemthing Else"
fldTypeCode = "SE"

case else
msgbox "Not sure what to do with this value"
end select

Then use fldTypeCode in your SQL String as before (except without the me.)
me. is used to reference a control on your form. If you have a variable
declared as above, you just need to use its name.

HTH.

Damian.

 

[Guest]

Thanks Damian. I'll try and implement this later this am. Rob