Hi all,
I have an annoying problem:
I have a form (frmViewCMR) with a subForm (frmVCMR). This form is opened as a modal pop up when a button is pressed on another main tab form.
The subform on frmViewCMR is a continuous list of records with held date and due date.
I have an 'Add new CMR date' button that opens another modal pop up form for users to enter new details.
When a user clicks this button i have programmed a check to make sure the details from the last previously entered records are complete, and if not the user is prevented from adding the new record until the previous one is fully completed.
The code behind this is as follows:
-------------------------------------------------
Private Sub cmdACMR_Click()
Dim stDocName As String
Dim stLinkCriteria As String
Dim rsCMR As DAO.Recordset
Me.Refresh
Me.Requery
Me.frmVCMR.Requery
Forms!frmViewCMR!frmVCMR.Form.Refresh
Set rsCMR = Me!frmVCMR.Form.RecordsetClone
If rsCMR.EOF Or rsCMR.BOF Then
stDocName = "frmAddCMRD"
stLinkCriteria = "[SBNO]= " & Me.SBNO
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , "Awaiting 1st Review"
Else
rsCMR.MoveFirst
If rsCMR!confirmed = False Then
MsgBox "Before adding a new Care Management Review date you must provide a full next due date for the previous record." & Chr(13) & Chr(13) & _
"Please press the 'Change Selected Dates' button and update the next due year, month and day.", , "ERROR MESSGAGE BOX: Cannot add a new CMR date yet"
With Me.frmVCMR.Form
.RecordsetClone.FindFirst "CMRID = " & rsCMR!CMRID
If Not .RecordsetClone.NoMatch Then
.Bookmark = .RecordsetClone.Bookmark
End If
End With
Else
stDocName = "frmAddCMRD"
stLinkCriteria = "[SBNO]= " & Me.SBNO
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , Me!frmVCMR.Form!NextDate
End If
End If
End Sub
----------------------------------------------------------
The problem is that in some instances this works but in others it doesn't.
E.G. I open frmViewCMR for a client with no previous CMR records (the subform frmVCMR has no records) and click the 'add record' button which opens the add record pop up form with openargs of "Awaiting first review" - No problem.
I complete the new record entry (but do not supply a next due date - therefore 'confirmed' is set to false)and return to frmViewCMR which now has the record i just entered showing in the subform - No problem.
So now when i try and add a new record again i should get the message about completing the last entry and add new record screen should be prevented from opening. BUT i don't get the message, instead the add new record screen opens. Upon looking into this by debugging i find that code:
If rsCMR.EOF Or rsCMR.BOF Then
is returning EOF = true, BOF = true.
However if i close the form frmViewCMR, return to the main tabbed form, then reopen frmViewCMR, try and add a new record the code works as it should and will not let me add a new record until i complete the last one!!!!
So somewhere along the line the recordsetclone does not refresh properly.
Any suggestions?
Thanks.
I have an annoying problem:
I have a form (frmViewCMR) with a subForm (frmVCMR). This form is opened as a modal pop up when a button is pressed on another main tab form.
The subform on frmViewCMR is a continuous list of records with held date and due date.
I have an 'Add new CMR date' button that opens another modal pop up form for users to enter new details.
When a user clicks this button i have programmed a check to make sure the details from the last previously entered records are complete, and if not the user is prevented from adding the new record until the previous one is fully completed.
The code behind this is as follows:
-------------------------------------------------
Private Sub cmdACMR_Click()
Dim stDocName As String
Dim stLinkCriteria As String
Dim rsCMR As DAO.Recordset
Me.Refresh
Me.Requery
Me.frmVCMR.Requery
Forms!frmViewCMR!frmVCMR.Form.Refresh
Set rsCMR = Me!frmVCMR.Form.RecordsetClone
If rsCMR.EOF Or rsCMR.BOF Then
stDocName = "frmAddCMRD"
stLinkCriteria = "[SBNO]= " & Me.SBNO
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , "Awaiting 1st Review"
Else
rsCMR.MoveFirst
If rsCMR!confirmed = False Then
MsgBox "Before adding a new Care Management Review date you must provide a full next due date for the previous record." & Chr(13) & Chr(13) & _
"Please press the 'Change Selected Dates' button and update the next due year, month and day.", , "ERROR MESSGAGE BOX: Cannot add a new CMR date yet"
With Me.frmVCMR.Form
.RecordsetClone.FindFirst "CMRID = " & rsCMR!CMRID
If Not .RecordsetClone.NoMatch Then
.Bookmark = .RecordsetClone.Bookmark
End If
End With
Else
stDocName = "frmAddCMRD"
stLinkCriteria = "[SBNO]= " & Me.SBNO
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , Me!frmVCMR.Form!NextDate
End If
End If
End Sub
----------------------------------------------------------
The problem is that in some instances this works but in others it doesn't.
E.G. I open frmViewCMR for a client with no previous CMR records (the subform frmVCMR has no records) and click the 'add record' button which opens the add record pop up form with openargs of "Awaiting first review" - No problem.
I complete the new record entry (but do not supply a next due date - therefore 'confirmed' is set to false)and return to frmViewCMR which now has the record i just entered showing in the subform - No problem.
So now when i try and add a new record again i should get the message about completing the last entry and add new record screen should be prevented from opening. BUT i don't get the message, instead the add new record screen opens. Upon looking into this by debugging i find that code:
If rsCMR.EOF Or rsCMR.BOF Then
is returning EOF = true, BOF = true.
However if i close the form frmViewCMR, return to the main tabbed form, then reopen frmViewCMR, try and add a new record the code works as it should and will not let me add a new record until i complete the last one!!!!
So somewhere along the line the recordsetclone does not refresh properly.
Any suggestions?
Thanks.
