This expression is typed incorrectly or it is too complex to be evaluated....

[watdahek]

Good Afternoon,
I am trying to create a subform that pulls data from a query that uses fields in the main form as its criteria. Everything works fine if I use 5 criteria fields but I need to use 7 and as soon as add the sixth and try to run I get the error mentioned in the title of this thread. Here is a brief synopsis of the database:

7 data tables: Account, Year, Location, Workgroup, Scenario, Version, Event (each table includes all possible values for the respective field plus an ID field)
1 Main Data table: includes a field for each table named above and one for each month (Jan, Feb, Mar...)

1 Form that includes 5 combo boxes for 5 of the categories (Location, Workgroup, Scenario, Version, Year) and 2 text boxes that include Account and Event values.

When I try to pull the values for each month field into the form I get an error, if I only use 5 criteria (any combination) it works but gives me too many results as my unique identifier contains 7 fields.

Does anyone know what I am doing wrong or if there is a better approach to accomplish this?

All help is greatly appreciated,
Steve

 

[strive4peace]

Hi Steve,

you're welcome

it would help if I could see your structure better ... just posted a standard module for you to import and run too

Show Fieldnames, Data Types, Sizes, and Descriptions for Tables
Show Table Structure in Debug Window - Roger's Access Library

copy what it prints in the debug window to this thread, thanks

 

[watdahek]

Thanks Again Crystal,

Here is the output:
tbl_Account
==========================
0 AcctID, 4 (Lng), 4, Account ID (Auto)
1 Account, 10 (Txt), 255, Account Name
2 Account Description, 10 (Txt), 255, Account Description
tbl_Event
==========================
0 EvID, 4 (Lng), 4, Event Id (Auto)
1 Event, 10 (Txt), 255, Name of Event - Default if not Used
2 Event Description, 10 (Txt), 255, Event Description
tbl_Master
==========================
0 MstID, 4 (Lng), 4, Master ID (Auto)
1 WGID, 4 (Lng), 4, Workgroup ID
2 WLID, 4 (Lng), 4, Work Location ID
3 AcctID, 4 (Lng), 4, Account ID
4 EvID, 4 (Lng), 4, Event ID
5 MnID, 4 (Lng), 4, Month ID
6 YrID, 4 (Lng), 4, Year ID
7 ScID, 4 (Lng), 4, Scenario ID
8 VerID, 4 (Lng), 4, Version ID
9 Data_Value, 20 (oDec), 16, Data Point
tbl_Month
==========================
0 mnID, 4 (Lng), 4, Month ID (Auto)
1 Mnth, 10 (Txt), 255, Month
tbl_Scenario
==========================
0 ScID, 4 (Lng), 4, Scenario ID (Auto)
1 Scenario, 10 (Txt), 255, Scenario Name
2 Scenario Description, 10 (Txt), 255, Scenario Description
tbl_Version
==========================
0 VerID, 4 (Lng), 4, Version ID (Auto)
1 Version, 10 (Txt), 255, Version Name
2 Version Description, 10 (Txt), 255, Version Description
tbl_WorkGroup
==========================
0 WGID, 4 (Lng), 4, WorkGroup ID (Auto)
1 WorkGroup Name, 10 (Txt), 255, Workgroup Name
2 WorkGroup Description, 10 (Txt), 255, Workgroup Description
tbl_WorkLocation
==========================
0 WLID, 4 (Lng), 4, Location ID (Auto)
1 Work Location, 10 (Txt), 255, Site Location
2 Work Location Description, 10 (Txt), 255, Site Description
tbl_Year
==========================
0 yrID, 4 (Lng), 4, Year ID (Auto)
1 Yr, 4 (Lng), 4, Year


Thanks,
Steve

 

[strive4peace]

you're welcome, Steve

thanks, that helps me see what you have set up ... and I modified my upload on RogersAccessLibrary to add blank lines ... guess it made you fill your field descriptions ... well if one was missing, it would have gotten an error, fixed that too

your fieldnames are pretty good except don't use spaces and some of them get long. I would use, for instance, ScenDesc, EvntDesc, VersnDesc

Version is a reserved word, use Versn. Although 'Event' is ok in Access, I would use EvntName instead

Problem names and reserved words in Access
by Allen Browne
Microsoft Access tips: Problem names and reserved words in Access

> "as add the sixth and try to run I get the error"

would this possibly have been your Version table?

I notice you have tables for names of months -- this is not necessary. You also have a table for years. In applications where I need numbers, I make a Numbers table with records from 1-31 or maybe higher -- then use criteria and calculated fields to make them what you need.

Some built-in ways to get names of months:
Format(expression[, format[, firstdayofweek[, firstweekofyear]]])
DatePart(interval, date[,firstdayofweek[, firstweekofyear]])
MonthName(#) -- 1-12

I notice that you have many text fields that need Size to be smaller -- my Analyzer will help you figure out what they should be if you run it on a database with a good sample of information

as for filtering your subform, here is what I would do:

have the subform show all records, then filter it for whatever criteria is specified.

Put unbound comboboxes/textboxes, in the form header, for instance. Then, to trigger the code, put this in the [Event Procedure] code of the AfterUpdate event of each filter control:

    SetFormFilter

Perhaps your filter comes from data in the main form. In that case, trigger the code to filter on the mainform Current event and AfterUpdate event of each control that affects it.

this is a generic version of the code that would also go behind your form:

Private Function SetFormFilter()
  'Crystal strive4peace

   dim varFilter as variant
   varFilter = Null
 
   If not IsNull(me.controlname_for_text) Then
      varFilter = (varFilter + " AND ") _
         & "[TextFieldname]= '" & me.controlname_for_text  & "'"
   end if
 
   If not IsNull(me.controlname_for_date  ) Then
      varFilter = (varFilter + " AND ") _
         & "[DateFieldname]= #" & me.controlname_for_date  & "#"
   end if
 
   If not IsNull(me.controlname_for_number ) Then
      varFilter = (varFilter + " AND ") _
         & "[NumericFieldname]= " & me.controlname_for_number
   end if
 
   With Me
       If Not IsNull(varFilter )  Then
          .Filter = varFilter
          .FilterOn = true
      Else
          .FilterOn = false
      End if
      .Requery
   End With
 
End Function

WHERE
me.controlname_for_number, controlname_for_date, and controlname_for_text refer to the NAME property of a control on the form you are behind
(Me. represents the form -- kinda like "me" for me is not "me" for you )

delimiters are used for data that is not a number
quote marks ' or " for text
number signs # for dates

varFilter is a variant that will hold the string you are building for each condition -- but if nothing is specified in the filter control (IsNull), then that addition to the filter string is skipped.

(varFilter + " AND ")

If something is already in the filter, the word AND will be added. The beauty of using the + operator is that nothing + something = nothing ... so if nothing is there then nothing is added before the new criteria

~~~
finally, when the filter string is done, it is applied to your form if it has something in it. If not, then all the records will be displayed

That means that as you flip through records, only records matching that filter will show

if you are wanting to filter a subform, instead of using
With Me
you would use -->

   With Me.subform_controlname.form

WHERE
.subform_controlname is the Name property of the subform control

if the form to get criteria from is in a subform of the same main form, use -->

   With me.parent.subform_controlname.form

 

[watdahek]

Thank You Crystal, I think I have a really good framework to start building on.

Steve

 

[strive4peace]

you're welcome, Steve -- so are you still having problems?

 

[watdahek]

Thanks Crystal...I am confident that I have a good base for the database and my filters are working. While I have your attention I was wondering if you knew away around the inability to update records in a crosstab query? I would like to have a subform that is in the format of a crosstab and I would like to be able to update the data. I found a method but haven't been able to get it to work yet. Any thoughts?

 

[strive4peace]

Hi Steve,

> "format of a crosstab and I would like to be able to update the data"

2 ideas come to mind

you obviously cannot use a crosstab since they are not updateable. Sometimes what I do is use a subform for each column -- and use it multiple times. The advantage of this method is that you can work with data in the tables directly. The disadvantage is that, unless you write code, Tab and Shift-Tab will not move the user across.

Alternately, you can use a temporary table for editing. In these cases, I delete data, not records (to minimize bloat if it is in the same database), and fill/clear the table as needed. To start, you can populate the table from a crosstab but you will need to write code to loop and put the data back ... actually you may not be able to use a crosstab in an update query -- don't often use this method. When I create temporary tables, I usually make them to a scratch database then use code to link to them -- then there is no worry about bloating -- just create the database each time temporary tables are needed