I've been thinking about your post. I'm not real sure what you are trying to do, maybe cascading combo boxes?? But I do see several problems.
-----------------------
The first two lines in every code page should be (IMO)
Option Compare Database
Option Explicit
There is an option that can be set so that "Option Explicit" is added to every new code page.
(In A2k - A2K3: TOOLS \ OPTIONS \ FORMS/REPORTS - Always use event procedures)
-----------------------
Next, the case statements. This is one of the lines that is causing problems:
SourcePulldown = "R06" And "06-" And "6L-"
Because there is an "AND" between the terms, the result would be TRUE or FALSE *if* the statement could be executed. It is trying to "AND" the three terms.
This is how you would write the case statement:
Code:
Public Function SourcePulldown(strIdentifier As String) As Variant
Select Case Left(strIdentifier, 3)
Case Is = "R02", "R12", "R10"
SourcePulldown = "R02"
Case Is = "R06", "6L-", "06-"
' You can't do this
'SourcePulldown = "R06" And "06-" And "6L-"
Case Is = "RTF"
SourcePulldown = "RTF"
Case Is = "R07"
SourcePulldown = "R07"
End Select
End Function
You could use: SourcePulldown = "R06, 06-, 6L-"
Then in some other code, generate the SELECT statement.
------------------------------------
The select statement
'***Then, I implimented this module in the row source of the pulldown menu:
'SELECT tblEMCC.[EMCC ID] FROM qryRptTotal, tblEMCC WHERE (((tblEMCC.[EMCC ID]) Like SourcePulldown(Forms![_frmInput]!strIdentifier)));
Having two tables in the FROM clause without a join creates a cartesian join. From OracleFAQ site
A
cartesian join is a join of every row of one table to every row of another table. This normally happens when no matching join columns are specified. For example, if table A with 100 rows is joined with table B with 1000 rows, a cartesian join will return 100,000 rows. Something to be avoided!
----------------------------------------
And the WHERE clause would need to be written something like:
WHERE tblEMCC.[EMCC ID] = "R06" or tblEMCC.[EMCC ID] = "06-" or tblEMCC.[EMCC ID] = "6L-"