Why "no current record" when there is a "newrecord?"

[LAS]

I have a bit of code in the Current event. Me.NewRecord is true, and
irst_StudentTracking was set to = me.RecordSet in the Load event (I watched
it happen in the debugger). But when I get to the .Edit statement, I get
the error "no current record." This happens the first time the Current
event is executed, triggered by the Load event, I believe. Why is there no
current record if there is a new record?

tia
las

Private Sub Form_Current()
If Me.NewRecord Then
irst_StudentTracking.Edit

 

[Bob Quintal]

I have a bit of code in the Current event. Me.NewRecord is true,
and irst_StudentTracking was set to = me.RecordSet in the Load
event (I watched it happen in the debugger). But when I get to
the .Edit statement, I get the error "no current record." This
happens the first time the Current event is executed, triggered by
the Load event, I believe. Why is there no current record if
there is a new record?

tia
las

Private Sub Form_Current()
If Me.NewRecord Then
irst_StudentTracking.Edit

You are confusing the new record position in the form's recordset with
the copy of the recordset irst_studentTracking that you have created in
the form's open event.

What are you trying to achieve, and why do you think that you need two
recordsets to do it?

 

[David W. Fenton]

I have a bit of code in the Current event. Me.NewRecord is true,
and irst_StudentTracking was set to = me.RecordSet in the Load
event (I watched it happen in the debugger). But when I get to
the .Edit statement, I get the error "no current record." This
happens the first time the Current event is executed, triggered by
the Load event, I believe. Why is there no current record if
there is a new record?

Private Sub Form_Current()
If Me.NewRecord Then
irst_StudentTracking.Edit

I'm assuming that you have irst_StudentTracking as a form-level
recordset variable and that's what you're assigning in the form's
OnLoad event.

My question:

Why are you editing the recordset? That's what the form is for,
i.e., to edit the data. You shouldn't really be editing the
recordset in code, in my opinion. I can't come up with a single
justification for doing so, myself.

Why do you think you need to do that instead of just editing the
fields/controls in your form?

 

[LAS]

I created irst_StudentTracking because I don't know how to use me.recordset
to get at things like RecordCount. Me.Recordset.RecordCount doesn't fly.

Could you elaborate about how my confusion explains what I'm seeing? I have
come to the conclusion that since NewRecord is boolean, it has to be true or
false, so there is no significance to its value if there is no current
record. Might that be the explanation?

 

[LAS]

See answer to Bob Quintal.

I'm assuming that you have irst_StudentTracking as a form-level
recordset variable and that's what you're assigning in the form's
OnLoad event.

My question:

Why are you editing the recordset? That's what the form is for,
i.e., to edit the data. You shouldn't really be editing the
recordset in code, in my opinion. I can't come up with a single
justification for doing so, myself.

Why do you think you need to do that instead of just editing the
fields/controls in your form?

 

[Access Developer]

I fear what was, and is, confusing you, is the definition of "RecordCount".
RecordCount is a count of records read from a recordset. If you want an
accurate count of the number of records in a recordset, use .MoveLast to
move to the last record and then test .RecordCount.

"NewRecord" means that the record under consideration is being created...
implied is that it has not yet been written/saved to the recordset.

Perhaps investing some time and effort in a good beginner's self-study
book... several MVPs list suggested books in their websites. In the past,
"Microsoft Access Step by Step", published by Microsoft Press, was a good
start, but I have not read the editions applying to the last few versions of
Access -- it might be worth your while to visit a local bookstore
well-stocked with technical books and browsing the Access books they carry.
You can't just jump in feet-first to VBA code if you don't understand some
of these basics.

 

[LAS]

Thanks for the detailed response, but actually it didn't tell me anything I
didn't know already. I think I've answered my question for myself this way:
"NewRecord" can only have two values, true and false. There can be three
states, though. A record exists and it is new. A record exists and it is
not new. No record exists. A boolean can't handle all three states. So if
there is no record, the value of newrecord is meaningless. It has to be
true or false, and both will be erroneous if there is no record. I was kind
of assuming that if there was no record, newrecord would automatically show
up as false. But that would be saying that it is an existing record... just
as erroneous.

 

[Bob Quintal]

I created irst_StudentTracking because I don't know how to use
me.recordset to get at things like RecordCount.
Me.Recordset.RecordCount doesn't fly.

It does fly. but to use it, you first need to move to the last record
in hte recordset, and optionally back to the first.

Could you elaborate about how my confusion explains what I'm
seeing? I have come to the conclusion that since NewRecord is
boolean, it has to be true or false, so there is no significance
to its value if there is no current record. Might that be the
explanation?

You have two recordsets, the one in the form, and the one you have
created as a copy named irst_StudentTracking.

Each recordset has it's own value of .NewRecord.

You are checking that the first recordset is on the new record but
then trying to edit the second recordset which is not on a new
record. .

 

[Bob Quintal]

Thanks for the detailed response, but actually it didn't tell me
anything I didn't know already. I think I've answered my question
for myself this way: "NewRecord" can only have two values, true
and false. There can be three states, though. A record exists
and it is new. A record exists and it is not new. No record
exists. A boolean can't handle all three states. So if there is
no record, the value of newrecord is meaningless. It has to be
true or false, and both will be erroneous if there is no record.
I was kind of assuming that if there was no record, newrecord
would automatically show up as false. But that would be saying
that it is an existing record... just as erroneous.

You are so wrong about this, see my other response that explains that
you have two recordsets and each has its own .newrecord property.

the .newrecord is ONLY True from the time that you or the form issues
an .AddNew until you or the form issues an .Update. It is False if
you are on an existing record OR at .BOF or .EOF.

You have 2 recorsdsets: Recordset 1 can be True but recordset 2 can
be false at the same time,

 

[LAS]

see below

When I type Me.Recordset. no suggest qualifiers show up. So I assumed I
couldn't use me.recordset in place of irst_studenttracking. But I did go
further, based on your response, and, indeed me.recordset.movelast and
me.recordset.recordcount "fly" at runtime. It's too bad to give up the
hints you get after the irst_studenttracking., but I'll do a search and
replace and give it a shot.

 

[LAS]

See my other reply from this morning in this thread.

 

[David W. Fenton]

See answer to Bob Quintal.

It doesn't answer the question of WHY you think you need to use the
form's recordset. Me.RecordsetClone.Recordcount gives you the
recordcount, and you can do Me.RecordsetClone.MoveLast to make sure
the recordcount is accurate without affecting the current record in
the form.

That's what the RecordsetClone is for.

And you shouldn't be editing either Me.RecordsetClone or
Me.Recordset -- edit the controls/fields in the form, because that's
the whole point of using bound forms.

 

[LAS]

See below

I'm assuming that you have irst_StudentTracking as a form-level
recordset variable and that's what you're assigning in the form's
OnLoad event.

My question:

Why are you editing the recordset? That's what the form is for,
i.e., to edit the data. You shouldn't really be editing the
recordset in code, in my opinion. I can't come up with a single
justification for doing so, myself.

Why do you think you need to do that instead of just editing the
fields/controls in your form?

By this are you saying, "Why are you using code instead of just letting the
user do the data entry?"

Or are you saying "Why are you using 'irst_studenttracking!Exclusion = true"
instead of 'chkExclusion = true?"

Surely you can think of lots of situations where columns need to be
populated, but not seen by the user. Are you saying to make an invisible
control for every column in the table you need to populate?

 

[Access Developer]

Help me understand: if the user is doing data entry, where is the other data
(that you don't want the user to see) coming from?

There is absolutely no reason to NOT use a bound Control with a Visible
property of No to set a value from VBA (and, the existence of the Control,
which will be visible in design view, will be a reminder that you are doing
_something_ with it); but, in a Form, you don't have to -- you can simply
set the value into a field in the RecordSource.

As an aside: in a Report, Fields in the RecordSource which are NOT the
Control Source of a Control are not available at runtime.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access

See below

I'm assuming that you have irst_StudentTracking as a form-level
recordset variable and that's what you're assigning in the form's
OnLoad event.

My question:

Why are you editing the recordset? That's what the form is for,
i.e., to edit the data. You shouldn't really be editing the
recordset in code, in my opinion. I can't come up with a single
justification for doing so, myself.

Why do you think you need to do that instead of just editing the
fields/controls in your form?

By this are you saying, "Why are you using code instead of just letting
the user do the data entry?"

Or are you saying "Why are you using 'irst_studenttracking!Exclusion =
true" instead of 'chkExclusion = true?"

Surely you can think of lots of situations where columns need to be
populated, but not seen by the user. Are you saying to make an invisible
control for every column in the table you need to populate?

 

[David W. Fenton]

See below



By this are you saying, "Why are you using code instead of just
letting the user do the data entry?"

Or are you saying "Why are you using
'irst_studenttracking!Exclusion = true" instead of 'chkExclusion
= true?"

Maybe, maybe not, though the latter should be:

(Me!chkExclusion) = True

....or...

(Me.chkExclusion) = True

The parens are crucial to force evaluation, and the parent object is
necessary to disambiguate (rather than depending on VBA to do it for
you).

Surely you can think of lots of situations where columns need to
be populated, but not seen by the user. Are you saying to make an
invisible control for every column in the table you need to
populate?

No, I'm not saying anything of the sort. The field's in the form's
recordsource are available for editing even if they are not used in
the controlsource of a control on the form.

For instance, I usually don't display the PK to the user, but this:

MsgBox Me!PersonID

....is still going to work, assuming PersonID is a field in the
form's recordsource.

All those fields are available for editing directly by just
referring to them the same way I've referred to PersonID.

The fact that you don't know this shows that you somewhere along the
line you skipped learning the most basic aspects of how Access
works. I don't mean that statement as any kind of value judgement,
just as a statement of fact -- and it's causing you problems because
you're making things way, way to hard for yourself.

The key point:

The default collection of a bound form or report is the union of the
Fields collection and the Controls collection.

Thus, chkExclusion might be bound to a field named Exclusion, but
Me!chkExclusion and Me!Exclusion will both allow you to edit the
underlying value, the first indirectly via the control, the second
directly (which will be immediately reflected in any control to
which it's bound).

And you can work with the value of Me!Exclusion in code and edit it
even if it's not bound to a control (in reports, things are
different, in that you can't reliably refer to a field in the
report's recordsource unless it's bound to a control; this is
something that did not used to be the case, and is, in my opinion, a
bug; but it's been that way for several versions now, so likely not
something that's going to be fixed).

 

[LAS]

Thanks to several of you for the concrete examples of what you mean by using
the form's controls instead of the recordset.

 

[David W. Fenton]

As an aside: in a Report, Fields in the RecordSource which are NOT
the Control Source of a Control are not available at runtime.

....in VBA.