If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
Cancel still adds record
Notice that there is a cancel argument in the BeforeUpdate event. By
setting Cancel to true, you are telling Access that you do not want to save the record. The cancel response to the MsgBox does not do this. It only tells you which button the user pressed. "Tee See" wrote in message . .. A further question ... If the MsgBox=vbCancel why is the code "cancel=true" required? "Pat Hartman(MVP)" wrote in message ... The code you posted belongs in the form's BeforeUpdate event. Not in the events suggested by the others. The BeforeUpdate event is the ONLY place where you are certain to trap the record save since no matter what causes the record save, all roads lead to the BeforeUpdate event for dirty records. An even better solution for required fields is to set their Required property to Yes in the table definition. That way, no matter how the record gets added, Jet will not allow the record to be saved if the required fields are null. In your forms, you can trap the standard error message and replace it with your own if you prefer. If IsNull(Me![CustomerIDCode]) Then If MsgBox("'CustomerIDCode' must contain a value." _ & Chr(13) & Chr(10) _ & "Press 'OK' to return and enter a value." _ & Chr(13) & Chr(10) _ & "Press 'Cancel' to abort the record.", _ vbOKCancel, "A Required field is Null") = _ vbCancel Then Cancel = True Me.Undo End If End If "Tee See" wrote in message .. . On a popup form I have a button to close the form. If the form is open to ADD a record and the user changes his/her mind I'd like to close the form without adding a blank record. The following bit of code (I thought) would handle this but I still get a blank rcord added. The code by the way comes right from an example page within access help. If IsNull(Me![CustomerIDCode]) Then If MsgBox("'CustomerIDCode' must contain a value." _ & Chr(13) & Chr(10) _ & "Press 'OK' to return and enter a value." _ & Chr(13) & Chr(10) _ & "Press 'Cancel' to abort the record.", _ vbOKCancel, "A Required field is Null") = _ vbCancel Then DoCmd.Close End If End If Any advice sincerely appreciated |
#22
|
|||
|
|||
Cancel still adds record
I don't mean to be smart but how long can this "thread" go? I still haven't
solved this to my satisfaction but have created a "workaround" which, as a novice, I am quite happy with. May be not the best or most professional ...... I don't actually understand how these forums work. How long can I keep this thread running and if I came back in say a week or so would those who were involved in this discussion be notified (somehow) that "Hey" here he is again or what?. If in a week or so should I start another question on the same topic? Because the workaround seems to work for me I am off to the next wee problem and will post that tonight. In the mean time THANKS to all who have contributed to this and rite or rong, judging from the response this was an interesting question. Best Regards to all "Pat Hartman(MVP)" wrote in message ... Notice that there is a cancel argument in the BeforeUpdate event. By setting Cancel to true, you are telling Access that you do not want to save the record. The cancel response to the MsgBox does not do this. It only tells you which button the user pressed. "Tee See" wrote in message . .. A further question ... If the MsgBox=vbCancel why is the code "cancel=true" required? "Pat Hartman(MVP)" wrote in message ... The code you posted belongs in the form's BeforeUpdate event. Not in the events suggested by the others. The BeforeUpdate event is the ONLY place where you are certain to trap the record save since no matter what causes the record save, all roads lead to the BeforeUpdate event for dirty records. An even better solution for required fields is to set their Required property to Yes in the table definition. That way, no matter how the record gets added, Jet will not allow the record to be saved if the required fields are null. In your forms, you can trap the standard error message and replace it with your own if you prefer. If IsNull(Me![CustomerIDCode]) Then If MsgBox("'CustomerIDCode' must contain a value." _ & Chr(13) & Chr(10) _ & "Press 'OK' to return and enter a value." _ & Chr(13) & Chr(10) _ & "Press 'Cancel' to abort the record.", _ vbOKCancel, "A Required field is Null") = _ vbCancel Then Cancel = True Me.Undo End If End If "Tee See" wrote in message .. . On a popup form I have a button to close the form. If the form is open to ADD a record and the user changes his/her mind I'd like to close the form without adding a blank record. The following bit of code (I thought) would handle this but I still get a blank rcord added. The code by the way comes right from an example page within access help. If IsNull(Me![CustomerIDCode]) Then If MsgBox("'CustomerIDCode' must contain a value." _ & Chr(13) & Chr(10) _ & "Press 'OK' to return and enter a value." _ & Chr(13) & Chr(10) _ & "Press 'Cancel' to abort the record.", _ vbOKCancel, "A Required field is Null") = _ vbCancel Then DoCmd.Close End If End If Any advice sincerely appreciated |
#23
|
|||
|
|||
Cancel still adds record
Bruce .... Appreciate your input and I think you suggested that I "DEFINE"
my database. What information, precisely do need ina definition of a database" "BruceM" wrote in message ... There needs to be a space before the underscore. For the other part of your question, is the form in question frmMaterialMasterMain? If so, try setting the focus to SISItemCode before Cancel = True. Also, if it is a control on the form you should be able to set focus with Me.SISItemCode.SetFocus. When I mentioned using vbOK and Cancel = True it was not so much a suggestion as an illustration of another approach. You could stick with your original approach of: If ... = vbCancel Then Me.Undo DoCmd.Close Else Me.SISItemCode.SetFocus End If That would eliminate the Cancel = True line of code. When confronted with such choices I tend to go with the one that involves less code, not that Cancel = True is a big deal. Either way should work. I'm not sure why your form closes no matter what you do. If SISItemCode is on another form, you have added a new wrinkle, and I don't know what you intend. In any case you would do well when asking a question in this forum to define forms and controls. People reading your postings do not see your database, and must rely only on what you tell us. I have another unsolicited suggestion. I find error handling to be much more helpful if I use something like the following after Err_Command2_Click: msgbox "Error #" & Err.Number & ", " & Err.Description & " - Command20" It gives you more to work with when an error occurs. You haven't mentioned an getting an error message, so this is just something else I'm tossing in here. I expect the reason you cannot close the form is the SetFocus command after the If statement. Cancel = True cancels the rest of the event, but in the case of clicking Cancel in the message box Access does not encounter Cancel = True, and therefore continues with the rest of the code, which is to set focus. That may be overriding the Close command. If setting focus is supposed to accompany clicking OK, it should be part of the Then section of the If statement. One other thought is that there could be a default value or something that prevents CustomerIDCode from being null. To test you could add a temporary message box right after the If IsNull line: msgbox "ID Is Null" If leaving the field blank doesn't produce that message box, that would explain why the next If statement always runs. You may need to use: If Me.CustomerIDCode = "" I am, as I think I have mentioned, relatively new at this stuff. Answering questions gets me to thinking about things in my own projects, so I am always glad when my suggestions can prove useful. If what I have suggested doesn't work, post back. I'm learning too, and would like to know what works and what doesn't. "Tee See" wrote in message news Bet you wish I had finished with this ..... One last piece of advice please. I'v changed the code based on all the previous discussions as below. Now I can't even get the syntax correct in defining the strMsg. I can do it when I type one long continuous line but can't get it when I use "_&". Also the OK button on the message box will not allow entry into the form it just closes and when I do add data to the form the button will not close the form. Here's the code. Private Sub Command20_Click() On Error GoTo Err_Command20_Click Dim strMsg As String strMsg = "'CustomerIDCode' must contain a value."_ & Chr(13)& Chr(10)_ & "Press 'OK' to return and enter a value."_ & Chr(13) & Chr(10)_ & "Press 'Cancel' to abort." If IsNull(Me![CustomerIDCode]) Then If MsgBox(strMsg, vbOKCancel, "A Required Field is Null") = vbOK Then Cancel = True Else Me.Undo DoCmd.Close End If End If Forms!frmMaterialMasterMain!SISItemCode.SetFocus Exit_Command20_Click: Exit Sub Err_Command20_Click: MsgBox Err.Description Resume Exit_Command20_Click "BruceM" wrote in message ... MS Access Help can be a bit fragmented sometimes. There may be a code example, for instance, but with no explanation close at hand about the syntax. I regard Help as one resource among many, and not necessarily the best one in all cases. Much of what I have learned has come from reading messages here, and following links that are in some of them. Good luck with your project. "Tee See" wrote in message .. . Bruce ... You're "Just as a thought" issue makes alot of sense to me in spite of the fact that the code I submitted came from MS access HELP "BruceM" wrote in message ... While I am nowhere near being a guru, I beieve that the DoMenuItem line of code you have provided can be replaced with: Me.Undo except that I think it needs to be added within the If statement, just before DoCmd.Close. If it is placed after the If statement then the code will run, and the record will be undone, no matter whether the user selects OK or Cancel. I think you could avoid that by using vbOK instead of vbCancel, and adding Cancel = True: If IsNull(Me![CustomerIDCode]) Then If MsgBox("'CustomerIDCode' must contain a value." _ & Chr(13) & Chr(10) _ & "Press 'OK' to return and enter a value." _ & Chr(13) & Chr(10) _ & "Press 'Cancel' to abort the record.", _ vbOKCancel, "A Required field is Null") = _ vbOK Then Cancel = True Else Me.Undo DoCmd.Close End If End If Just as a thought, I find it easier to work with the code if I am not dealing with a long string in the middle of an If statement or something like that. I like to start with: dim strMsg as String strMsg = "CustomerIDCode must contain a value...etc." Then in the msgbox line of code: If msgbox strMsg = vbCancel Then etc. "Gina Whipp" wrote in message ... All it is Un-Do. I had the same problem you did. I realized that the record is created as soon as the form to add new is opened. So I was trying to cancel a record that in a sense was already added and it couldn't. So I had to Un-Do to get rid of the new record and then cancel to cancel the event. I finally figured it out by reading about how and when form events are triggered. It probably could be better explained by one of the gurus but I hope that helps. Gina "Tee See" wrote in message .. . Thanks Gina that seems to work .... Would you be able to explain briefly what that line of code is about? Greatly appreciate the help. "Gina Whipp" wrote in message ... Try adding: DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70 one space downs after the last End If HTH, Gina "Ed Robichaud" wrote in message ... Looks like it should work. This code is fired on the form's BeforeUpdate event? And CustomerID is not an auto-number with one or more other fields defaulted? -Ed "Tee See" wrote in message .. . On a popup form I have a button to close the form. If the form is open to ADD a record and the user changes his/her mind I'd like to close the form without adding a blank record. The following bit of code (I thought) would handle this but I still get a blank rcord added. The code by the way comes right from an example page within access help. If IsNull(Me![CustomerIDCode]) Then If MsgBox("'CustomerIDCode' must contain a value." _ & Chr(13) & Chr(10) _ & "Press 'OK' to return and enter a value." _ & Chr(13) & Chr(10) _ & "Press 'Cancel' to abort the record.", _ vbOKCancel, "A Required field is Null") = _ vbCancel Then DoCmd.Close End If End If Any advice sincerely appreciated |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
"Next Record If", Mail Merge Issues | [email protected] | Mailmerge | 8 | February 8th, 2006 07:11 PM |
Append blank rows and first row to 11 | [email protected] | Running & Setting Up Queries | 1 | January 17th, 2006 01:59 PM |
User Saving a Record else it deletes | Carter Wexler | Using Forms | 1 | August 2nd, 2005 11:57 PM |
Access Mail Merge to Word.doc files ? | RNUSZ@OKDPS | Setting Up & Running Reports | 1 | May 18th, 2005 06:31 PM |
Need Help In Printing Current Record in Specific Report | RNUSZ@OKDPS | Setting Up & Running Reports | 1 | May 16th, 2005 09:06 PM |