ReplaceWhereClause error

[rkmaitra]

Hello,
I am trying to cascade two combo boxes on the same form. The first box (cboType) is derived from a table, the rowsource being:
SELECT tblTypes.Type_ID, tblTypes.Type
FROM tblTypes;

The second box (cboSubtype) is derived from a table, the rowsource being:
SELECT tblSubtypes.SubType_ID, tblSubtypes.SubType_Name, tblSubtypes.Type_ID
FROM tblSubtypes;

In the AfterUpdate procedure of the first box, I have put the following code:

Private Sub cboType_AfterUpdate()
Me!cboSubtype = Null

If IsNull(cboType) Then
Me!cboType.SetFocus
Me!cboSubtype.Enabled = False
Else
Me!cboSubtype.SetFocus
Me!cboSubtype.RowSource = ReplaceWhereClause(Me!cboSubtype.RowSource, "Where Type_ID = " & _ Me!cboType)*****
Me!cboSubtype.Requery
End If

End Sub

I have checked the value of cboType in the immediate window and it returns a numeric value based on the selection in the combo box.
I am persistently getting an error 'Sub or Function not defined' with Me!cboType highlighted (indicated with *). I have tried to change the expression to a string with quotes, but then it says 'Expected end of statement'.
What am I doing wrong? Any help would be greatly appreciated.

Thanks a lot,
Rudra

 

[boblarson]

You would need to change this line:
Me!cboSubtype.RowSource = ReplaceWhereClause(Me!cboSubtype.RowSource, "Where Type_ID = " & _ Me!cboType)*****

to this

Me!cboSubtype.RowSource = ReplaceWhereClause(Me!cboSubtype.RowSource, "Where Type_ID = " & Me!cboType)

Get rid of the line continuation character.

 

[rkmaitra]

Thanks for the response. The line continuation character does not exist in the actual code. I only put it in here to make the code more readable. As it stands, the code is exactly as you have amended it. I still get the same error. Any other ideas?
Rudra

 

[Banana]

Do you have a function named ReplaceWhereClause in your code? It's a user-defined function and if it's not defined, that may be why you're getting the error.

 

[boblarson]

Do you have a function named ReplaceWhereClause in your code? It's a user-defined function and if it's not defined, that may be why you're getting the error.

I was originally going to post that as well and perhaps I should have gone with my first instinct.

So rkmaitra, if you don't have ReplaceWhereClause, you can get the code for it (and many other good SQL procedures) by clicking here:
http://www.jstreettech.com/files/basJStreetSQLTools.zip
and downloading the zip file from Access MVP Armen Stein's website and then unzip the txt file, copy the contents of that txt file into a new, standard module (not a form, report or class module) and then name the module basJStreetSQLTools. Then the function should work.

 

[rkmaitra]

Thank you both for the solutions. Sorted!
Rudra