Results 1 to 15 of 15
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Navigate through subform records (2000 (all updates))

    What is the correct procedure to move through records in a subform, stopping if at the first record?

    Here is my code, which takes me from the [RemovedDate] control on the main form to the subform.
    Private Sub RemovedDate_AfterUpdate()
    On Error GoTo RemovedDate_AfterUpdate_Error

    Call MsgBox("Any existing Envelope #s will now be discontinued." _
    & vbCrLf & "" _
    & vbCrLf & " You will then be returned to Remarks" _
    & vbCrLf & "in case you wish to enter reasons for removing this record." _
    , vbExclamation Or vbDefaultButton1, "Envelope #s to be Discontinued")
    Me.Form![tblEnvelopeNumbers subform].SetFocus
    DoCmd.GoToRecord , , acLast
    Me.Form![tblEnvelopeNumbers subform]!EndDate.SetFocus
    If Me.Form![tblEnvelopeNumbers subform]!EndDate > Date Then
    Me.Form![tblEnvelopeNumbers subform]!EndDate = Date
    If Me.Form![tblEnvelopeNumbers subform]!AssignedTo = "A" Then
    GoTo ProcessFinished
    Else
    DoCmd.GoToRecord , , acPrevious
    If Me.Form![tblEnvelopeNumbers subform]!EndDate > Date Then
    Me.Form![tblEnvelopeNumbers subform]!EndDate = Date
    GoTo ProcessFinished
    End If
    End If
    End If

    ProcessFinished:
    Me.Remarks.SetFocus

    On Error GoTo 0
    Exit Sub

    RemovedDate_AfterUpdate_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure RemovedDate_AfterUpdate of VBA Document Form_frmTrinity"
    End Sub

    The above code works backwards from the last record in the subform (acLast) and works if there are only 2 records to which an EndDate needs to be applied. However, in the odd case there are 3 records which need an EndDate. But I can't go back more than 1 record using this procedure. What I need is a test that says "if you've reached the first record, go to ProcessFinished." I tried testing for acFirst (If acFirst Then...) but this produced erratic results.

    Thanks.

    Tom

  2. #2
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Navigate through subform records (2000 (all updates))

    If you're just trying to loop through all the records displayed in a subform, you can use the subform's recordsetclone, and loop through that.
    If you want to do it one by one, as it seems you do, to allow users to enter comments for each record, you'll obviously have to bin the move last line, and add some code to the Comments exit event to check if all the records in the subform have had their end dates completed, and if so exit (but you can't close a form from the onexit event). Alternatively, you could loop through, put up an inputbox for each record for any comments, and then fill the comments field of that record from the input box, then continue through the loop.

    If you really want to stick with the code you have at the moment, which doesn't really make a lot of sense to me, I have to say, then you could trap the "You can't go to that record" error - I think it might be number 2107, but you can check, and that will let you know that you've done the first record already. But it's not the best way, as you might occasionally have another reason for that error.

    Just in case you're unfamiliar with recordsets, it would be something like this

    Dim rst as DAO.Recordset
    Set rst=Me![tblEnvelopeNumbers subform].Form.Recordsetclone
    With rst
    .MoveFirst
    Do
    If !fldEndDate > Date Then !fldEndDate = Date
    .MoveNext
    Loop Until .EOF
    End With
    Set rst=nothing

    Note that fldEndDate is the name of the FIELD that is the controlsource of EndDate, not the name of the control.

  3. #3
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Navigate through subform records (2000 (all updates))

    Mark
    Thanks for the Recordset code. And I agree that I want something such as this. Unfortunately, the code you suggested doesn't work. I tried tinkering with it in a variety of ways but get errors no matter what I do (e.g. Error 3020).

    What I am trying to do is this...
    When the user enters a date in the RemovedDate box on the main form (not actually removing the record from the database, removing it from current activity), she is taken to the [tblEnvelopeNumbers subform] so that corresponding EnvelopeNumbers can have an EndDate applied, and then returned to the Remarks box on the main form to make necessary comments there.

    There could be 1, 2 or 3 EnvelopeNumbers to which an EndDate should be applied. I want that EndDate in the subform to be handled automatically without user intervention. BUT this will happen for the one record only, the one that is being "removed."

    Tom

  4. #4
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Navigate through subform records (2000 (all updates))

    Mark
    Well, I did manage to modify the code, and get it to deal with the first record in the subform, using the following...

    Me.Form![tblEnvelopeNumbers subform].SetFocus
    Dim rst As DAO.Recordset
    Set rst = Me![tblEnvelopeNumbers subform].Form.RecordsetClone
    With rst
    .MoveFirst
    Do
    If Me.Form![tblEnvelopeNumbers subform]!EndDate > Date Then Me.Form![tblEnvelopeNumbers subform]!EndDate = Date
    .MoveNext
    Loop Until .EOF
    End With
    Set rst = Nothing

    But it changes the EndDate in the first subform record only and then returns to the main form.

    Any suggestion as to what to do now would be appreciated.

    Tom

  5. #5
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Navigate through subform records (2000 (all updates))

    Tom,

    I'm still not sure what you mean when you say the end date will be marked for one record, whilst you talk of 1,2 or 3 records in the subform.

    As to your modified code, you're still referring to the control on the form, whereas recordsets deal with data in the table or query underlying the form. That's why I made the point that you need to replace fldEndDate with the name of the field from which the EndDate control takes its data (ie the ControlSource of the EndDate control, which you can check in the control's properties), though I can't immediately work out why that should be giving you 3020 errors. When dealing with recordsets, you don't care about where the focus lies in a form. Your modified code is only changing the EndDate for the current record in the subform, as your still referring to the control.

  6. #6
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Navigate through subform records (2000 (all updates))

    Mark
    Sorry for the confusion. I'll try to explain.

    example...
    John and Sally Smith is the record on the main form, and that (and only that) main form record is being ended. But in the EnvelopeNumbers subform, the Smiths could have as many as 3 EnvelopeNumbers (one for both, one for John, one for Sally). So, in the EnvelopeNumbers subform, there could be 1, 2 or 3 EnvelopeNumbers that relate to them.

    As for the name of the control, it is EndDate both on the subform and in the table that underlies the subform.

    Going back to the John and Sally Smith example, if they have 3 active records in the EnvelopeNumbers subform I need to change the EndDate for each of those 3 to Date. As I indicated in my last post, I can get the first one changed, but only the first one. The code doesn't loop through and change the other 2.

    Tom

  7. #7
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Navigate through subform records (2000 (all updates))

    Mark

    Further clarification. The control source for the EndDate control is EndDate in the underlying table. And when the subform was created, Access automatically gave it the name EndDate. So the control source and the name of the control is the same. Does that cause any difficulty? I notice that Access always does it that way.

    Tom

  8. #8
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Navigate through subform records (2000 (all updates))

    No difficulty, although it's usually good practice to name your controls independently, depending on what type of control they are - eg txtEndDate if it's a text box. There's a set of guidelines somewhere, but I'm an amateur, so don't have them to hand!

    So this code should work:

    Dim rst as DAO.Recordset
    Set rst=Me![tblEnvelopeNumbers subform].Form.Recordsetclone
    With rst
    .MoveFirst
    Do
    If !EndDate > Date Then !EndDate = Date
    .MoveNext
    Loop Until .EOF
    End With
    Set rst=nothing

    Of course you'll need to put the Dim statement up at the beginning of the procedure, and add your msgbox and final set focus line.
    If you're still getting errors, can you post the a db with the forms, necessary underlying queries and tables, and some sample data with 2 or 3 envelopes per unit.

  9. #9
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Navigate through subform records (2000 (all updates))

    Mark
    Thanks for sticking with this but, unfortunately, the code sure enough doesn't work. It produces Error 3020 (Update or Cancel Update without AddNew or Edit).

    The following code will change the EndDate in the first subform record, but won't go further (NOTE that the only difference is in the line that references the EndDate field in the subform)
    Me.Form![tblEnvelopeNumbers subform].SetFocus
    Dim rst As DAO.Recordset
    Set rst = Me![tblEnvelopeNumbers subform].Form.RecordsetClone
    With rst
    .MoveFirst
    Do
    If Me.Form![tblEnvelopeNumbers subform]!EndDate > Date Then Me.Form![tblEnvelopeNumbers subform]!EndDate = Date
    .MoveNext
    Loop Until .EOF
    End With
    Set rst = Nothing

    I'm going to accept your offer, and will prepare and post a small copy of the database.

    Tom

  10. #10
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Navigate through subform records (2000 (all updates))

    Mark
    Here is a small zipped copy of the database. I have included only 3 records. One has only 1 entry in the Envelope Numbers subform, one has 2, and one has 3.

    The form to be looked at is frmTrinity. As you will note, the Envelope Numbers are the means through which weekly donations are credited.

    Tom
    Attached Files Attached Files

  11. #11
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Navigate through subform records (2000 (all updates))

    It's a Homer Simpson moment for me. That's what you get for dealing with amateurs.

    Try this:

    Dim rst As DAO.Recordset
    Set rst = Me![tblEnvelopeNumbers subform].Form.RecordsetClone
    With rst
    .MoveFirst
    .Edit
    Do
    If !EndDate > Date Then !EndDate = Date
    .MoveNext
    Loop Until .EOF
    .Update
    End With
    Set rst = Nothing

    The clue was in the error message. I'd made the additional Doh! mistake of trying to find the message from the error number from your previous post by raising that error, but that technique gives one the wrong description. Sorry for the wasted time.

  12. #12
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Navigate through subform records (2000 (all updates))

    Mark
    Did the code you send work properly on the db I sent to you? It didn't here. It still gave me the Error 3020 message.

    However, I altered the code a bit and what I am posting below seems to work (at least in a short bit of testing).

    The only significant difference is the placement of the .Update line, which I put before the .MoveNext rather than after. I also put the Until .EOF in the Do line rather than the Until line, but I don't know whether or not that makes any difference.

    Dim rst As DAO.Recordset
    Set rst = Me![tblEnvelopeNumbers subform].Form.RecordsetClone
    With rst
    .MoveFirst
    Do Until .EOF
    .Edit
    If !EndDate > Date Then !EndDate = Date
    .Update
    .MoveNext
    Loop
    End With
    Set rst = Nothing

    Mark, I know about amateurs. That's me, through and through.

    Thanks a whole lot for working through this with me.

    Tom

  13. #13
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Navigate through subform records (2000 (all updates))

    Sorry, I didn't think you had to edit and update for each record, but it seems you do. I didn't try the code, as the problem in my code seemed obvious from your error message.

    Glad it's working now though.

  14. #14
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Navigate through subform records (2000 (all updates))

    Mark
    Yep. A whole bunch of testing would indicate that, as Homer Simpson might say, "Marge, it's as handy as a pocket in a shirt."

    Thanks again for all your help.
    Tom

  15. #15
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Navigate through subform records (2000 (all updates))

    If you're updating a DAO recordset ( which is what a recordsetclone returns), then you have to use an update to save the edit before you move to the next record. One Edit, one update. ADO has batch updates, but they work somewhat differently.
    Charlotte

Posting Permissions

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