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

vba code to close form (with a subform) after user clicks on the back record navigation selector button

If there are records, the form with the subform opens up.  The user then clicks the record selector button (bottom left of the form with the arrows) one by one to incrementally move through the records, which automatically set values in the subform based on the values in the main form until the user hits the last record.  At that point, if the user starts to move backwards using the back record selector button, there are no more values to fill in the subform as they were already filled out in the first place when the user was moving forward using the forward record selector button.  So if the user decides to move backwards, I want a message saying that vaues already full or applied etc etc. and basically close the form.  That is all.  How do I get this form to close programatically using VBA?  The form is called "z CCR_ProblemCode_RGO" and the VBA code is attached behind it.  Thanks.
test.mdb
0
sxxgupta
Asked:
sxxgupta
  • 18
  • 6
  • 3
2 Solutions
 
SheilsCommented:
you can close a form with the following code:

DoCmd.Close

or

DoCmd.Close "Formname"
0
 
sxxguptaAuthor Commented:
I tried that and I got error error 2585 "this action cannot be carried out while processing a form or report event.  I wish it was as simple as that:)
0
 
sxxguptaAuthor Commented:
doevents and cancelevent does not work either:)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
sxxguptaAuthor Commented:
The OnCurrent event property of "z CCR_ProblemCode_RGO" has to be adjusted somehow....
0
 
SheilsCommented:
I tried open you form and it closes after the message.

What exactly is the problem
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can test for the EOF in the form's Current event:

If Me.Recordset.EOF Then
  Msgbox "You have completed this assignment. Please close the form.", vbOkOnly
End If
0
 
sxxguptaAuthor Commented:
sb9, keep clicking the forward record selector button, then click the back button, which at that point in time I want the form to close.
0
 
sxxguptaAuthor Commented:
LSM consulting:
 
Nothing is happening with the code below....no message boxes.

Private Sub Form_Current()

' Provide a record counter for using with
' custom navigation buttons (when not using Access built in navigation)

Dim rst As DAO.Recordset
Dim lngCount As Long

Set rst = Me.RecordsetClone

With rst
    .MoveFirst
    .MoveLast
    lngCount = .RecordCount
End With
    
'Show the result of the record count in the text box (txtRecordNo)

Me.txtRecordNo = "Record: " & Me.CurrentRecord & " of " & lngCount

If Me.Recordset.EOF Then
    MsgBox "The recordset behind this form does not have any records!"
    DoCmd.Close acForm, "z CCR_ProblemCode_RGO"
ElseIf Me.RecordsetClone.RecordCount > 0 Then
    Me.z_CRF_RcvdDate.Form.RcvdDate.Value = Me.Return_Date
    Me.z_CRF_RcvdDate.Form.CurrentStatus.Value = 2
    Me.z_CRF_RcvdDate.Form.DateClosed.Value = ""
End If

End Sub

Open in new window

0
 
sxxguptaAuthor Commented:
LSM consulting:
I can keep going forward and backwards with no message boxes...
0
 
SheilsCommented:
I have downloaded the form but it does not open. Just get the message. When I remove the docmd.close in the on open event it opens.

Please post a sample with some data to demonstrate the problem
0
 
sxxguptaAuthor Commented:
I have the db file attached below.  Please open form "z CCR_ProblemCode_RGO".  You should see about 10 records.  Then, keep clicking the right record selector button on the navigation bar.  This sets the RcvdDate value of the embedded subform.  Once you reach the last record, I want to prevent the user from being able to "move back" through the records.  So if the user clicks the back record selector button, I want a prompt to come up saying "All Date Values set" and after the user acknowledges the message, the form closes.
test.mdb
0
 
sxxguptaAuthor Commented:
If you need to test the code over and over , you will have to open the dbo_Crfrpt table and delete some RcvdDate field values in order to get the form to open again......Just delete the entered values in the table...do not delete the records themselves.   Once the form is open, these values get "re-filled" into the table again as you select the forward record selector button.
0
 
sxxguptaAuthor Commented:
Just want the form to close once the user decides to select the back record selector button after the user has reached the last record.....
0
 
SheilsCommented:
What about before they reach the end. Do you want them to be able to go back if they have not reach the end.
0
 
sxxguptaAuthor Commented:
yeah, that's fine:)  Thanks.
0
 
sxxguptaAuthor Commented:
I am trying to force the user to go all the way to the end of the recordset.  Once there, the user cannot go back and the form closes.  Thanks again.
0
 
SheilsCommented:
Ok

I have come up with a method that provides triggers at the right time. However, there is an error 2585 on the Docmd.Close event which I can't quiet figure out. Many more experienced experts like LSMConsulting may be able to shed some light.

Basically my method uses the following approach.

Count the number of records in the recordset when the form opens.

Then compare the current record with the past record and the number of record in the recordset.

If the current record is smaller than the past record and the past record equals the number of record in the recordset then the close action is triggered.

I have left the msg box that monitors the process

test-5-.mdb
0
 
sxxguptaAuthor Commented:
Hi sb9:
That is the same problem that I am facing with error 2585.  The form does not want to close if a docmd.close is inserted into the OnCurrent event property......... I have been struggling with this for days now:(
0
 
sxxguptaAuthor Commented:
LSMConsulting, would u be kind enough to elaborate or solve our problem.........Thanks.  How does one close a form using the OnCurrent event property and not deal with error 2585.  It is an annoying error for a form to not be able to close.  But clicking the "x" button on the form closes the form......go figure...
0
 
SheilsCommented:
It may have something to do with the way the received date is updated. I noticed that when the table is opened at the same time as the form, then last record is not update until you move back. Also it seems that a black space is entered in the new record because you have to click in the new record field and delete. This is strange since the auto number has not activated.
0
 
sxxguptaAuthor Commented:
The last entry will not update until you move back out of the last record.  The user will not have the table open.  For testing purposes, I delete several RcvdDate values from the table and then close the table.  I then open the form and navigate thrugh the records.AutoNumber will only generate once you start typing anything in any field.....by that is not relevant in this case.
0
 
sxxguptaAuthor Commented:
The last entry will not update until you move back out of the last record IF the table is open.
0
 
sxxguptaAuthor Commented:
Maybe the whole approach is incorrect.  Instead of using recordsetclone maybe we need to use the underlying query on which the form is based.....?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If you need this much control over your user's movements, then don't use the builtin navigation buttons and instead build your own. Here's a small database that shows how to do this. The code in this database makes use of Form Properties and such to communicate between the navigation function and the "calling" form. If you need to move this sort of code into your database, then copy the basNavigation module to your database, and add the Properties found in fBooks to your form. You would then add buttons like are on fBooks to move around in your form.


NavButtons.zip
0
 
sxxguptaAuthor Commented:
ok.  Will try.  One item, is there a way I could capture error 2585 on the form's OnError event property that would solve this problem.  If I could, what would the code look like....Thanks again LSM.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You could, but you'd be no further along in your issue, since you could basically "throw away" the error, but you still wouldn't be able to close the form.

Access bound forms are great, but they can be somewhat annoying at times when you get outside their event models, as you're doing now. Using your own navigation methods allows you to trap events on YOUR terms and tends to make things easier, at least on that side of the coin. The downside is that you must handle all your own navigation, but it's pretty straight forward when you use routines as I've shown in the attachment.
0
 
sxxguptaAuthor Commented:
Thank you LSM!:)  Happy New Year.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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