Navigate through subform records (2000 (all updates))

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote