Row Source Criteria: Too Complex error

[161]

Hello, I'm currently using forms to fill in vast amounts of "loads" that are in many different "categories". I have a pulldown field which shows ALL of the categories so the user can select which one it belongs to. However, loads can only be connected to certain categories by thier codename (primary key). Therefore I want to make the pulldown box smaller by cutting out the options that don't make sense.



Example: A load R05-###-###-### can only be connected to one of several categories that begin with R05.

I've actually got that part to work by using Like Left(load,3) as the criteria in the row source. But here is the issue: Several loads do not follow the same pattern.

Example: A load R06-###-### can also belong to a category beginning with 6L-, or 06-. A load 6L-###-### can also belong to a category beginning with R06, or 06-.
And loads R10 and R12-###-### belong to categorys beginning with R02.

As this was kind-of complicated, I attempted to do it as Select Case code, shown below:

Option Compare Database
Public Function SourcePulldown(strIdentifier As String) As Variant
Select Case Left(strIdentifier, 3)
Case Is = "R02"
SourcePulldown = "R02"
Case Is = "6L-"
SourcePulldown = "R06" And "06-" And "6L-"
Case Is = "06-"
SourcePulldown = "R06" And "06-" And "6L-"
Case Is = "RTF"
SourcePulldown = "RTF"
Case Is = "R06"
SourcePulldown = "R06" And "06-" And "6L-"
Case Is = "R07"
SourcePulldown = "R07"
Case Is = "R10"
SourcePulldown = "R02"
Case Is = "R12"
SourcePulldown = "R02"
End Select
End Function

***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)));


When I go to use the pulldown menu in the form, it is empty. As I was tinkering around, I've also gotten error: "the expression is typed incorrectly or is too complex to be evaluated..."

Thank you for reading, I look forward to your guidance

[161]

Shameful self-bump...

Still having issues with this. Any ideas?

[ssanfu]

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:

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-"

[161]

The cascading combo box technique looks to be very simliar to what im trying to do. Thank you for the tip, and the help in the code! You've given me a lot to look at, hopefully I will make my next post in victory!