Control Source Expression is too Complex

[Jim Johnson]

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.

 

[boblarson]

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.

--
Bob Larson
Access World Forums Super Moderator

Tutorials at http://www.btabdevelopment.com

__________________________________

 

[Jim Johnson]

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

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

 

[boblarson]

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).
--
Bob Larson
Access World Forums Super Moderator

Tutorials at http://www.btabdevelopment.com

__________________________________

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

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

 

[Dirk Goldgar]

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

I think you mean CONTROL source.

 

[boblarson]

Yep, - control source (tongue got tangled up there for a moment)
--
Bob Larson
Access World Forums Super Moderator

Tutorials at http://www.btabdevelopment.com

__________________________________