• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1099
  • Last Modified:

DAO / VB6 code - .MoveFirst statement gives "No Current Record" error

Hi,

I am using DAO in VB6 code, here's the fragment that is giving the error.

NOZ = datSelectedZValues.Recordset.RecordCount 'the debug window shows that NOZ=1 datSelectedZValues.Recordset.MoveFirst '20070808 - bombs here with "No Current Record" error!

I am getting a "No Current Record" error when the .MoveFirst statement is executed.

How can this error occur when the ..RecordCount statement shows that there is one record?

Regards,
Leigh

0
LeighWardle
Asked:
LeighWardle
  • 3
  • 3
1 Solution
 
harfangCommented:
I have never encountered this problem. My only guess is that datSelectedZValues isn't a permanent object or that the recordset is otherwise changed between these lines.

As a general rule, access an object once, and then perform all needed operations, as in:

    With datSelectedZValues.Recordset
        NOZ = .RecordCount 'the debug window shows that NOZ=1
        If .RecordCount Then .MoveFirst
    End With

Can you show us a little bit of context, i.e. what datSelectedZValues is, and how you open the recordset?

(°v°)
0
 
LeighWardleAuthor Commented:
Dear harfang,

I hope these code fragments give you the entire context...

Regards
Leigh

    datSelectedZValues.DatabaseName = strDataPath & "\layers.mdb" '20030603
    datSelectedZValues.Exclusive = gboolReadOnly '20070225

' This code empties the table
    SQL = "SELECT SelectedZValues.* From SelectedZValues ORDER BY SelectedZValues.Z"
    datSelectedZValues.RecordSource = SQL
    datSelectedZValues.Refresh
   
   
    'delete all existing records in table first!
    datSelectedZValues.Database.Execute ("Delete * from SelectedZValues")
    datSelectedZValues.Refresh

' User Clicks the "New" button
    If Not (datSelectedZValues.Recordset.BOF And datSelectedZValues.Recordset.EOF) Then '20070808
        datSelectedZValues.Recordset.MoveLast
    End If
    Call AddRecordMultiFields(datSelectedZValues, "Z", "0.0", "intZInterfaceAboveOrBelow", "-1") 'adds the

new record
' at this stage the TrueDB Grid control bound to datSelectedZValues displays the correct default data

' now we attempt to move to the first record
NOZ = datSelectedZValues.Recordset.RecordCount 'the debug window shows that NOZ=1 datSelectedZValues.Recordset.MoveFirst '20070808 - bombs here with "No Current Record" error!


Sub AddRecordMultiFields(datControl As Data, strFieldName As String, strFieldContent As String, Optional

strFieldName2 As String = "", Optional strFieldContent2 As String = "", Optional strFieldName3 As String =

"", Optional strFieldContent3 As String = "", Optional strFieldName4 As String = "", Optional

strFieldContent4 As String = "", Optional strFieldName5 As String = "", Optional strFieldContent5 As

String = "", Optional strFieldName6 As String = "", Optional strFieldContent6 As String = "", Optional

strFieldName7 As String = "", Optional strFieldContent7 As String = "", Optional strFieldName8 As String =

"", Optional strFieldContent8 As String = "", Optional strFieldName9 As String = "", Optional

strFieldContent9 As String = "", Optional strFieldName10 As String = "", Optional strFieldContent10 As

String = "")
    '+++ VB/Rig Begin +++
    Const sPROC_ID = "AddRecordMultiFields"
    Dim nlErr As Long, sErrMsg As String
    On Error GoTo AddRecordMultiFields_Err
    Call VBRig_Error(VBRIG_PUSH_PROC_STACK, 0, "", sMOD_ID, sPROC_ID)
    '+++ VB/Rig End +++
    'generalized version of AddRecord2 etc.
   
    Dim MyBookMark As Variant
   
    datControl.Recordset.AddNew
   
    datControl.Recordset(strFieldName) = strFieldContent
   
    If strFieldName2 <> "" Then
        datControl.Recordset(strFieldName2) = strFieldContent2
    End If
   
    If strFieldName3 <> "" Then
        datControl.Recordset(strFieldName3) = strFieldContent3
    End If

    If strFieldName4 <> "" Then
        datControl.Recordset(strFieldName4) = strFieldContent4
    End If

    If strFieldName5 <> "" Then
        datControl.Recordset(strFieldName5) = strFieldContent5
    End If

    If strFieldName6 <> "" Then
        datControl.Recordset(strFieldName6) = strFieldContent6
    End If

    If strFieldName7 <> "" Then
        datControl.Recordset(strFieldName7) = strFieldContent7
    End If

    If strFieldName8 <> "" Then
        datControl.Recordset(strFieldName8) = strFieldContent8
    End If

    If strFieldName9 <> "" Then
        datControl.Recordset(strFieldName9) = strFieldContent9
    End If

    If strFieldName10 <> "" Then
        datControl.Recordset(strFieldName10) = strFieldContent10
    End If
   
   
   
    datControl.Recordset.Update
    MyBookMark = datControl.Recordset.LastModified
    datControl.Recordset.Bookmark = MyBookMark
    '+++ VB/Rig Begin +++
    Call VBRig_Error(VBRIG_POP_PROC_STACK, 0, "", sMOD_ID, sPROC_ID)
    Exit Sub

'========================
AddRecordMultiFields_Err:
'========================
    nlErr = Err.Number
    sErrMsg = Err.Description & " [Source=" & Err.Source & "]"
    Call VBRig_Error(VBRIG_SHOWLOG_ERROR, nlErr, sErrMsg, sMOD_ID, sPROC_ID)
    Call VBRig_Error(VBRIG_POP_PROC_STACK, 0, "", sMOD_ID, sPROC_ID)
    Exit Sub
    '+++ VB/Rig End +++
End Sub

0
 
harfangCommented:
OK, I can work with that. Let's summarize:

1) you open a database
2) you set the .RecordSource, implicitly creating a .Recordset
3) you delete all records from the table used as recordsource
4) you attempt to add a record to the .Recordset
5) you try to move to the first record

It appears that you need to switch 2) and 3) or add a 3b): .Requery (which seems inefficient, as you don't use the .Recordset before deleting all its contents).

Another idea: use this SQL:

    SQL = "SELECT * From SelectedZValues WHERE False ORDER BY Z"

This creates an empty recordset, regardless of existing records in the table. This makes sense, as you are going to delete them anyway. But note that every .Requery would again hide any existing records.

The ORDER BY clause is meaningless in this case, as the order of records will always be the data entry order.

The problem could simply come from the remaining record(s) in the .Recordset which have been deleted. As you don't requery, any deleted records remain in the open .Recordset.

On the other hand, the .Recordcount=1 suggests that you have only one (valid) record in the .Recordset. Normally, .Recordcount includes any deleted records from the table, until you .Requery... So I'm not sure about all this...

(°v°)
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
jkaiosCommented:
Use both the BOF and EOF properties first to check if the recordset is, in fact, contains records before setting the current record position, such as calling the MoveFirst, MoveNext, MoveLast, etc methods.

Private Sub Command1_Click()
   If datSelectedZValues.Recordset.BOF And datSelectedZValues.Recordset.EOF
      MsgBox "Recordset is currently empty."
   ElseIf datSelectedZValues.Recordset.BOF Then
      MsgBox "Beginning of recordset."
      datSelectedZValues.Recordset.MoveFirst
   ElseIf datSelectedZValues.Recordset.EOF Then
      MsgBox "End of recordset."
      datSelectedZValues.Recordset.MoveLast
   Else
      datSelectedZValues.Recordset.MoveFirst
   End If
End Sub
0
 
LeighWardleAuthor Commented:
Thanks, harfang, for your suggestions.

I now have fixed the problem.  Sorry, jkaios, but your reply arrived after I fixed my problem.

Although harfang did not fix the problem, I tried his suggestion to use the .Requery statement.
This gave me a "This action was cancelled by an associated object" error, which suggested the problem was related to tabSelectedZValues (the TrueDB Grid control bound to datSelectedZValues).  The previous statement relating to the TrueDB Grid control was tabSelectedZValues.EditActive = True.

The fix for the problem was to insert:

 tabSelectedZValues.EditActive = True

Immediately prior to:

datSelectedZValues.Recordset.MoveFirst '20070808 - bombs here with "No Current Record" error!

Regards,
Leigh

0
 
LeighWardleAuthor Commented:
Sorry,  I made a mistake with the fixed code in my previous comment.

It should be:

The fix for the problem was to insert:

 tabSelectedZValues.EditActive = False  '<<<<< this was wrong in the previous comment

Immediately prior to:

datSelectedZValues.Recordset.MoveFirst '20070808 - bombs here with "No Current Record" error!
0
 
harfangCommented:
Well, grats on finding that! I could not have guessed...
Thanks and success with your project!
(°v°)
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now