ACC: How to Display Line Numbers on Subform Records

[Access User]

I found this article on MS' website

http://support.microsoft.com/kb/q120913/

but ran into problems when I pasted the code and tried to compile it. It
gives a Compile Error:

Method or data member not found

when it reached the line below:

RS.FindFirst "[" & KeyName & "] = " & KeyValue

and it highlights "FindFirst".

I have a sub-form (child) of a parent form's and I have a variable in the
child that the user currently manually increments from 1,2,3 and so forth.
Can this not be done w/o the user's intervention?

 

[Access User]

Hi,

I sort of don't know the difference but after downloading this one, I manage
to find another one on MS' website which IS for A2K and has the instructions
in it to take the action you suggest.

The 'thing' is that though this compiles, I'm running into another barrier.
I got the feeling this thing is somehow compelling me to uniquely number
every sub-form record which is clearly not appropriate to this applicaiton.
It's for recording anuerisms found on MRAs (like MRI images). The main form
has patient specific stuff and the subform records properties of the image's
per each patient for any aneurisms found on his/her image. So, there is a
form on the sub-form called 'Aneurism' which is currently entered manually
1,2,3,4,....

I don't think that this is really the code for the job. Would you agree?

I found this article on MS' website

http://support.microsoft.com/kb/q120913/

but ran into problems when I pasted the code and tried to compile it. It
gives a Compile Error:

Method or data member not found

when it reached the line below:

RS.FindFirst "[" & KeyName & "] = " & KeyValue

and it highlights "FindFirst".

I have a sub-form (child) of a parent form's and I have a variable in the
child that the user currently manually increments from 1,2,3 and so forth.
Can this not be done w/o the user's intervention?

Check your References (Tools menu).

FindFirst is a DAO method. If you are using A2000 or A2002,
most likely you left the references at their default setting
with the ADO library. If you don't know the difference
between them, you should uncheck the ADO library and check
the DAO library/

 

[Access User]

Again, thanks, this seems as though it's going to really work....indeed,
your hunch was on point..all I want to do is replace the user's manual
requirement to enumerate the records incrementally per each patient in the
sub-table with a nice algorithm: the table's called "tbl_Aneurism" and the
two keys making up the composite PK are "ID" and "Aneurism" in that order.
Does that start the ball rolling?

The article you started out with will work even if it is
more than a little clumsy. A more elegant version is
available at:
http://www.lebans.com/rownumber.htm

Note that a fundamental concept of databases is that each
record must be uniquely identifiable. The set of fields
used to uniquely identify each record is known as the
primary key. If you do not have a primary key, then there
isn't much you can do with your records, much less display a
row number when they are displayed on a form.

It is possible to number the records as each new record is
added to its table. Is this what you are trying to do? If
so, then that code is not what you want to use. I will need
to know the names of the table and relevant fields (foreign
key to patient table and the row number) if this is what you
are trying to do.
--
Marsh
MVP [MS Access]

I sort of don't know the difference but after downloading this one, I manage
to find another one on MS' website which IS for A2K and has the instructions
in it to take the action you suggest.

The 'thing' is that though this compiles, I'm running into another barrier.
I got the feeling this thing is somehow compelling me to uniquely number
every sub-form record which is clearly not appropriate to this applicaiton.
It's for recording anuerisms found on MRAs (like MRI images). The main form
has patient specific stuff and the subform records properties of the image's
per each patient for any aneurisms found on his/her image. So, there is a
form on the sub-form called 'Aneurism' which is currently entered manually
1,2,3,4,....

I don't think that this is really the code for the job. Would you agree?

Access User wrote:

I found this article on MS' website

http://support.microsoft.com/kb/q120913/

but ran into problems when I pasted the code and tried to compile it. It
gives a Compile Error:

Method or data member not found

when it reached the line below:

RS.FindFirst "[" & KeyName & "] = " & KeyValue

and it highlights "FindFirst".

I have a sub-form (child) of a parent form's and I have a variable in the
child that the user currently manually increments from 1,2,3 and so forth.
Can this not be done w/o the user's intervention?


Check your References (Tools menu).

FindFirst is a DAO method. If you are using A2000 or A2002,
most likely you left the references at their default setting
with the ADO library. If you don't know the difference
between them, you should uncheck the ADO library and check
the DAO library/

 

[Access User]

I placed this into the before_update event of the "Aneurism" form since it is
the field to take the line numbers and, the code

Me.[Aneurism] = Nz(DMax("[Aneurism]", _
"tbl_Aneurism", "ID=" & Me.ID & " And Aneurism=" _
Me.Aneurism), 0) + 1


resutls in a compile error talking about a syntax error.

Both fields, ID and Aneurism are number fields.

Any feelings about what's behind the glitch?

Again, thanks, this seems as though it's going to really work....indeed,
your hunch was on point..all I want to do is replace the user's manual
requirement to enumerate the records incrementally per each patient in the
sub-table with a nice algorithm: the table's called "tbl_Aneurism" and the
two keys making up the composite PK are "ID" and "Aneurism" in that order.

Then I think, maybe, all you need is to add a statement to
the form's BeforeUpdate event.

If the ID and Aneurism fields in the table are numeric
types:
Me.[Line Number Field] = Nz(DMax("[Line Number Field]", _
"tbl_Aneurism", "ID=" & Me.ID & " And Aneurism=" _
Me.Aneurism), 0) + 1

If either field is type Text, then you need to quote the
values. For example, if the Aneurism field is a Text field:

Me.[Line Number Field] = Nz(DMax("[Line Number Field]", _
"tbl_Aneurism", "ID=" & Me.ID & " And Aneurism=""" _
Me.Aneurism & """"), 0) + 1

Be sure to replace [Line Number Field] with the name of the
table field for the line number.

 

[Douglas J. Steele]

You've got a circular reference in there.

Your condition includes a reference to the value in the field whose value
you're trying to change (Me.Aneurism)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I placed this into the before_update event of the "Aneurism" form since it
is
the field to take the line numbers and, the code

Me.[Aneurism] = Nz(DMax("[Aneurism]", _
"tbl_Aneurism", "ID=" & Me.ID & " And Aneurism=" _
Me.Aneurism), 0) + 1


resutls in a compile error talking about a syntax error.

Both fields, ID and Aneurism are number fields.

Any feelings about what's behind the glitch?

Again, thanks, this seems as though it's going to really work....indeed,
your hunch was on point..all I want to do is replace the user's manual
requirement to enumerate the records incrementally per each patient in
the
sub-table with a nice algorithm: the table's called "tbl_Aneurism" and
the
two keys making up the composite PK are "ID" and "Aneurism" in that
order.

Then I think, maybe, all you need is to add a statement to
the form's BeforeUpdate event.

If the ID and Aneurism fields in the table are numeric
types:
Me.[Line Number Field] = Nz(DMax("[Line Number Field]", _
"tbl_Aneurism", "ID=" & Me.ID & " And Aneurism=" _
Me.Aneurism), 0) + 1

If either field is type Text, then you need to quote the
values. For example, if the Aneurism field is a Text field:

Me.[Line Number Field] = Nz(DMax("[Line Number Field]", _
"tbl_Aneurism", "ID=" & Me.ID & " And Aneurism=""" _
Me.Aneurism & """"), 0) + 1

Be sure to replace [Line Number Field] with the name of the
table field for the line number.

 

[Access User]

I guess it wasn't clear from my earlier post that Aneurism is the field that
we're incrementally numbering....still no cigar however :-(

I placed this into the before_update event of the "Aneurism" form since it is
the field to take the line numbers and, the code

Me.[Aneurism] = Nz(DMax("[Aneurism]", _
"tbl_Aneurism", "ID=" & Me.ID & " And Aneurism=" _
Me.Aneurism), 0) + 1


resutls in a compile error talking about a syntax error.

Both fields, ID and Aneurism are number fields.

If Aneurism is the field you are numbering, then it should
not be in SMax where condition:

Me.[Aneurism] = Nz(DMax("[Aneurism]", "tbl_Aneurism", "ID="
& Me.ID)

 

[Access User]

I guess what I meant to say was that it spit out some opaque error message,
but having tried the latest one,

Private Sub Aneurism_AfterUpdate()

Me.[Aneurism] = Nz(DMax("[Aneurism]", "tbl_Aneurism", "ID=" _
& Me.ID), 0) + 1

End Sub

with the missing underscore inserted, happily I get no error msgs, but at
the same time, nothing happens. Meaning when I create a new parent record and
enter the 'required' fields the child record is created with an empty Anurism
field. Simillarly, if I enter '1' in the first child record, a '2' does not
miraculously make its appearance in the 2nd row. Hope this problem
description is a little clearer than the last.

"no cigar" is singularly uninformative, but maybe you just
used the partial expression that I posted. The entire
expression would be:

Me.[Aneurism] = Nz(DMax("[Aneurism]", "tbl_Aneurism", "ID="
& Me.ID), 0) + 1
--
Marsh
MVP [MS Access]

I guess it wasn't clear from my earlier post that Aneurism is the field that
we're incrementally numbering....still no cigar however :-(

Access User wrote:

I placed this into the before_update event of the "Aneurism" form since it is
the field to take the line numbers and, the code

Me.[Aneurism] = Nz(DMax("[Aneurism]", _
"tbl_Aneurism", "ID=" & Me.ID & " And Aneurism=" _
Me.Aneurism), 0) + 1


resutls in a compile error talking about a syntax error.

Both fields, ID and Aneurism are number fields.


If Aneurism is the field you are numbering, then it should
not be in SMax where condition:

Me.[Aneurism] = Nz(DMax("[Aneurism]", "tbl_Aneurism", "ID="
& Me.ID)

 

[Access User]

Well, it seems as though the tbl_Aneurism reference in the VBA might need
updating as the subform seems to be using a qry_Aneurism which depends upon
tbl_Aneurism, so here's the revised VBA

Private Sub Aneurism_BeforeUpdate(Cancel As Integer)

Me.[Aneurism] = Nz(DMax("[Aneurism]", "qry_Aneurism", "ID=" _
& Me.ID), 0) + 1

End Sub

which you'll notice now reflects your suggestion that it go with the BU
event prop'y. Still 'no cigar' however. As far as I can discern, the number's
not making it into the qry, much less the table.

and in the interest of reality, the SQL version of the IDE qry

SELECT tbl_Aneurism.ID, tbl_Aneurism.Aneurism, tbl_Aneurism.Reviewer,
tbl_Aneurism.Clarity, tbl_Aneurism.Locaton, tbl_Aneurism.Side,
tbl_Aneurism.Size
FROM tbl_Aneurism
ORDER BY tbl_Aneurism.ID, tbl_Aneurism.Aneurism;

I guess what I meant to say was that it spit out some opaque error message,
but having tried the latest one,

Private Sub Aneurism_AfterUpdate()

Me.[Aneurism] = Nz(DMax("[Aneurism]", "tbl_Aneurism", "ID=" _
& Me.ID), 0) + 1

End Sub

with the missing underscore inserted, happily I get no error msgs, but at
the same time, nothing happens. Meaning when I create a new parent record and
enter the 'required' fields the child record is created with an empty Anurism
field. Simillarly, if I enter '1' in the first child record, a '2' does not
miraculously make its appearance in the 2nd row. Hope this problem
description is a little clearer than the last.

Are you sure the number was not inserted? Did you check the
reord in the table?

Don't forget that the BeforeUpdate event occurs just before
the record is saved so the number should not appear until
you go to the next new record or do something that moves the
focus back to the main form.

The only reasons I can imagine why the number would not be
saved to the table is if the Aneurism field was not in the
subform's record source or if you have an unbound text box
named Aneurism.

What might seem like an opaque error message to you could
actually be very infirmative. It is important that you
provide that information to those of us that are trying to
help you.

That wasn't a missing underscore. I intended it to be one
line but either your news reader program or mine thought it
was too long and line wrapped it. This happens often enough
that you should you should learn to recognize and deal with
it.

 

[Access User]

Easier said than done, if by FORM you meant sub-Form, then the only Event
Properties I see when I click on its name as it appears nested on the parent
were On Enter and On Exit. If you meant the parent form, then it turns out
A2K02 won't let me even see the properties, for some reason.

Well, it seems as though the tbl_Aneurism reference in the VBA might need
updating as the subform seems to be using a qry_Aneurism which depends upon
tbl_Aneurism, so here's the revised VBA

Private Sub Aneurism_BeforeUpdate(Cancel As Integer)

Me.[Aneurism] = Nz(DMax("[Aneurism]", "qry_Aneurism", "ID=" _
& Me.ID), 0) + 1

That's the wrong BeforeUpdate event. The code is supposed
to be in the FORM's BeforeUpdate event.

 

[Access User]

Sitationally speaking, still 'no cigar'....

I add the following

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me.[Aneurism] = Nz(DMax("[Aneurism]", "qry_Aneurism", "ID=" _
& Me.ID), 0) + 1

End Sub

to the subform's event property. I enter the ID value and some other info
into the parent form's controls and voila, as always a child record having
the same ID value appears, but, as unwaveringly before, still NO entry in the
'Aneurism' control of the sub-form's. (BTW - I used approach in your
strategy, i.e. I opened the subform in design view directly vs. indirectly
from w/in the parent form design view).

If you are editing your subform as part of the main form,
then the first click in the subform only select the subform
control that contains the form object it will display. If
you the click again in the subform's upper left corner where
the two rulers come together you should see the form
object's property sheet.

Personally, I really dislike that combined design view. I
prefer to close the main form and open the subform's form
object directly from the database window.

However you get to the subform's design view, you still need
to add the code to the subform's BeforeUpdate event.
--
Marsh
MVP [MS Access]

Easier said than done, if by FORM you meant sub-Form, then the only Event
Properties I see when I click on its name as it appears nested on the parent
were On Enter and On Exit. If you meant the parent form, then it turns out
A2K02 won't let me even see the properties, for some reason.

That's the wrong BeforeUpdate event. The code is supposed
to be in the FORM's BeforeUpdate event.

Access User wrote:
Well, it seems as though the tbl_Aneurism reference in the VBA might need
updating as the subform seems to be using a qry_Aneurism which depends upon
tbl_Aneurism, so here's the revised VBA

Private Sub Aneurism_BeforeUpdate(Cancel As Integer)

Me.[Aneurism] = Nz(DMax("[Aneurism]", "qry_Aneurism", "ID=" _
& Me.ID), 0) + 1

 

[Access User]

I think it might be prudent to revise my response to this in view of the way
it seems to be working out at the moment.....the Aneurism # DOES show up in
the sub-form after all, it would appear. I have implemented a disabling of
the field from the user adding that property to list of other controls (i.e.
ID and Reviewer). I thought that it would 'immediately' appear once the
values of ID and Reviewer appeared (ID comes from the parent and the
relationship and Reviewer is entered as a default value), but I see now that
the Aneurism field is empty until the cursor moves to the next record, which
brings me to my next and hopefully final question about this issues. You see,
there is an upper limit of 4 Aneurisms allowed at this point, and I have
established that upper limit (as "<= 4") in the properties fields of the
table and subform's for this Aneurism control. So when you try to exceed it,
whether intentionally or otherwies, you currently get a -2147352767
(80020009) error citing the fact that it's bumping up against the upper limit
I established. What would be cooler would be something other than this gnarly
looking error message which would totally space out my high school level
user. I know that you can program custom tailored error messages, however I
can not because of my unfamiliarity, but the behavior would be something like
this, a msg telling the user that the upper limit of 4 was reached and that
it was time to back off.

Thoughts (or is this a new thread)?

If you are editing your subform as part of the main form,
then the first click in the subform only select the subform
control that contains the form object it will display. If
you the click again in the subform's upper left corner where
the two rulers come together you should see the form
object's property sheet.

Personally, I really dislike that combined design view. I
prefer to close the main form and open the subform's form
object directly from the database window.

However you get to the subform's design view, you still need
to add the code to the subform's BeforeUpdate event.
--
Marsh
MVP [MS Access]

Easier said than done, if by FORM you meant sub-Form, then the only Event
Properties I see when I click on its name as it appears nested on the parent
were On Enter and On Exit. If you meant the parent form, then it turns out
A2K02 won't let me even see the properties, for some reason.

That's the wrong BeforeUpdate event. The code is supposed
to be in the FORM's BeforeUpdate event.

Access User wrote:
Well, it seems as though the tbl_Aneurism reference in the VBA might need
updating as the subform seems to be using a qry_Aneurism which depends upon
tbl_Aneurism, so here's the revised VBA

Private Sub Aneurism_BeforeUpdate(Cancel As Integer)

Me.[Aneurism] = Nz(DMax("[Aneurism]", "qry_Aneurism", "ID=" _
& Me.ID), 0) + 1

 

[Access User]

While there's something to be said for preventing users from circumventing
forms, this database is slotted to be entered by a single (volunteer,
trusted) users over a period spanning six months until study data is entered
and then an analysis involving other software of its contents exported
begins.

The composite PK (uses ID and Aneurism) I would think prevents non-unique
entries, but go ahead and correct me if otherwise.

I thought I mentioned that I had a validation criterion (<4) already in the
field and that that strange error message was trapping it. Perhaps I ought to
use yours instead along with the verbiage you're suggesting, but don't you
think one of 'error' type messages that are incorporated into some
conditional logic wrapped inside the validation rule + msgbox is the way to
go. I'm more concerned that the high schooler who does the data entry not get
confused by the opaquely worded native error msges I cited in my earlier
posting.

 

[Access User]

Out of curiousity, I removed the validation rule/text from the sub-form and
placed your suggesteds into the sub-table itself. When the user tries to
exceed the 4 limit, the message that results is from "Microsoft Visual Basic"
and gives that familiar refrain about "Run time error '-2147352567
(80020009): Only four Aneurism entries are allowed". I'll grant this is
terser and not as outerworldly as the generic one I cited but it gives the
user the unwanted opp'y to hit the 'Debug' button. True, that can be SOP-ed
out, but I just have to think that there's a way to get a msgbox to the user
that undoes the attempted 5th record and removes that said temptation. No?

 

[Access User]

I should've tried clicking the 'End' button because when I do I find that
nothing I do after that lets the user out of the sub-form....the same error
message recurs over and over..