Help with Dimming variable correctly

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

With the code below I'm trying to use a combo box (on main Form to filter
another combo box on a sub form. cboOPmedsClass is the first combo box and it
has re: 10 major drug classification in it, i.e., "Cardiovascular". Based on
the choice made in this combo I want the choices in the second combo box to
be limited to the drugs that are in the chosen class.

I've tried to accomplish this by creating the variable sDrugClass assigning
its value to what ever is in the first combo box and then using it to filter
the query that populates the items on the second combo box's list.

Of course it doesn't work. Any help would be greatly appreciated.

Thanks,

Rob
************************************************************
Private Sub cboOPmedsClass_AfterUpdate()

Dim sDrugClass As String

sDrugClass = Me.cboOPmedsClass.Text

Me!fsubOPmeds.Form!cboOPmedsLU.RowSource = "SELECT * FROM tblOPmedsLU " & _
"WHERE tblOPmedsLU.fldClassification = sDrugClass " & "ORDER BY
tblOPmedsLU.fldBRAND_NAME"

Me!fsubOPmeds.Form!cboOPmedsLU.Requery
End Sub
 
You need to take the sDrugClass outside the string

Something like

Me!fsubOPmeds.Form!cboOPmedsLU.RowSource = "SELECT * FROM blOPmedsLU " & _
"WHERE tblOPmedsLU.fldClassification = " & sDrugClass & " ORDER BY
tblOPmedsLU.fldBRAND_NAME"
==========================
If the sDrugClass field is text then you need to add single quote before and
after the variable

Me!fsubOPmeds.Form!cboOPmedsLU.RowSource = "SELECT * FROM blOPmedsLU " & _
"WHERE tblOPmedsLU.fldClassification = '" & sDrugClass & "' ORDER BY
tblOPmedsLU.fldBRAND_NAME"
================================
Another approch, which I prefer, is to add the criteria to the Combo RowSource

SELECT * FROM blOPmedsLU WHERE tblOPmedsLU.fldClassification =
Forms!MainFormNam!cboOPmedsClass ORDER BY
tblOPmedsLU.fldBRAND_NAME

***** Need to change the name of the Main form in the SQL****
And on the After Update event you need to run only the requery

Me!fsubOPmeds.Form!cboOPmedsLU.Requery
 
Here's the sql that I put in the row source of the second combo box. When I
open the main form two things happen; 1) the existing entries in field where
where the combo # 2 resides go blank. I need to keep the existing medications
for that patient. 2) the second combo box goes blank even with
Me!fsubOPmeds.Form!cboOPmedsLU.Requery in the afterUpdate event of the combo
on the main form. Any suggestion would be greatly appreciated. Thanks, Rob


SELECT *
FROM tblOPmedsLU
WHERE (((tblOPmedsLU.fldClassification)=[Forms]![frmOPmeds]![cboOPmedsClass]))
ORDER BY tblOPmedsLU.fldBRAND_NAME;

"
 
You can set it in a way that if no record was selected in the first combo all
the records will be displayed in the second one.

Try
SELECT *
FROM tblOPmedsLU
WHERE tblOPmedsLU.fldClassification=[Forms]![frmOPmeds]![cboOPmedsClass]
OR [Forms]![frmOPmeds]![cboOPmedsClass] Is Null
ORDER BY tblOPmedsLU.fldBRAND_NAME
--
Good Luck
BS"D


RobUCSD said:
Here's the sql that I put in the row source of the second combo box. When I
open the main form two things happen; 1) the existing entries in field where
where the combo # 2 resides go blank. I need to keep the existing medications
for that patient. 2) the second combo box goes blank even with
Me!fsubOPmeds.Form!cboOPmedsLU.Requery in the afterUpdate event of the combo
on the main form. Any suggestion would be greatly appreciated. Thanks, Rob


SELECT *
FROM tblOPmedsLU
WHERE (((tblOPmedsLU.fldClassification)=[Forms]![frmOPmeds]![cboOPmedsClass]))
ORDER BY tblOPmedsLU.fldBRAND_NAME;

"
You need to take the sDrugClass outside the string

Something like

Me!fsubOPmeds.Form!cboOPmedsLU.RowSource = "SELECT * FROM blOPmedsLU " & _
"WHERE tblOPmedsLU.fldClassification = " & sDrugClass & " ORDER BY
tblOPmedsLU.fldBRAND_NAME"
==========================
If the sDrugClass field is text then you need to add single quote before and
after the variable

Me!fsubOPmeds.Form!cboOPmedsLU.RowSource = "SELECT * FROM blOPmedsLU " & _
"WHERE tblOPmedsLU.fldClassification = '" & sDrugClass & "' ORDER BY
tblOPmedsLU.fldBRAND_NAME"
================================
Another approch, which I prefer, is to add the criteria to the Combo RowSource

SELECT * FROM blOPmedsLU WHERE tblOPmedsLU.fldClassification =
Forms!MainFormNam!cboOPmedsClass ORDER BY
tblOPmedsLU.fldBRAND_NAME

***** Need to change the name of the Main form in the SQL****
And on the After Update event you need to run only the requery

Me!fsubOPmeds.Form!cboOPmedsLU.Requery
Click to expand...
Click to expand...
 
Back
Top