Results 1 to 7 of 7
  1. #1
    Join Date
    May 2010
    Posts
    67

    Unanswered: Question regardig RecordsetClone Property?

    My code select the records that meet the selection criteria. I then want the a count of records that meet the selection criteria. There exist records that do meet the critieria, but I am getting runtime error 3021 "No Current Record" on the MoveFirst.

    If blnCalcTotalRecs Then
    Dim lngTotalrec As Long
    Dim rst As DAO.Recordset
    Set rst = Me.RecordsetClone
    rst.MoveFirst
    rst.MoveLast
    txtTotalrec = rst.RecordCount
    Set rst = Nothing
    End If

    Can I use the RecordsetClone property (RecordCount) to calculate the number of records that only meet the selection criteria that the user input, which is what I want. Or does the RecordsetClone (Recordcount) property calculate the total number of records that are bound to the form?

    Why am I getting an error on the MoveFirst?

    I am totally confused!

    Your help is appreciated!

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    The problem with the RecordSetClone is that the pointer or BookMark is not positioned to the same place that the form's recordset is. If you want to be able create a clone and position the bookmark you need to add:

    rst.BookMark = Me.RecordSet.BookMark

  3. #3
    Join Date
    May 2010
    Posts
    67
    Thank you DCKunkle.

    I added the code, however now I am getting the 3021 runtime error "No Current Record" on the BOOKMARK.

    Can I use DAO recordset to manipulate a ADO recordset ...I thought I read somewhere that I could....excuse me if I sound stupid...I am still learning Access, it can be a doosy!

    My code follows:

    If blnCalcTotalRecs Then
    Dim lngTotalRec As Long
    Dim rst As DAO.Recordset
    Set rst = Me.RecordsetClone
    rst.Bookmark = Me.Recordset.Bookmark
    rst.MoveFirst
    rst.MoveLast
    lngTotalRec = rst.RecordCount
    Set rst = Nothing
    End If

    My code looks like:

    Dim Conn As ADODB.Connection
    Set Conn = CurrentProject.Connection
    Dim rs1 As New ADODB.Recordset
    rs1.ActiveConnection = Conn



    Dim strSQL As String
    Dim blnCalcTotalRecs As Boolean
    blnCalcTotalRecs = True


    If Not IsNull(Me.JobNumber) Or _
    Not IsNull(Me.CampaignType) Or _
    Not IsNull(Me.MailerStatus) Then
    If Not IsNull(Me.JobNumber) Then
    strSQL = "select *" & _
    " from tblClientMailSchedule" & _
    " where clientid = " & Me.clientid & _
    " and jobnumber = '" & Me.JobNumber & "';"

    Else
    If Not IsNull(Me.CampaignType) And Not IsNull(Me.MailerStatus) Then
    strSQL = "select *" & _
    " from tblClientMailSchedule" & _
    " where clientid = " & Me.clientid & _
    " and campaigntype = '" & Me.CampaignType & "'" & _
    " and mailerstatus = '" & Me.MailerStatus & "'" & _
    " order by startdate;"
    Else
    If Not IsNull(Me.CampaignType) Then
    strSQL = "select *" & _
    " from tblClientMailSchedule" & _
    " where clientid = " & Me.clientid.Value & _
    " and campaigntype = '" & Me.CampaignType.Value & "'" & _
    " order by startdate;"
    Else
    If Not IsNull(Me.MailerStatus) Then
    strSQL = "select *" & _
    " from tblClientMailSchedule" & _
    " where clientid = " & Me.clientid.Value & _
    " and mailerstatus = '" & Me.MailerStatus.Value & "'" & _
    " order by startdate;"
    End If
    End If
    End If
    End If
    Else
    strSQL = "select * " & _
    "from tblClientMailSchedule" & _
    " where clientid = " & Me.clientid.Value & _
    " order by startdate ; "
    End If

    Debug.Print strSQL

    rs1.Open strSQL, Conn, adOpenDynamic, adLockOptimistic

    If rs1.BOF Or rs1.EOF Then
    MsgBox "NO SCHEDULE EXIST FOR CLIENT - Please verify your selection criteria"
    Exit Sub
    End If


    If blnCalcTotalRecs Then
    Dim lngTotalRec As Long
    Dim rst As DAO.Recordset
    Set rst = Me.RecordsetClone
    rst.Bookmark = Me.Recordset.Bookmark

    rst.MoveFirst
    rst.MoveLast
    lngTotalRec = rst.RecordCount
    Set rst = Nothing
    End If

    Debug.Print lngTotalRec
    Me.txtRecordNo = "Record " & Me.CurrentRecord & " of " & lngTotalRec

    Call Move_Record_To_Form(rs1)

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    DAO and ADO are different and do not play together. DAO is an older technology but can still be used. But in general I would recommend using ADO for any future code.

    There are certain situations that using DAO is required. I tried a while ago to move Access objects around to different databases using code and I think you can only use DAO for this. But for accessing data in Access, SQL server and/or other data stores use ADO.

    It looks to me like you have created a Query By Form form. The user enters (or leaves blank) certain fields, you create a SQL statement that searches for what the user wants. Is this correct?

    So is the form bound to any data? Do you have anything in the form's Record Source field? If not then you cannot use Me.RecordsetClone. RecordsetClone makes a copy of the data that the form is currently bound to whether it is a table, a query or a filtered version of either one. If you want a count of the records you can just count the records in rs1. But I think you have to change the record type to a KeySet cursor type.

    Change:
    rs1.Open strSQL, Conn, adOpenDynamic, adLockOptimistic
    to
    rs1.Open strSQL, Conn, adOpenKeySet, adLockOptimistic

    and then you can:
    rs1.MoveLast
    lngTotalRec = rs1.RecordCount

  5. #5
    Join Date
    May 2010
    Posts
    67
    Thank you DCKunkle, it worked! I implemented your suggestions.

    The form is bound. And the Record Source field contains the source table used. Based on the selection criteria that the user enters, I do create a SQL statement and display the data on the form.

    More reading to do.....

    Question. Do the Recordsetclone property work with ADO? Most examples that I have seen used DAO.

    Again, I truly appreciate your help.

  6. #6
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I had to double check the help on RecordsetClone when I first replied. And it looks like whenever you use RecordsetClone it creates a DAO recordset. There is no option to make it ADO. I am not sure why that is. Probably not enough interest from programmers for Microsoft to change it.

    The other thing I read in the help is if the recordset for the form is changed then any clones will become invalid. So I am not sure on your timing but if you are showing the filtered results and then checking the clone you may be invalidating the clone.

    I have done several forms like this one and one thing that I have noticed is that, typically, the only part of the SQL that changes is the WHERE portion. I have also had code where I used alot if IF THEN ELSE statements and the code got confusing quickly. I would suggest trying code similar to:

    Code:
    Dim strSQL as String
    Dim strWhere as String
    
    strSQL = "SELECT * FROM tblClientMailSchedule"
    strWhere =  "WHERE clientid = " & Me.clientid 
    
    If Not IsNull(Me.JobNumber) Then strWhere = strWhere & " and jobnumber = '" & Me.JobNumber
    If Not Isnull(Me.MailerStatus) Then strWhere = strWhere & " and mailerstatus = '" & Me.MailerStatus.Value & "'"
    
    strSQL = strSQL & strWhere & " ORDER BY startdate;"
    I know I missed some logic but I think you can get the idea. Basically the code just keeps adding to the WHERE clause of the SQL depending on if the user put in a value. The other benefit to this code is that if/when the table name changes you only have to change it once.

  7. #7
    Join Date
    May 2010
    Posts
    67
    Thank you. Excellent coding technique. I will definitely use it.

    Again, thanks so very much for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •