Full Version: OnClick Procedure Changing Records
UtterAccess Forums > Microsoft® Access > Access Forms
quest4
I have a form, with a couple of cmdButtons on it which open other forms ove the main original form, because we need to see the data on the main or original form. I have a little procedure which I run on all three of the form which open by cmdButtons on the main form. It worked, for the first test record and all was well in the world. Then I added a couple more records and I discovered something very strange happening, when I click the cmdClose button. Here is the code:
Dim strSQL As String
Dim strApprovalType As String
If Me!MRComplete = True And Me!MREMailed = False Then
Select Case Forms!frmQualityControlAction.QCTypes
Case 100
strSQL = "SELECT [EMail] FROM tblAuthorizedUsers WHERE [ACForm] = True"
strApprovalType = "Credit"
Call EMailUsersNotices(strSQL, strApprovalType)
Case 400
strSQL = "SELECT [EMail] FROM tblAuthorizedUsers WHERE [PDForm] = True"
strApprovalType = "Problem Definition"
Call EMailUsersNotices(strSQL, strApprovalType)
Case Else
GoTo Check_Complete
End Select
Set rst = Me.RecordsetClone
rst.MoveFirst
Do
If rst.Fields("MRComplete") = True And rst.Fields("MREmailed") = False Then
With rst
.Edit
.Fields("MREmailed") = True
.Update
End With
End If
rst.MoveNext
Loop While Not (rst.EOF)
rst.Close
Set rst = Nothing
End If
Check_Complete:
Dim intCountTrue As Integer
Dim intCountAll As Integer
Dim iItemsOpen As Integer
Forms!frmMaterialReturn.RecordsetType = conDynaset
DoCmd.RunCommand acCmdSaveRecord
intCountAll = Me.RecordsetClone.RecordCount
'MsgBox intCountAll
Me.Recalc
intCountTrue = Me.txtTrue
'MsgBox intCountTrue

iItemsOpen = intCountAll - intCountTrue
If iItemsOpen = 0 Then
DoCmd.Close ACForm, "frmMaterialReturn"
Else
MsgBox "There Are Still " & iItemsOpen & " Open Item(s) in This QCA."
DoCmd.Close ACForm, "frmMaterialReturn"
End If
When I click the cmdClose, if the the MRComplete chkbox is checked it and the QCType is 100 or 400 it should E-Mail a notice and check chkbox MEEMailed, Then it should could the number of items in the QCANo and the number of checked MRComplete and if everything is complet then close, else flash a message and then close. What is happening nowe is when I click the cmdClose, instead of QCANo 4 which I opened, it changes to QCANo 1 and then runs the procedure. Can anyone tell me why this is happening? Thank you inadvance to anyone who can point out what is wrong with this mess. giveup.gif giveup.gif giveup.gif
ChrisO
I don’t know but we must start somewhere.
If the problem is that the wrong form is closing then there must be code running that closes it.
The only code, I can see, that closes any form are these two instances: -
CODE
iItemsOpen = intCountAll - intCountTrue
If iItemsOpen = 0 Then
    DoCmd.Close acForm, "frmMaterialReturn"
Else
    MsgBox "There Are Still " & iItemsOpen & " Open Item(s) in This QCA."
    DoCmd.Close acForm, "frmMaterialReturn"
End If
  
That code seems to equate to this: -
  
iItemsOpen = intCountAll - intCountTrue
If iItemsOpen <> 0 Then
    MsgBox "There Are Still " & iItemsOpen & " Open Item(s) in This QCA."
End If
    
DoCmd.Close acForm, "frmMaterialReturn"
If the wrong form is being closed then it looks like it is being closed elsewhere.
The only other place it could be closed seems to be in Call EMailUsersNotices(strSQL, strApprovalType).
Therefore, comment out those two lines and see how it behaves.
May I please add?
There still seems to be a lot of redundant code here.
Set rst = Me.RecordsetClone. Why create a recordset of the RecordsetClone?
Odon’t know what this means: -
Forms!frmMaterialReturn.RecordsetType = conDynaset
DoCmd.RunCommand acCmdSaveRecord
intCountAll = Me.RecordsetClone.RecordCount
Where is the code running?
If running locally to the RecordsetClone then
Me.Dirty = False
Should do the trick.
Me.Recalc
Seems to be happening after the record is saved.
The Select Case structure carries some redundant code.
Since the Case Else has a GoTo Check_Complete it jumps over everything between End Select and Check_Complete:
(I’m not a ‘purist’ when it comes to using a GoTo instruction. It equates to the Jump instruction at machine code level and BASIC was designed that way for a very good reason. However it can almost, not always, be done away with and it can also make the code hard to read if used excessively. I say ‘almost be done away with’ because I have found it difficult to write an On Error GoTo without a GoTo.
The ‘purist’ would say never use a GoTo but always have error trapping in every procedure.
Seems to me that the ‘purist’ violates everything they write. Which of course makes them ‘purist’. wary.gif )
In any case the Select Case structure can be re-written without the redundancy.
Anyhow, I can’t test your code and therefore that’s my best shot.
Regards,
Chris.
quest4
Thank you for the assistance, Chris. Let me first try and make things clearer. The correct form is closing, what is happening, when I click cmdClose, even I am working on QCANo 4, it changes to QCANo 1and all of the parts change also. It suddenly is like I opened QCANo 1 instead of QCANo 4. It is like Access suddenly got amnesia. On QCANo 1 everything works correctly. As for what this is supposed to be doing, the top, the case statement is for the e-mailing of a notice to approprate users, there is no attachment or text body. The second part, just below the case statementis the check-off the MREMailed chkbox, so it will not e-mail a notice next time someone opens and closes the form. The last section isto see if all of the MRComplete chkboxes have been checked, which means the QCANo is complete on this form. Then it closes the form. Does this clearify things a little? I will try a few things here. If you have any ideas on why I am loosing the record, please let me know. Thank you again for the assistance. giveup.gif confused.gif
quest4
I hope this helps. The main form is frmQualityControlAction and the trouble making form is Material Return. Open frmQualityControlAction click NO enter QCANo 4 and the form should open to that QCANo. Click thecmdButton for Material Return. Check and uncheck the MRComplete chkbox then cross your fingers and click the cmdClose button. Watch carefully at what is happening. You will now understand my confusion, the records change, from QCANo 4 to QCANo 1. WHY???? Thanks again for the assistance. giveup.gif giveup.gif giveup.gif
quest4
Update. What ever is sending this thing nutsie-kook-kook, is happening in the second section, I believe. I just changed a QCType to 400 and when intothe Material Return an checked the MRComplete chkbox and clicked cmdClose. It sent me my e-mail notice and then the QCANo 1 show its' ugly head. WHY?? How do I cure it?? giveup.gif cryhard.gif giveup.gif
quest4
OK I added a Requery just after the EndWith clause. That section appears to have started to work correctly, now. The last section, Checked_Complete, is not so easy. Requery did nothing there. These are two completely different checks and both are need, each works well by themselves, so why can't they work together? The first section starts the problem, but the Requery manages to get it back and the third section is completely lost in the world. I will be enterially grateful to anyone who can show me how to get this working correctly. confused.gif confused.gif confused.gif
quest4
Forget Requery, doesn't always work.
ChrisO
Would this beast convert back to A97?
ight be time for the posting of just the minimum fault.
I’ll be a bit busy today but others may cast an eye over it.
Regards,
Chris.
quest4
Thanks again for the response, under stand you are busy. I won't hold my breath for someone else to look at this. I thought the gurus would but they are staying far a way. At least I got it pinned down to what is causing this, the e-mail function, I think. Well it is Miller time here, and I have pulled out half of my hair with this, already. I have more commented out lines now than you could ever shack a stick at. Have a good one, and thank you for the assistance that you have given me. giveup.gif confused.gif giveup.gif
quest4
Alright, I have isolated the problem. I hope someone can point out the cure. The problem is being caused by the Check_Complete section. The one hundred dollar question, how can I get this section of the procedure to stop bring up QCANo 1? I use this same procedre in two other forms and I am sure it is going to screw-up also. I would deeply appreciate anyone pointing out what I need to change. shrug.gif
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.