#Name? error from Default Value expression

[Guest]

amongst other controls, my table underlying this form has a PK comprising
"Patient Number" and "Lesion Number" as they are also called on the
continuous form which is what i'm focusing on.

when a new record's to be added to the form, i use a default expression of

=[Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient Number]

which seems to work nicely so far thank you very much.

when we get to "Lesion Number" the defalut expression i used was

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]","Lesions:
Non-Target","[Patient Number] = " & [Me]![Patient Number]))

and that seems to be populating the form's control field with "#Name?".

not exactly what i'd had in mind: i.e. the first time this record gets
completed we'd expect there to be nothing in the "Lesion Number" field so the
sequence would start with the integer value '1'. the next time and every
other when a new record got added via the form we'd expect the Lesion Number
to be incrementally larger by a factor of 1.

so what's up with that?

anyone care to guess?

 

[Andreas]

Use square brackets for your datasource (necessary because of your
naming convention):

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]","[Lesions:
Non-Target]","[Patient Number] = " & [Me]![Patient Number]))

Also, if your patient number is not numeric, you will need to embed the
appropriate delimeters. For text:
"[Patient Number] = '" & [Me]![Patient Number] & "'"

Regards,
Andreas

 

[tina]

where are you using the IIf() expression? if it's in the ControlSource of a
control on the form, then take a look at the control's Name property - it
cannot be the same as any of the field names used in the expression (that's
called a "circular reference" which Access won't allow in forms and
reports).

if that's not the problem, verify that all the "names" in the expression are
spelled correctly. you've probably realized by now that putting spaces or
special characters in object names is not desirable, so you know to avoid
doing that in future databases.

if all the names are correct and the expression still returns "#Name?",
suggest you deconstruct the IIf() expression to see what part of it is
causing the problem. do that by replacing each argument, one at a time, with
a hard value, and then testing the expression, until it runs correctly. at
that point you'll know where the problem lies, and can work on fixing it.
for example, try

=IIf(True, 1, ...)
=IIf(False, 1, ...)
=IIf([Lesion Number] Is Null, 1, ...)

hth

 

[Rob Oldfield]

See response in your other thread on this problem.

 

[Guest]

what's different about your rendition of my IIF statement and the one i
wrote? i didn't understand this.

-ted

ps: yes, it's numeric.

Use square brackets for your datasource (necessary because of your
naming convention):

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]","[Lesions:
Non-Target]","[Patient Number] = " & [Me]![Patient Number]))

Also, if your patient number is not numeric, you will need to embed the
appropriate delimeters. For text:
"[Patient Number] = '" & [Me]![Patient Number] & "'"

Regards,
Andreas

amongst other controls, my table underlying this form has a PK comprising
"Patient Number" and "Lesion Number" as they are also called on the
continuous form which is what i'm focusing on.

when a new record's to be added to the form, i use a default expression of

=[Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient Number]

which seems to work nicely so far thank you very much.

when we get to "Lesion Number" the defalut expression i used was

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]","Lesions:
Non-Target","[Patient Number] = " & [Me]![Patient Number]))

and that seems to be populating the form's control field with "#Name?".

not exactly what i'd had in mind: i.e. the first time this record gets
completed we'd expect there to be nothing in the "Lesion Number" field so the
sequence would start with the integer value '1'. the next time and every
other when a new record got added via the form we'd expect the Lesion Number
to be incrementally larger by a factor of 1.

so what's up with that?

anyone care to guess?

 

[Guest]

tina,

where are you using the IIf() expression? if it's in the ControlSource of a
control on the form, then take a look at the control's Name property - it
cannot be the same as any of the field names used in the expression (that's
called a "circular reference" which Access won't allow in forms and
reports).

i'm using it in the Default property of each control's so this eliminates
the possibility

if that's not the problem, verify that all the "names" in the expression are
spelled correctly. you've probably realized by now that putting spaces or
special characters in object names is not desirable, so you know to avoid
doing that in future databases.

i'm a believer in making these 'readable' by beginners

if all the names are correct and the expression still returns "#Name?",
suggest you deconstruct the IIf() expression to see what part of it is
causing the problem. do that by replacing each argument, one at a time, with
a hard value, and then testing the expression, until it runs correctly. at
that point you'll know where the problem lies, and can work on fixing it.
for example, try

thanks for the example, tina. i will try.

=IIf(True, 1, ...)
=IIf(False, 1, ...)
=IIf([Lesion Number] Is Null, 1, ...)

hth

amongst other controls, my table underlying this form has a PK comprising
"Patient Number" and "Lesion Number" as they are also called on the
continuous form which is what i'm focusing on.

when a new record's to be added to the form, i use a default expression of

=[Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient Number]

which seems to work nicely so far thank you very much.

when we get to "Lesion Number" the defalut expression i used was

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]","Lesions:
Non-Target","[Patient Number] = " & [Me]![Patient Number]))

and that seems to be populating the form's control field with "#Name?".

not exactly what i'd had in mind: i.e. the first time this record gets
completed we'd expect there to be nothing in the "Lesion Number" field so the
sequence would start with the integer value '1'. the next time and every
other when a new record got added via the form we'd expect the Lesion Number
to be incrementally larger by a factor of 1.

so what's up with that?

anyone care to guess?

 

[Guest]

......tina,

i rethought the meaning of your

if all the names are correct and the expression still returns "#Name?",
suggest you deconstruct the IIf() expression to see what part of it is
causing the problem. do that by replacing each argument, one at a time, with
a hard value, and then testing the expression, until it runs correctly. at
that point you'll know where the problem lies, and can work on fixing it.
for example, try

=IIf(True, 1, ...)
=IIf(False, 1, ...)
=IIf([Lesion Number] Is Null, 1, ...)

above and decided i didn't really understand what that code up there meant.
take the first =IIf(True, 1, ...) ...is that supposed to mean you substitute
the literal "True" for "[Lesion Number]"? that's kind of hard to believe upon
reflection...what's to tell a2k i'm talking about Lesion Number? what am i
not seeing?

-ted

where are you using the IIf() expression? if it's in the ControlSource of a
control on the form, then take a look at the control's Name property - it
cannot be the same as any of the field names used in the expression (that's
called a "circular reference" which Access won't allow in forms and
reports).

if that's not the problem, verify that all the "names" in the expression are
spelled correctly. you've probably realized by now that putting spaces or
special characters in object names is not desirable, so you know to avoid
doing that in future databases.

if all the names are correct and the expression still returns "#Name?",
suggest you deconstruct the IIf() expression to see what part of it is
causing the problem. do that by replacing each argument, one at a time, with
a hard value, and then testing the expression, until it runs correctly. at
that point you'll know where the problem lies, and can work on fixing it.
for example, try

=IIf(True, 1, ...)
=IIf(False, 1, ...)
=IIf([Lesion Number] Is Null, 1, ...)

hth

amongst other controls, my table underlying this form has a PK comprising
"Patient Number" and "Lesion Number" as they are also called on the
continuous form which is what i'm focusing on.

when a new record's to be added to the form, i use a default expression of

=[Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient Number]

which seems to work nicely so far thank you very much.

when we get to "Lesion Number" the defalut expression i used was

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]","Lesions:
Non-Target","[Patient Number] = " & [Me]![Patient Number]))

and that seems to be populating the form's control field with "#Name?".

not exactly what i'd had in mind: i.e. the first time this record gets
completed we'd expect there to be nothing in the "Lesion Number" field so the
sequence would start with the integer value '1'. the next time and every
other when a new record got added via the form we'd expect the Lesion Number
to be incrementally larger by a factor of 1.

so what's up with that?

anyone care to guess?

 

[Andreas]

In your DMax function, you used:
"Lesions: Non-Target"
I used:
"[Lesions: Non-Target]"


Also, you might want to make this additional change:

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]",
"[Lesions: Non-Target]","[Patient Number] = " & [Forms]![RECIST Disease
Evaluation: Nontarget Lesions]![Patient Number]))

Regards,
Andreas

what's different about your rendition of my IIF statement and the one i
wrote? i didn't understand this.

-ted

ps: yes, it's numeric.


:

Use square brackets for your datasource (necessary because of your
naming convention):

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]","[Lesions:
Non-Target]","[Patient Number] = " & [Me]![Patient Number]))

Also, if your patient number is not numeric, you will need to embed the
appropriate delimeters. For text:
"[Patient Number] = '" & [Me]![Patient Number] & "'"

Regards,
Andreas

amongst other controls, my table underlying this form has a PK comprising
"Patient Number" and "Lesion Number" as they are also called on the
continuous form which is what i'm focusing on.

when a new record's to be added to the form, i use a default expression of

=[Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient Number]

which seems to work nicely so far thank you very much.

when we get to "Lesion Number" the defalut expression i used was

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]","Lesions:
Non-Target","[Patient Number] = " & [Me]![Patient Number]))

and that seems to be populating the form's control field with "#Name?".

not exactly what i'd had in mind: i.e. the first time this record gets
completed we'd expect there to be nothing in the "Lesion Number" field so the
sequence would start with the integer value '1'. the next time and every
other when a new record got added via the form we'd expect the Lesion Number
to be incrementally larger by a factor of 1.

so what's up with that?

anyone care to guess?

 

[Guest]

hi andreas,

so as it stands, the Patient Number default was as before:

=[Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient Number]

and the Lesion Number default is now:

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]","[Lesions:
Non-Target]","[Patient Number] = " & [Forms]![RECIST Disease
Evaluation: Nontarget Lesions]![Patient Number]))

which i'm prepared to believe is what you proposed.

still though.......no cigar? hmmmm....

what could it be this time? did it matter that the controls Patient and
Lesion number are comboboxes and not textboxes?

In your DMax function, you used:
"Lesions: Non-Target"
I used:
"[Lesions: Non-Target]"


Also, you might want to make this additional change:

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]",
"[Lesions: Non-Target]","[Patient Number] = " & [Forms]![RECIST Disease
Evaluation: Nontarget Lesions]![Patient Number]))

Regards,
Andreas

what's different about your rendition of my IIF statement and the one i
wrote? i didn't understand this.

-ted

ps: yes, it's numeric.


:

Use square brackets for your datasource (necessary because of your
naming convention):

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]","[Lesions:
Non-Target]","[Patient Number] = " & [Me]![Patient Number]))

Also, if your patient number is not numeric, you will need to embed the
appropriate delimeters. For text:
"[Patient Number] = '" & [Me]![Patient Number] & "'"

Regards,
Andreas


Ted wrote:

amongst other controls, my table underlying this form has a PK comprising
"Patient Number" and "Lesion Number" as they are also called on the
continuous form which is what i'm focusing on.

when a new record's to be added to the form, i use a default expression of

=[Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient Number]

which seems to work nicely so far thank you very much.

when we get to "Lesion Number" the defalut expression i used was

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]","Lesions:
Non-Target","[Patient Number] = " & [Me]![Patient Number]))

and that seems to be populating the form's control field with "#Name?".

not exactly what i'd had in mind: i.e. the first time this record gets
completed we'd expect there to be nothing in the "Lesion Number" field so the
sequence would start with the integer value '1'. the next time and every
other when a new record got added via the form we'd expect the Lesion Number
to be incrementally larger by a factor of 1.

so what's up with that?

anyone care to guess?

 

[Andreas]

Actually, thinking about it, the problem is quite obvious.
Just didn't see it before.
You are going to a new record.
What value will Lesion Number have there?

Try:
Nz(DMax("[Lesion Number]","[Lesions: Non-Target]","[Patient Number] = "
& [Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient
Number]),0) + 1

Regards,
Andreas

hi andreas,

so as it stands, the Patient Number default was as before:

=[Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient Number]

and the Lesion Number default is now:

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]","[Lesions:
Non-Target]","[Patient Number] = " & [Forms]![RECIST Disease
Evaluation: Nontarget Lesions]![Patient Number]))

which i'm prepared to believe is what you proposed.

still though.......no cigar? hmmmm....

what could it be this time? did it matter that the controls Patient and
Lesion number are comboboxes and not textboxes?


:

In your DMax function, you used:
"Lesions: Non-Target"
I used:
"[Lesions: Non-Target]"


Also, you might want to make this additional change:

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]",
"[Lesions: Non-Target]","[Patient Number] = " & [Forms]![RECIST Disease
Evaluation: Nontarget Lesions]![Patient Number]))

Regards,
Andreas

what's different about your rendition of my IIF statement and the one i
wrote? i didn't understand this.

-ted

ps: yes, it's numeric.


:



Use square brackets for your datasource (necessary because of your
naming convention):

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]","[Lesions:
Non-Target]","[Patient Number] = " & [Me]![Patient Number]))

Also, if your patient number is not numeric, you will need to embed the
appropriate delimeters. For text:
"[Patient Number] = '" & [Me]![Patient Number] & "'"

Regards,
Andreas


Ted wrote:


amongst other controls, my table underlying this form has a PK comprising
"Patient Number" and "Lesion Number" as they are also called on the
continuous form which is what i'm focusing on.

when a new record's to be added to the form, i use a default expression of

=[Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient Number]

which seems to work nicely so far thank you very much.

when we get to "Lesion Number" the defalut expression i used was

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]","Lesions:
Non-Target","[Patient Number] = " & [Me]![Patient Number]))

and that seems to be populating the form's control field with "#Name?".

not exactly what i'd had in mind: i.e. the first time this record gets
completed we'd expect there to be nothing in the "Lesion Number" field so the
sequence would start with the integer value '1'. the next time and every
other when a new record got added via the form we'd expect the Lesion Number
to be incrementally larger by a factor of 1.

so what's up with that?

anyone care to guess?

 

[Guest]

let me try this one .....keeping my fingers X-ed!

talk to you later.

-ted

Actually, thinking about it, the problem is quite obvious.
Just didn't see it before.
You are going to a new record.
What value will Lesion Number have there?

Try:
Nz(DMax("[Lesion Number]","[Lesions: Non-Target]","[Patient Number] = "
& [Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient
Number]),0) + 1

Regards,
Andreas

hi andreas,

so as it stands, the Patient Number default was as before:

=[Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient Number]

and the Lesion Number default is now:

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]","[Lesions:
Non-Target]","[Patient Number] = " & [Forms]![RECIST Disease
Evaluation: Nontarget Lesions]![Patient Number]))

which i'm prepared to believe is what you proposed.

still though.......no cigar? hmmmm....

what could it be this time? did it matter that the controls Patient and
Lesion number are comboboxes and not textboxes?


:

In your DMax function, you used:
"Lesions: Non-Target"
I used:
"[Lesions: Non-Target]"


Also, you might want to make this additional change:

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]",
"[Lesions: Non-Target]","[Patient Number] = " & [Forms]![RECIST Disease
Evaluation: Nontarget Lesions]![Patient Number]))

Regards,
Andreas


Ted wrote:

what's different about your rendition of my IIF statement and the one i
wrote? i didn't understand this.

-ted

ps: yes, it's numeric.


:



Use square brackets for your datasource (necessary because of your
naming convention):

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]","[Lesions:
Non-Target]","[Patient Number] = " & [Me]![Patient Number]))

Also, if your patient number is not numeric, you will need to embed the
appropriate delimeters. For text:
"[Patient Number] = '" & [Me]![Patient Number] & "'"

Regards,
Andreas


Ted wrote:


amongst other controls, my table underlying this form has a PK comprising
"Patient Number" and "Lesion Number" as they are also called on the
continuous form which is what i'm focusing on.

when a new record's to be added to the form, i use a default expression of

=[Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient Number]

which seems to work nicely so far thank you very much.

when we get to "Lesion Number" the defalut expression i used was

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]","Lesions:
Non-Target","[Patient Number] = " & [Me]![Patient Number]))

and that seems to be populating the form's control field with "#Name?".

not exactly what i'd had in mind: i.e. the first time this record gets
completed we'd expect there to be nothing in the "Lesion Number" field so the
sequence would start with the integer value '1'. the next time and every
other when a new record got added via the form we'd expect the Lesion Number
to be incrementally larger by a factor of 1.

so what's up with that?

anyone care to guess?

 

[Guest]

here's the code i pasted into the Default property setting of "Lesion Number"
(one of the two PK fields, the other being "Patient Number")

=Nz(DMax("[Lesion Number]","[Lesions: Non-Target]","[Patient Number] = " &
[Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient
Number]),0)+1

it's resulted in "#Error" ??

Actually, thinking about it, the problem is quite obvious.
Just didn't see it before.
You are going to a new record.
What value will Lesion Number have there?

Try:
Nz(DMax("[Lesion Number]","[Lesions: Non-Target]","[Patient Number] = "
& [Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient
Number]),0) + 1

Regards,
Andreas

hi andreas,

so as it stands, the Patient Number default was as before:

=[Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient Number]

and the Lesion Number default is now:

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]","[Lesions:
Non-Target]","[Patient Number] = " & [Forms]![RECIST Disease
Evaluation: Nontarget Lesions]![Patient Number]))

which i'm prepared to believe is what you proposed.

still though.......no cigar? hmmmm....

what could it be this time? did it matter that the controls Patient and
Lesion number are comboboxes and not textboxes?


:

In your DMax function, you used:
"Lesions: Non-Target"
I used:
"[Lesions: Non-Target]"


Also, you might want to make this additional change:

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]",
"[Lesions: Non-Target]","[Patient Number] = " & [Forms]![RECIST Disease
Evaluation: Nontarget Lesions]![Patient Number]))

Regards,
Andreas


Ted wrote:

what's different about your rendition of my IIF statement and the one i
wrote? i didn't understand this.

-ted

ps: yes, it's numeric.


:



Use square brackets for your datasource (necessary because of your
naming convention):

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]","[Lesions:
Non-Target]","[Patient Number] = " & [Me]![Patient Number]))

Also, if your patient number is not numeric, you will need to embed the
appropriate delimeters. For text:
"[Patient Number] = '" & [Me]![Patient Number] & "'"

Regards,
Andreas


Ted wrote:


amongst other controls, my table underlying this form has a PK comprising
"Patient Number" and "Lesion Number" as they are also called on the
continuous form which is what i'm focusing on.

when a new record's to be added to the form, i use a default expression of

=[Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient Number]

which seems to work nicely so far thank you very much.

when we get to "Lesion Number" the defalut expression i used was

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]","Lesions:
Non-Target","[Patient Number] = " & [Me]![Patient Number]))

and that seems to be populating the form's control field with "#Name?".

not exactly what i'd had in mind: i.e. the first time this record gets
completed we'd expect there to be nothing in the "Lesion Number" field so the
sequence would start with the integer value '1'. the next time and every
other when a new record got added via the form we'd expect the Lesion Number
to be incrementally larger by a factor of 1.

so what's up with that?

anyone care to guess?

 

[Guest]

i did snooping in the Access KB website and came up w/ KB 209785 which speaks
to a prohibition against using circular references to a control name in both
the Name and Control Source properties.

a quote

"If you create a form based on a table or query and drag a numeric field
onto the form, by default the ControlSource property and the Name property
are given the name of the actual field in the underlying table or query. If
you change the ControlSource property to an expression that includes the Name
property, you see "#Error" in this field when you switch to Form or Datasheet
view.

The following example demonstrates an expression within the ControlSource
property:
Name: Person_ID
ControlSource: =[Person_ID] + 1 "

Actually, thinking about it, the problem is quite obvious.
Just didn't see it before.
You are going to a new record.
What value will Lesion Number have there?

Try:
Nz(DMax("[Lesion Number]","[Lesions: Non-Target]","[Patient Number] = "
& [Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient
Number]),0) + 1

Regards,
Andreas

hi andreas,

so as it stands, the Patient Number default was as before:

=[Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient Number]

and the Lesion Number default is now:

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]","[Lesions:
Non-Target]","[Patient Number] = " & [Forms]![RECIST Disease
Evaluation: Nontarget Lesions]![Patient Number]))

which i'm prepared to believe is what you proposed.

still though.......no cigar? hmmmm....

what could it be this time? did it matter that the controls Patient and
Lesion number are comboboxes and not textboxes?


:

In your DMax function, you used:
"Lesions: Non-Target"
I used:
"[Lesions: Non-Target]"


Also, you might want to make this additional change:

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]",
"[Lesions: Non-Target]","[Patient Number] = " & [Forms]![RECIST Disease
Evaluation: Nontarget Lesions]![Patient Number]))

Regards,
Andreas


Ted wrote:

what's different about your rendition of my IIF statement and the one i
wrote? i didn't understand this.

-ted

ps: yes, it's numeric.


:



Use square brackets for your datasource (necessary because of your
naming convention):

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]","[Lesions:
Non-Target]","[Patient Number] = " & [Me]![Patient Number]))

Also, if your patient number is not numeric, you will need to embed the
appropriate delimeters. For text:
"[Patient Number] = '" & [Me]![Patient Number] & "'"

Regards,
Andreas


Ted wrote:


amongst other controls, my table underlying this form has a PK comprising
"Patient Number" and "Lesion Number" as they are also called on the
continuous form which is what i'm focusing on.

when a new record's to be added to the form, i use a default expression of

=[Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient Number]

which seems to work nicely so far thank you very much.

when we get to "Lesion Number" the defalut expression i used was

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]","Lesions:
Non-Target","[Patient Number] = " & [Me]![Patient Number]))

and that seems to be populating the form's control field with "#Name?".

not exactly what i'd had in mind: i.e. the first time this record gets
completed we'd expect there to be nothing in the "Lesion Number" field so the
sequence would start with the integer value '1'. the next time and every
other when a new record got added via the form we'd expect the Lesion Number
to be incrementally larger by a factor of 1.

so what's up with that?

anyone care to guess?

 

[Andreas]

Last attempt:
- Create a query in design view.
- Add the table [Lesions: Non-Target] as your datasource.
- Bring in the fields [Lesion Number] and [Patient Number].
- Click on the Sigma button on the toolbar to change the query to a
Summary Totals query.
- In the Totals row for the field [Lesion Number], select Max.
- In the Totals row for the field [Patient Number], select Where.
- In the Criteria row for the field [Patient Number], enter
[Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient Number]
- In the Field row for the field [Lesion Number], in front of the text
that is already there, type LN:
- Save the query as "qryGetPatientNumber" (without the quotes).

In the form where you want this as a default value, select the control
and set the ControlSource property to:
=Nz(DLookUp("LN","qryGetPatientNumber"),0)+1

Provided the default for the Patient number works (in the other control)
this should work, unless there is a problem with your names or data
types. If this does not work, put a blank, new text box on the form and
enter the above and see what value is returned. Just to make sure, I
take it the Lesion number is a number field?

Regards,
Andreas


Nz(DMax("[Lesion Number]","[Lesions: Non-Target]","[Patient Number] = "

& [Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient
Number]),0) + 1

i did snooping in the Access KB website and came up w/ KB 209785 which speaks
to a prohibition against using circular references to a control name in both
the Name and Control Source properties.

a quote

"If you create a form based on a table or query and drag a numeric field
onto the form, by default the ControlSource property and the Name property
are given the name of the actual field in the underlying table or query. If
you change the ControlSource property to an expression that includes the Name
property, you see "#Error" in this field when you switch to Form or Datasheet
view.

The following example demonstrates an expression within the ControlSource
property:
Name: Person_ID
ControlSource: =[Person_ID] + 1 "



:

Actually, thinking about it, the problem is quite obvious.
Just didn't see it before.
You are going to a new record.
What value will Lesion Number have there?

Try:
Nz(DMax("[Lesion Number]","[Lesions: Non-Target]","[Patient Number] = "
& [Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient
Number]),0) + 1

Regards,
Andreas

hi andreas,

so as it stands, the Patient Number default was as before:

=[Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient Number]

and the Lesion Number default is now:

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]","[Lesions:
Non-Target]","[Patient Number] = " & [Forms]![RECIST Disease
Evaluation: Nontarget Lesions]![Patient Number]))

which i'm prepared to believe is what you proposed.

still though.......no cigar? hmmmm....

what could it be this time? did it matter that the controls Patient and
Lesion number are comboboxes and not textboxes?


:



In your DMax function, you used:
"Lesions: Non-Target"
I used:
"[Lesions: Non-Target]"


Also, you might want to make this additional change:

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]",
"[Lesions: Non-Target]","[Patient Number] = " & [Forms]![RECIST Disease
Evaluation: Nontarget Lesions]![Patient Number]))

Regards,
Andreas


Ted wrote:


what's different about your rendition of my IIF statement and the one i
wrote? i didn't understand this.

-ted

ps: yes, it's numeric.


:




Use square brackets for your datasource (necessary because of your
naming convention):

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]","[Lesions:
Non-Target]","[Patient Number] = " & [Me]![Patient Number]))

Also, if your patient number is not numeric, you will need to embed the
appropriate delimeters. For text:
"[Patient Number] = '" & [Me]![Patient Number] & "'"

Regards,
Andreas


Ted wrote:



amongst other controls, my table underlying this form has a PK comprising
"Patient Number" and "Lesion Number" as they are also called on the
continuous form which is what i'm focusing on.

when a new record's to be added to the form, i use a default expression of

=[Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient Number]

which seems to work nicely so far thank you very much.

when we get to "Lesion Number" the defalut expression i used was

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]","Lesions:
Non-Target","[Patient Number] = " & [Me]![Patient Number]))

and that seems to be populating the form's control field with "#Name?".

not exactly what i'd had in mind: i.e. the first time this record gets
completed we'd expect there to be nothing in the "Lesion Number" field so the
sequence would start with the integer value '1'. the next time and every
other when a new record got added via the form we'd expect the Lesion Number
to be incrementally larger by a factor of 1.

so what's up with that?

anyone care to guess?

 

[Guest]

andreas,

i think i've run out of chits

at the point where i typed "NLLesion Number" and attempted to save it as
qryGetPatientNumber, it returns an error message "The expression you entered
contains invalid syntax" followed by "You may have entered an operand without
an operator".

bummer. i've got another approach i'm going to try but thanks a 1,000,000
for all the help and bandwidth.

-ted

Last attempt:
- Create a query in design view.
- Add the table [Lesions: Non-Target] as your datasource.
- Bring in the fields [Lesion Number] and [Patient Number].
- Click on the Sigma button on the toolbar to change the query to a
Summary Totals query.
- In the Totals row for the field [Lesion Number], select Max.
- In the Totals row for the field [Patient Number], select Where.
- In the Criteria row for the field [Patient Number], enter
[Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient Number]
- In the Field row for the field [Lesion Number], in front of the text
that is already there, type LN:
- Save the query as "qryGetPatientNumber" (without the quotes).

In the form where you want this as a default value, select the control
and set the ControlSource property to:
=Nz(DLookUp("LN","qryGetPatientNumber"),0)+1

Provided the default for the Patient number works (in the other control)
this should work, unless there is a problem with your names or data
types. If this does not work, put a blank, new text box on the form and
enter the above and see what value is returned. Just to make sure, I
take it the Lesion number is a number field?

Regards,
Andreas


Nz(DMax("[Lesion Number]","[Lesions: Non-Target]","[Patient Number] = "

& [Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient
Number]),0) + 1

i did snooping in the Access KB website and came up w/ KB 209785 which speaks
to a prohibition against using circular references to a control name in both
the Name and Control Source properties.

a quote

"If you create a form based on a table or query and drag a numeric field
onto the form, by default the ControlSource property and the Name property
are given the name of the actual field in the underlying table or query. If
you change the ControlSource property to an expression that includes the Name
property, you see "#Error" in this field when you switch to Form or Datasheet
view.

The following example demonstrates an expression within the ControlSource
property:
Name: Person_ID
ControlSource: =[Person_ID] + 1 "



:

Actually, thinking about it, the problem is quite obvious.
Just didn't see it before.
You are going to a new record.
What value will Lesion Number have there?

Try:
Nz(DMax("[Lesion Number]","[Lesions: Non-Target]","[Patient Number] = "
& [Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient
Number]),0) + 1

Regards,
Andreas


Ted wrote:

hi andreas,

so as it stands, the Patient Number default was as before:

=[Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient Number]

and the Lesion Number default is now:

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]","[Lesions:
Non-Target]","[Patient Number] = " & [Forms]![RECIST Disease
Evaluation: Nontarget Lesions]![Patient Number]))

which i'm prepared to believe is what you proposed.

still though.......no cigar? hmmmm....

what could it be this time? did it matter that the controls Patient and
Lesion number are comboboxes and not textboxes?


:



In your DMax function, you used:
"Lesions: Non-Target"
I used:
"[Lesions: Non-Target]"


Also, you might want to make this additional change:

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]",
"[Lesions: Non-Target]","[Patient Number] = " & [Forms]![RECIST Disease
Evaluation: Nontarget Lesions]![Patient Number]))

Regards,
Andreas


Ted wrote:


what's different about your rendition of my IIF statement and the one i
wrote? i didn't understand this.

-ted

ps: yes, it's numeric.


:




Use square brackets for your datasource (necessary because of your
naming convention):

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]","[Lesions:
Non-Target]","[Patient Number] = " & [Me]![Patient Number]))

Also, if your patient number is not numeric, you will need to embed the
appropriate delimeters. For text:
"[Patient Number] = '" & [Me]![Patient Number] & "'"

Regards,
Andreas


Ted wrote:



amongst other controls, my table underlying this form has a PK comprising
"Patient Number" and "Lesion Number" as they are also called on the
continuous form which is what i'm focusing on.

when a new record's to be added to the form, i use a default expression of

=[Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient Number]

which seems to work nicely so far thank you very much.

when we get to "Lesion Number" the defalut expression i used was

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]","Lesions:
Non-Target","[Patient Number] = " & [Me]![Patient Number]))

and that seems to be populating the form's control field with "#Name?".

not exactly what i'd had in mind: i.e. the first time this record gets
completed we'd expect there to be nothing in the "Lesion Number" field so the
sequence would start with the integer value '1'. the next time and every
other when a new record got added via the form we'd expect the Lesion Number
to be incrementally larger by a factor of 1.

so what's up with that?

anyone care to guess?

 

[Andreas]

You left out the colon ).
"NL:Lesion Number" without the quotes.

Regards,
Andreas

andreas,

i think i've run out of chits

at the point where i typed "NLLesion Number" and attempted to save it as
qryGetPatientNumber, it returns an error message "The expression you entered
contains invalid syntax" followed by "You may have entered an operand without
an operator".

bummer. i've got another approach i'm going to try but thanks a 1,000,000
for all the help and bandwidth.

-ted



:

Last attempt:
- Create a query in design view.
- Add the table [Lesions: Non-Target] as your datasource.
- Bring in the fields [Lesion Number] and [Patient Number].
- Click on the Sigma button on the toolbar to change the query to a
Summary Totals query.
- In the Totals row for the field [Lesion Number], select Max.
- In the Totals row for the field [Patient Number], select Where.
- In the Criteria row for the field [Patient Number], enter
[Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient Number]
- In the Field row for the field [Lesion Number], in front of the text
that is already there, type LN:
- Save the query as "qryGetPatientNumber" (without the quotes).

In the form where you want this as a default value, select the control
and set the ControlSource property to:
=Nz(DLookUp("LN","qryGetPatientNumber"),0)+1

Provided the default for the Patient number works (in the other control)
this should work, unless there is a problem with your names or data
types. If this does not work, put a blank, new text box on the form and
enter the above and see what value is returned. Just to make sure, I
take it the Lesion number is a number field?

Regards,
Andreas


Nz(DMax("[Lesion Number]","[Lesions: Non-Target]","[Patient Number] = "

& [Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient
Number]),0) + 1

i did snooping in the Access KB website and came up w/ KB 209785 which speaks
to a prohibition against using circular references to a control name in both
the Name and Control Source properties.

a quote

"If you create a form based on a table or query and drag a numeric field
onto the form, by default the ControlSource property and the Name property
are given the name of the actual field in the underlying table or query. If
you change the ControlSource property to an expression that includes the Name
property, you see "#Error" in this field when you switch to Form or Datasheet
view.

The following example demonstrates an expression within the ControlSource
property:
Name: Person_ID
ControlSource: =[Person_ID] + 1 "



:



Actually, thinking about it, the problem is quite obvious.
Just didn't see it before.
You are going to a new record.
What value will Lesion Number have there?

Try:
Nz(DMax("[Lesion Number]","[Lesions: Non-Target]","[Patient Number] = "
& [Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient
Number]),0) + 1

Regards,
Andreas


Ted wrote:


hi andreas,

so as it stands, the Patient Number default was as before:

=[Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient Number]

and the Lesion Number default is now:

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]","[Lesions:
Non-Target]","[Patient Number] = " & [Forms]![RECIST Disease
Evaluation: Nontarget Lesions]![Patient Number]))

which i'm prepared to believe is what you proposed.

still though.......no cigar? hmmmm....

what could it be this time? did it matter that the controls Patient and
Lesion number are comboboxes and not textboxes?


:




In your DMax function, you used:
"Lesions: Non-Target"
I used:
"[Lesions: Non-Target]"


Also, you might want to make this additional change:

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]",
"[Lesions: Non-Target]","[Patient Number] = " & [Forms]![RECIST Disease
Evaluation: Nontarget Lesions]![Patient Number]))

Regards,
Andreas


Ted wrote:



what's different about your rendition of my IIF statement and the one i
wrote? i didn't understand this.

-ted

ps: yes, it's numeric.


:





Use square brackets for your datasource (necessary because of your
naming convention):

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]","[Lesions:
Non-Target]","[Patient Number] = " & [Me]![Patient Number]))

Also, if your patient number is not numeric, you will need to embed the
appropriate delimeters. For text:
"[Patient Number] = '" & [Me]![Patient Number] & "'"

Regards,
Andreas


Ted wrote:




amongst other controls, my table underlying this form has a PK comprising
"Patient Number" and "Lesion Number" as they are also called on the
continuous form which is what i'm focusing on.

when a new record's to be added to the form, i use a default expression of

=[Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient Number]

which seems to work nicely so far thank you very much.

when we get to "Lesion Number" the defalut expression i used was

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]","Lesions:
Non-Target","[Patient Number] = " & [Me]![Patient Number]))

and that seems to be populating the form's control field with "#Name?".

not exactly what i'd had in mind: i.e. the first time this record gets
completed we'd expect there to be nothing in the "Lesion Number" field so the
sequence would start with the integer value '1'. the next time and every
other when a new record got added via the form we'd expect the Lesion Number
to be incrementally larger by a factor of 1.

so what's up with that?

anyone care to guess?

 

[Guest]

this is getting so allmost to the mark ......

i'm sorry about the missing punctuation. i took off yesterday and i wasn't
using my good pair of glasses i keep in my desk drawer.

here's where things stand at the present time:

definitely, Lesion Number IS a number (vs text).

the ControlSource for Lesion Number is now
"=NZ(DLookUp("LN","qryGetPatientNumber"),0)+1"

the ControlSource for Patient Number is now "=[Forms]![RECIST Disease
Evaluation: Nontarget Lesions]![Patient Number]"

the behavior's not quite there though; here's what happens when i open a new
record in the form where this is happening. a new record with the selected
Patient Number appears and it has the value '01' (format is 00) in the Lesion
Number field. the instant i enter some data in the adjoining control (e.g.
'Site of Lesion'), the "triangular" symbol at the left of the screen on this
record turns into a "pencil", a record having an "*" (asterisk) symbol is
created below the first one and the values in the Patient Number and Lesion
Number are identical with the ones seen on opening. if you proceed to enter
more data and then attempt to add data in the record having the "*" you get a
message from Access telling you that "Index or Primary key cannot contain a
null value".

You left out the colon ).
"NL:Lesion Number" without the quotes.

Regards,
Andreas

andreas,

i think i've run out of chits

at the point where i typed "NLLesion Number" and attempted to save it as
qryGetPatientNumber, it returns an error message "The expression you entered
contains invalid syntax" followed by "You may have entered an operand without
an operator".

bummer. i've got another approach i'm going to try but thanks a 1,000,000
for all the help and bandwidth.

-ted



:

Last attempt:
- Create a query in design view.
- Add the table [Lesions: Non-Target] as your datasource.
- Bring in the fields [Lesion Number] and [Patient Number].
- Click on the Sigma button on the toolbar to change the query to a
Summary Totals query.
- In the Totals row for the field [Lesion Number], select Max.
- In the Totals row for the field [Patient Number], select Where.
- In the Criteria row for the field [Patient Number], enter
[Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient Number]
- In the Field row for the field [Lesion Number], in front of the text
that is already there, type LN:
- Save the query as "qryGetPatientNumber" (without the quotes).

In the form where you want this as a default value, select the control
and set the ControlSource property to:
=Nz(DLookUp("LN","qryGetPatientNumber"),0)+1

Provided the default for the Patient number works (in the other control)
this should work, unless there is a problem with your names or data
types. If this does not work, put a blank, new text box on the form and
enter the above and see what value is returned. Just to make sure, I
take it the Lesion number is a number field?

Regards,
Andreas


Nz(DMax("[Lesion Number]","[Lesions: Non-Target]","[Patient Number] = "
& [Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient
Number]),0) + 1



Ted wrote:

i did snooping in the Access KB website and came up w/ KB 209785 which speaks
to a prohibition against using circular references to a control name in both
the Name and Control Source properties.

a quote

"If you create a form based on a table or query and drag a numeric field
onto the form, by default the ControlSource property and the Name property
are given the name of the actual field in the underlying table or query. If
you change the ControlSource property to an expression that includes the Name
property, you see "#Error" in this field when you switch to Form or Datasheet
view.

The following example demonstrates an expression within the ControlSource
property:
Name: Person_ID
ControlSource: =[Person_ID] + 1 "



:



Actually, thinking about it, the problem is quite obvious.
Just didn't see it before.
You are going to a new record.
What value will Lesion Number have there?

Try:
Nz(DMax("[Lesion Number]","[Lesions: Non-Target]","[Patient Number] = "
& [Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient
Number]),0) + 1

Regards,
Andreas


Ted wrote:


hi andreas,

so as it stands, the Patient Number default was as before:

=[Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient Number]

and the Lesion Number default is now:

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]","[Lesions:
Non-Target]","[Patient Number] = " & [Forms]![RECIST Disease
Evaluation: Nontarget Lesions]![Patient Number]))

which i'm prepared to believe is what you proposed.

still though.......no cigar? hmmmm....

what could it be this time? did it matter that the controls Patient and
Lesion number are comboboxes and not textboxes?


:




In your DMax function, you used:
"Lesions: Non-Target"
I used:
"[Lesions: Non-Target]"


Also, you might want to make this additional change:

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]",
"[Lesions: Non-Target]","[Patient Number] = " & [Forms]![RECIST Disease
Evaluation: Nontarget Lesions]![Patient Number]))

Regards,
Andreas


Ted wrote:



what's different about your rendition of my IIF statement and the one i
wrote? i didn't understand this.

-ted

ps: yes, it's numeric.


:





Use square brackets for your datasource (necessary because of your
naming convention):

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]","[Lesions:
Non-Target]","[Patient Number] = " & [Me]![Patient Number]))

Also, if your patient number is not numeric, you will need to embed the
appropriate delimeters. For text:
"[Patient Number] = '" & [Me]![Patient Number] & "'"

Regards,
Andreas


Ted wrote:




amongst other controls, my table underlying this form has a PK comprising
"Patient Number" and "Lesion Number" as they are also called on the
continuous form which is what i'm focusing on.

when a new record's to be added to the form, i use a default expression of

=[Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient Number]

which seems to work nicely so far thank you very much.

when we get to "Lesion Number" the defalut expression i used was

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]","Lesions:
Non-Target","[Patient Number] = " & [Me]![Patient Number]))

and that seems to be populating the form's control field with "#Name?".

not exactly what i'd had in mind: i.e. the first time this record gets
completed we'd expect there to be nothing in the "Lesion Number" field so the
sequence would start with the integer value '1'. the next time and every
other when a new record got added via the form we'd expect the Lesion Number
to be incrementally larger by a factor of 1.

so what's up with that?

anyone care to guess?

 

[Andreas]

Assumption: The default values are correct for the first record.

The error message for the next record is a mystery to me.
I would have expected a duplicate primary key error message.

Anyway, now that we can get the correct Lesion Number, let's make it
work "better"

- Remove the expression from the ControlSource property for the Lesion
Number textbox.
- Change the Name property for the textbox to "txtLesionNumber" (without
the quotes).
- Go to the BeforeInsert or the BeforeUpdate property of the form.
(BeforeUpdate would be my preference, especially if you don't need to
see the textbox and you make it invisible).
- Click on the 3 buttons to the right of the property and choose the
last option (can't remember what it's called but it will take you to the
Visual Basic Editor).
- In between the lines of the procedure created, enter the following:
Me.txtLesionNumber = Nz(DLookUp("LN","qryGetPatientNumber"),0)+1

Regards,
Andreas

this is getting so allmost to the mark ......

i'm sorry about the missing punctuation. i took off yesterday and i wasn't
using my good pair of glasses i keep in my desk drawer.

here's where things stand at the present time:

definitely, Lesion Number IS a number (vs text).

the ControlSource for Lesion Number is now
"=NZ(DLookUp("LN","qryGetPatientNumber"),0)+1"

the ControlSource for Patient Number is now "=[Forms]![RECIST Disease
Evaluation: Nontarget Lesions]![Patient Number]"

the behavior's not quite there though; here's what happens when i open a new
record in the form where this is happening. a new record with the selected
Patient Number appears and it has the value '01' (format is 00) in the Lesion
Number field. the instant i enter some data in the adjoining control (e.g.
'Site of Lesion'), the "triangular" symbol at the left of the screen on this
record turns into a "pencil", a record having an "*" (asterisk) symbol is
created below the first one and the values in the Patient Number and Lesion
Number are identical with the ones seen on opening. if you proceed to enter
more data and then attempt to add data in the record having the "*" you get a
message from Access telling you that "Index or Primary key cannot contain a
null value".




:

You left out the colon ).
"NL:Lesion Number" without the quotes.

Regards,
Andreas

andreas,

i think i've run out of chits

at the point where i typed "NLLesion Number" and attempted to save it as
qryGetPatientNumber, it returns an error message "The expression you entered
contains invalid syntax" followed by "You may have entered an operand without
an operator".

bummer. i've got another approach i'm going to try but thanks a 1,000,000
for all the help and bandwidth.

-ted



:



Last attempt:
- Create a query in design view.
- Add the table [Lesions: Non-Target] as your datasource.
- Bring in the fields [Lesion Number] and [Patient Number].
- Click on the Sigma button on the toolbar to change the query to a
Summary Totals query.
- In the Totals row for the field [Lesion Number], select Max.
- In the Totals row for the field [Patient Number], select Where.
- In the Criteria row for the field [Patient Number], enter
[Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient Number]
- In the Field row for the field [Lesion Number], in front of the text
that is already there, type LN:
- Save the query as "qryGetPatientNumber" (without the quotes).

In the form where you want this as a default value, select the control
and set the ControlSource property to:
=Nz(DLookUp("LN","qryGetPatientNumber"),0)+1

Provided the default for the Patient number works (in the other control)
this should work, unless there is a problem with your names or data
types. If this does not work, put a blank, new text box on the form and
enter the above and see what value is returned. Just to make sure, I
take it the Lesion number is a number field?

Regards,
Andreas


Nz(DMax("[Lesion Number]","[Lesions: Non-Target]","[Patient Number] = "

& [Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient
Number]),0) + 1



Ted wrote:


i did snooping in the Access KB website and came up w/ KB 209785 which speaks
to a prohibition against using circular references to a control name in both
the Name and Control Source properties.

a quote

"If you create a form based on a table or query and drag a numeric field
onto the form, by default the ControlSource property and the Name property
are given the name of the actual field in the underlying table or query. If
you change the ControlSource property to an expression that includes the Name
property, you see "#Error" in this field when you switch to Form or Datasheet
view.

The following example demonstrates an expression within the ControlSource
property:
Name: Person_ID
ControlSource: =[Person_ID] + 1 "



:




Actually, thinking about it, the problem is quite obvious.
Just didn't see it before.
You are going to a new record.
What value will Lesion Number have there?

Try:
Nz(DMax("[Lesion Number]","[Lesions: Non-Target]","[Patient Number] = "
& [Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient
Number]),0) + 1

Regards,
Andreas


Ted wrote:



hi andreas,

so as it stands, the Patient Number default was as before:

=[Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient Number]

and the Lesion Number default is now:

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]","[Lesions:
Non-Target]","[Patient Number] = " & [Forms]![RECIST Disease
Evaluation: Nontarget Lesions]![Patient Number]))

which i'm prepared to believe is what you proposed.

still though.......no cigar? hmmmm....

what could it be this time? did it matter that the controls Patient and
Lesion number are comboboxes and not textboxes?


:





In your DMax function, you used:
"Lesions: Non-Target"
I used:
"[Lesions: Non-Target]"


Also, you might want to make this additional change:

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]",
"[Lesions: Non-Target]","[Patient Number] = " & [Forms]![RECIST Disease
Evaluation: Nontarget Lesions]![Patient Number]))

Regards,
Andreas


Ted wrote:




what's different about your rendition of my IIF statement and the one i
wrote? i didn't understand this.

-ted

ps: yes, it's numeric.


:






Use square brackets for your datasource (necessary because of your
naming convention):

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]","[Lesions:
Non-Target]","[Patient Number] = " & [Me]![Patient Number]))

Also, if your patient number is not numeric, you will need to embed the
appropriate delimeters. For text:
"[Patient Number] = '" & [Me]![Patient Number] & "'"

Regards,
Andreas


Ted wrote:





amongst other controls, my table underlying this form has a PK comprising
"Patient Number" and "Lesion Number" as they are also called on the
continuous form which is what i'm focusing on.

when a new record's to be added to the form, i use a default expression of

=[Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient Number]

which seems to work nicely so far thank you very much.

when we get to "Lesion Number" the defalut expression i used was

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]","Lesions:
Non-Target","[Patient Number] = " & [Me]![Patient Number]))

and that seems to be populating the form's control field with "#Name?".

not exactly what i'd had in mind: i.e. the first time this record gets
completed we'd expect there to be nothing in the "Lesion Number" field so the
sequence would start with the integer value '1'. the next time and every
other when a new record got added via the form we'd expect the Lesion Number
to be incrementally larger by a factor of 1.

so what's up with that?

anyone care to guess?

 

[Andreas]

Oops, slight change here, my mistake.

This step:
- In between the lines of the procedure created, enter the following:
Me.txtLesionNumber = Nz(DLookUp("LN","qryGetPatientNumber"),0)+1

Should be:
If IsNull(Me.txtLesionNumber) Then Me.txtLesionNumber =
Nz(DLookUp("LN","qryGetPatientNumber"),0)+1

Regards,
Andreas

Assumption: The default values are correct for the first record.

The error message for the next record is a mystery to me.
I would have expected a duplicate primary key error message.

Anyway, now that we can get the correct Lesion Number, let's make it
work "better"

- Remove the expression from the ControlSource property for the Lesion
Number textbox.
- Change the Name property for the textbox to "txtLesionNumber" (without
the quotes).
- Go to the BeforeInsert or the BeforeUpdate property of the form.
(BeforeUpdate would be my preference, especially if you don't need to
see the textbox and you make it invisible).
- Click on the 3 buttons to the right of the property and choose the
last option (can't remember what it's called but it will take you to the
Visual Basic Editor).
- In between the lines of the procedure created, enter the following:
Me.txtLesionNumber = Nz(DLookUp("LN","qryGetPatientNumber"),0)+1

Regards,
Andreas

this is getting so allmost to the mark ......

i'm sorry about the missing punctuation. i took off yesterday and i
wasn't using my good pair of glasses i keep in my desk drawer.

here's where things stand at the present time:

definitely, Lesion Number IS a number (vs text).

the ControlSource for Lesion Number is now
"=NZ(DLookUp("LN","qryGetPatientNumber"),0)+1"

the ControlSource for Patient Number is now "=[Forms]![RECIST Disease
Evaluation: Nontarget Lesions]![Patient Number]"

the behavior's not quite there though; here's what happens when i open
a new record in the form where this is happening. a new record with
the selected Patient Number appears and it has the value '01' (format
is 00) in the Lesion Number field. the instant i enter some data in
the adjoining control (e.g. 'Site of Lesion'), the "triangular" symbol
at the left of the screen on this record turns into a "pencil", a
record having an "*" (asterisk) symbol is created below the first one
and the values in the Patient Number and Lesion Number are identical
with the ones seen on opening. if you proceed to enter more data and
then attempt to add data in the record having the "*" you get a
message from Access telling you that "Index or Primary key cannot
contain a null value".




:

You left out the colon ).
"NL:Lesion Number" without the quotes.

Regards,
Andreas

Ted wrote:

andreas,

i think i've run out of chits

at the point where i typed "NLLesion Number" and attempted to save
it as qryGetPatientNumber, it returns an error message "The
expression you entered contains invalid syntax" followed by "You may
have entered an operand without an operator".
bummer. i've got another approach i'm going to try but thanks a
1,000,000 for all the help and bandwidth.

-ted



:



Last attempt:
- Create a query in design view.
- Add the table [Lesions: Non-Target] as your datasource.
- Bring in the fields [Lesion Number] and [Patient Number].
- Click on the Sigma button on the toolbar to change the query to a
Summary Totals query.
- In the Totals row for the field [Lesion Number], select Max.
- In the Totals row for the field [Patient Number], select Where.
- In the Criteria row for the field [Patient Number], enter
[Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient
Number]
- In the Field row for the field [Lesion Number], in front of the
text that is already there, type LN:
- Save the query as "qryGetPatientNumber" (without the quotes).

In the form where you want this as a default value, select the
control and set the ControlSource property to:
=Nz(DLookUp("LN","qryGetPatientNumber"),0)+1

Provided the default for the Patient number works (in the other
control) this should work, unless there is a problem with your
names or data types. If this does not work, put a blank, new text
box on the form and enter the above and see what value is returned.
Just to make sure, I take it the Lesion number is a number field?

Regards,
Andreas


Nz(DMax("[Lesion Number]","[Lesions: Non-Target]","[Patient Number]
= "

& [Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient
Number]),0) + 1



Ted wrote:


i did snooping in the Access KB website and came up w/ KB 209785
which speaks to a prohibition against using circular references to
a control name in both the Name and Control Source properties.

a quote

"If you create a form based on a table or query and drag a numeric
field onto the form, by default the ControlSource property and the
Name property are given the name of the actual field in the
underlying table or query. If you change the ControlSource
property to an expression that includes the Name property, you see
"#Error" in this field when you switch to Form or Datasheet view.
The following example demonstrates an expression within the
ControlSource property: Name: Person_ID
ControlSource: =[Person_ID] + 1 "



:




Actually, thinking about it, the problem is quite obvious.
Just didn't see it before.
You are going to a new record.
What value will Lesion Number have there?

Try:
Nz(DMax("[Lesion Number]","[Lesions: Non-Target]","[Patient
Number] = " & [Forms]![RECIST Disease Evaluation: Nontarget
Lesions]![Patient Number]),0) + 1

Regards,
Andreas


Ted wrote:



hi andreas,

so as it stands, the Patient Number default was as before:

=[Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient
Number]

and the Lesion Number default is now:

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion
Number]","[Lesions: Non-Target]","[Patient Number] = " &
[Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient
Number]))

which i'm prepared to believe is what you proposed.

still though.......no cigar? hmmmm....

what could it be this time? did it matter that the controls
Patient and Lesion number are comboboxes and not textboxes?


:





In your DMax function, you used:
"Lesions: Non-Target"
I used:
"[Lesions: Non-Target]"


Also, you might want to make this additional change:

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion Number]",
"[Lesions: Non-Target]","[Patient Number] = " & [Forms]![RECIST
Disease Evaluation: Nontarget Lesions]![Patient Number]))

Regards,
Andreas


Ted wrote:




what's different about your rendition of my IIF statement and
the one i wrote? i didn't understand this.

-ted

ps: yes, it's numeric.


:






Use square brackets for your datasource (necessary because of
your naming convention):

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion
Number]","[Lesions:
Non-Target]","[Patient Number] = " & [Me]![Patient Number]))

Also, if your patient number is not numeric, you will need to
embed the appropriate delimeters. For text:
"[Patient Number] = '" & [Me]![Patient Number] & "'"

Regards,
Andreas


Ted wrote:





amongst other controls, my table underlying this form has a
PK comprising "Patient Number" and "Lesion Number" as they
are also called on the continuous form which is what i'm
focusing on.

when a new record's to be added to the form, i use a default
expression of

=[Forms]![RECIST Disease Evaluation: Nontarget
Lesions]![Patient Number]

which seems to work nicely so far thank you very much.

when we get to "Lesion Number" the defalut expression i used
was

=IIf(IsNull("[Lesion Number]"),1,1+DMax("[Lesion
Number]","Lesions: Non-Target","[Patient Number] = " &
[Me]![Patient Number]))

and that seems to be populating the form's control field
with "#Name?".

not exactly what i'd had in mind: i.e. the first time this
record gets completed we'd expect there to be nothing in the
"Lesion Number" field so the sequence would start with the
integer value '1'. the next time and every other when a new
record got added via the form we'd expect the Lesion Number
to be incrementally larger by a factor of 1.

so what's up with that?

anyone care to guess?