Cascading Combo Box Help!

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

Guest

I'm sorry for the repost but I'm desparate for help.

With the code below I'm trying to filter a combo box on a sub form based on a
selection made on a combo box from the main form. Main form cbo is
cboOPmedsClass and sform cbo is cboOPmedsLU

I'm getting a complile error "Sub or function not defined" on the line with
the
* below. (* placed to identify where the break is.) I'm not sure why this
happens.

Please Help. I have to deploy my app on Monday. Thanks in advance, Rob

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

Me.fsubOPmeds!cboOPmedsLU = Null

If IsNull(Me.cboOPmedsClass) Then
Me.fsubOPmeds!cboOPmedsLU.Enabled = False
Else
Me.fsubOPmeds!cboOPmedsLU.Enabled = True
Me.fsubOPmeds!cboOPmedsLU.RowSource =
ReplaceWhereClause(Me.fsubOPmeds!cboOPmedsLU.RowSource, _
"Where fldClassification = '" & Me!cboOPmedsClass & "'")

Me.fsubOPmeds!cboPOmedsLU.Requery
End If

End Sub
 
Truth is Doug, I got it out of a book and I really don't have a clue. I
thought it might be some built in function in access. I guess not. Do you
have any suggestions on how I can make this code work. I would really
appreciate it. Thanks, Robert
 
What you need to do is figure out what the SQL needs to be for the combo
box. It's seldom necessary to have a function to do that. However, without
information about your tables, I can't do that for you.

Also be aware that you may need to change how you're referencing the combo
box on the subform.

Me.fsubOPmeds!cboOPmedsLU

should really be

Me!fsubOPmeds.Form!cboOPmedsLU

(assuming the name of the subform control on the parent form is named
fsubOPmeds: the subform control name isn't always the same as the name of
the form being used as a subform.)
 
Doug, thanks for your help. Here's the tables structures.

tblOPmeds; fldOPmedNo (autoNumber), fldPINO (number, foriegn key),
fldOPmedLUno (stores the unique ID from the look up table), fldDose,
fldUnits, fldRoute, fldFrequency, fldStartDate, fldReasonStopped.

tblOPmedsLU: fldOPMedsLUno (autoNumber), fldBRAND_NAME, fldGENERIC_NAME,
fldClassification (this field is the variable for the WHERE condition),
fldTheraputicCode, fldClassCode.

Thanks you again for your help, Robert
 
Doug, here is my code with the method for referencing the control. Also, I've
tried to declare the variable sDrugClass. I know this is probably incorrect,
but I'm not very good at this as you can tell.

Your help is greatly needed and appreciated. Thanks, Rob

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

Dim sDrugClass As String

sDrugClass = Me!cboOPmedsClass
Me!fsubOPmeds.Form!cboOPmedsLU = Null

If IsNull(Me.cboOPmedsClass) Then
Me!fsubOPmeds.Form!cboOPmedsLU.Enabled = False
Else
Me!fsubOPmeds.Form!cboOPmedsLU.Enabled = True
Me!fsubOPmeds.Form!cboOPmedsLU.RowSource =
Me!fsubOPmeds.Form!cboOPmedsLU.RowSource
"Where fldClassification = sDrugClass"

Me!fsubOPmeds.Form!cboOPmedsLU.Requery
End If

End Sub
 
Is cboOPmedsClass based on tblOPmeds, and cboOPmedsLU based on tblOPmedsLU?
If so, I don't understand what it is you're trying to do.

I would have expected that you'd be picking something from cboOPmeds and
that would limit what's displayed in cboOpmedsLU, but given the tables
you've described, I don't see how that would work.

What's the initial RowSource for cboOPmedsLU?
 
cboMedsClass contains re: 12 records that refer to major drug classificatios
like "Cardiac Drugs" etc. The field is; fldClassification. cboMedsClass is
on the main form and I want to choose a classification to narrow down the
choices on my sub form where cboOPmedsLU resides. Hope this clarifies. Thanks
for your help.

RowSource for cboOPmesLU =

SELECT tblOPmedsLU.fldOPMedsLUno, tblOPmedsLU.fldBRAND_NAME,
tblOPmedsLU.fldGENERIC_NAME, tblOPmedsLU.fldClassification,
tblOPmedsLU.fldTheraputicCode, tblOPmedsLU.fldClassCode
FROM tblOPmedsLU
ORDER BY tblOPmedsLU.fldBRAND_NAME;
 
Assuming that cboMedsClass indicates the fldClassification, try something
like:

Private Sub cboOPmedsClass_AfterUpdate()

If IsNull(Me.cboOPmedsClass) Then
Me!fsubOPmeds.Form!cboOPmedsLU.Enabled = False
Else
Me!fsubOPmeds.Form!cboOPmedsLU.Enabled = True
Me!fsubOPmeds.Form!cboOPmedsLU.RowSource = _
"SELECT tblOPmedsLU.fldOPMedsLUno, " & _
"tblOPmedsLU.fldBRAND_NAME, " & _
"tblOPmedsLU.fldGENERIC_NAME, " & _
"tblOPmedsLU.fldClassification, " & _
"tblOPmedsLU.fldTheraputicCode, " & _
"tblOPmedsLU.fldClassCode " & _
"FROM tblOPmedsLU " & _
"WHERE fldClassification ='" & Me!cboOPmedsClass & "' " & _
"ORDER BY tblOPmedsLU.fldBRAND_NAME;"
End If

End Sub
 
Thanks Doug, but it still doesn't work. I don't get any error messages, but
when I select a classification 2 things happen: 1) The existing medications
disapear from the form and 2) the lookup field cboOPmedsLU has no data

I have tried a different strategy that I've used other places in my
application. I get a syntax error with this one. Could you help with the
syntax on this one?

Me!fsubOPmeds.Form!cboOPmedsLU.RowSource =
Me!fsubOPmeds.Form!cboOPmedsLU.RowSource
' "Where fldClassification = '" & Me!cboOPmedsClass & "'")
 
That won't work.

You've already said that your RowSource for cboOPmedsLU starts out as

SELECT tblOPmedsLU.fldOPMedsLUno, tblOPmedsLU.fldBRAND_NAME,
tblOPmedsLU.fldGENERIC_NAME, tblOPmedsLU.fldClassification,
tblOPmedsLU.fldTheraputicCode, tblOPmedsLU.fldClassCode
FROM tblOPmedsLU
ORDER BY tblOPmedsLU.fldBRAND_NAME

With what you're trying to do, you'd end up with

SELECT tblOPmedsLU.fldOPMedsLUno, tblOPmedsLU.fldBRAND_NAME,
tblOPmedsLU.fldGENERIC_NAME, tblOPmedsLU.fldClassification,
tblOPmedsLU.fldTheraputicCode, tblOPmedsLU.fldClassCode
FROM tblOPmedsLU
ORDER BY tblOPmedsLU.fldBRAND_NAME
Where fldClassification = 'xxx')

which isn't valid SQL.

Try:

Private Sub cboOPmedsClass_AfterUpdate()
Dim strSQL As String

If IsNull(Me.cboOPmedsClass) Then
Me!fsubOPmeds.Form!cboOPmedsLU.Enabled = False
Else
Me!fsubOPmeds.Form!cboOPmedsLU.Enabled = True
strSQL = "SELECT tblOPmedsLU.fldOPMedsLUno, " & _
"tblOPmedsLU.fldBRAND_NAME, " & _
"tblOPmedsLU.fldGENERIC_NAME, " & _
"tblOPmedsLU.fldClassification, " & _
"tblOPmedsLU.fldTheraputicCode, " & _
"tblOPmedsLU.fldClassCode " & _
"FROM tblOPmedsLU " & _
"WHERE fldClassification ='" & Me!cboOPmedsClass & "' " & _
"ORDER BY tblOPmedsLU.fldBRAND_NAME;"
Debug.Print strSQL
Me!fsubOPmeds.Form!cboOPmedsLU.RowSource = strSQL
End If

End Sub

Once you've selected something from cboOPmedsClass, go to the Immediate
Window (Ctrl-G) and see what was printed out as the SQL. Copy it into the
Query Editor and see whether it runs.
 
Back
Top