Control Source Expression is too Complex

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jim Johnson - 14 Apr 2008 19:59 GMT

I am using =Choose() command to display only current users of my database.
This allows me to limit the drop-down list of current users only while
keeping the records of past users intact. This has worked well, until now.

Apparently, there is a limit to the number of values for the "choose"
expression. When I tried to had the name of a new employee to my expression
in the control source, I got the following error:

"The expression you entered is too complex."

Can someone please help me to simplify the expression? It is simply
=Choose([Requestor],"Name1","Name2",..."Name27","Name28"). Once I add the
29th name and tab to the next field, I get the above error. Thanks in
advance.

Reply to this Message

boblarson - 14 Apr 2008 21:09 GMT

Why not just base the combo's rowsource on a query and, you should have a
boolean field in your employees table to mark an employee as InActive. So
your query could pull

Select tblEmployees.EmployeeID, tblEmployees.EmployeeName FROM tblEmployees
Where tblEmployees.InActive = False

No choose or typing in employees all the time is necessary. Just add them
to the table and they are there. If they leave check the InActive box and
they disappear from the combo.

Signature

Bob Larson
Access World Forums Super Moderator

Tutorials at http://www.btabdevelopment.com

__________________________________

> I am using =Choose() command to display only current users of my database.
> This allows me to limit the drop-down list of current users only while
[quoted text clipped - 10 lines]
> 29th name and tab to the next field, I get the above error. Thanks in
> advance.

Reply to this Message

Jim Johnson - 15 Apr 2008 21:05 GMT

Hi Bob,

I was able to create the select query but I'm not quite sure how to pull the
RequestorName into the control. Can you please help me with what to put in
the control source for this field? I have pasted the SQL of the query below.

SELECT tblRequestor.RequestorName, tblRequestor.Function, tblRequestor.Active
FROM tblRequestor
WHERE (((tblRequestor.Active)=True))
ORDER BY tblRequestor.RequestorID;

Thank you for your assistance,
Jim

> Why not just base the combo's rowsource on a query and, you should have a
> boolean field in your employees table to mark an employee as InActive. So
[quoted text clipped - 21 lines]
> > 29th name and tab to the next field, I get the above error. Thanks in
> > advance.

Reply to this Message

boblarson - 16 Apr 2008 04:16 GMT

That select statement should be in the combo box's ROW SOURCE property.
Then, you bind that combo box's RECORD source to the ID field on your form.

If you aren't sure about how the combo works, check out the samples on my
website (see below).

Signature

Bob Larson
Access World Forums Super Moderator

Tutorials at http://www.btabdevelopment.com

__________________________________

> Hi Bob,
>
[quoted text clipped - 35 lines]
> > > 29th name and tab to the next field, I get the above error. Thanks in
> > > advance.

Reply to this Message

Dirk Goldgar - 16 Apr 2008 04:24 GMT

> Then, you bind that combo box's RECORD source to the ID field on your
> form.

I think you mean CONTROL source.

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

boblarson - 16 Apr 2008 05:57 GMT

Yep, - control source (tongue got tangled up there for a moment)

Signature

Bob Larson
Access World Forums Super Moderator

Tutorials at http://www.btabdevelopment.com

__________________________________

> > Then, you bind that combo box's RECORD source to the ID field on your
> > form.
>
> I think you mean CONTROL source.

Reply to this Message