Struggling with current record

  • Thread starter Thread starter Laurel
  • Start date Start date
L

Laurel

I'm struggling with having "no current record." I see a row or rows in the
form in question (this happens to me in a variety of forms), but the code
associated with the form detects "no current record." I've asked this of
this forum before and various suggestions for testing are below. Just now I
inserted a row, the first row in an empty form, and edited one column and
clicked on a button that triggered the offending script. .EOF was TRUE and
..BOF was false - thus no current record. I did it again and both were TRUE.
Further Me.CurrentRecord = 1 and lrstMe.RecordCount = 0. (How can
Me.CurrentRecord = 1 if .EOF is TRUE????) Most times when I do the same
thing, .EOF and .BOF are both false and the rest of the script executes as
expected. HELP!

I guess there are two issues - testing for no records in the recordset, and
testing for a current record... But that remains confusing.

I've gotten a lot of suggestions about how to test for whether there is a
current record. What I need is advice about how to ensure that there is a
current record when I execute a script when I can see a record in front of
me on the form.

Dim lrstMe As Recordset
Set lrstMe = Me.Recordset
With lrstMe
If (.EOF Or .BOF) Then
lngRecCount = 0 'No current record
Else
.MoveLast
lngRecCount = .RecordCount
.MoveFirst
End If
DoEvents 'Trying to get rid of occasional weird errors - feel like timing
problems

If (Me.CurrentRecord = 0) Or (lrstMe.RecordCount = 0) Then
MsgBox ("No current record" & vbCrLf & "Copy will not be done.")
Exit Sub
End If


OTHER SUGGESTIONS FOR TESTING FOR WH

'HERE'S ANOTHER APPROACH FROM THE NEWSGROUP
' You could try code like this:
' dim bkmk as variant
' with rst
' bkmk=.bookmark
' .movelast
' .movefirst
' .bookmark=bkmk NOTE THIS METHOD OF USING BOOKMARK

and another

'If Not (.EOF And .BOF) Then
' .MoveLast
' lngRecCount = .RecordCount
' .MoveFirst
' Else
' lngRecCount = 0
' End If
'End With
 
I think there is an error in the If statement. Try:

....
If (.EOF = True) AND (.BOF = True) Then
lngRecCount = 0 'No current record
....

Note the AND operator instead of OR ...

Try that and see if it makes more sense.

HTH
Van T. Dinh
MVP (Access)
 
Your appraoch is causing the problem.

You are using the forms recordset and changing it. Don't.
Leave it alone and use a snapshot of it, if you want to
play with the data.

The code below looks as though it's doing nothing more
than getting the count of records. If that is the case,
then use DCount("*",strTable,strCriteria)

As far as creating a new record, until you save it, it
doesn't exit in the table. Use the Me.NewRecord rather
than .EOF and .BOF as they are tell you the truth, there
is no record in the table, until you save it.

The current record is also telling you the truth, you have
created the record object, just not added it to the table
yet.
 
Van T. Dinh said:
I think there is an error in the If statement. Try:

...
If (.EOF = True) AND (.BOF = True) Then
lngRecCount = 0 'No current record
...

Note the AND operator instead of OR ...

Try that and see if it makes more sense.
Click to expand...
Yes, it hit me in the night that I'd put that OR in erroneously, but that
was a very late change. Most of the time the code I was using was the
"not(.eof and .bof) that was commented out below it.

Here is a cleaner statement, I think, of one aspect of the problem.

Overview. I want to 1) get a good recordcount of the rows visible in the
form and 2)make sure I have a current record , so that I can execute
subsequent scripts without error. To that end, I want to execute the
..movelast .movefirst. I get an error if I attempt .movelast .movefirst when
the form has no rows, so I was advised to use .eof and .bof to test for no
rows before trying to get the good recordcount. My problem is that EOF and
BOF are both true (sometimes) even when there is a row in the form. One
reproducible instance is when I create the first new row. I can clear up
these problems if I click on the empty newrow that is automatically
generated and then cick back on the row I just created, but I can't expect
my users to do that (although they have, indeed, learned to do that....)

Insert first row into empty form. Choose from a dropdown for a column with a
mouse. Click button that executes script.

EOF BOF Me.CurrentRecord lrstMe.RecordCount

TRUE TRUE 1 0

TRUE TRUE 1 0

TRUE TRUE 1 0



If (.EOF And .BOF) Then

lngRecCount = 0 'No current record

Else

..MoveLast

lngRecCount = .RecordCount

..MoveFirst

End If

DoEvents 'Trying to get rid of occasional weird errors - feel like timing
problems

End With

Dim li_debug As Integer

li_debug = lrstMe.RecordCount

If (Me.CurrentRecord = 0) Or (lrstMe.RecordCount = 0) Then

MsgBox ("No current record" & vbCrLf & "Copy will not be done.")

Exit Sub

End If
 
Sorry, I didn't make myself clear. My concern is not to be sure data is
saved in the table. It is rather to be able to step through the rows in the
form (whether or not they exist yet in the table). Please see response to
Van Dinh above for a clearer statement of the problem.

Thanks,
Laurel
 
See comments in-line.
--
HTH
Van T. Dinh
MVP (Access)


Laurel said:
Yes, it hit me in the night that I'd put that OR in erroneously, but that
was a very late change. Most of the time the code I was using was the
"not(.eof and .bof) that was commented out below it.
Click to expand...
Not (.EOF AND .BOF) is equivalent to
Not .EOF OR Not .BOF (by the Distributive Law)

This is NOT equivalent to what I posted.


Here is a cleaner statement, I think, of one aspect of the problem.

Overview. I want to 1) get a good recordcount of the rows visible in the
form and 2)make sure I have a current record , so that I can execute
subsequent scripts without error. To that end, I want to execute the
.movelast .movefirst. I get an error if I attempt .movelast .movefirst when
the form has no rows, so I was advised to use .eof and .bof to test for no
rows before trying to get the good recordcount. My problem is that EOF and
BOF are both true (sometimes) even when there is a row in the form. One
reproducible instance is when I create the first new row. I can clear up
these problems if I click on the empty newrow that is automatically
generated and then cick back on the row I just created, but I can't expect
my users to do that (although they have, indeed, learned to do that....)

Insert first row into empty form. Choose from a dropdown for a column with a
mouse. Click button that executes script.

EOF BOF Me.CurrentRecord lrstMe.RecordCount

TRUE TRUE 1 0

TRUE TRUE 1 0

TRUE TRUE 1 0
Click to expand...
That's is entirely reasonable. The New Record is still in the Form's
buffer. The CurrentRecord is simply an ordinal number but the number of
Records in the Form's Recordset is still zero since (I think) the New Record
is not added to the Recordset until you save the Record. Hence, you get
True, True, 1, 0.


If (.EOF And .BOF) Then

lngRecCount = 0 'No current record

Else

.MoveLast

lngRecCount = .RecordCount

.MoveFirst

End If

DoEvents 'Trying to get rid of occasional weird errors - feel like timing
problems

End With

Dim li_debug As Integer

li_debug = lrstMe.RecordCount

If (Me.CurrentRecord = 0) Or (lrstMe.RecordCount = 0) Then

MsgBox ("No current record" & vbCrLf & "Copy will not be done.")

Exit Sub

End If
Click to expand...
I normally use only (example only):

Set rs = Me.RecordsetClone
If rs.RecordCount = 0 then
'Empty Recordset
Else
rs.MoveLast
rs.MoveFirst
Debug.Print "True Count = " & rs.RecordCount
End If
 
Van T. Dinh said:
See comments in-line. My problem is that EOF and
Click to expand...

Thanks for the chart clarifying which variables mean which. But do you (or
anyone) have a practical answer to my problem. A user has entered the first
row in an empty buffer. They have not clicked on the next automatically
generated empty row, so the record is only in the buffer. They click on a
button that is meant to process that new row, but the code behind the button
failes, because the code thinks there is no row there.
 
Sorry, I thought I'd responded to this a few days ago, but I don't see it.
The issue is not counting the rows in the table. It's counting the rows in
the form, and making new ones available to the code. Please see my latest
response to the Tran thread.
 
1. If you only use the values in the Controls on the Form, then simply use
the Control reference to access these values. You don't need to check the
Form's Recordset.

2. If you need the newly entered Recordset to be actually updated into the
Table and included in the Form's Recordset, then you can simply save the
Record by code before your processing code. You can simply use one of:

DoCmd.RunCommand acCmdSaveRecord

or

Me.Dirty = False

Either of the above statements is equivalent to your user's action of
clicking the next row and then clicking back.

(if your code is executed outside the Form's context, use full reference to
the Form instead of Me.)
 
Getting there.
The suggestion to use DoCmd.RunCommand acCmdSaveRecord to force the effect
of having the user click on the new empty record seems to have solved most
of my practical problems. Hopefully this is a theoretical question: My
understanding was that the statements MoveLast, MoveFirst were to firce the
RecordCount value to be valid. If one tests RecordCount before executing
the MoveLast, MoveFirst, what is the point of those statements?
 
The RecordCount before the MoveLast only counts the Records / Rows that have
been populated at that time which may not be the true count. Hence, you
need the MoveLast for the true count.

However, for a *non-empty* Recordset, the RecordCount is never zero and an
empty Recordset always has zero RecordCount. Thus, I used the RecordCount =
0 whether the Recordset is empty or not.
 
Back
Top