incrementing records in linked parent/child (main/subform) situati

[Ted]

have a pair of linked (parent and child) forms; the child is I guess what
you’d call the subform. They are linked by LN, FN, MI, MR_Number, IRB Number,
where the first three fields record the last name, first name and middle
initial of each patient.

The main form is called ‘Screening Log (Edit Only)’, the subform is called
‘DaysView’. Their respective record sources are ‘Screening Log Query For Form
(Revised)’ and ‘DaysView’.

There is a control on the subform/child I call ‘Visit #’ which I would like
to automatically increment, as in 1,2,3,4,5,……,etc. when I click an ‘Add
Record’ button I’m going to create on the main/parent form. Since the records
from the pair of forms are linked, we would want to reset the counter to 1
when ‘Visit #’ has never been created for a patient. I’m very sure that this
is doable but would like to know if there’s some template to get me started.

With thanks in advance,

-ted

 

[Wayne Morgan]

When you create a new record, you could just have the subform go to a new
record. When you're at a new record, the Default Value of the control for
that field will be used if you've set one. You can set it to

=Nz(DMax("[Visit#FieldName]", "[DaysView]", "[IdField]=" & [IdField]),0) + 1

If you don't have a unique ID field, you'll have to use "And" statements in
the criteria portion to use the 3 fields LN, FN, and MI instead. Using names
is not a good way to get the correct record for a person, there are too many
people with the same name.

 

[Ted]

hi wayne,

what i've done is paste the following into the "Default Value" property of
my "DaysView" subform i linked to the main form (called "Screening Log (Edit
Only)") and am having displayed to the user in datasheet view

=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

the effect of this manoeuvering is that our friendly #Name? message appears
in the "Visit #" control on the sub-form.

i guess we're not quite there, yet.

best,

-ted

When you create a new record, you could just have the subform go to a new
record. When you're at a new record, the Default Value of the control for
that field will be used if you've set one. You can set it to

=Nz(DMax("[Visit#FieldName]", "[DaysView]", "[IdField]=" & [IdField]),0) + 1

If you don't have a unique ID field, you'll have to use "And" statements in
the criteria portion to use the 3 fields LN, FN, and MI instead. Using names
is not a good way to get the correct record for a person, there are too many
people with the same name.

--
Wayne Morgan
MS Access MVP

have a pair of linked (parent and child) forms; the child is I guess what
you'd call the subform. They are linked by LN, FN, MI, MR_Number, IRB
Number,
where the first three fields record the last name, first name and middle
initial of each patient.

The main form is called 'Screening Log (Edit Only)', the subform is called
'DaysView'. Their respective record sources are 'Screening Log Query For
Form
(Revised)' and 'DaysView'.

There is a control on the subform/child I call 'Visit #' which I would
like
to automatically increment, as in 1,2,3,4,5,..,etc. when I click an 'Add
Record' button I'm going to create on the main/parent form. Since the
records
from the pair of forms are linked, we would want to reset the counter to 1
when 'Visit #' has never been created for a patient. I'm very sure that
this
is doable but would like to know if there's some template to get me
started.

With thanks in advance,

-ted

 

[Wayne Morgan]

"Me" is used in VBA modules, but not in the Control Source of a calculated
control. It will cause a Name# error if used in a Control Source. Once you
remove "Me", if you still get a Name# error, it may be that one of the bound
controls you refer to in the expression has the same name as the field it is
bound to. If so, change the name of the control. For example, if the bound
field is called [Last Name], then try naming the textbox txtLastName.

Also, it appears that the quotes syntax may be incorrect. I assume that
[Last Name] is text, if so, it needs to be delimited by quotes as you have
done for [MR_Number] and if [MR_Number] is actually a Number data type then
it should not be delimited by quotes. You may also have problems with DMax
and the single quotes as a text delimiter, if so, replace all of the single
quotes with double double quotes. This will definitely be a problem if the
[Last Name] field contains names that have apostrophes in them. If it does,
you'll have to use the double double quotes syntax or concatenate in Chr(34)
in lieu of the single quotes.

 

[Ted]

Hi Wayne,

On my ‘DaysView’ form I have the following Control Sources and their
respective Names

Source Form Name
Last Name LastName
First Name FirstName
MI M_I
MR_Number MRNumber
IRB Number IRBNumber
RecordNumber Visit

The code I have in the ‘Default Value’ property of the (form name) ‘Visit’
field (w/ Record Number as its Control Source) is

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

I’m having trouble understanding the confusing instructions needed to
unscramble the quotations concerns in your response. I’m all for it if it’ll
get this thing to work, but would you mind demonstrating what you meant; I’ll
worry about grokking the meaning of it all when I can see it—it’s probably
easier for you to undo what needs undoing it than describing it verbally if
I’m correct.

Regards,

Ted

"Me" is used in VBA modules, but not in the Control Source of a calculated
control. It will cause a Name# error if used in a Control Source. Once you
remove "Me", if you still get a Name# error, it may be that one of the bound
controls you refer to in the expression has the same name as the field it is
bound to. If so, change the name of the control. For example, if the bound
field is called [Last Name], then try naming the textbox txtLastName.

Also, it appears that the quotes syntax may be incorrect. I assume that
[Last Name] is text, if so, it needs to be delimited by quotes as you have
done for [MR_Number] and if [MR_Number] is actually a Number data type then
it should not be delimited by quotes. You may also have problems with DMax
and the single quotes as a text delimiter, if so, replace all of the single
quotes with double double quotes. This will definitely be a problem if the
[Last Name] field contains names that have apostrophes in them. If it does,
you'll have to use the double double quotes syntax or concatenate in Chr(34)
in lieu of the single quotes.

--
Wayne Morgan
MS Access MVP

hi wayne,

what i've done is paste the following into the "Default Value" property of
my "DaysView" subform i linked to the main form (called "Screening Log
(Edit
Only)") and am having displayed to the user in datasheet view

=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

the effect of this manoeuvering is that our friendly #Name? message
appears
in the "Visit #" control on the sub-form.

i guess we're not quite there, yet.

 

[Wayne Morgan]

Text values need to be delimited by quotes, numbers don't. In fact, not only
do number not need it, they need to not be delimited.

In your statement you have

"[IRB Number] = ' " & [IRBNumber] & " ' "

This is putting single quotes around the value IRBNumber. This would
indicate that IRBNumber is a text value, is this correct? If it is a number
data type in the field description then the code should be

"[IRB Number] = " & [IRBNumber]

Just the reverse is true of your line

"[Last Name] = " & [LastName] And

Here, you don't have LastName delimited. Names are usually string/text, so I
assume that [Last Name] is a text data type field. If so, the line should be

"[Last Name] = '" & [LastName] & "' And

You'll notice that I didn't put a double quote at the end, the word "And"
should also be inside the quotations. Now, for the problem I mentioned with
last names. If you have a name, such as O'Hare, which has an apostrophe in
it, using single quotes as delimiter will fail when it hits this name. There
are two ways to get double quotes in as delimiters. The problem is that the
double quote itself is a delimiter, so when VBA sees the double quote it
thinks that it is the beginning or end of the text string, so we need a way
to tell VBA that "I really mean to include this double quote as part of the
text string, not as the delimiter at the start or end of the text string."
There are two ways to do this. If you put in two of them together, VBA takes
that as "I really want this in there...". The other way is to use the Chr()
function. Chr(34) is a double quote. Using these two methods would change
the line above to

"[Last Name] = """ & [LastName] & """ And
or
"[Last Name] = " & Chr(34) & [LastName] & Chr(34) & " And

Now to modify the entire line, assuming that all of the "name" fields are
text and that all of the fields with "number" in their name are numbers, the
line should look like:

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

The names on the left side of the = should be the name of the field in the
table and the names on the right side of the = should be the name of the
control on the form.

--
Wayne Morgan
MS Access MVP

Hi Wayne,

On my 'DaysView' form I have the following Control Sources and their
respective Names

Source Form Name
Last Name LastName
First Name FirstName
MI M_I
MR_Number MRNumber
IRB Number IRBNumber
RecordNumber Visit

The code I have in the 'Default Value' property of the (form name) 'Visit'
field (w/ Record Number as its Control Source) is

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

I'm having trouble understanding the confusing instructions needed to
unscramble the quotations concerns in your response. I'm all for it if it'll
get this thing to work, but would you mind demonstrating what you meant; I'll
worry about grokking the meaning of it all when I can see it-it's probably
easier for you to undo what needs undoing it than describing it verbally
if
I'm correct.

 

[Ted]

wow, that's really instructive and i'm sure more folks than just myself will
find it useful.

there's one small 'catch' though and that was that when i pasted the final
equation into the 'Default Value' property the result was not any different:
#Name? continues to show up. so i'm wondering where else can the solution be
hiding given we've eliminated all the likely suspect. should there be a
default of zero ("0") in the underlying table for 'RecordNumber'? or ?

-ted

Text values need to be delimited by quotes, numbers don't. In fact, not only
do number not need it, they need to not be delimited.

In your statement you have

"[IRB Number] = ' " & [IRBNumber] & " ' "

This is putting single quotes around the value IRBNumber. This would
indicate that IRBNumber is a text value, is this correct? If it is a number
data type in the field description then the code should be

"[IRB Number] = " & [IRBNumber]

Just the reverse is true of your line

"[Last Name] = " & [LastName] And

Here, you don't have LastName delimited. Names are usually string/text, so I
assume that [Last Name] is a text data type field. If so, the line should be

"[Last Name] = '" & [LastName] & "' And

You'll notice that I didn't put a double quote at the end, the word "And"
should also be inside the quotations. Now, for the problem I mentioned with
last names. If you have a name, such as O'Hare, which has an apostrophe in
it, using single quotes as delimiter will fail when it hits this name. There
are two ways to get double quotes in as delimiters. The problem is that the
double quote itself is a delimiter, so when VBA sees the double quote it
thinks that it is the beginning or end of the text string, so we need a way
to tell VBA that "I really mean to include this double quote as part of the
text string, not as the delimiter at the start or end of the text string."
There are two ways to do this. If you put in two of them together, VBA takes
that as "I really want this in there...". The other way is to use the Chr()
function. Chr(34) is a double quote. Using these two methods would change
the line above to

"[Last Name] = """ & [LastName] & """ And
or
"[Last Name] = " & Chr(34) & [LastName] & Chr(34) & " And

Now to modify the entire line, assuming that all of the "name" fields are
text and that all of the fields with "number" in their name are numbers, the
line should look like:

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

The names on the left side of the = should be the name of the field in the
table and the names on the right side of the = should be the name of the
control on the form.

--
Wayne Morgan
MS Access MVP

Hi Wayne,

On my 'DaysView' form I have the following Control Sources and their
respective Names

Source Form Name
Last Name LastName
First Name FirstName
MI M_I
MR_Number MRNumber
IRB Number IRBNumber
RecordNumber Visit

The code I have in the 'Default Value' property of the (form name) 'Visit'
field (w/ Record Number as its Control Source) is

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

I'm having trouble understanding the confusing instructions needed to
unscramble the quotations concerns in your response. I'm all for it if it'll
get this thing to work, but would you mind demonstrating what you meant; I'll
worry about grokking the meaning of it all when I can see it-it's probably
easier for you to undo what needs undoing it than describing it verbally
if
I'm correct.

 

[Wayne Morgan]

The Nz function should take care of the "default of 0". Setting a default
won't help because that value isn't stored until the record is saved, so
DMax won't find it. DMax will return Null if it doesn't find a result and Nz
will change that to 0.

Make sure that when the expression is placed on a single line in the control
source that there is one space on each side of the word "and". Also, did I
guess correctly about the data types of the fields?

 

[Ted]

wayne,

i think you misspoke in your closing line when you referenced the control
source; did you mean to say default value?

in other respects, i could find no fault with the string and yes, you are
quite right in the assumptions wrt the character types of the
fields--however, MY fault, IRBNumber is a TEXT field!!! arrrghh, how did that
escape my laserlike concentration!

-ted

 

[Wayne Morgan]

You're correct, it should have been Default Value. If IRBNumber is text
then:

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

 

[Ted]

hi wayne,

i pasted the expression below which i'm copying and pasting 'as is' from the
'Default Value' property's setting below for good measure:

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

and still the #Name? appears in the 'Visit' control when viewing this
sub-form in datasheet view on the mainform. arrrgh :-(

-ted

You're correct, it should have been Default Value. If IRBNumber is text
then:

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

--
Wayne Morgan
MS Access MVP

wayne,

i think you misspoke in your closing line when you referenced the control
source; did you mean to say default value?

in other respects, i could find no fault with the string and yes, you are
quite right in the assumptions wrt the character types of the
fields--however, MY fault, IRBNumber is a TEXT field!!! arrrghh, how did
that
escape my laserlike concentration!

 

[Wayne Morgan]

Copying "as is" will cause a problem. The news reader has wrapped it to 4
lines. It needs to all be on one line. If that still doesn't get it, please
zip and forward me a copy of the file (or a link where it can be downloaded)
to (e-mail address removed). Attach a note telling me where in the file to look.

 

[Ted]

i opened a blank word document which i set to be in landscape mode and whose
margins i set to as wide as possible along with reducing the fontsize
maximally. i pasted the script you sent as is onto the said document but yet
even so it didn't fit onto one line but spilled over slightly onto the 2nd
one. i selected the first line and pasted it in the Default Value window and
then reprised that with the 10 or so characters remaining from the 2nd one.
still no cigar when i view the form but i suppose that was a long shot
anyway.

the infor'n in the file is 'sensitive' and i suppose i could remove all but
the two tables and forms involved in this thread, delete all but a few
records and alter the names of the persons described to anonymize it. still i
sort of wish it didn't have to come to that. which brings me to my next
'idea' -- would it be possible to use an event property instead of the
entering the expression into the 'Default Value' property? i just read
something on the web which seems to me to suggest that conditional defaults
could be vba-ed into an anfter update event? i'm thinking about
workarounds....

-ted

Copying "as is" will cause a problem. The news reader has wrapped it to 4
lines. It needs to all be on one line. If that still doesn't get it, please
zip and forward me a copy of the file (or a link where it can be downloaded)
to (e-mail address removed). Attach a note telling me where in the file to look.

--
Wayne Morgan
MS Access MVP

hi wayne,

i pasted the expression below which i'm copying and pasting 'as is' from
the
'Default Value' property's setting below for good measure:

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

and still the #Name? appears in the 'Visit' control when viewing this
sub-form in datasheet view on the mainform. arrrgh :-(

 

[Ted]

just remembered to add the fact that in the DaysView table, last name, first
name, mi, mr number, irb number and record number are PKs and that on the
control source of the mainform, last name, first name, mi, mr number and irb
number are the PKs? in case that matters....

-ted

Copying "as is" will cause a problem. The news reader has wrapped it to 4
lines. It needs to all be on one line. If that still doesn't get it, please
zip and forward me a copy of the file (or a link where it can be downloaded)
to (e-mail address removed). Attach a note telling me where in the file to look.

--
Wayne Morgan
MS Access MVP

hi wayne,

i pasted the expression below which i'm copying and pasting 'as is' from
the
'Default Value' property's setting below for good measure:

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

and still the #Name? appears in the 'Visit' control when viewing this
sub-form in datasheet view on the mainform. arrrgh :-(

 

[Wayne Morgan]

Yes, you may be able to do this in a VBA event. If you use the form's
Current event then check to see if you're at a new record, you could assign
a value to the control at that point. To do this in the Current event:

If Me.NewRecord Then
Me.NameOfControl = Nz(DMax("[RecordNumber]","[DaysView]","[Last Name] =
""" & [LastName] & """
And [First Name] = """ & [FirstName] & """ And [MI] = """ & [M_I] & """ And
[MR_Number] = " & [MRNumber] & " And [IRB Number] = """ & [IRBNumber] &
""""),0)+1
End If

There is one potential problem with this. If the value is set by the Default
Value property, it won't "dirty" the record. This prevents going to a new
record but not making any manual entries from causing a new record to be
created. However, doing this in the Current event will "dirty" the record.
You'll also need to see if the user makes any other entry and Undo the
record if they don't.

Another option is to place the above code in the form's Dirty event. That
way the value will only be inserted if the user first makes another entry to
create the record. If the user made that first entry in this field, then the
code would overwrite their entry. So you would need to keep the user from
making their first entry in the new record in this field.

--
Wayne Morgan
MS Access MVP

i opened a blank word document which i set to be in landscape mode and
whose
margins i set to as wide as possible along with reducing the fontsize
maximally. i pasted the script you sent as is onto the said document but
yet
even so it didn't fit onto one line but spilled over slightly onto the 2nd
one. i selected the first line and pasted it in the Default Value window
and
then reprised that with the 10 or so characters remaining from the 2nd
one.
still no cigar when i view the form but i suppose that was a long shot
anyway.

the infor'n in the file is 'sensitive' and i suppose i could remove all
but
the two tables and forms involved in this thread, delete all but a few
records and alter the names of the persons described to anonymize it.
still i
sort of wish it didn't have to come to that. which brings me to my next
'idea' -- would it be possible to use an event property instead of the
entering the expression into the 'Default Value' property? i just read
something on the web which seems to me to suggest that conditional
defaults
could be vba-ed into an anfter update event? i'm thinking about
workarounds....

-ted

Copying "as is" will cause a problem. The news reader has wrapped it to 4
lines. It needs to all be on one line. If that still doesn't get it,
please
zip and forward me a copy of the file (or a link where it can be
downloaded)
to (e-mail address removed). Attach a note telling me where in the file to
look.

--
Wayne Morgan
MS Access MVP

hi wayne,

i pasted the expression below which i'm copying and pasting 'as is'
from
the
'Default Value' property's setting below for good measure:

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

and still the #Name? appears in the 'Visit' control when viewing this
sub-form in datasheet view on the mainform. arrrgh :-(

 

[Ted]

we seem to be getting nearer something more useful.....

i pasted your string and modified the lefthand member into th current event
property vba thus

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


although when looking at it, it is on one (1) line; nevertheless when i went
to compile it, it gave me an error first about a missing ")" and now it's
just talking about 'Syntax Error'?

your Dirty Event idea sounds goog (did you read my posting about which are
going to make the PK elements)?

-ted

Yes, you may be able to do this in a VBA event. If you use the form's
Current event then check to see if you're at a new record, you could assign
a value to the control at that point. To do this in the Current event:

If Me.NewRecord Then
Me.NameOfControl = Nz(DMax("[RecordNumber]","[DaysView]","[Last Name] =
""" & [LastName] & """
And [First Name] = """ & [FirstName] & """ And [MI] = """ & [M_I] & """ And
[MR_Number] = " & [MRNumber] & " And [IRB Number] = """ & [IRBNumber] &
""""),0)+1
End If

There is one potential problem with this. If the value is set by the Default
Value property, it won't "dirty" the record. This prevents going to a new
record but not making any manual entries from causing a new record to be
created. However, doing this in the Current event will "dirty" the record.
You'll also need to see if the user makes any other entry and Undo the
record if they don't.

Another option is to place the above code in the form's Dirty event. That
way the value will only be inserted if the user first makes another entry to
create the record. If the user made that first entry in this field, then the
code would overwrite their entry. So you would need to keep the user from
making their first entry in the new record in this field.

--
Wayne Morgan
MS Access MVP

i opened a blank word document which i set to be in landscape mode and
whose
margins i set to as wide as possible along with reducing the fontsize
maximally. i pasted the script you sent as is onto the said document but
yet
even so it didn't fit onto one line but spilled over slightly onto the 2nd
one. i selected the first line and pasted it in the Default Value window
and
then reprised that with the 10 or so characters remaining from the 2nd
one.
still no cigar when i view the form but i suppose that was a long shot
anyway.

the infor'n in the file is 'sensitive' and i suppose i could remove all
but
the two tables and forms involved in this thread, delete all but a few
records and alter the names of the persons described to anonymize it.
still i
sort of wish it didn't have to come to that. which brings me to my next
'idea' -- would it be possible to use an event property instead of the
entering the expression into the 'Default Value' property? i just read
something on the web which seems to me to suggest that conditional
defaults
could be vba-ed into an anfter update event? i'm thinking about
workarounds....

-ted

Copying "as is" will cause a problem. The news reader has wrapped it to 4
lines. It needs to all be on one line. If that still doesn't get it,
please
zip and forward me a copy of the file (or a link where it can be
downloaded)
to (e-mail address removed). Attach a note telling me where in the file to
look.

--
Wayne Morgan
MS Access MVP


hi wayne,

i pasted the expression below which i'm copying and pasting 'as is'
from
the
'Default Value' property's setting below for good measure:

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

and still the #Name? appears in the 'Visit' control when viewing this
sub-form in datasheet view on the mainform. arrrgh :-(

 

[Wayne Morgan]

Thanks for copying and pasting what you have in the code.

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

The last set of quotes should be 4 double quotes ", you have 5.

Yes, I saw the second message, let's see if this helps first.

--
Wayne Morgan
MS Access MVP

we seem to be getting nearer something more useful.....

i pasted your string and modified the lefthand member into th current
event
property vba thus

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


although when looking at it, it is on one (1) line; nevertheless when i
went
to compile it, it gave me an error first about a missing ")" and now it's
just talking about 'Syntax Error'?

your Dirty Event idea sounds goog (did you read my posting about which are
going to make the PK elements)?

-ted

Yes, you may be able to do this in a VBA event. If you use the form's
Current event then check to see if you're at a new record, you could
assign
a value to the control at that point. To do this in the Current event:

If Me.NewRecord Then
Me.NameOfControl = Nz(DMax("[RecordNumber]","[DaysView]","[Last Name]
=
""" & [LastName] & """
And [First Name] = """ & [FirstName] & """ And [MI] = """ & [M_I] & """
And
[MR_Number] = " & [MRNumber] & " And [IRB Number] = """ & [IRBNumber] &
""""),0)+1
End If

There is one potential problem with this. If the value is set by the
Default
Value property, it won't "dirty" the record. This prevents going to a new
record but not making any manual entries from causing a new record to be
created. However, doing this in the Current event will "dirty" the
record.
You'll also need to see if the user makes any other entry and Undo the
record if they don't.

Another option is to place the above code in the form's Dirty event. That
way the value will only be inserted if the user first makes another entry
to
create the record. If the user made that first entry in this field, then
the
code would overwrite their entry. So you would need to keep the user from
making their first entry in the new record in this field.

--
Wayne Morgan
MS Access MVP

i opened a blank word document which i set to be in landscape mode and
whose
margins i set to as wide as possible along with reducing the fontsize
maximally. i pasted the script you sent as is onto the said document
but
yet
even so it didn't fit onto one line but spilled over slightly onto the
2nd
one. i selected the first line and pasted it in the Default Value
window
and
then reprised that with the 10 or so characters remaining from the 2nd
one.
still no cigar when i view the form but i suppose that was a long shot
anyway.

the infor'n in the file is 'sensitive' and i suppose i could remove all
but
the two tables and forms involved in this thread, delete all but a few
records and alter the names of the persons described to anonymize it.
still i
sort of wish it didn't have to come to that. which brings me to my next
'idea' -- would it be possible to use an event property instead of the
entering the expression into the 'Default Value' property? i just read
something on the web which seems to me to suggest that conditional
defaults
could be vba-ed into an anfter update event? i'm thinking about
workarounds....

-ted

:

Copying "as is" will cause a problem. The news reader has wrapped it
to 4
lines. It needs to all be on one line. If that still doesn't get it,
please
zip and forward me a copy of the file (or a link where it can be
downloaded)
to (e-mail address removed). Attach a note telling me where in the file to
look.

--
Wayne Morgan
MS Access MVP


hi wayne,

i pasted the expression below which i'm copying and pasting 'as is'
from
the
'Default Value' property's setting below for good measure:

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

and still the #Name? appears in the 'Visit' control when viewing
this
sub-form in datasheet view on the mainform. arrrgh :-(

 

[Ted]

so, i'm working on the Dirty Event property and adjusted the quotation number
to just 4 (i don't know where the extra quotation came from, but anyway)..

Private Sub Form_Dirty(Cancel As Integer)
Me.Visit = Nz(DMax("[RecordNumber]", "[DaysView]", "[Last Name] = """ &
[LastName] & """ And [First Name] = """ & [FirstName] & """ And [MI] = """ &
[M_I] & """ And [MR_Number] = " & [MRNumber] & " And [IRB Number] = """ &
[IRBNumber] & """"), 0) + 1
End Sub

when i open the mainform and view the subform datasheet, i see an asterisk
on the lefthand side sitting on what will be the (for the current patient)
9th subform record. the famous, or rather infamous, #Name? appears in the
Visit control. when i attempt to enter something in the Visit Type control a
message arrives on the screen which reads "Rim=time error '2465': Microsoft
can't find the field '|' in your expression." with several buttons at the
botoom like End, Debug and the like. when i clicked on Debug, it took me to
the Me.Visit line which was highlighted in yellow. hovering over Me.Visit
though, failed to show me any value?

best,

-ted

Thanks for copying and pasting what you have in the code.

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

The last set of quotes should be 4 double quotes ", you have 5.

Yes, I saw the second message, let's see if this helps first.

--
Wayne Morgan
MS Access MVP

we seem to be getting nearer something more useful.....

i pasted your string and modified the lefthand member into th current
event
property vba thus

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


although when looking at it, it is on one (1) line; nevertheless when i
went
to compile it, it gave me an error first about a missing ")" and now it's
just talking about 'Syntax Error'?

your Dirty Event idea sounds goog (did you read my posting about which are
going to make the PK elements)?

-ted

Yes, you may be able to do this in a VBA event. If you use the form's
Current event then check to see if you're at a new record, you could
assign
a value to the control at that point. To do this in the Current event:

If Me.NewRecord Then
Me.NameOfControl = Nz(DMax("[RecordNumber]","[DaysView]","[Last Name]
=
""" & [LastName] & """
And [First Name] = """ & [FirstName] & """ And [MI] = """ & [M_I] & """
And
[MR_Number] = " & [MRNumber] & " And [IRB Number] = """ & [IRBNumber] &
""""),0)+1
End If

There is one potential problem with this. If the value is set by the
Default
Value property, it won't "dirty" the record. This prevents going to a new
record but not making any manual entries from causing a new record to be
created. However, doing this in the Current event will "dirty" the
record.
You'll also need to see if the user makes any other entry and Undo the
record if they don't.

Another option is to place the above code in the form's Dirty event. That
way the value will only be inserted if the user first makes another entry
to
create the record. If the user made that first entry in this field, then
the
code would overwrite their entry. So you would need to keep the user from
making their first entry in the new record in this field.

--
Wayne Morgan
MS Access MVP


i opened a blank word document which i set to be in landscape mode and
whose
margins i set to as wide as possible along with reducing the fontsize
maximally. i pasted the script you sent as is onto the said document
but
yet
even so it didn't fit onto one line but spilled over slightly onto the
2nd
one. i selected the first line and pasted it in the Default Value
window
and
then reprised that with the 10 or so characters remaining from the 2nd
one.
still no cigar when i view the form but i suppose that was a long shot
anyway.

the infor'n in the file is 'sensitive' and i suppose i could remove all
but
the two tables and forms involved in this thread, delete all but a few
records and alter the names of the persons described to anonymize it.
still i
sort of wish it didn't have to come to that. which brings me to my next
'idea' -- would it be possible to use an event property instead of the
entering the expression into the 'Default Value' property? i just read
something on the web which seems to me to suggest that conditional
defaults
could be vba-ed into an anfter update event? i'm thinking about
workarounds....

-ted

:

Copying "as is" will cause a problem. The news reader has wrapped it
to 4
lines. It needs to all be on one line. If that still doesn't get it,
please
zip and forward me a copy of the file (or a link where it can be
downloaded)
to (e-mail address removed). Attach a note telling me where in the file to
look.

--
Wayne Morgan
MS Access MVP


hi wayne,

i pasted the expression below which i'm copying and pasting 'as is'
from
the
'Default Value' property's setting below for good measure:

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

and still the #Name? appears in the 'Visit' control when viewing
this
sub-form in datasheet view on the mainform. arrrgh :-(

 

[Wayne Morgan]

Me.Visit won't show a value while you're stopped at that line, it's not
assigned until that line is executed.

Either a name of a control or field is wrong or there are quotes in the
wrong place. Without actually seeing it, I would have a hard time fixing it.
If you could create a new mdb file, import the form, table structure (if you
import only the structure, no data will be imported) for these tables and
the queries I will type in some "dummy" data and see what I come up with.

To import only the table structure, click on the Advanced button in the
Import dialog (File|Get External Data|Import).

--
Wayne Morgan
MS Access MVP

so, i'm working on the Dirty Event property and adjusted the quotation
number
to just 4 (i don't know where the extra quotation came from, but anyway)..

Private Sub Form_Dirty(Cancel As Integer)
Me.Visit = Nz(DMax("[RecordNumber]", "[DaysView]", "[Last Name] = """ &
[LastName] & """ And [First Name] = """ & [FirstName] & """ And [MI] = """
&
[M_I] & """ And [MR_Number] = " & [MRNumber] & " And [IRB Number] = """ &
[IRBNumber] & """"), 0) + 1
End Sub

when i open the mainform and view the subform datasheet, i see an asterisk
on the lefthand side sitting on what will be the (for the current patient)
9th subform record. the famous, or rather infamous, #Name? appears in the
Visit control. when i attempt to enter something in the Visit Type control
a
message arrives on the screen which reads "Rim=time error '2465':
Microsoft
can't find the field '|' in your expression." with several buttons at the
botoom like End, Debug and the like. when i clicked on Debug, it took me
to
the Me.Visit line which was highlighted in yellow. hovering over Me.Visit
though, failed to show me any value?

best,

-ted

Thanks for copying and pasting what you have in the code.

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

The last set of quotes should be 4 double quotes ", you have 5.

Yes, I saw the second message, let's see if this helps first.

--
Wayne Morgan
MS Access MVP

we seem to be getting nearer something more useful.....

i pasted your string and modified the lefthand member into th current
event
property vba thus

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


although when looking at it, it is on one (1) line; nevertheless when i
went
to compile it, it gave me an error first about a missing ")" and now
it's
just talking about 'Syntax Error'?

your Dirty Event idea sounds goog (did you read my posting about which
are
going to make the PK elements)?

-ted

:

Yes, you may be able to do this in a VBA event. If you use the form's
Current event then check to see if you're at a new record, you could
assign
a value to the control at that point. To do this in the Current event:

If Me.NewRecord Then
Me.NameOfControl = Nz(DMax("[RecordNumber]","[DaysView]","[Last
Name]
=
""" & [LastName] & """
And [First Name] = """ & [FirstName] & """ And [MI] = """ & [M_I] &
"""
And
[MR_Number] = " & [MRNumber] & " And [IRB Number] = """ & [IRBNumber]
&
""""),0)+1
End If

There is one potential problem with this. If the value is set by the
Default
Value property, it won't "dirty" the record. This prevents going to a
new
record but not making any manual entries from causing a new record to
be
created. However, doing this in the Current event will "dirty" the
record.
You'll also need to see if the user makes any other entry and Undo the
record if they don't.

Another option is to place the above code in the form's Dirty event.
That
way the value will only be inserted if the user first makes another
entry
to
create the record. If the user made that first entry in this field,
then
the
code would overwrite their entry. So you would need to keep the user
from
making their first entry in the new record in this field.

--
Wayne Morgan
MS Access MVP


i opened a blank word document which i set to be in landscape mode
and
whose
margins i set to as wide as possible along with reducing the
fontsize
maximally. i pasted the script you sent as is onto the said document
but
yet
even so it didn't fit onto one line but spilled over slightly onto
the
2nd
one. i selected the first line and pasted it in the Default Value
window
and
then reprised that with the 10 or so characters remaining from the
2nd
one.
still no cigar when i view the form but i suppose that was a long
shot
anyway.

the infor'n in the file is 'sensitive' and i suppose i could remove
all
but
the two tables and forms involved in this thread, delete all but a
few
records and alter the names of the persons described to anonymize
it.
still i
sort of wish it didn't have to come to that. which brings me to my
next
'idea' -- would it be possible to use an event property instead of
the
entering the expression into the 'Default Value' property? i just
read
something on the web which seems to me to suggest that conditional
defaults
could be vba-ed into an anfter update event? i'm thinking about
workarounds....

-ted

:

Copying "as is" will cause a problem. The news reader has wrapped
it
to 4
lines. It needs to all be on one line. If that still doesn't get
it,
please
zip and forward me a copy of the file (or a link where it can be
downloaded)
to (e-mail address removed). Attach a note telling me where in the file
to
look.

--
Wayne Morgan
MS Access MVP


hi wayne,

i pasted the expression below which i'm copying and pasting 'as
is'
from
the
'Default Value' property's setting below for good measure:

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

and still the #Name? appears in the 'Visit' control when viewing
this
sub-form in datasheet view on the mainform. arrrgh :-(

 

[Ted]

i've been ooo on friday and well, y'know the weekend, but i managed to put
this idea of yours into the DaysView subform:

Private Sub Form_Current()
Me.txtCurKey1 = Me.LastName
Me.txtCurKey2 = Me.First_Name
Me.txtCurKey3 = Me.M_I
Me.txtCurKey4 = Me.MRNumber
Me.txtCurKey5 = Me.IRBNumber
Me.txtCurKey6 = Me.Visit
If Me.NewRecord Then
Me.Visit = Nz(DMax("[RecordNumber]", "[DaysView]", "[Last Name] = """ &
[LastName] & """ And [First Name] = """ & [FirstName] & """ And [MI] = """ &
[M_I] & """ And [MR_Number] = " & [MRNumber] & " And [IRB Number] = """ &
[IRBNumber] & """"), 0) + 1
End If
End Sub

(there was some extant code already in the OnCurrent event) and it's talking
about an error 2465 and "Oncology Screening Log can't find the field '|'
referred to in your expression". when i click Debug button it took me to the
Me.Visit line which is yellow hightlingted and hoevering over Me.Visit it
tells me its value is 'Null'.

getting an abbreviated structure to you that would be workable is not as
simple as it sounds because of the need to incorporate numerous l/u tables. i
wish it were.

where it stands right now.

-ted

Me.Visit won't show a value while you're stopped at that line, it's not
assigned until that line is executed.

Either a name of a control or field is wrong or there are quotes in the
wrong place. Without actually seeing it, I would have a hard time fixing it.
If you could create a new mdb file, import the form, table structure (if you
import only the structure, no data will be imported) for these tables and
the queries I will type in some "dummy" data and see what I come up with.

To import only the table structure, click on the Advanced button in the
Import dialog (File|Get External Data|Import).

--
Wayne Morgan
MS Access MVP

so, i'm working on the Dirty Event property and adjusted the quotation
number
to just 4 (i don't know where the extra quotation came from, but anyway)..

Private Sub Form_Dirty(Cancel As Integer)
Me.Visit = Nz(DMax("[RecordNumber]", "[DaysView]", "[Last Name] = """ &
[LastName] & """ And [First Name] = """ & [FirstName] & """ And [MI] = """
&
[M_I] & """ And [MR_Number] = " & [MRNumber] & " And [IRB Number] = """ &
[IRBNumber] & """"), 0) + 1
End Sub

when i open the mainform and view the subform datasheet, i see an asterisk
on the lefthand side sitting on what will be the (for the current patient)
9th subform record. the famous, or rather infamous, #Name? appears in the
Visit control. when i attempt to enter something in the Visit Type control
a
message arrives on the screen which reads "Rim=time error '2465':
Microsoft
can't find the field '|' in your expression." with several buttons at the
botoom like End, Debug and the like. when i clicked on Debug, it took me
to
the Me.Visit line which was highlighted in yellow. hovering over Me.Visit
though, failed to show me any value?

best,

-ted

Thanks for copying and pasting what you have in the code.

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

The last set of quotes should be 4 double quotes ", you have 5.

Yes, I saw the second message, let's see if this helps first.

--
Wayne Morgan
MS Access MVP


we seem to be getting nearer something more useful.....

i pasted your string and modified the lefthand member into th current
event
property vba thus

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


although when looking at it, it is on one (1) line; nevertheless when i
went
to compile it, it gave me an error first about a missing ")" and now
it's
just talking about 'Syntax Error'?

your Dirty Event idea sounds goog (did you read my posting about which
are
going to make the PK elements)?

-ted

:

Yes, you may be able to do this in a VBA event. If you use the form's
Current event then check to see if you're at a new record, you could
assign
a value to the control at that point. To do this in the Current event:

If Me.NewRecord Then
Me.NameOfControl = Nz(DMax("[RecordNumber]","[DaysView]","[Last
Name]
=
""" & [LastName] & """
And [First Name] = """ & [FirstName] & """ And [MI] = """ & [M_I] &
"""
And
[MR_Number] = " & [MRNumber] & " And [IRB Number] = """ & [IRBNumber]
&
""""),0)+1
End If

There is one potential problem with this. If the value is set by the
Default
Value property, it won't "dirty" the record. This prevents going to a
new
record but not making any manual entries from causing a new record to
be
created. However, doing this in the Current event will "dirty" the
record.
You'll also need to see if the user makes any other entry and Undo the
record if they don't.

Another option is to place the above code in the form's Dirty event.
That
way the value will only be inserted if the user first makes another
entry
to
create the record. If the user made that first entry in this field,
then
the
code would overwrite their entry. So you would need to keep the user
from
making their first entry in the new record in this field.

--
Wayne Morgan
MS Access MVP


i opened a blank word document which i set to be in landscape mode
and
whose
margins i set to as wide as possible along with reducing the
fontsize
maximally. i pasted the script you sent as is onto the said document
but
yet
even so it didn't fit onto one line but spilled over slightly onto
the
2nd
one. i selected the first line and pasted it in the Default Value
window
and
then reprised that with the 10 or so characters remaining from the
2nd
one.
still no cigar when i view the form but i suppose that was a long
shot
anyway.

the infor'n in the file is 'sensitive' and i suppose i could remove
all
but
the two tables and forms involved in this thread, delete all but a
few
records and alter the names of the persons described to anonymize
it.
still i
sort of wish it didn't have to come to that. which brings me to my
next
'idea' -- would it be possible to use an event property instead of
the
entering the expression into the 'Default Value' property? i just
read
something on the web which seems to me to suggest that conditional
defaults
could be vba-ed into an anfter update event? i'm thinking about
workarounds....

-ted

:

Copying "as is" will cause a problem. The news reader has wrapped
it
to 4
lines. It needs to all be on one line. If that still doesn't get
it,
please
zip and forward me a copy of the file (or a link where it can be
downloaded)
to (e-mail address removed). Attach a note telling me where in the file
to
look.

--
Wayne Morgan
MS Access MVP


hi wayne,

i pasted the expression below which i'm copying and pasting 'as
is'
from
the
'Default Value' property's setting below for good measure:

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

and still the #Name? appears in the 'Visit' control when viewing
this
sub-form in datasheet view on the mainform. arrrgh :-(