Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2009
    Posts
    13

    Exclamation Unanswered: Looping through record on a Continuous Form

    I have a continuous form that feeds off of a query. Currently, I fixed this query so only 19 records will pull in. I have a modified check box on the form so the user can select individual records. After selecting the records, I have a button that I would like to update another table with the selected records. I am looping through the records and have the code in there to move to the first record, however, it seems to stay on the last record I selected 19 times and will only insert that one row into my table. What am I missing???


    Private Sub Command39_Click()

    Dim rs As DAO.Recordset
    Dim strSQL As String

    Set rs = Me.RecordsetClone

    rs.MoveFirst

    Do While Not rs.EOF
    'If IsChecked(Me.ID_INVC) = True Then
    If Me.Check36 = True Then
    strSQL = "Insert into dbo_sales_tax_info (ORDER_NO, INVOICE_NO) Values('" & Me.ID_ORD & "','" & Me.ID_INVC & "');"
    'DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    'DoCmd.SetWarnings True
    End If
    rs.MoveNext
    Loop

    End Sub

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Moving the current record of the clone does not move the current record of your recordset. You have to use bookmarks to equate both current records:

    Code:
    Dim strBookmark as String
    Set rs = Me.RecordsetClone
    rs.MoveFirst
    strBookmark = rs.Bookmark
    rs.Close
    Me.Bookmark = strBookmark
    Have a nice day!

  3. #3
    Join Date
    Jan 2009
    Posts
    13
    I applied the code you suggested, and it is still only looping through the one record 19 times.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Post the code again.

    Every time you move to a new record in the RecodsetClone, you have to do the bookmark thing again if you are going to use the Me. construct.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Is your form continuous? Or is a subform on the main form that is continuous? It sounds to me like you have the query set up as a subform. In that case use:

    Set rs = me.subformname.Form.RecordsetClone

    To set up the recordset. Substitute subformname for your actual subform name.

  6. #6
    Join Date
    Jan 2009
    Posts
    13
    I guess I'm not understanding the whole bookmark thing. The code I have now is below. This is not a subform but a form that is continuous. It takes the last record I select and loops through that record 19 times. Am I applying the bookmark code in the wrong way (I'm sure I am)? Thanks for all your help!



    Private Sub Command39_Click()

    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim strBookmark As String

    Set rs = Me.RecordsetClone

    rs.MoveFirst

    strBookmark = rs.Bookmark

    Do While Not rs.EOF
    'If IsChecked(Me.ID_INVC) = True Then
    If Me.Check36 = True Then
    strSQL = "Insert into dbo_sales_tax_info (ORDER_NO, INVOICE_NO) Values('" & Me.ID_ORD & "','" & Me.ID_INVC & "');"
    'DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    'DoCmd.SetWarnings True
    End If
    rs.MoveNext
    Loop

    rs.Close
    Me.Bookmark = strBookmark

    End Sub

  7. #7
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    The confusion is, you are making a copy of the data that the form is displaying by using the RecordsetClone. So now you have two datasets DataA and DataB, DataA is the form data and DataB is the rs data. When you use Me you are asking about the form data (DataA) the form data is referencing the currently selected record. When you are using rs you are referencing the second dataset. You use the Bookmark property to synchronize the two recordset. So if you want the form to point to the first record in the clone (rs) you need to synchronize them by setting the form to look at the same record:

    Me.BookMark = rs.BookMark

    The easiest thing to do (in my opinion) is to reference the data itself instead of the form objects (Me.Check36, Me.ID_ORD, Me.ID_INV). To do this, Me.Check36 is bound to some data field, reference the data instead of the object displaying the data. You can get the name of the data field by checking the ControlSource property. Then change the code to:

    Assume Check36 is bound to the Checked field:

    if rs!Checked then

    And then change Me.ID_ORD to rs!ID_ORD and Me.ID_INVC to rs!ID_INVC

  8. #8
    Join Date
    Jan 2009
    Posts
    13
    Thank you for the explanation because now I totally get the difference and now I can get it to loop through my records. Here lies my next problem: becuase this is a continuous form, I have a modified check box so the user can select multiple records - I then want these selected records to write to a table. I do not know how to reference to this check box in the recordset as it is not bound to a table. The code for the check box is below. The modified check box is a check box with a button behind it. I found the code in the attached database. Am I able to refer to this check box in a recordset? Thanks for all your help!

    Option Compare Database
    Option Explicit

    Dim colCheckBox As New Collection


    Public Function IsChecked(vID As Variant) As Boolean

    Dim lngID As Long

    IsChecked = False

    On Error GoTo exit1

    lngID = colCheckBox(CStr(vID))
    If lngID <> 0 Then
    IsChecked = True
    End If

    exit1:

    End Function


    Private Sub Command33_Click()
    Debug.Print "invoice = " & Me.ID_INVC

    If IsChecked(Me.ID_INVC) = False Then
    colCheckBox.Add CLng(Me.ID_INVC), CStr(Me.ID_INVC)
    Else
    colCheckBox.Remove (CStr(Me.ID_INVC))
    End If
    Me.Check36.Requery
    End Sub
    Attached Files Attached Files

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Nothing to add: the explanation was perfect, except that I'm glad your problem is solved.

    Have a nice day!

  10. #10
    Join Date
    Jan 2009
    Posts
    13
    Sinndho,

    My problem is not totally resolved as you can see from my last reply. Yes, now I can loop through the records, but I am still having an issue.......

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I'm not sure to totally understand what you try to do but anyway you cannot reference the checkbox into the recordset because this checkbox is unbound, meaning that it is not related to any data (any column if you prefer) in the data source of you form.

    When you use the RecordsetClone method you create a clone (a copy) of the recordset of you form, not a copy of the form itself. The recordset of a form handles the data from the data source of this form (table or query) and nothing else. As the checkbox is unbound, it has no related data hence it has no reference in the recordset.

    Have a nice day!

  12. #12
    Join Date
    Dec 2009
    Posts
    1

    Cool Great Thread ... Bookmark worked for me .. here is my code

    I am displaying a continous form with a bound check box control.

    The only records that display are records that are not checked.

    Two buttons are on the form, Update and Cancel.

    If the user clicks the check box for any of the records AND they hit the cancel button, I want to revert any of the checked records back to their original form.

    Since all of the records that initially display were not checked, here is how I solved my problem:

    Private Sub Form_Unload(Cancel As Integer)
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim strBookmark As String
    ' This code will uncheck all of the checked fee's if the
    ' User cancels the Update from EDS request
    Set rs = Me.RecordsetClone
    rs.MoveFirst
    Do While Not rs.EOF
    strBookmark = rs.Bookmark
    If Me.chkFeeLocked = True Then Me.chkFeeLocked = False
    Me.Bookmark = strBookmark
    rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    End Sub

    Thank you for the informative thread.

    ElMatador

Posting Permissions

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