Expression builder limitation

[Guest]

Hi,
I've run into a limitation in number of characters that can be used in the
Expression Builder the following expression:

[Forms]![MONITORING_DATA_ENTRY]![Abbr]
& "-" &
[Forms]![SEARCH_AREA]![SURVEY].[Form]![OBSERVATION].[Form]![Trap_Line_ID]
& "-" &
[Forms]![SEARCH_AREA]![SURVEY].[Form]![OBSERVATION].[Form]![Trap_ID]
& "-" &
[Forms]![SEARCH_AREA]![SURVEY].[Form]![Start_Date]

it gives me an error "Microsoft Access can't parse the expression:...."
but when I shorten the query by one expression i.e remove & "-" &
[Forms]![SEARCH_AREA]![SURVEY].[Form]![Start_Date] it works fine.

What I'm trying to do is get a field in the form to update when it gets
focus, the value has to come in from all over the forms and subforms I have
to create an ID.

Does anyone know if there is a limitation to how many characters are allowed
in the expression builder box? Is there a way to increase it? Any
suggestions how to fix this issue or work around it? I suppose one way would
be to use the ON GOT FOCUS to run a code instead of a macro but I was hoping
to avoid that.
Thanks in advance for lending your brain cells on this problem.
Marco

 

[Brendan Reynolds]

The Expression Builder doesn't do anything that you can't do yourself
without using the Expression Builder, so if this was just a limitation of
the Expression Builder, you could simply enter the expression manually.
However, according to the help topic 'Access Specifications', the maximum
number of characters in a macro condition or a macro action argument are
both limited to 255, so you may be unable to use this expression in your
macro even if you enter it manually.

 

[Guest]

Thanks Brendan,
This is exactly what I thought, I'm going to do it by using an Event
Procedure, too bad about the 255 character limitation.
Marco

The Expression Builder doesn't do anything that you can't do yourself
without using the Expression Builder, so if this was just a limitation of
the Expression Builder, you could simply enter the expression manually.
However, according to the help topic 'Access Specifications', the maximum
number of characters in a macro condition or a macro action argument are
both limited to 255, so you may be unable to use this expression in your
macro even if you enter it manually.

--
Brendan Reynolds
Access MVP

Hi,
I've run into a limitation in number of characters that can be used in the
Expression Builder the following expression:

[Forms]![MONITORING_DATA_ENTRY]![Abbr]
& "-" &
[Forms]![SEARCH_AREA]![SURVEY].[Form]![OBSERVATION].[Form]![Trap_Line_ID]
& "-" &
[Forms]![SEARCH_AREA]![SURVEY].[Form]![OBSERVATION].[Form]![Trap_ID]
& "-" &
[Forms]![SEARCH_AREA]![SURVEY].[Form]![Start_Date]

it gives me an error "Microsoft Access can't parse the expression:...."
but when I shorten the query by one expression i.e remove & "-" &
[Forms]![SEARCH_AREA]![SURVEY].[Form]![Start_Date] it works fine.

What I'm trying to do is get a field in the form to update when it gets
focus, the value has to come in from all over the forms and subforms I
have
to create an ID.

Does anyone know if there is a limitation to how many characters are
allowed
in the expression builder box? Is there a way to increase it? Any
suggestions how to fix this issue or work around it? I suppose one way
would
be to use the ON GOT FOCUS to run a code instead of a macro but I was
hoping
to avoid that.
Thanks in advance for lending your brain cells on this problem.
Marco

 

[Steve Schapel]

Marco,

You could try to shorten the name of some of the forms.

Also, it is not clear which form the macro is being run from, but
whatever it is, you don't need the reference to that form in your
expression.

Also, in practice I don't really think you need the [Form] in your
references to subforms.

So, in other words, it should be very easy to get this down below the
255 character limit.

For example, if this macro is being run from an event on the
[SEARCH_AREA] form, then this should work...

[Forms]![MONITORING_DATA_ENTRY]![Abbr] & "-" &
[SURVEY]![OBSERVATION]![Trap_Line_ID] & "-" &
[SURVEY]![OBSERVATION]![Trap_ID] & "-" & [SURVEY]![Start_Date]

I assume this is in Expression argument of a SetValue action in your macro?

 

[Guest]

Hi Steve,
Thanks muchly for your advice, I did decide to shorten the name of the form
and it worked well, also the macro was being run from the OBSERVATION subform
and I tried what you said, and again it worked well! Easy to fit within the
255 character limit with tricks like these (or common sense depending on who
you are).
To answer your query, yes I was running the SetValue action in the macro.
Thanks again for your help!
Marco

Marco,

You could try to shorten the name of some of the forms.

Also, it is not clear which form the macro is being run from, but
whatever it is, you don't need the reference to that form in your
expression.

Also, in practice I don't really think you need the [Form] in your
references to subforms.

So, in other words, it should be very easy to get this down below the
255 character limit.

For example, if this macro is being run from an event on the
[SEARCH_AREA] form, then this should work...

[Forms]![MONITORING_DATA_ENTRY]![Abbr] & "-" &
[SURVEY]![OBSERVATION]![Trap_Line_ID] & "-" &
[SURVEY]![OBSERVATION]![Trap_ID] & "-" & [SURVEY]![Start_Date]

I assume this is in Expression argument of a SetValue action in your macro?

--
Steve Schapel, Microsoft Access MVP

Thanks Brendan,
This is exactly what I thought, I'm going to do it by using an Event
Procedure, too bad about the 255 character limitation.
Marco