automatically apply index counter to subform records

[Bob Quintal]

=?Utf-8?B?QWNjZXNzTWFu?= <[email protected]>
wrote in

I have a subform that has a hidden field that is part of the
key, but which is irrelevant for this particular form. I need
to apply a unique value to each subform record to avoid key
violations, and I would like this to happen automatically as
each subform record is updated. I'm new to VB, and I think
I know how to update the field value, but it's not obvious to
me what to use as a source for the update value. Ideally, it
should be a sequential autonumber that starts at 1 each time
the subform is opened. Any ideas? Thanks!

you say as each subform record is updated. Do you mean as each
subform record is created?

And do you mean that it should start at 1 for each new parent
record that is created?

if no is the answer to either of those questions, we'll need a
lot more detail on what you are trying to accomplish.

If you've answered yes to both my questions, just add this to
the On Current event of the subform

If me.newRecord then
me.hiddenfield = nz(dMax("hiddenfield","subformtablename",
"mainkeyname = " & parent.mainkeyname & ")"
end if

substitute real names for hiddenfield, subformtablename and
mainkeyname.

Also if mainkeyname refers to a text field, not a number, then
the last portion of the query (after the = sign should have
extra quotes: = """ & parent.mainkeyname & """)"

 

[Guest]

I have a subform that has a hidden field that is part of the key, but which
is irrelevant for this particular form. I need to apply a unique value to
each subform record to avoid key violations, and I would like this to happen
automatically as each subform record is updated. I'm new to VB, and I think
I know how to update the field value, but it's not obvious to me what to use
as a source for the update value. Ideally, it should be a sequential
autonumber that starts at 1 each time the subform is opened. Any ideas?
Thanks!

 

[Guest]

Thanks Bob. I'm happy to say that the answer is yes to both questions! This
is so intriguing, I have a few questions before I dive in. I'm new to VBA.

1 - There seems to be a missing closing parentheses. I suppose it must go
at the end, but that makes me wonder what the ")" is all about.

2 - After looking up dmax and nz, this expression looks like it is returning
a string. How does this return the sequential integers that I need?

3 - My subform recordsource is a query. Would I use the query name for
subformtablename?

Thanks!

 

[Bob Quintal]

=?Utf-8?B?QWNjZXNzTWFu?= <[email protected]>
wrote in

Thanks Bob. I'm happy to say that the answer is yes to both
questions! This is so intriguing, I have a few questions
before I dive in. I'm new to VBA.

1 - There seems to be a missing closing parentheses. I
suppose it must go at the end, but that makes me wonder what
the ")" is all about.

Yes, I did a sloppy cut and paste, and my proofreader (me) was
out to lunch. It's also missing a ,0 preceding it.
The correct version should be:
me.hiddenfield = nz(dMax("hiddenfield","subformtablename",
"mainkeyname = " & parent.mainkeyname & "),0)

2 - After looking up dmax and nz, this expression looks like
it is returning a string. How does this return the sequential
integers that I need?

Dmax should return the value for a numeric field. the nz
actually returns a variant which is either a string or a number
dependent on the values inside the parentheses

As well, through the magic of Access, if the data in the textbox
can be treated as a number and is bound to a numeric field, it
will be coerced into a number when the record is saved.

3 - My subform recordsource is a query. Would I use the query
name for subformtablename?
Yes.

Thanks!

No problem.

=?Utf-8?B?QWNjZXNzTWFu?=

you say as each subform record is updated. Do you mean as
each subform record is created?

And do you mean that it should start at 1 for each new parent
record that is created?

if no is the answer to either of those questions, we'll need
a lot more detail on what you are trying to accomplish.

If you've answered yes to both my questions, just add this to
the On Current event of the subform

If me.newRecord then
me.hiddenfield = nz(dMax("hiddenfield","subformtablename",
"mainkeyname = " & parent.mainkeyname & ")"
end if

substitute real names for hiddenfield, subformtablename and
mainkeyname.

Also if mainkeyname refers to a text field, not a number,
then the last portion of the query (after the = sign should
have extra quotes: = """ & parent.mainkeyname & """)"

 

[Bob Quintal]

=?Utf-8?B?QWNjZXNzTWFu?= <[email protected]>
wrote in

Bob:

I am trying to implement your suggestion, but there is a
complication that I am having difficulty dealing with. The
key of the parent has multiple fields (3), and I'm trying to
figure out how to accommodate that. Again, I'm new to VBA.
I've entered the following which yields a compile error:
expected list separator or ).

me.hiddenfield =
nz(dmax("hiddenfield","subformtablename","[parentkey1]& [parentk
ey2]&[parentkey3] = """ & me.parent.parentkey1 &
me.parent.parentkey2 & me.parent.parentkey3 & """)",0)

All fields are text.

I'll often break long clauses into sections for debugging.

dim stWhereClause as string
stWhereClause = "[ParentKey1] & [parentkey2] & [parentkey3]=""
stWhereClause = stWhereClause & parent!parentkey1
stWhereClause = stWhereClause & parent!parentkey2
stWhereClause = stWhereClause & parent!parentkey3 & """"
nz(dmax("hiddenfield","subformtablename", stwhereClause &
""""),0)

And it worked, because I noticed that there is a misplaced ) in
the original """)",0) should be """"),0)

Also, should the reference to the parent key be of the format
me.parent!parentkey instead of parent.parentkey, i.e., use of
! instead of . (dot) before parentkey?

parent!Parentkey is correct but either will work. No need for
the me.

Thanks!

=?Utf-8?B?QWNjZXNzTWFu?=


Yes, I did a sloppy cut and paste, and my proofreader (me)
was out to lunch. It's also missing a ,0 preceding it.
The correct version should be:
me.hiddenfield = nz(dMax("hiddenfield","subformtablename",
"mainkeyname = " & parent.mainkeyname & "),0)


Dmax should return the value for a numeric field. the nz
actually returns a variant which is either a string or a
number dependent on the values inside the parentheses

As well, through the magic of Access, if the data in the
textbox can be treated as a number and is bound to a numeric
field, it will be coerced into a number when the record is
saved.
No problem.

 

[Guest]

Bob:

I am trying to implement your suggestion, but there is a complication that I
am having difficulty dealing with. The key of the parent has multiple fields
(3), and I'm trying to figure out how to accommodate that. Again, I'm new to
VBA. I've entered the following which yields a compile error: expected list
separator or ).

me.hiddenfield =
nz(dmax("hiddenfield","subformtablename","[parentkey1]&[parentkey2]&[parentkey3]
= """ & me.parent.parentkey1 & me.parent.parentkey2 & me.parent.parentkey3 &
""")",0)

All fields are text.

Also, should the reference to the parent key be of the format
me.parent!parentkey instead of parent.parentkey, i.e., use of ! instead of .
(dot) before parentkey?

Thanks!

=?Utf-8?B?QWNjZXNzTWFu?= <[email protected]>
wrote in

Thanks Bob. I'm happy to say that the answer is yes to both
questions! This is so intriguing, I have a few questions
before I dive in. I'm new to VBA.

1 - There seems to be a missing closing parentheses. I
suppose it must go at the end, but that makes me wonder what
the ")" is all about.

Yes, I did a sloppy cut and paste, and my proofreader (me) was
out to lunch. It's also missing a ,0 preceding it.
The correct version should be:
me.hiddenfield = nz(dMax("hiddenfield","subformtablename",
"mainkeyname = " & parent.mainkeyname & "),0)

2 - After looking up dmax and nz, this expression looks like
it is returning a string. How does this return the sequential
integers that I need?

Dmax should return the value for a numeric field. the nz
actually returns a variant which is either a string or a number
dependent on the values inside the parentheses

As well, through the magic of Access, if the data in the textbox
can be treated as a number and is bound to a numeric field, it
will be coerced into a number when the record is saved.

3 - My subform recordsource is a query. Would I use the query
name for subformtablename?
Yes.

Thanks!

No problem.

 

[Bob Quintal]

=?Utf-8?B?QWNjZXNzTWFu?= <[email protected]>
wrote in

You used two double quotes at the end of the first
stWhereClause statement. When I enter that, VBA adds a third
set for some reason. I entered everything else as you wrote.

Should be three double quotes. Reason: to embed a one Double
quote inside a quoted string.

When I try to open the form I get run-time error 2001: you
canceled the previous operation. The debugger had the
me.hiddenfield = statement highlighted.


I moved up to the first stWhereClause statement and deleted
two of the three double quotes at the end of the statement,
leaving just one double quote. I saved the VBA, then closed
the form and opened it again and got ...

Run-time error '3075': Syntax error (missing operator) in
query expression '[parentkey1]&[parentkey2]&[parentkey3]=
<N/A><N/A>'''

The last above ''' could actually be '" or "'.

It would be " ' and the wholee rerror message indicates that
it's missing the balancing double quote in front of <N/A
Observe that

there is no value for parentkey3 in the error message: <N/A>
is the default value for parentkey1 and parentkey2.

That means there is no value in parentkey3.

I am

testing this solution using a main form that is configured for
Data Entry, so there is no parent record when I open the form
and subform. Also, parentkey3 is date format, not text.

That might explain the 2501 error you mentioned above in that
some event is being cancelled because there is no record.

The code needs to be deferred from executing until the mainform
is populated. I'm not sure where you have it set to run now. but
I think it needs to go in another event.

I'm not sure which, however.

Sorry for the gory detail, but I'm struggling.

=?Utf-8?B?QWNjZXNzTWFu?=

Bob:

I am trying to implement your suggestion, but there is a
complication that I am having difficulty dealing with. The
key of the parent has multiple fields (3), and I'm trying
to figure out how to accommodate that. Again, I'm new to
VBA. I've entered the following which yields a compile
error: expected list separator or ).

me.hiddenfield =
nz(dmax("hiddenfield","subformtablename","[parentkey1]& [parentk
ey2]&[parentkey3] = """ & me.parent.parentkey1 &
me.parent.parentkey2 & me.parent.parentkey3 & """)",0)

All fields are text.

I'll often break long clauses into sections for debugging.

dim stWhereClause as string
stWhereClause = "[ParentKey1] & [parentkey2] &
[parentkey3]="" stWhereClause = stWhereClause &
parent!parentkey1 stWhereClause = stWhereClause &
parent!parentkey2 stWhereClause = stWhereClause &
parent!parentkey3 & """"
nz(dmax("hiddenfield","subformtablename", stwhereClause &
""""),0)

And it worked, because I noticed that there is a misplaced )
in the original """)",0) should be """"),0)

Also, should the reference to the parent key be of the
format me.parent!parentkey instead of parent.parentkey,
i.e., use of ! instead of . (dot) before parentkey?

parent!Parentkey is correct but either will work. No need for
the me.

Thanks!

:

=?Utf-8?B?QWNjZXNzTWFu?=

Thanks Bob. I'm happy to say that the answer is yes to
both questions! This is so intriguing, I have a few
questions before I dive in. I'm new to VBA.

1 - There seems to be a missing closing parentheses. I
suppose it must go at the end, but that makes me wonder
what the ")" is all about.

Yes, I did a sloppy cut and paste, and my proofreader (me)
was out to lunch. It's also missing a ,0 preceding it.
The correct version should be:
me.hiddenfield = nz(dMax("hiddenfield","subformtablename",
"mainkeyname = " & parent.mainkeyname & "),0)


2 - After looking up dmax and nz, this expression looks
like it is returning a string. How does this return the
sequential integers that I need?

Dmax should return the value for a numeric field. the nz
actually returns a variant which is either a string or a
number dependent on the values inside the parentheses

As well, through the magic of Access, if the data in the
textbox can be treated as a number and is bound to a
numeric field, it will be coerced into a number when the
record is saved.

3 - My subform recordsource is a query. Would I use the
query name for subformtablename?

Yes.

Thanks!

No problem.


:

=?Utf-8?B?QWNjZXNzTWFu?=
@microsoft.com:

I have a subform that has a hidden field that is part
of the key, but which is irrelevant for this
particular form. I need to apply a unique value to
each subform record to avoid key violations, and I
would like this to happen automatically as each
subform record is updated. I'm new to VB, and I think
I know how to update the field value, but it's not
obvious to me what to use as a source for the update
value. Ideally, it should be a sequential autonumber
that starts at 1 each time the subform is opened.
Any ideas? Thanks!

you say as each subform record is updated. Do you mean
as each subform record is created?

And do you mean that it should start at 1 for each new
parent record that is created?

if no is the answer to either of those questions, we'll
need a lot more detail on what you are trying to
accomplish.

If you've answered yes to both my questions, just add
this to the On Current event of the subform

If me.newRecord then
me.hiddenfield =
nz(dMax("hiddenfield","subformtablename", "mainkeyname
= " & parent.mainkeyname & ")" end if

substitute real names for hiddenfield, subformtablename
and mainkeyname.

Also if mainkeyname refers to a text field, not a
number, then the last portion of the query (after the =
sign should have extra quotes: = """ &
parent.mainkeyname & """)"

 

[Guest]

You used two double quotes at the end of the first stWhereClause statement.
When I enter that, VBA adds a third set for some reason. I entered
everything else as you wrote.

When I try to open the form I get run-time error 2001: you canceled the
previous operation. The debugger had the me.hiddenfield = statement
highlighted.

I moved up to the first stWhereClause statement and deleted two of the three
double quotes at the end of the statement, leaving just one double quote. I
saved the VBA, then closed the form and opened it again and got ...

Run-time error '3075': Syntax error (missing operator) in query expression
'[parentkey1]&[parentkey2]&[parentkey3]= <N/A><N/A>'''

The last above ''' could actually be '" or "'. Observe that there is no
value for parentkey3 in the error message: <N/A> is the default value for
parentkey1 and parentkey2. I am testing this solution using a main form that
is configured for Data Entry, so there is no parent record when I open the
form and subform. Also, parentkey3 is date format, not text.

Sorry for the gory detail, but I'm struggling.

=?Utf-8?B?QWNjZXNzTWFu?= <[email protected]>
wrote in

Bob:

I am trying to implement your suggestion, but there is a
complication that I am having difficulty dealing with. The
key of the parent has multiple fields (3), and I'm trying to
figure out how to accommodate that. Again, I'm new to VBA.
I've entered the following which yields a compile error:
expected list separator or ).

me.hiddenfield =
nz(dmax("hiddenfield","subformtablename","[parentkey1]& [parentk
ey2]&[parentkey3] = """ & me.parent.parentkey1 &
me.parent.parentkey2 & me.parent.parentkey3 & """)",0)

All fields are text.

I'll often break long clauses into sections for debugging.

dim stWhereClause as string
stWhereClause = "[ParentKey1] & [parentkey2] & [parentkey3]=""
stWhereClause = stWhereClause & parent!parentkey1
stWhereClause = stWhereClause & parent!parentkey2
stWhereClause = stWhereClause & parent!parentkey3 & """"
nz(dmax("hiddenfield","subformtablename", stwhereClause &
""""),0)

And it worked, because I noticed that there is a misplaced ) in
the original """)",0) should be """"),0)

Also, should the reference to the parent key be of the format
me.parent!parentkey instead of parent.parentkey, i.e., use of
! instead of . (dot) before parentkey?

parent!Parentkey is correct but either will work. No need for
the me.

 

[Guest]

I restored the 3 double quotes.

The code is associated with On Current of the subform. The run-time error
2001 (you canceled the previous operation) appears as soon as I open the form.

I moved the code to Before Update, and got the run-time error 2001, but this
time it appeared after I entered data into a subform record and tried
changing focus to a new subform record. The debugger had the nz statement
highlighted.

So much to my surprise, On Current on the subform seems to be getting
triggered as soon as the main form is opened. I've dealt with that by moving
the code to Before Update of the subform, but something is still awry. Hmmmm.

=?Utf-8?B?QWNjZXNzTWFu?= <[email protected]>
wrote in

You used two double quotes at the end of the first
stWhereClause statement. When I enter that, VBA adds a third
set for some reason. I entered everything else as you wrote.

Should be three double quotes. Reason: to embed a one Double
quote inside a quoted string.

When I try to open the form I get run-time error 2001: you
canceled the previous operation. The debugger had the
me.hiddenfield = statement highlighted.


I moved up to the first stWhereClause statement and deleted
two of the three double quotes at the end of the statement,
leaving just one double quote. I saved the VBA, then closed
the form and opened it again and got ...

Run-time error '3075': Syntax error (missing operator) in
query expression '[parentkey1]&[parentkey2]&[parentkey3]=
<N/A><N/A>'''

The last above ''' could actually be '" or "'.

It would be " ' and the wholee rerror message indicates that
it's missing the balancing double quote in front of <N/A
Observe that

there is no value for parentkey3 in the error message: <N/A>
is the default value for parentkey1 and parentkey2.

That means there is no value in parentkey3.

I am

testing this solution using a main form that is configured for
Data Entry, so there is no parent record when I open the form
and subform. Also, parentkey3 is date format, not text.

That might explain the 2501 error you mentioned above in that
some event is being cancelled because there is no record.

The code needs to be deferred from executing until the mainform
is populated. I'm not sure where you have it set to run now. but
I think it needs to go in another event.

I'm not sure which, however.

Sorry for the gory detail, but I'm struggling.

=?Utf-8?B?QWNjZXNzTWFu?=

Bob:

I am trying to implement your suggestion, but there is a
complication that I am having difficulty dealing with. The
key of the parent has multiple fields (3), and I'm trying
to figure out how to accommodate that. Again, I'm new to
VBA. I've entered the following which yields a compile
error: expected list separator or ).

me.hiddenfield =
nz(dmax("hiddenfield","subformtablename","[parentkey1]&
[parentk
ey2]&[parentkey3] = """ & me.parent.parentkey1 &
me.parent.parentkey2 & me.parent.parentkey3 & """)",0)

All fields are text.

I'll often break long clauses into sections for debugging.

dim stWhereClause as string
stWhereClause = "[ParentKey1] & [parentkey2] &
[parentkey3]="" stWhereClause = stWhereClause &
parent!parentkey1 stWhereClause = stWhereClause &
parent!parentkey2 stWhereClause = stWhereClause &
parent!parentkey3 & """"
nz(dmax("hiddenfield","subformtablename", stwhereClause &
""""),0)

And it worked, because I noticed that there is a misplaced )
in the original """)",0) should be """"),0)


Also, should the reference to the parent key be of the
format me.parent!parentkey instead of parent.parentkey,
i.e., use of ! instead of . (dot) before parentkey?

parent!Parentkey is correct but either will work. No need for
the me.


Thanks!

:

=?Utf-8?B?QWNjZXNzTWFu?=

Thanks Bob. I'm happy to say that the answer is yes to
both questions! This is so intriguing, I have a few
questions before I dive in. I'm new to VBA.

1 - There seems to be a missing closing parentheses. I
suppose it must go at the end, but that makes me wonder
what the ")" is all about.

Yes, I did a sloppy cut and paste, and my proofreader (me)
was out to lunch. It's also missing a ,0 preceding it.
The correct version should be:
me.hiddenfield = nz(dMax("hiddenfield","subformtablename",
"mainkeyname = " & parent.mainkeyname & "),0)


2 - After looking up dmax and nz, this expression looks
like it is returning a string. How does this return the
sequential integers that I need?

Dmax should return the value for a numeric field. the nz
actually returns a variant which is either a string or a
number dependent on the values inside the parentheses

As well, through the magic of Access, if the data in the
textbox can be treated as a number and is bound to a
numeric field, it will be coerced into a number when the
record is saved.

3 - My subform recordsource is a query. Would I use the
query name for subformtablename?

Yes.

Thanks!

No problem.


:

=?Utf-8?B?QWNjZXNzTWFu?=
@microsoft.com:

I have a subform that has a hidden field that is part
of the key, but which is irrelevant for this
particular form. I need to apply a unique value to
each subform record to avoid key violations, and I
would like this to happen automatically as each
subform record is updated. I'm new to VB, and I think
I know how to update the field value, but it's not
obvious to me what to use as a source for the update
value. Ideally, it should be a sequential autonumber
that starts at 1 each time the subform is opened.
Any ideas? Thanks!

you say as each subform record is updated. Do you mean
as each subform record is created?

And do you mean that it should start at 1 for each new
parent record that is created?

if no is the answer to either of those questions, we'll
need a lot more detail on what you are trying to
accomplish.

If you've answered yes to both my questions, just add
this to the On Current event of the subform

If me.newRecord then
me.hiddenfield =
nz(dMax("hiddenfield","subformtablename", "mainkeyname
= " & parent.mainkeyname & ")" end if

substitute real names for hiddenfield, subformtablename
and mainkeyname.

Also if mainkeyname refers to a text field, not a
number, then the last portion of the query (after the =
sign should have extra quotes: = """ &
parent.mainkeyname & """)"


--
Bob Quintal

PA is y I've altered my email address.

 

[Guest]

Bob:

Research with HELP confirms that the subform gets its On Current event
before the subform, so Before Update is the way to go.

I think there is something not right about the code in the nz statement.
Would a simpler solution be to just set my hidden field to the count of
subform records as they are created? Would you know the code for that? I
haven't reached a sufficient critical mass of VBA knowledge to figure it out
myself.

Thanks!

=?Utf-8?B?QWNjZXNzTWFu?= <[email protected]>
wrote in

You used two double quotes at the end of the first
stWhereClause statement. When I enter that, VBA adds a third
set for some reason. I entered everything else as you wrote.

Should be three double quotes. Reason: to embed a one Double
quote inside a quoted string.

When I try to open the form I get run-time error 2001: you
canceled the previous operation. The debugger had the
me.hiddenfield = statement highlighted.


I moved up to the first stWhereClause statement and deleted
two of the three double quotes at the end of the statement,
leaving just one double quote. I saved the VBA, then closed
the form and opened it again and got ...

Run-time error '3075': Syntax error (missing operator) in
query expression '[parentkey1]&[parentkey2]&[parentkey3]=
<N/A><N/A>'''

The last above ''' could actually be '" or "'.

It would be " ' and the wholee rerror message indicates that
it's missing the balancing double quote in front of <N/A
Observe that

there is no value for parentkey3 in the error message: <N/A>
is the default value for parentkey1 and parentkey2.

That means there is no value in parentkey3.

I am

testing this solution using a main form that is configured for
Data Entry, so there is no parent record when I open the form
and subform. Also, parentkey3 is date format, not text.

That might explain the 2501 error you mentioned above in that
some event is being cancelled because there is no record.

The code needs to be deferred from executing until the mainform
is populated. I'm not sure where you have it set to run now. but
I think it needs to go in another event.

I'm not sure which, however.

Sorry for the gory detail, but I'm struggling.

=?Utf-8?B?QWNjZXNzTWFu?=

Bob:

I am trying to implement your suggestion, but there is a
complication that I am having difficulty dealing with. The
key of the parent has multiple fields (3), and I'm trying
to figure out how to accommodate that. Again, I'm new to
VBA. I've entered the following which yields a compile
error: expected list separator or ).

me.hiddenfield =
nz(dmax("hiddenfield","subformtablename","[parentkey1]&
[parentk
ey2]&[parentkey3] = """ & me.parent.parentkey1 &
me.parent.parentkey2 & me.parent.parentkey3 & """)",0)

All fields are text.

I'll often break long clauses into sections for debugging.

dim stWhereClause as string
stWhereClause = "[ParentKey1] & [parentkey2] &
[parentkey3]="" stWhereClause = stWhereClause &
parent!parentkey1 stWhereClause = stWhereClause &
parent!parentkey2 stWhereClause = stWhereClause &
parent!parentkey3 & """"
nz(dmax("hiddenfield","subformtablename", stwhereClause &
""""),0)

And it worked, because I noticed that there is a misplaced )
in the original """)",0) should be """"),0)


Also, should the reference to the parent key be of the
format me.parent!parentkey instead of parent.parentkey,
i.e., use of ! instead of . (dot) before parentkey?

parent!Parentkey is correct but either will work. No need for
the me.


Thanks!

:

=?Utf-8?B?QWNjZXNzTWFu?=

Thanks Bob. I'm happy to say that the answer is yes to
both questions! This is so intriguing, I have a few
questions before I dive in. I'm new to VBA.

1 - There seems to be a missing closing parentheses. I
suppose it must go at the end, but that makes me wonder
what the ")" is all about.

Yes, I did a sloppy cut and paste, and my proofreader (me)
was out to lunch. It's also missing a ,0 preceding it.
The correct version should be:
me.hiddenfield = nz(dMax("hiddenfield","subformtablename",
"mainkeyname = " & parent.mainkeyname & "),0)


2 - After looking up dmax and nz, this expression looks
like it is returning a string. How does this return the
sequential integers that I need?

Dmax should return the value for a numeric field. the nz
actually returns a variant which is either a string or a
number dependent on the values inside the parentheses

As well, through the magic of Access, if the data in the
textbox can be treated as a number and is bound to a
numeric field, it will be coerced into a number when the
record is saved.

3 - My subform recordsource is a query. Would I use the
query name for subformtablename?

Yes.

Thanks!

No problem.


:

=?Utf-8?B?QWNjZXNzTWFu?=
@microsoft.com:

I have a subform that has a hidden field that is part
of the key, but which is irrelevant for this
particular form. I need to apply a unique value to
each subform record to avoid key violations, and I
would like this to happen automatically as each
subform record is updated. I'm new to VB, and I think
I know how to update the field value, but it's not
obvious to me what to use as a source for the update
value. Ideally, it should be a sequential autonumber
that starts at 1 each time the subform is opened.
Any ideas? Thanks!

you say as each subform record is updated. Do you mean
as each subform record is created?

And do you mean that it should start at 1 for each new
parent record that is created?

if no is the answer to either of those questions, we'll
need a lot more detail on what you are trying to
accomplish.

If you've answered yes to both my questions, just add
this to the On Current event of the subform

If me.newRecord then
me.hiddenfield =
nz(dMax("hiddenfield","subformtablename", "mainkeyname
= " & parent.mainkeyname & ")" end if

substitute real names for hiddenfield, subformtablename
and mainkeyname.

Also if mainkeyname refers to a text field, not a
number, then the last portion of the query (after the =
sign should have extra quotes: = """ &
parent.mainkeyname & """)"


--
Bob Quintal

PA is y I've altered my email address.

 

[Bob Quintal]

=?Utf-8?B?QWNjZXNzTWFu?= <[email protected]>
wrote in

Bob:

Research with HELP confirms that the subform gets its On
Current event before the subform, so Before Update is the way
to go.

Yes. The on current triggers immidately whenever a form displays
a different record. The before update is triggered just before
Access writes data to the table, which is when you need it. You
may also want to put an If me.newrec /end if block around the
code so that you don't modify the value of an existing row in
the subform.

I think there is something not right about the code in the nz
statement. Would a simpler solution be to just set my hidden
field to the count of subform records as they are created?
Would you know the code for that? I haven't reached a
sufficient critical mass of VBA knowledge to figure it out
myself.

It would be just as complicated to do a DCount() instead of a
DMax() and has the disadvantage that given records 1,2,4,5
(because 3 has been deleted) gives the wrong result.

Could the fact that one of your fields in the key is creating
the problem?

We could change the DMax/Dcount to check the three criteria
separately, anded instead of concatenating them


dim stWhereClause as string
stWhereClause = "[ParentKey1] = """
stWhereClause = stWhereClause & parent!parentkey1 & """"
stWhereClause = stWhereClause & " AND [ParentKey2] = """
stWhereClause = stWhereClause & parent!parentkey2 & """"
' Note the different delimiters because key 3 is a date.
stWhereClause = stWhereClause & " AND [ParentKey3] = #"
stWhereClause = stWhereClause & parent!parentkey3 & "#"


Hiddenfield = nz(dmax("hiddenfield","subformtablename",
stwhereClause & """"),0) + 1

Thanks!

 

[Guest]

Bob:

Yes, you make an excellent point about use of DMax instead of DCount - thank
you.

I'm still getting run-time error 3075: Syntax error in string in query
expression '[parentkey1]="<N/A>" AND [parentkey2]="<N/A>" AND
[parentkey3]="<N/A>" AND [parentkey4]=#7/9/2007 9:30:00 AM#"'. The <N/A>
values are correct, and the date looks OK. I can't see what is tripping this
up.

=?Utf-8?B?QWNjZXNzTWFu?= <[email protected]>
wrote in

Bob:

Research with HELP confirms that the subform gets its On
Current event before the subform, so Before Update is the way
to go.

Yes. The on current triggers immidately whenever a form displays
a different record. The before update is triggered just before
Access writes data to the table, which is when you need it. You
may also want to put an If me.newrec /end if block around the
code so that you don't modify the value of an existing row in
the subform.

I think there is something not right about the code in the nz
statement. Would a simpler solution be to just set my hidden
field to the count of subform records as they are created?
Would you know the code for that? I haven't reached a
sufficient critical mass of VBA knowledge to figure it out
myself.

It would be just as complicated to do a DCount() instead of a
DMax() and has the disadvantage that given records 1,2,4,5
(because 3 has been deleted) gives the wrong result.

Could the fact that one of your fields in the key is creating
the problem?

We could change the DMax/Dcount to check the three criteria
separately, anded instead of concatenating them


dim stWhereClause as string
stWhereClause = "[ParentKey1] = """
stWhereClause = stWhereClause & parent!parentkey1 & """"
stWhereClause = stWhereClause & " AND [ParentKey2] = """
stWhereClause = stWhereClause & parent!parentkey2 & """"
' Note the different delimiters because key 3 is a date.
stWhereClause = stWhereClause & " AND [ParentKey3] = #"
stWhereClause = stWhereClause & parent!parentkey3 & "#"


Hiddenfield = nz(dmax("hiddenfield","subformtablename",
stwhereClause & """"),0) + 1

Thanks!

 

[Bob Quintal]

=?Utf-8?B?QWNjZXNzTWFu?= <[email protected]>
wrote in

Bob:

Yes, you make an excellent point about use of DMax instead of
DCount - thank you.

I'm still getting run-time error 3075: Syntax error in string
in query expression '[parentkey1]="<N/A>" AND
[parentkey2]="<N/A>" AND [parentkey3]="<N/A>" AND
[parentkey4]=#7/9/2007 9:30:00 AM#"'. The <N/A> values are
correct, and the date looks OK. I can't see what is tripping
this up.

Do I ever hate embedded quotes. There is an extra doublequote
after the date, I think that
stwhereClause & """"),0) + 1 should become
stwhereClause ),0) + 1

Hope that helps.

=?Utf-8?B?QWNjZXNzTWFu?=

Bob:

Research with HELP confirms that the subform gets its On
Current event before the subform, so Before Update is the
way to go.

Yes. The on current triggers immidately whenever a form
displays a different record. The before update is triggered
just before Access writes data to the table, which is when
you need it. You may also want to put an If me.newrec /end if
block around the code so that you don't modify the value of
an existing row in the subform.

I think there is something not right about the code in the
nz statement. Would a simpler solution be to just set my
hidden field to the count of subform records as they are
created? Would you know the code for that? I haven't
reached a sufficient critical mass of VBA knowledge to
figure it out myself.

It would be just as complicated to do a DCount() instead of a
DMax() and has the disadvantage that given records 1,2,4,5
(because 3 has been deleted) gives the wrong result.

Could the fact that one of your fields in the key is creating
the problem?

We could change the DMax/Dcount to check the three criteria
separately, anded instead of concatenating them


dim stWhereClause as string
stWhereClause = "[ParentKey1] = """
stWhereClause = stWhereClause & parent!parentkey1 & """"
stWhereClause = stWhereClause & " AND [ParentKey2] = """
stWhereClause = stWhereClause & parent!parentkey2 & """"
' Note the different delimiters because key 3 is a date.
stWhereClause = stWhereClause & " AND [ParentKey3] = #"
stWhereClause = stWhereClause & parent!parentkey3 & "#"


Hiddenfield = nz(dmax("hiddenfield","subformtablename",
stwhereClause & """"),0) + 1

Thanks!

 

[Guest]

Bob:

Problem solved! The criteria field in the DMax function did not need the &
"""". It works as you advertised when stWhereClause appears alone here.

Thanks for solving my problem and opening my eyes up to some VBA programming!

=?Utf-8?B?QWNjZXNzTWFu?= <[email protected]>
wrote in

Bob:

Research with HELP confirms that the subform gets its On
Current event before the subform, so Before Update is the way
to go.

Yes. The on current triggers immidately whenever a form displays
a different record. The before update is triggered just before
Access writes data to the table, which is when you need it. You
may also want to put an If me.newrec /end if block around the
code so that you don't modify the value of an existing row in
the subform.

I think there is something not right about the code in the nz
statement. Would a simpler solution be to just set my hidden
field to the count of subform records as they are created?
Would you know the code for that? I haven't reached a
sufficient critical mass of VBA knowledge to figure it out
myself.

It would be just as complicated to do a DCount() instead of a
DMax() and has the disadvantage that given records 1,2,4,5
(because 3 has been deleted) gives the wrong result.

Could the fact that one of your fields in the key is creating
the problem?

We could change the DMax/Dcount to check the three criteria
separately, anded instead of concatenating them


dim stWhereClause as string
stWhereClause = "[ParentKey1] = """
stWhereClause = stWhereClause & parent!parentkey1 & """"
stWhereClause = stWhereClause & " AND [ParentKey2] = """
stWhereClause = stWhereClause & parent!parentkey2 & """"
' Note the different delimiters because key 3 is a date.
stWhereClause = stWhereClause & " AND [ParentKey3] = #"
stWhereClause = stWhereClause & parent!parentkey3 & "#"


Hiddenfield = nz(dmax("hiddenfield","subformtablename",
stwhereClause & """"),0) + 1

Thanks!

 

[Bob Quintal]

=?Utf-8?B?QWNjZXNzTWFu?= <[email protected]>
wrote in

Bob:

Problem solved! The criteria field in the DMax function did
not need the & """". It works as you advertised when
stWhereClause appears alone here.

Thanks for solving my problem and opening my eyes up to some
VBA programming!

Glad to have helped. I find that finding problems here helps me
find the problems in my own databases. And I put a lot of
problems in my own databases.

Q

=?Utf-8?B?QWNjZXNzTWFu?=

Bob:

Research with HELP confirms that the subform gets its On
Current event before the subform, so Before Update is the
way to go.

Yes. The on current triggers immidately whenever a form
displays a different record. The before update is triggered
just before Access writes data to the table, which is when
you need it. You may also want to put an If me.newrec /end if
block around the code so that you don't modify the value of
an existing row in the subform.

I think there is something not right about the code in the
nz statement. Would a simpler solution be to just set my
hidden field to the count of subform records as they are
created? Would you know the code for that? I haven't
reached a sufficient critical mass of VBA knowledge to
figure it out myself.

It would be just as complicated to do a DCount() instead of a
DMax() and has the disadvantage that given records 1,2,4,5
(because 3 has been deleted) gives the wrong result.

Could the fact that one of your fields in the key is creating
the problem?

We could change the DMax/Dcount to check the three criteria
separately, anded instead of concatenating them


dim stWhereClause as string
stWhereClause = "[ParentKey1] = """
stWhereClause = stWhereClause & parent!parentkey1 & """"
stWhereClause = stWhereClause & " AND [ParentKey2] = """
stWhereClause = stWhereClause & parent!parentkey2 & """"
' Note the different delimiters because key 3 is a date.
stWhereClause = stWhereClause & " AND [ParentKey3] = #"
stWhereClause = stWhereClause & parent!parentkey3 & "#"


Hiddenfield = nz(dmax("hiddenfield","subformtablename",
stwhereClause & """"),0) + 1

Thanks!

 

[Bob Quintal]

=?Utf-8?B?QWNjZXNzTWFu?= <[email protected]>
wrote in

Bob:

I'm having some trouble working with the results of the nz
function. I would like to concatenate some text with the
results of the nz function and then assign that value to my
hiddenfield. It works sometimes, but I eventually get a type
mismatch error on the original nz statement and not on the new
concatenation code. Very strange.

Yes that is strange.

Can you explain how the DMax function returns a counter of new
records?

Dmax("field","table","filter") simply looks at the records
returned from "table" where "filter" for the maximum number in
"field"

And why is the nz function needed at all?

NZ is needed because if there are no records that pass the
filter, the maximum is not 0, it is null.. if you add 1 to a
null the answer is still null, so we must test for null and
replace it with 0
e.g. nz(value to test,value if null) +1

Thanks!

=?Utf-8?B?QWNjZXNzTWFu?=

Bob:

Research with HELP confirms that the subform gets its On
Current event before the subform, so Before Update is the
way to go.

Yes. The on current triggers immidately whenever a form
displays a different record. The before update is triggered
just before Access writes data to the table, which is when
you need it. You may also want to put an If me.newrec /end if
block around the code so that you don't modify the value of
an existing row in the subform.

I think there is something not right about the code in the
nz statement. Would a simpler solution be to just set my
hidden field to the count of subform records as they are
created? Would you know the code for that? I haven't
reached a sufficient critical mass of VBA knowledge to
figure it out myself.

It would be just as complicated to do a DCount() instead of a
DMax() and has the disadvantage that given records 1,2,4,5
(because 3 has been deleted) gives the wrong result.

Could the fact that one of your fields in the key is creating
the problem?

We could change the DMax/Dcount to check the three criteria
separately, anded instead of concatenating them


dim stWhereClause as string
stWhereClause = "[ParentKey1] = """
stWhereClause = stWhereClause & parent!parentkey1 & """"
stWhereClause = stWhereClause & " AND [ParentKey2] = """
stWhereClause = stWhereClause & parent!parentkey2 & """"
' Note the different delimiters because key 3 is a date.
stWhereClause = stWhereClause & " AND [ParentKey3] = #"
stWhereClause = stWhereClause & parent!parentkey3 & "#"


Hiddenfield = nz(dmax("hiddenfield","subformtablename",
stwhereClause & """"),0) + 1

Thanks!

 

[Guest]

Bob:

I'm having some trouble working with the results of the nz function. I
would like to concatenate some text with the results of the nz function and
then assign that value to my hiddenfield. It works sometimes, but I
eventually get a type mismatch error on the original nz statement and not on
the new concatenation code. Very strange.

Can you explain how the DMax function returns a counter of new records?

And why is the nz function needed at all?

Thanks!

=?Utf-8?B?QWNjZXNzTWFu?= <[email protected]>
wrote in

Bob:

Research with HELP confirms that the subform gets its On
Current event before the subform, so Before Update is the way
to go.

Yes. The on current triggers immidately whenever a form displays
a different record. The before update is triggered just before
Access writes data to the table, which is when you need it. You
may also want to put an If me.newrec /end if block around the
code so that you don't modify the value of an existing row in
the subform.

I think there is something not right about the code in the nz
statement. Would a simpler solution be to just set my hidden
field to the count of subform records as they are created?
Would you know the code for that? I haven't reached a
sufficient critical mass of VBA knowledge to figure it out
myself.

It would be just as complicated to do a DCount() instead of a
DMax() and has the disadvantage that given records 1,2,4,5
(because 3 has been deleted) gives the wrong result.

Could the fact that one of your fields in the key is creating
the problem?

We could change the DMax/Dcount to check the three criteria
separately, anded instead of concatenating them


dim stWhereClause as string
stWhereClause = "[ParentKey1] = """
stWhereClause = stWhereClause & parent!parentkey1 & """"
stWhereClause = stWhereClause & " AND [ParentKey2] = """
stWhereClause = stWhereClause & parent!parentkey2 & """"
' Note the different delimiters because key 3 is a date.
stWhereClause = stWhereClause & " AND [ParentKey3] = #"
stWhereClause = stWhereClause & parent!parentkey3 & "#"


Hiddenfield = nz(dmax("hiddenfield","subformtablename",
stwhereClause & """"),0) + 1

Thanks!

 

[Guest]

Bob:

I got it to work. It wasn't a good idea for me to update the value of
hiddenfield (by adding text) after setting it to the results of nz(dmax()).
That messed up the results of dmax(). All set now, again!

I have to admit that I find VBA a little nonrobust.

Thanks again!

=?Utf-8?B?QWNjZXNzTWFu?= <[email protected]>
wrote in

Bob:

I'm having some trouble working with the results of the nz
function. I would like to concatenate some text with the
results of the nz function and then assign that value to my
hiddenfield. It works sometimes, but I eventually get a type
mismatch error on the original nz statement and not on the new
concatenation code. Very strange.

Yes that is strange.

Can you explain how the DMax function returns a counter of new
records?

Dmax("field","table","filter") simply looks at the records
returned from "table" where "filter" for the maximum number in
"field"

And why is the nz function needed at all?

NZ is needed because if there are no records that pass the
filter, the maximum is not 0, it is null.. if you add 1 to a
null the answer is still null, so we must test for null and
replace it with 0
e.g. nz(value to test,value if null) +1

Thanks!

=?Utf-8?B?QWNjZXNzTWFu?=

Bob:

Research with HELP confirms that the subform gets its On
Current event before the subform, so Before Update is the
way to go.

Yes. The on current triggers immidately whenever a form
displays a different record. The before update is triggered
just before Access writes data to the table, which is when
you need it. You may also want to put an If me.newrec /end if
block around the code so that you don't modify the value of
an existing row in the subform.


I think there is something not right about the code in the
nz statement. Would a simpler solution be to just set my
hidden field to the count of subform records as they are
created? Would you know the code for that? I haven't
reached a sufficient critical mass of VBA knowledge to
figure it out myself.

It would be just as complicated to do a DCount() instead of a
DMax() and has the disadvantage that given records 1,2,4,5
(because 3 has been deleted) gives the wrong result.

Could the fact that one of your fields in the key is creating
the problem?

We could change the DMax/Dcount to check the three criteria
separately, anded instead of concatenating them


dim stWhereClause as string
stWhereClause = "[ParentKey1] = """
stWhereClause = stWhereClause & parent!parentkey1 & """"
stWhereClause = stWhereClause & " AND [ParentKey2] = """
stWhereClause = stWhereClause & parent!parentkey2 & """"
' Note the different delimiters because key 3 is a date.
stWhereClause = stWhereClause & " AND [ParentKey3] = #"
stWhereClause = stWhereClause & parent!parentkey3 & "#"


Hiddenfield = nz(dmax("hiddenfield","subformtablename",
stwhereClause & """"),0) + 1



Thanks!