incrementing records in linked subform

[Guest]

i tried to send this same reply a minute earlier but the computer hiccuped
and perhaps didn't send...

would you mind rewriting it to conform to what you're saying is appropriate
given that these fields are sometimes number and sometimes (last, first,
middle name)character fields?

-ted

first thing that came to mind because SOMETIMES its' the obvious and the
only thing I saw that MIGHT be wrong is if this is a number .... then would
have to be changed [Me]![MR_Number] & "[IRB Number] = " & [Me]![IRB Number]
because numbers require "[field]=' " & Me![field] &" ' " formatting
"Ted" <[email protected] OR why do you have Me!

wrote in message
i think those are some reasons you cut and pasted from the help doc'n, am i
wrong? thanks, i don't think it's a or c. i'm kind of thinking that
there's
something about the way the criteria was written (i.e. in b) that's giving
it
heartburn but alas 'what'?

Reasons (and resolutions) you see #NAME?

a.. Make sure that the field specified in the control's ControlSource
property hasn't been removed from the underlying table or record source
b.. Check the spelling of the field name in the control's ControlSource
property.
c.. If you specified an expression in the control's ControlSource
property, make sure that there is an equal sign preceding the expression.
wait, don't go yet, it gets interesting.

i thought i'd work with the idea of using an expression in the 'Default
Value' property of the 'Visit #' field on the subform ("DaysView")
which
used
the DMax expression, so i whipped (no pun intended) up this one:


=Nz(DMax("[RecordNumber]","[DaysView]"),0)+1

and viewed the result. i found that for a condition where my sub-form
had
8
pre-existing values (1,2,3,...,8), that there was 'lo and behold a 9 in
the
ninth record. wunderbar, but for the fact that every other subform had
a 9
in
the same control as well. (there are only three mainform records having
any
meaningful subform records in this test version of my mdb file, and the
others had 3 and 5 records in the subform respectively, so the function
up
there is failing to filter for the PK values i reasoned and globally
identifying 8 as the max and popping a 9 everywhere).

i then decided i'd beef the expression up a bit and whipped (there i go
again) up the next one:

=Nz(DMax("[RecordNumber]","[DaysView]","[Last Name] = " & [Me]![Last
Name]
&
"[First Name] = " & [Me]![First Name] & "[MI] = " & [Me]![MI] &
"[MR_Number]
= " & [Me]![MR_Number] & "[IRB Number] = " & [Me]![IRB Number]),0)+1

which filters out those subform records not belonging to the one that
the
user is viewing (or at least that's my theory) and what happens is that
the
user sees "#Name?" (w/o the quotations) in the "Visit #" control's
field.

any ideas you'd like to share?

:

Yes you are correct, and I MISread what you required (too much
coffee)...
pardon the interuption...

i think what it does is automatically create twenty four records
having
the
same serial number and item_numbers like 1,2,3,.....24. am i
correct?
if
so,
this isn't quite what i had in mind: i.e., the ability of the user
to
click a
cmd button on the main form which added a new sub-form record to it
and
launched some code that incremented the value of 'Visit #' based on
the
maximum value of the preceding visit.

-ted


:

Well, if you have a problem let me know, I got the sample database
that
it
actually works in...


it's probably worth a try, gina. i'll just have to see what
happens
(when
i
'map' into my controls etc.).

thanks,

-ted

:

Ted,

I found this database online (and I REALLY wish I could remember
where
so
I
could at least acknowledge the person who wrote it) that
increments
records.
I have not tested or debugged it but I do know it works in the
databse
example I downloaded. Perhaps it will help?


Private Sub Form_AfterInsert()

Dim db As Database
Dim LSQL As String
Dim LCntr As Integer

'Establish connection to current database
Set db = CurrentDb()

LCntr = 1

'Create SQL to insert item_numbers 1 to 24
Do Until LCntr > 24

LSQL = "insert into inventory_details (serial_number,
item_number)"
LSQL = LSQL & " values ("
LSQL = LSQL & "'" & serial_number & "', " & LCntr & ")"

'Perform SQL
db.Execute LSQL

'Increment counter variable
LCntr = LCntr + 1
Loop

'Requery subform that new records are listing
frmInventory_details.Requery

End Sub

thanks but what did this have to do with my question, i.e. the
job
of
sequentially numbering records per combination of MR and IRB
numbers?



:

Then you should be using the MR# to link the parents to the
children
and
omit the names as they are an unncessary level.

I would still go with identifying the visits by Date/Time as
you're
not
going to have two visits occur simulatenously (unless your
dealing
with
pan-demensional, alternate realities that intersect at a
specific
space/time) and abandon trying to number them. If you set the
..DefaultValue of the field that will contain the Date/Time
to
NOW(),
the
users will not have to enter it as it will be added when a
new
record
is
created. Also, by using Date/Time data you'll be able to
provide
information on the last visit and whatnot. Additionally,
separate
from
all of this you can do a DCount() to provide a count of the
number
of
visits that a person has had and the Date/Time of the last
visit
using
DMax()

David H

Ted wrote:
i agree, fn, mi, ln are insufficient to guarantee
uniqueness,
agreed.
mr#
(medical record number, akin to SSN) is unique, though.

regarding your 2nd point, the record number is there to
differentiate
one
record from the other and also for appearance's sake --
given
the
almost
aesthetic rationale for its existence, i figure i'd
unburden
the
user
from
the 'task' of entering it manually.

there is no appointment calendar to this database;
apparently
that
is
being
done w/in another software venue; there is however some
interest
on
the
part
of some users to have a few components regarding the study
visits
in
this
database. currently, when they are entered they are entered
into
a
global
comments field.

-ted

:


I would HIGHLY recommend that you reevaluate how you're
linking
the
forms. The problem with linking using a name is that you
can't
guarantee
that a full name (first, middle, last) will be unique.
Trust
me I
made
that mistake one and it hurt and hurt bad. The more common
approach
is
to create a field in the parent table (tblPatients) whose
datatype
is
set to Autonumber and to set this field as the primary key.
Then
in
the
child table (tblVisits) to create another field which
captures
the
value
of the primary key as the records are added.

Second, is there a SPECIFIC need to know that what took
place
on
visit
#3 - as opposed to the visit that occurred on 6/17/2005 at
5:15
PM?
Its
a lot easier to document the visit by Date/Time as opposed
to
sequence

 

[Gina Whipp]

Ted,

Sorry for delay, had to go into a meeting (what a waste of time)... Anyway,
I'm glad you asked for clarification in my haste I made a mistake...

The below is used for TEXT fields NOT numbers, as I mistakenly told you:

"[FieldName In Table Or Query]=' " & Me![FieldName on Form or Report] &" ' "

and the below is used for Numeric fields:

"[FieldName In Table Or Query]=" & Me![FieldName on Form or Report]

I also note that you brackets arouns the [Me]! If those are being
automatically placed and you are not typing them, then there is something
wrong with the expression. Also, as I was retyping your code, I replaced
you ampersands (&) with the word AND. Didn't even notice that before. Now
in a perfect world, you should be able to copy and paste that and it SHOULD
work but Murphy's Law... let me know. Oh, and all the fields you are
calling should be found in "DaysView".

=Nz(DMax("[RecordNumber]","[DaysView]","[Last Name] = " & Me![Last
Name] And "[First Name] = " & Me![First Name] And "[MI] = " & Me![MI] &
"[MR_Number]
= ' " & Me![MR_Number] & " ' " And "[IRB Number] = ' " & Me![IRB Number] & "
' "),0)+1

And just to throw in 2 cents worth when naming fields try not to use spaces,
just makes things difficult.

Good Luck!

i tried to send this same reply a minute earlier but the computer hiccuped
and perhaps didn't send...

would you mind rewriting it to conform to what you're saying is
appropriate
given that these fields are sometimes number and sometimes (last, first,
middle name)character fields?

-ted

first thing that came to mind because SOMETIMES its' the obvious and the
only thing I saw that MIGHT be wrong is if this is a number .... then
would
have to be changed [Me]![MR_Number] & "[IRB Number] = " & [Me]![IRB
Number]
because numbers require "[field]=' " & Me![field] &" ' " formatting
"Ted" <[email protected] OR why do you have Me!

wrote in message
i think those are some reasons you cut and pasted from the help doc'n,
am i
wrong? thanks, i don't think it's a or c. i'm kind of thinking that
there's
something about the way the criteria was written (i.e. in b) that's
giving
it
heartburn but alas 'what'?

:

Reasons (and resolutions) you see #NAME?

a.. Make sure that the field specified in the control's
ControlSource
property hasn't been removed from the underlying table or record
source
b.. Check the spelling of the field name in the control's
ControlSource
property.
c.. If you specified an expression in the control's ControlSource
property, make sure that there is an equal sign preceding the
expression.
wait, don't go yet, it gets interesting.

i thought i'd work with the idea of using an expression in the
'Default
Value' property of the 'Visit #' field on the subform ("DaysView")
which
used
the DMax expression, so i whipped (no pun intended) up this one:


=Nz(DMax("[RecordNumber]","[DaysView]"),0)+1

and viewed the result. i found that for a condition where my
sub-form
had
8
pre-existing values (1,2,3,...,8), that there was 'lo and behold a 9
in
the
ninth record. wunderbar, but for the fact that every other subform
had
a 9
in
the same control as well. (there are only three mainform records
having
any
meaningful subform records in this test version of my mdb file, and
the
others had 3 and 5 records in the subform respectively, so the
function
up
there is failing to filter for the PK values i reasoned and globally
identifying 8 as the max and popping a 9 everywhere).

i then decided i'd beef the expression up a bit and whipped (there i
go
again) up the next one:

=Nz(DMax("[RecordNumber]","[DaysView]","[Last Name] = " & [Me]![Last
Name]
&
"[First Name] = " & [Me]![First Name] & "[MI] = " & [Me]![MI] &
"[MR_Number]
= " & [Me]![MR_Number] & "[IRB Number] = " & [Me]![IRB Number]),0)+1

which filters out those subform records not belonging to the one
that
the
user is viewing (or at least that's my theory) and what happens is
that
the
user sees "#Name?" (w/o the quotations) in the "Visit #" control's
field.

any ideas you'd like to share?

:

Yes you are correct, and I MISread what you required (too much
coffee)...
pardon the interuption...

i think what it does is automatically create twenty four records
having
the
same serial number and item_numbers like 1,2,3,.....24. am i
correct?
if
so,
this isn't quite what i had in mind: i.e., the ability of the
user
to
click a
cmd button on the main form which added a new sub-form record to
it
and
launched some code that incremented the value of 'Visit #' based
on
the
maximum value of the preceding visit.

-ted


:

Well, if you have a problem let me know, I got the sample
database
that
it
actually works in...


it's probably worth a try, gina. i'll just have to see what
happens
(when
i
'map' into my controls etc.).

thanks,

-ted

:

Ted,

I found this database online (and I REALLY wish I could
remember
where
so
I
could at least acknowledge the person who wrote it) that
increments
records.
I have not tested or debugged it but I do know it works in
the
databse
example I downloaded. Perhaps it will help?


Private Sub Form_AfterInsert()

Dim db As Database
Dim LSQL As String
Dim LCntr As Integer

'Establish connection to current database
Set db = CurrentDb()

LCntr = 1

'Create SQL to insert item_numbers 1 to 24
Do Until LCntr > 24

LSQL = "insert into inventory_details
(serial_number,
item_number)"
LSQL = LSQL & " values ("
LSQL = LSQL & "'" & serial_number & "', " & LCntr &
")"

'Perform SQL
db.Execute LSQL

'Increment counter variable
LCntr = LCntr + 1
Loop

'Requery subform that new records are listing
frmInventory_details.Requery

End Sub

thanks but what did this have to do with my question, i.e.
the
job
of
sequentially numbering records per combination of MR and
IRB
numbers?



:

Then you should be using the MR# to link the parents to
the
children
and
omit the names as they are an unncessary level.

I would still go with identifying the visits by Date/Time
as
you're
not
going to have two visits occur simulatenously (unless your
dealing
with
pan-demensional, alternate realities that intersect at a
specific
space/time) and abandon trying to number them. If you set
the
..DefaultValue of the field that will contain the
Date/Time
to
NOW(),
the
users will not have to enter it as it will be added when a
new
record
is
created. Also, by using Date/Time data you'll be able to
provide
information on the last visit and whatnot. Additionally,
separate
from
all of this you can do a DCount() to provide a count of
the
number
of
visits that a person has had and the Date/Time of the last
visit
using
DMax()

David H

Ted wrote:
i agree, fn, mi, ln are insufficient to guarantee
uniqueness,
agreed.
mr#
(medical record number, akin to SSN) is unique, though.

regarding your 2nd point, the record number is there to
differentiate
one
record from the other and also for appearance's sake --
given
the
almost
aesthetic rationale for its existence, i figure i'd
unburden
the
user
from
the 'task' of entering it manually.

there is no appointment calendar to this database;
apparently
that
is
being
done w/in another software venue; there is however some
interest
on
the
part
of some users to have a few components regarding the
study
visits
in
this
database. currently, when they are entered they are
entered
into
a
global
comments field.

-ted

:


I would HIGHLY recommend that you reevaluate how you're
linking
the
forms. The problem with linking using a name is that you
can't
guarantee
that a full name (first, middle, last) will be unique.
Trust
me I
made
that mistake one and it hurt and hurt bad. The more
common
approach
is
to create a field in the parent table (tblPatients)
whose
datatype
is
set to Autonumber and to set this field as the primary
key.
Then
in
the
child table (tblVisits) to create another field which
captures
the
value
of the primary key as the records are added.

Second, is there a SPECIFIC need to know that what took
place
on
visit
#3 - as opposed to the visit that occurred on 6/17/2005
at
5:15
PM?
Its
a lot easier to document the visit by Date/Time as
opposed
to
sequence

 

[Guest]

hi gina,

i finally got around to seeing your reply. here's what i've done. i've
pasted the following

=Nz(DMax("[RecordNumber]","[DaysView]","[Last Name] = " & Me![Last Name] And
"[First Name] = " & Me![First Name] And "[MI] = " & Me![MI] & "[MR_Number] =
' " & Me![MR_Number] & " ' " And "[IRB Number] = ' " & Me![IRB Number] & "
' "),0)+1

into the "DaysView" subform's "Default Value" property and viewed the
main/subform. and [drum roll, maestro], our friendly #Name? message appears
in the "Visit #" control on the sub-form (which just for the record is being
viewed as a datasheet -- in case that were to matter).

anyway, still no cigar.

thanks for the bandwidth,

and the beat goes on.

Ted,

Sorry for delay, had to go into a meeting (what a waste of time)... Anyway,
I'm glad you asked for clarification in my haste I made a mistake...

The below is used for TEXT fields NOT numbers, as I mistakenly told you:

"[FieldName In Table Or Query]=' " & Me![FieldName on Form or Report] &" ' "

and the below is used for Numeric fields:

"[FieldName In Table Or Query]=" & Me![FieldName on Form or Report]

I also note that you brackets arouns the [Me]! If those are being
automatically placed and you are not typing them, then there is something
wrong with the expression. Also, as I was retyping your code, I replaced
you ampersands (&) with the word AND. Didn't even notice that before. Now
in a perfect world, you should be able to copy and paste that and it SHOULD
work but Murphy's Law... let me know. Oh, and all the fields you are
calling should be found in "DaysView".

=Nz(DMax("[RecordNumber]","[DaysView]","[Last Name] = " & Me![Last
Name] And "[First Name] = " & Me![First Name] And "[MI] = " & Me![MI] &
"[MR_Number]
= ' " & Me![MR_Number] & " ' " And "[IRB Number] = ' " & Me![IRB Number] & "
' "),0)+1

And just to throw in 2 cents worth when naming fields try not to use spaces,
just makes things difficult.

Good Luck!

i tried to send this same reply a minute earlier but the computer hiccuped
and perhaps didn't send...

would you mind rewriting it to conform to what you're saying is
appropriate
given that these fields are sometimes number and sometimes (last, first,
middle name)character fields?

-ted

first thing that came to mind because SOMETIMES its' the obvious and the
only thing I saw that MIGHT be wrong is if this is a number .... then
would
have to be changed [Me]![MR_Number] & "[IRB Number] = " & [Me]![IRB
Number]
because numbers require "[field]=' " & Me![field] &" ' " formatting
"Ted" <[email protected] OR why do you have Me!


wrote in message
i think those are some reasons you cut and pasted from the help doc'n,
am i
wrong? thanks, i don't think it's a or c. i'm kind of thinking that
there's
something about the way the criteria was written (i.e. in b) that's
giving
it
heartburn but alas 'what'?

:

Reasons (and resolutions) you see #NAME?

a.. Make sure that the field specified in the control's
ControlSource
property hasn't been removed from the underlying table or record
source
b.. Check the spelling of the field name in the control's
ControlSource
property.
c.. If you specified an expression in the control's ControlSource
property, make sure that there is an equal sign preceding the
expression.
wait, don't go yet, it gets interesting.

i thought i'd work with the idea of using an expression in the
'Default
Value' property of the 'Visit #' field on the subform ("DaysView")
which
used
the DMax expression, so i whipped (no pun intended) up this one:


=Nz(DMax("[RecordNumber]","[DaysView]"),0)+1

and viewed the result. i found that for a condition where my
sub-form
had
8
pre-existing values (1,2,3,...,8), that there was 'lo and behold a 9
in
the
ninth record. wunderbar, but for the fact that every other subform
had
a 9
in
the same control as well. (there are only three mainform records
having
any
meaningful subform records in this test version of my mdb file, and
the
others had 3 and 5 records in the subform respectively, so the
function
up
there is failing to filter for the PK values i reasoned and globally
identifying 8 as the max and popping a 9 everywhere).

i then decided i'd beef the expression up a bit and whipped (there i
go
again) up the next one:

=Nz(DMax("[RecordNumber]","[DaysView]","[Last Name] = " & [Me]![Last
Name]
&
"[First Name] = " & [Me]![First Name] & "[MI] = " & [Me]![MI] &
"[MR_Number]
= " & [Me]![MR_Number] & "[IRB Number] = " & [Me]![IRB Number]),0)+1

which filters out those subform records not belonging to the one
that
the
user is viewing (or at least that's my theory) and what happens is
that
the
user sees "#Name?" (w/o the quotations) in the "Visit #" control's
field.

any ideas you'd like to share?

:

Yes you are correct, and I MISread what you required (too much
coffee)...
pardon the interuption...

i think what it does is automatically create twenty four records
having
the
same serial number and item_numbers like 1,2,3,.....24. am i
correct?
if
so,
this isn't quite what i had in mind: i.e., the ability of the
user
to
click a
cmd button on the main form which added a new sub-form record to
it
and
launched some code that incremented the value of 'Visit #' based
on
the
maximum value of the preceding visit.

-ted


:

Well, if you have a problem let me know, I got the sample
database
that
it
actually works in...


it's probably worth a try, gina. i'll just have to see what
happens
(when
i
'map' into my controls etc.).

thanks,

-ted

:

Ted,

I found this database online (and I REALLY wish I could
remember
where
so
I
could at least acknowledge the person who wrote it) that
increments
records.
I have not tested or debugged it but I do know it works in
the
databse
example I downloaded. Perhaps it will help?


Private Sub Form_AfterInsert()

Dim db As Database
Dim LSQL As String
Dim LCntr As Integer

'Establish connection to current database
Set db = CurrentDb()

LCntr = 1

'Create SQL to insert item_numbers 1 to 24
Do Until LCntr > 24

LSQL = "insert into inventory_details
(serial_number,
item_number)"
LSQL = LSQL & " values ("
LSQL = LSQL & "'" & serial_number & "', " & LCntr &
")"

'Perform SQL
db.Execute LSQL

'Increment counter variable
LCntr = LCntr + 1
Loop

'Requery subform that new records are listing
frmInventory_details.Requery

End Sub

thanks but what did this have to do with my question, i.e.
the
job
of
sequentially numbering records per combination of MR and
IRB
numbers?



:

Then you should be using the MR# to link the parents to
the
children
and
omit the names as they are an unncessary level.

I would still go with identifying the visits by Date/Time
as
you're
not
going to have two visits occur simulatenously (unless your
dealing
with
pan-demensional, alternate realities that intersect at a
specific
space/time) and abandon trying to number them. If you set
the
..DefaultValue of the field that will contain the
Date/Time
to
NOW(),
the
users will not have to enter it as it will be added when a
new
record
is
created. Also, by using Date/Time data you'll be able to
provide
information on the last visit and whatnot. Additionally,
separate
from
all of this you can do a DCount() to provide a count of
the
number
of
visits that a person has had and the Date/Time of the last
visit
using
DMax()

David H

Ted wrote:
i agree, fn, mi, ln are insufficient to guarantee
uniqueness,
agreed.

 

[Gina Whipp]

What's in the "Visit #" control and if it's that '#' (pound sign) get rid of
it and see if you still get the error '#' is a reserved symbol! Getting
more coffee, missed that before!

hi gina,

i finally got around to seeing your reply. here's what i've done. i've
pasted the following

=Nz(DMax("[RecordNumber]","[DaysView]","[Last Name] = " & Me![Last Name]
And
"[First Name] = " & Me![First Name] And "[MI] = " & Me![MI] & "[MR_Number]
=
' " & Me![MR_Number] & " ' " And "[IRB Number] = ' " & Me![IRB Number] & "
' "),0)+1

into the "DaysView" subform's "Default Value" property and viewed the
main/subform. and [drum roll, maestro], our friendly #Name? message
appears
in the "Visit #" control on the sub-form (which just for the record is
being
viewed as a datasheet -- in case that were to matter).

anyway, still no cigar.

thanks for the bandwidth,

and the beat goes on.

Ted,

Sorry for delay, had to go into a meeting (what a waste of time)...
Anyway,
I'm glad you asked for clarification in my haste I made a mistake...

The below is used for TEXT fields NOT numbers, as I mistakenly told you:

"[FieldName In Table Or Query]=' " & Me![FieldName on Form or Report] &"
' "

and the below is used for Numeric fields:

"[FieldName In Table Or Query]=" & Me![FieldName on Form or Report]

I also note that you brackets arouns the [Me]! If those are being
automatically placed and you are not typing them, then there is something
wrong with the expression. Also, as I was retyping your code, I replaced
you ampersands (&) with the word AND. Didn't even notice that before.
Now
in a perfect world, you should be able to copy and paste that and it
SHOULD
work but Murphy's Law... let me know. Oh, and all the fields you are
calling should be found in "DaysView".

=Nz(DMax("[RecordNumber]","[DaysView]","[Last Name] = " & Me![Last
Name] And "[First Name] = " & Me![First Name] And "[MI] = " & Me![MI] &
"[MR_Number]
= ' " & Me![MR_Number] & " ' " And "[IRB Number] = ' " & Me![IRB Number]
& "
' "),0)+1

And just to throw in 2 cents worth when naming fields try not to use
spaces,
just makes things difficult.

Good Luck!

i tried to send this same reply a minute earlier but the computer
hiccuped
and perhaps didn't send...

would you mind rewriting it to conform to what you're saying is
appropriate
given that these fields are sometimes number and sometimes (last,
first,
middle name)character fields?

-ted

:

first thing that came to mind because SOMETIMES its' the obvious and
the
only thing I saw that MIGHT be wrong is if this is a number .... then
would
have to be changed [Me]![MR_Number] & "[IRB Number] = " & [Me]![IRB
Number]
because numbers require "[field]=' " & Me![field] &" ' " formatting
"Ted" <[email protected] OR why do you have Me!


wrote in message
i think those are some reasons you cut and pasted from the help
doc'n,
am i
wrong? thanks, i don't think it's a or c. i'm kind of thinking that
there's
something about the way the criteria was written (i.e. in b) that's
giving
it
heartburn but alas 'what'?

:

Reasons (and resolutions) you see #NAME?

a.. Make sure that the field specified in the control's
ControlSource
property hasn't been removed from the underlying table or record
source
b.. Check the spelling of the field name in the control's
ControlSource
property.
c.. If you specified an expression in the control's ControlSource
property, make sure that there is an equal sign preceding the
expression.
wait, don't go yet, it gets interesting.

i thought i'd work with the idea of using an expression in the
'Default
Value' property of the 'Visit #' field on the subform
("DaysView")
which
used
the DMax expression, so i whipped (no pun intended) up this one:


=Nz(DMax("[RecordNumber]","[DaysView]"),0)+1

and viewed the result. i found that for a condition where my
sub-form
had
8
pre-existing values (1,2,3,...,8), that there was 'lo and behold
a 9
in
the
ninth record. wunderbar, but for the fact that every other
subform
had
a 9
in
the same control as well. (there are only three mainform records
having
any
meaningful subform records in this test version of my mdb file,
and
the
others had 3 and 5 records in the subform respectively, so the
function
up
there is failing to filter for the PK values i reasoned and
globally
identifying 8 as the max and popping a 9 everywhere).

i then decided i'd beef the expression up a bit and whipped
(there i
go
again) up the next one:

=Nz(DMax("[RecordNumber]","[DaysView]","[Last Name] = " &
[Me]![Last
Name]
&
"[First Name] = " & [Me]![First Name] & "[MI] = " & [Me]![MI] &
"[MR_Number]
= " & [Me]![MR_Number] & "[IRB Number] = " & [Me]![IRB
Number]),0)+1

which filters out those subform records not belonging to the one
that
the
user is viewing (or at least that's my theory) and what happens
is
that
the
user sees "#Name?" (w/o the quotations) in the "Visit #"
control's
field.

any ideas you'd like to share?

:

Yes you are correct, and I MISread what you required (too much
coffee)...
pardon the interuption...

i think what it does is automatically create twenty four
records
having
the
same serial number and item_numbers like 1,2,3,.....24. am i
correct?
if
so,
this isn't quite what i had in mind: i.e., the ability of the
user
to
click a
cmd button on the main form which added a new sub-form record
to
it
and
launched some code that incremented the value of 'Visit #'
based
on
the
maximum value of the preceding visit.

-ted


:

Well, if you have a problem let me know, I got the sample
database
that
it
actually works in...


it's probably worth a try, gina. i'll just have to see what
happens
(when
i
'map' into my controls etc.).

thanks,

-ted

:

Ted,

I found this database online (and I REALLY wish I could
remember
where
so
I
could at least acknowledge the person who wrote it) that
increments
records.
I have not tested or debugged it but I do know it works in
the
databse
example I downloaded. Perhaps it will help?


Private Sub Form_AfterInsert()

Dim db As Database
Dim LSQL As String
Dim LCntr As Integer

'Establish connection to current database
Set db = CurrentDb()

LCntr = 1

'Create SQL to insert item_numbers 1 to 24
Do Until LCntr > 24

LSQL = "insert into inventory_details
(serial_number,
item_number)"
LSQL = LSQL & " values ("
LSQL = LSQL & "'" & serial_number & "', " & LCntr
&
")"

'Perform SQL
db.Execute LSQL

'Increment counter variable
LCntr = LCntr + 1
Loop

'Requery subform that new records are listing
frmInventory_details.Requery

End Sub

thanks but what did this have to do with my question,
i.e.
the
job
of
sequentially numbering records per combination of MR and
IRB
numbers?



:

Then you should be using the MR# to link the parents to
the
children
and
omit the names as they are an unncessary level.

I would still go with identifying the visits by
Date/Time
as
you're
not
going to have two visits occur simulatenously (unless
your
dealing
with
pan-demensional, alternate realities that intersect at
a
specific
space/time) and abandon trying to number them. If you
set
the
..DefaultValue of the field that will contain the
Date/Time
to
NOW(),
the
users will not have to enter it as it will be added
when a
new
record
is
created. Also, by using Date/Time data you'll be able
to
provide
information on the last visit and whatnot.
Additionally,
separate
from
all of this you can do a DCount() to provide a count of
the
number
of
visits that a person has had and the Date/Time of the
last
visit
using
DMax()

David H

Ted wrote:
i agree, fn, mi, ln are insufficient to guarantee
uniqueness,
agreed.

 

[Guest]

someone has suggested that the control source and their respective sub-form
names ought not be the same. also, that the quotes syntax is incorrect. that
since "Last Name" is text it oguht to be delimited by quotes as was done for
[MR_Number] and that if [MR_Number] is number (which it is) that then it
oughtn't be delimited by quotes and that there may be a problem with the DMax
single quote delimiter (which oughta be replaced by pairs of double quotes
(whihc in turn will definitely be a problem if last names can have
apostrophes in them -- and who am i to say they won't!!)...arrrgh

-ted

ps: this is the state of what i'm using right now

=Nz(DMax("[RecordNumber]","[DaysView]","[Last Name] = " & [LastName] And
"[First Name] = " & [FirstName] And "[MI] = " & [M_I] & "[MR_Number] = ' " &
[MRNumber] & " ' " And "[IRB Number] = ' " & [IRBNumber] & " ' "),0)+1

-ted

What's in the "Visit #" control and if it's that '#' (pound sign) get rid of
it and see if you still get the error '#' is a reserved symbol! Getting
more coffee, missed that before!

hi gina,

i finally got around to seeing your reply. here's what i've done. i've
pasted the following

=Nz(DMax("[RecordNumber]","[DaysView]","[Last Name] = " & Me![Last Name]
And
"[First Name] = " & Me![First Name] And "[MI] = " & Me![MI] & "[MR_Number]
=
' " & Me![MR_Number] & " ' " And "[IRB Number] = ' " & Me![IRB Number] & "
' "),0)+1

into the "DaysView" subform's "Default Value" property and viewed the
main/subform. and [drum roll, maestro], our friendly #Name? message
appears
in the "Visit #" control on the sub-form (which just for the record is
being
viewed as a datasheet -- in case that were to matter).

anyway, still no cigar.

thanks for the bandwidth,

and the beat goes on.

Ted,

Sorry for delay, had to go into a meeting (what a waste of time)...
Anyway,
I'm glad you asked for clarification in my haste I made a mistake...

The below is used for TEXT fields NOT numbers, as I mistakenly told you:

"[FieldName In Table Or Query]=' " & Me![FieldName on Form or Report] &"
' "

and the below is used for Numeric fields:

"[FieldName In Table Or Query]=" & Me![FieldName on Form or Report]

I also note that you brackets arouns the [Me]! If those are being
automatically placed and you are not typing them, then there is something
wrong with the expression. Also, as I was retyping your code, I replaced
you ampersands (&) with the word AND. Didn't even notice that before.
Now
in a perfect world, you should be able to copy and paste that and it
SHOULD
work but Murphy's Law... let me know. Oh, and all the fields you are
calling should be found in "DaysView".

=Nz(DMax("[RecordNumber]","[DaysView]","[Last Name] = " & Me![Last
Name] And "[First Name] = " & Me![First Name] And "[MI] = " & Me![MI] &
"[MR_Number]
= ' " & Me![MR_Number] & " ' " And "[IRB Number] = ' " & Me![IRB Number]
& "
' "),0)+1

And just to throw in 2 cents worth when naming fields try not to use
spaces,
just makes things difficult.

Good Luck!

i tried to send this same reply a minute earlier but the computer
hiccuped
and perhaps didn't send...

would you mind rewriting it to conform to what you're saying is
appropriate
given that these fields are sometimes number and sometimes (last,
first,
middle name)character fields?

-ted

:

first thing that came to mind because SOMETIMES its' the obvious and
the
only thing I saw that MIGHT be wrong is if this is a number .... then
would
have to be changed [Me]![MR_Number] & "[IRB Number] = " & [Me]![IRB
Number]
because numbers require "[field]=' " & Me![field] &" ' " formatting
"Ted" <[email protected] OR why do you have Me!


wrote in message
i think those are some reasons you cut and pasted from the help
doc'n,
am i
wrong? thanks, i don't think it's a or c. i'm kind of thinking that
there's
something about the way the criteria was written (i.e. in b) that's
giving
it
heartburn but alas 'what'?

:

Reasons (and resolutions) you see #NAME?

a.. Make sure that the field specified in the control's
ControlSource
property hasn't been removed from the underlying table or record
source
b.. Check the spelling of the field name in the control's
ControlSource
property.
c.. If you specified an expression in the control's ControlSource
property, make sure that there is an equal sign preceding the
expression.
wait, don't go yet, it gets interesting.

i thought i'd work with the idea of using an expression in the
'Default
Value' property of the 'Visit #' field on the subform
("DaysView")
which
used
the DMax expression, so i whipped (no pun intended) up this one:


=Nz(DMax("[RecordNumber]","[DaysView]"),0)+1

and viewed the result. i found that for a condition where my
sub-form
had
8
pre-existing values (1,2,3,...,8), that there was 'lo and behold
a 9
in
the
ninth record. wunderbar, but for the fact that every other
subform
had
a 9
in
the same control as well. (there are only three mainform records
having
any
meaningful subform records in this test version of my mdb file,
and
the
others had 3 and 5 records in the subform respectively, so the
function
up
there is failing to filter for the PK values i reasoned and
globally
identifying 8 as the max and popping a 9 everywhere).

i then decided i'd beef the expression up a bit and whipped
(there i
go
again) up the next one:

=Nz(DMax("[RecordNumber]","[DaysView]","[Last Name] = " &
[Me]![Last
Name]
&
"[First Name] = " & [Me]![First Name] & "[MI] = " & [Me]![MI] &
"[MR_Number]
= " & [Me]![MR_Number] & "[IRB Number] = " & [Me]![IRB
Number]),0)+1

which filters out those subform records not belonging to the one
that
the
user is viewing (or at least that's my theory) and what happens
is
that
the
user sees "#Name?" (w/o the quotations) in the "Visit #"
control's
field.

any ideas you'd like to share?

:

Yes you are correct, and I MISread what you required (too much
coffee)...
pardon the interuption...

i think what it does is automatically create twenty four
records
having
the
same serial number and item_numbers like 1,2,3,.....24. am i
correct?
if
so,
this isn't quite what i had in mind: i.e., the ability of the
user
to
click a
cmd button on the main form which added a new sub-form record
to
it
and
launched some code that incremented the value of 'Visit #'
based
on
the
maximum value of the preceding visit.

-ted


:

Well, if you have a problem let me know, I got the sample
database
that
it
actually works in...


it's probably worth a try, gina. i'll just have to see what
happens
(when
i
'map' into my controls etc.).

thanks,

-ted

:

Ted,

I found this database online (and I REALLY wish I could
remember
where
so
I
could at least acknowledge the person who wrote it) that
increments
records.
I have not tested or debugged it but I do know it works in
the
databse
example I downloaded. Perhaps it will help?


Private Sub Form_AfterInsert()

Dim db As Database
Dim LSQL As String
Dim LCntr As Integer

'Establish connection to current database
Set db = CurrentDb()

LCntr = 1

'Create SQL to insert item_numbers 1 to 24
Do Until LCntr > 24

LSQL = "insert into inventory_details
(serial_number,
item_number)"
LSQL = LSQL & " values ("
LSQL = LSQL & "'" & serial_number & "', " & LCntr
&
")"

'Perform SQL
db.Execute LSQL

'Increment counter variable
LCntr = LCntr + 1
Loop

'Requery subform that new records are listing
frmInventory_details.Requery

End Sub

"Ted" <[email protected]> wrote in message

 

[Gina Whipp]

I COULD be wrong but the quote syntax is they I have been doing it and it
hasn't failed me yet. (I even went back into the databse I'm using now to
confirm, because I got it wrong before.) Whether or not the last name has
apostrophes in them or not is irrelevant (I know I've had a few thru the
years). But something else you said, are some of the fields on MainForm and
some on Subform? If yes, then the syntax is wrong...

someone has suggested that the control source and their respective
sub-form
names ought not be the same. also, that the quotes syntax is incorrect.
that
since "Last Name" is text it oguht to be delimited by quotes as was done
for
[MR_Number] and that if [MR_Number] is number (which it is) that then it
oughtn't be delimited by quotes and that there may be a problem with the
DMax
single quote delimiter (which oughta be replaced by pairs of double quotes
(whihc in turn will definitely be a problem if last names can have
apostrophes in them -- and who am i to say they won't!!)...arrrgh

-ted

ps: this is the state of what i'm using right now

=Nz(DMax("[RecordNumber]","[DaysView]","[Last Name] = " & [LastName] And
"[First Name] = " & [FirstName] And "[MI] = " & [M_I] & "[MR_Number] = ' "
&
[MRNumber] & " ' " And "[IRB Number] = ' " & [IRBNumber] & " ' "),0)+1

-ted

What's in the "Visit #" control and if it's that '#' (pound sign) get rid
of
it and see if you still get the error '#' is a reserved symbol! Getting
more coffee, missed that before!

hi gina,

i finally got around to seeing your reply. here's what i've done. i've
pasted the following

=Nz(DMax("[RecordNumber]","[DaysView]","[Last Name] = " & Me![Last
Name]
And
"[First Name] = " & Me![First Name] And "[MI] = " & Me![MI] &
"[MR_Number]
=
' " & Me![MR_Number] & " ' " And "[IRB Number] = ' " & Me![IRB Number]
& "
' "),0)+1

into the "DaysView" subform's "Default Value" property and viewed the
main/subform. and [drum roll, maestro], our friendly #Name? message
appears
in the "Visit #" control on the sub-form (which just for the record is
being
viewed as a datasheet -- in case that were to matter).

anyway, still no cigar.

thanks for the bandwidth,

and the beat goes on.


:

Ted,

Sorry for delay, had to go into a meeting (what a waste of time)...
Anyway,
I'm glad you asked for clarification in my haste I made a mistake...

The below is used for TEXT fields NOT numbers, as I mistakenly told
you:

"[FieldName In Table Or Query]=' " & Me![FieldName on Form or Report]
&"
' "

and the below is used for Numeric fields:

"[FieldName In Table Or Query]=" & Me![FieldName on Form or Report]

I also note that you brackets arouns the [Me]! If those are being
automatically placed and you are not typing them, then there is
something
wrong with the expression. Also, as I was retyping your code, I
replaced
you ampersands (&) with the word AND. Didn't even notice that before.
Now
in a perfect world, you should be able to copy and paste that and it
SHOULD
work but Murphy's Law... let me know. Oh, and all the fields you are
calling should be found in "DaysView".

=Nz(DMax("[RecordNumber]","[DaysView]","[Last Name] = " & Me![Last
Name] And "[First Name] = " & Me![First Name] And "[MI] = " &
Me![MI] &
"[MR_Number]
= ' " & Me![MR_Number] & " ' " And "[IRB Number] = ' " & Me![IRB
Number]
& "
' "),0)+1

And just to throw in 2 cents worth when naming fields try not to use
spaces,
just makes things difficult.

Good Luck!

i tried to send this same reply a minute earlier but the computer
hiccuped
and perhaps didn't send...

would you mind rewriting it to conform to what you're saying is
appropriate
given that these fields are sometimes number and sometimes (last,
first,
middle name)character fields?

-ted

:

first thing that came to mind because SOMETIMES its' the obvious
and
the
only thing I saw that MIGHT be wrong is if this is a number ....
then
would
have to be changed [Me]![MR_Number] & "[IRB Number] = " &
[Me]![IRB
Number]
because numbers require "[field]=' " & Me![field] &" ' " formatting
"Ted" <[email protected] OR why do you have Me!


wrote in message
i think those are some reasons you cut and pasted from the help
doc'n,
am i
wrong? thanks, i don't think it's a or c. i'm kind of thinking
that
there's
something about the way the criteria was written (i.e. in b)
that's
giving
it
heartburn but alas 'what'?

:

Reasons (and resolutions) you see #NAME?

a.. Make sure that the field specified in the control's
ControlSource
property hasn't been removed from the underlying table or record
source
b.. Check the spelling of the field name in the control's
ControlSource
property.
c.. If you specified an expression in the control's
ControlSource
property, make sure that there is an equal sign preceding the
expression.
wait, don't go yet, it gets interesting.

i thought i'd work with the idea of using an expression in the
'Default
Value' property of the 'Visit #' field on the subform
("DaysView")
which
used
the DMax expression, so i whipped (no pun intended) up this
one:


=Nz(DMax("[RecordNumber]","[DaysView]"),0)+1

and viewed the result. i found that for a condition where my
sub-form
had
8
pre-existing values (1,2,3,...,8), that there was 'lo and
behold
a 9
in
the
ninth record. wunderbar, but for the fact that every other
subform
had
a 9
in
the same control as well. (there are only three mainform
records
having
any
meaningful subform records in this test version of my mdb
file,
and
the
others had 3 and 5 records in the subform respectively, so the
function
up
there is failing to filter for the PK values i reasoned and
globally
identifying 8 as the max and popping a 9 everywhere).

i then decided i'd beef the expression up a bit and whipped
(there i
go
again) up the next one:

=Nz(DMax("[RecordNumber]","[DaysView]","[Last Name] = " &
[Me]![Last
Name]
&
"[First Name] = " & [Me]![First Name] & "[MI] = " & [Me]![MI]
&
"[MR_Number]
= " & [Me]![MR_Number] & "[IRB Number] = " & [Me]![IRB
Number]),0)+1

which filters out those subform records not belonging to the
one
that
the
user is viewing (or at least that's my theory) and what
happens
is
that
the
user sees "#Name?" (w/o the quotations) in the "Visit #"
control's
field.

any ideas you'd like to share?

:

Yes you are correct, and I MISread what you required (too
much
coffee)...
pardon the interuption...

i think what it does is automatically create twenty four
records
having
the
same serial number and item_numbers like 1,2,3,.....24. am
i
correct?
if
so,
this isn't quite what i had in mind: i.e., the ability of
the
user
to
click a
cmd button on the main form which added a new sub-form
record
to
it
and
launched some code that incremented the value of 'Visit #'
based
on
the
maximum value of the preceding visit.

-ted


:

Well, if you have a problem let me know, I got the sample
database
that
it
actually works in...


it's probably worth a try, gina. i'll just have to see
what
happens
(when
i
'map' into my controls etc.).

thanks,

-ted

:

Ted,

I found this database online (and I REALLY wish I could
remember
where
so
I
could at least acknowledge the person who wrote it)
that
increments
records.
I have not tested or debugged it but I do know it works
in
the
databse
example I downloaded. Perhaps it will help?


Private Sub Form_AfterInsert()

Dim db As Database
Dim LSQL As String
Dim LCntr As Integer

'Establish connection to current database
Set db = CurrentDb()

LCntr = 1

'Create SQL to insert item_numbers 1 to 24
Do Until LCntr > 24

LSQL = "insert into inventory_details
(serial_number,
item_number)"
LSQL = LSQL & " values ("
LSQL = LSQL & "'" & serial_number & "', " &
LCntr
&
")"

'Perform SQL
db.Execute LSQL

'Increment counter variable
LCntr = LCntr + 1
Loop

'Requery subform that new records are listing
frmInventory_details.Requery

End Sub

"Ted" <[email protected]> wrote in message

 

[Guest]

no gina,, all the fields are on the same (sub)form.

best,

-ted

I COULD be wrong but the quote syntax is they I have been doing it and it
hasn't failed me yet. (I even went back into the databse I'm using now to
confirm, because I got it wrong before.) Whether or not the last name has
apostrophes in them or not is irrelevant (I know I've had a few thru the
years). But something else you said, are some of the fields on MainForm and
some on Subform? If yes, then the syntax is wrong...

someone has suggested that the control source and their respective
sub-form
names ought not be the same. also, that the quotes syntax is incorrect.
that
since "Last Name" is text it oguht to be delimited by quotes as was done
for
[MR_Number] and that if [MR_Number] is number (which it is) that then it
oughtn't be delimited by quotes and that there may be a problem with the
DMax
single quote delimiter (which oughta be replaced by pairs of double quotes
(whihc in turn will definitely be a problem if last names can have
apostrophes in them -- and who am i to say they won't!!)...arrrgh

-ted

ps: this is the state of what i'm using right now

=Nz(DMax("[RecordNumber]","[DaysView]","[Last Name] = " & [LastName] And
"[First Name] = " & [FirstName] And "[MI] = " & [M_I] & "[MR_Number] = ' "
&
[MRNumber] & " ' " And "[IRB Number] = ' " & [IRBNumber] & " ' "),0)+1

-ted

What's in the "Visit #" control and if it's that '#' (pound sign) get rid
of
it and see if you still get the error '#' is a reserved symbol! Getting
more coffee, missed that before!


hi gina,

i finally got around to seeing your reply. here's what i've done. i've
pasted the following

=Nz(DMax("[RecordNumber]","[DaysView]","[Last Name] = " & Me![Last
Name]
And
"[First Name] = " & Me![First Name] And "[MI] = " & Me![MI] &
"[MR_Number]
=
' " & Me![MR_Number] & " ' " And "[IRB Number] = ' " & Me![IRB Number]
& "
' "),0)+1

into the "DaysView" subform's "Default Value" property and viewed the
main/subform. and [drum roll, maestro], our friendly #Name? message
appears
in the "Visit #" control on the sub-form (which just for the record is
being
viewed as a datasheet -- in case that were to matter).

anyway, still no cigar.

thanks for the bandwidth,

and the beat goes on.


:

Ted,

Sorry for delay, had to go into a meeting (what a waste of time)...
Anyway,
I'm glad you asked for clarification in my haste I made a mistake...

The below is used for TEXT fields NOT numbers, as I mistakenly told
you:

"[FieldName In Table Or Query]=' " & Me![FieldName on Form or Report]
&"
' "

and the below is used for Numeric fields:

"[FieldName In Table Or Query]=" & Me![FieldName on Form or Report]

I also note that you brackets arouns the [Me]! If those are being
automatically placed and you are not typing them, then there is
something
wrong with the expression. Also, as I was retyping your code, I
replaced
you ampersands (&) with the word AND. Didn't even notice that before.
Now
in a perfect world, you should be able to copy and paste that and it
SHOULD
work but Murphy's Law... let me know. Oh, and all the fields you are
calling should be found in "DaysView".

=Nz(DMax("[RecordNumber]","[DaysView]","[Last Name] = " & Me![Last
Name] And "[First Name] = " & Me![First Name] And "[MI] = " &
Me![MI] &
"[MR_Number]
= ' " & Me![MR_Number] & " ' " And "[IRB Number] = ' " & Me![IRB
Number]
& "
' "),0)+1

And just to throw in 2 cents worth when naming fields try not to use
spaces,
just makes things difficult.

Good Luck!

i tried to send this same reply a minute earlier but the computer
hiccuped
and perhaps didn't send...

would you mind rewriting it to conform to what you're saying is
appropriate
given that these fields are sometimes number and sometimes (last,
first,
middle name)character fields?

-ted

:

first thing that came to mind because SOMETIMES its' the obvious
and
the
only thing I saw that MIGHT be wrong is if this is a number ....
then
would
have to be changed [Me]![MR_Number] & "[IRB Number] = " &
[Me]![IRB
Number]
because numbers require "[field]=' " & Me![field] &" ' " formatting
"Ted" <[email protected] OR why do you have Me!


wrote in message
i think those are some reasons you cut and pasted from the help
doc'n,
am i
wrong? thanks, i don't think it's a or c. i'm kind of thinking
that
there's
something about the way the criteria was written (i.e. in b)
that's
giving
it
heartburn but alas 'what'?

:

Reasons (and resolutions) you see #NAME?

a.. Make sure that the field specified in the control's
ControlSource
property hasn't been removed from the underlying table or record
source
b.. Check the spelling of the field name in the control's
ControlSource
property.
c.. If you specified an expression in the control's
ControlSource
property, make sure that there is an equal sign preceding the
expression.
wait, don't go yet, it gets interesting.

i thought i'd work with the idea of using an expression in the
'Default
Value' property of the 'Visit #' field on the subform
("DaysView")
which
used
the DMax expression, so i whipped (no pun intended) up this
one:


=Nz(DMax("[RecordNumber]","[DaysView]"),0)+1

and viewed the result. i found that for a condition where my
sub-form
had
8
pre-existing values (1,2,3,...,8), that there was 'lo and
behold
a 9
in
the
ninth record. wunderbar, but for the fact that every other
subform
had
a 9
in
the same control as well. (there are only three mainform
records
having
any
meaningful subform records in this test version of my mdb
file,
and
the
others had 3 and 5 records in the subform respectively, so the
function
up
there is failing to filter for the PK values i reasoned and
globally
identifying 8 as the max and popping a 9 everywhere).

i then decided i'd beef the expression up a bit and whipped
(there i
go
again) up the next one:

=Nz(DMax("[RecordNumber]","[DaysView]","[Last Name] = " &
[Me]![Last
Name]
&
"[First Name] = " & [Me]![First Name] & "[MI] = " & [Me]![MI]
&
"[MR_Number]
= " & [Me]![MR_Number] & "[IRB Number] = " & [Me]![IRB
Number]),0)+1

which filters out those subform records not belonging to the
one
that
the
user is viewing (or at least that's my theory) and what
happens
is
that
the
user sees "#Name?" (w/o the quotations) in the "Visit #"
control's
field.

any ideas you'd like to share?

:

Yes you are correct, and I MISread what you required (too
much
coffee)...
pardon the interuption...

i think what it does is automatically create twenty four
records
having
the
same serial number and item_numbers like 1,2,3,.....24. am
i
correct?
if
so,
this isn't quite what i had in mind: i.e., the ability of
the
user
to
click a
cmd button on the main form which added a new sub-form
record
to
it
and
launched some code that incremented the value of 'Visit #'
based
on
the
maximum value of the preceding visit.

-ted


:

Well, if you have a problem let me know, I got the sample
database
that
it
actually works in...


news:[email protected]...

 

[Gina Whipp]

I'm on a mission now... this is wrong... THAT syntax should work.... I
just went to read to be SURE again! Okay do you have a blank (as in no
data) database you could send? Can't look till tonight (not really fair to
study it at work). If you don't want to it's okay, just send me the name of
the mainform, subform, table and fields and I'll figure it out if it kills
me.

no gina,, all the fields are on the same (sub)form.

best,

-ted

I COULD be wrong but the quote syntax is they I have been doing it and it
hasn't failed me yet. (I even went back into the databse I'm using now
to
confirm, because I got it wrong before.) Whether or not the last name
has
apostrophes in them or not is irrelevant (I know I've had a few thru the
years). But something else you said, are some of the fields on MainForm
and
some on Subform? If yes, then the syntax is wrong...

someone has suggested that the control source and their respective
sub-form
names ought not be the same. also, that the quotes syntax is incorrect.
that
since "Last Name" is text it oguht to be delimited by quotes as was
done
for
[MR_Number] and that if [MR_Number] is number (which it is) that then
it
oughtn't be delimited by quotes and that there may be a problem with
the
DMax
single quote delimiter (which oughta be replaced by pairs of double
quotes
(whihc in turn will definitely be a problem if last names can have
apostrophes in them -- and who am i to say they won't!!)...arrrgh

-ted

ps: this is the state of what i'm using right now

=Nz(DMax("[RecordNumber]","[DaysView]","[Last Name] = " & [LastName]
And
"[First Name] = " & [FirstName] And "[MI] = " & [M_I] & "[MR_Number] =
' "
&
[MRNumber] & " ' " And "[IRB Number] = ' " & [IRBNumber] & " ' "),0)+1

-ted

:

What's in the "Visit #" control and if it's that '#' (pound sign) get
rid
of
it and see if you still get the error '#' is a reserved symbol!
Getting
more coffee, missed that before!


hi gina,

i finally got around to seeing your reply. here's what i've done.
i've
pasted the following

=Nz(DMax("[RecordNumber]","[DaysView]","[Last Name] = " & Me![Last
Name]
And
"[First Name] = " & Me![First Name] And "[MI] = " & Me![MI] &
"[MR_Number]
=
' " & Me![MR_Number] & " ' " And "[IRB Number] = ' " & Me![IRB
Number]
& "
' "),0)+1

into the "DaysView" subform's "Default Value" property and viewed
the
main/subform. and [drum roll, maestro], our friendly #Name? message
appears
in the "Visit #" control on the sub-form (which just for the record
is
being
viewed as a datasheet -- in case that were to matter).

anyway, still no cigar.

thanks for the bandwidth,

and the beat goes on.


:

Ted,

Sorry for delay, had to go into a meeting (what a waste of time)...
Anyway,
I'm glad you asked for clarification in my haste I made a
mistake...

The below is used for TEXT fields NOT numbers, as I mistakenly told
you:

"[FieldName In Table Or Query]=' " & Me![FieldName on Form or
Report]
&"
' "

and the below is used for Numeric fields:

"[FieldName In Table Or Query]=" & Me![FieldName on Form or Report]

I also note that you brackets arouns the [Me]! If those are being
automatically placed and you are not typing them, then there is
something
wrong with the expression. Also, as I was retyping your code, I
replaced
you ampersands (&) with the word AND. Didn't even notice that
before.
Now
in a perfect world, you should be able to copy and paste that and
it
SHOULD
work but Murphy's Law... let me know. Oh, and all the fields you
are
calling should be found in "DaysView".

=Nz(DMax("[RecordNumber]","[DaysView]","[Last Name] = " & Me![Last
Name] And "[First Name] = " & Me![First Name] And "[MI] = " &
Me![MI] &
"[MR_Number]
= ' " & Me![MR_Number] & " ' " And "[IRB Number] = ' " & Me![IRB
Number]
& "
' "),0)+1

And just to throw in 2 cents worth when naming fields try not to
use
spaces,
just makes things difficult.

Good Luck!

i tried to send this same reply a minute earlier but the computer
hiccuped
and perhaps didn't send...

would you mind rewriting it to conform to what you're saying is
appropriate
given that these fields are sometimes number and sometimes (last,
first,
middle name)character fields?

-ted

:

first thing that came to mind because SOMETIMES its' the obvious
and
the
only thing I saw that MIGHT be wrong is if this is a number ....
then
would
have to be changed [Me]![MR_Number] & "[IRB Number] = " &
[Me]![IRB
Number]
because numbers require "[field]=' " & Me![field] &" ' "
formatting
"Ted" <[email protected] OR why do you have Me!


wrote in message
i think those are some reasons you cut and pasted from the help
doc'n,
am i
wrong? thanks, i don't think it's a or c. i'm kind of thinking
that
there's
something about the way the criteria was written (i.e. in b)
that's
giving
it
heartburn but alas 'what'?

:

Reasons (and resolutions) you see #NAME?

a.. Make sure that the field specified in the control's
ControlSource
property hasn't been removed from the underlying table or
record
source
b.. Check the spelling of the field name in the control's
ControlSource
property.
c.. If you specified an expression in the control's
ControlSource
property, make sure that there is an equal sign preceding the
expression.
wait, don't go yet, it gets interesting.

i thought i'd work with the idea of using an expression in
the
'Default
Value' property of the 'Visit #' field on the subform
("DaysView")
which
used
the DMax expression, so i whipped (no pun intended) up this
one:


=Nz(DMax("[RecordNumber]","[DaysView]"),0)+1

and viewed the result. i found that for a condition where
my
sub-form
had
8
pre-existing values (1,2,3,...,8), that there was 'lo and
behold
a 9
in
the
ninth record. wunderbar, but for the fact that every other
subform
had
a 9
in
the same control as well. (there are only three mainform
records
having
any
meaningful subform records in this test version of my mdb
file,
and
the
others had 3 and 5 records in the subform respectively, so
the
function
up
there is failing to filter for the PK values i reasoned and
globally
identifying 8 as the max and popping a 9 everywhere).

i then decided i'd beef the expression up a bit and whipped
(there i
go
again) up the next one:

=Nz(DMax("[RecordNumber]","[DaysView]","[Last Name] = " &
[Me]![Last
Name]
&
"[First Name] = " & [Me]![First Name] & "[MI] = " &
[Me]![MI]
&
"[MR_Number]
= " & [Me]![MR_Number] & "[IRB Number] = " & [Me]![IRB
Number]),0)+1

which filters out those subform records not belonging to
the
one
that
the
user is viewing (or at least that's my theory) and what
happens
is
that
the
user sees "#Name?" (w/o the quotations) in the "Visit #"
control's
field.

any ideas you'd like to share?

:

Yes you are correct, and I MISread what you required (too
much
coffee)...
pardon the interuption...

i think what it does is automatically create twenty four
records
having
the
same serial number and item_numbers like 1,2,3,.....24.
am
i
correct?
if
so,
this isn't quite what i had in mind: i.e., the ability
of
the
user
to
click a
cmd button on the main form which added a new sub-form
record
to
it
and
launched some code that incremented the value of 'Visit
#'
based
on
the
maximum value of the preceding visit.

-ted


:

Well, if you have a problem let me know, I got the
sample
database
that
it
actually works in...


news:[email protected]...