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 |
#11
|
|||
|
|||
Required Fields on Form
snipI'd rather it give the message for the first
unpopulated field go back to the form to populate it, then when they click save and close it gives the next unpopulated field, if anysnip Yep, me too but I haven't figured out how to do that yet. Might have to make a separate post. On the positive side, at least they know what is required and the odds of somebody leaving all of that data should be pretty slim. Users become trained as to what is expected. When does the No Current Record message show up? After the error messages when you move to another record or after the error messages after you have pushed your close button? To cancel and close the form, create a CANCEL button. Your code would be: DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70 DoCmd.Close Access does not create a new record until the user types data in. Therefore, if the form is left completely blank, no record is created so it does not need to check for invalid data. -- Thanks for taking the time! CJ --------------------------------------------------------- Know thyself, know thy limits....know thy newsgroups! "Whitney" wrote in message ... Thanks CJ for the clarification. I'm almost there, but have a few more issues. I added the code to the form_BeforeUpdate, so now if they have only populated one of the four required fields they get a message for each unpopulated field before going back to the form. I don't really like that, but willing to accept it. I'd rather it give the message for the first unpopulated field go back to the form to populate it, then when they click save and close it gives the next unpopulated field, if any. Also when all of the messages pop up, the last message is always No Current Record. I'm not sure why this is happening. My last issue, is that if the user populated one field, but then decides to cancel the form, it also gives the error messages that fields are required. How can I prevent this and just close the form? If no fields are populated it closes fine. Also if the user does not populate any fields and clicks save and close it closes the form without the error messages, this should be opposite. "CJ" wrote: First thing, the difference between fields and records: A field is each piece of data that you complete for each record. So, if you have data for Charlie Brown: Charlie would be in the first name Field, Brown would be in the last name Field. All of his data together is Charlie Brown's Record. This is important because you need to understand when Access events run. If you want the validation to only happen when the use tries to either move to the next record (Caller) or save and close the form, then you MUST have the code in the FORMS Before Update event NOT the FIELDS Before Update. Having the code in the fields event would produce the error message when the user tries to move to the next field...this is not what you want. To make sure you are putting the code in the correct event, make sure you select Form from the dropdown box at the very top of the Properties box. Also, I actually built this to make sure that it works for you, instead of trying to wing it....=D Feel free to copy and paste..... The code for your Form Before Update event should be: If IsNull(Me.Caller) Then DoCmd.CancelEvent MsgBox "Please fill in Caller's Name." Me.Caller.SetFocus End If If IsNull(Me.Date) Then DoCmd.CancelEvent MsgBox "Please fill in Date." Me.Date.SetFocus End If If IsNull(Me.Reason) Then DoCmd.CancelEvent MsgBox "Please fill in reason for call." Me.Reason.SetFocus End If If IsNull(Me.Submitter) Then DoCmd.CancelEvent MsgBox "Please fill in your name." Me.Submitter.SetFocus End If The code for your Close button should be: On Error GoTo Err_Save_Click DoCmd.Close ' You do not need to tell Access to save the data. ' It is saved automatically when the form closes Exit_Save_Click: Exit Sub Err_Save_Click: MsgBox "Unable to Save and Close" Resume Exit_Save_Click -- HTH CJ --------------------------------------------------------- Know thyself, know thy limits....know thy newsgroups! "Whitney" wrote in message news Ok I figured out the reason I wasn't getting the error message from the required table fields validation rule. I had the test fe file still referrerencing the original be file instead of the test be file where I made the table changes. Silly me. So now I'm getting the error message, which I understand but is too complicated for a user to understand. So I'm still using the vb code in order to provide a more straight forward error message, however it's not working. I wasn't sure where to put your code, as I think it might be better on click for the save button instead of checking as they move between fields. I want to give them the flexibility to populate the fields in any order without the error messages, but to validate when they click save and close. So I added the code on click, before the save and close command, see below. However I get an error message Run-time error '438': Object doesn't support this property or method. When I click Debug it points to the Cancel = True line of the IF Is Null (Me. Caller) code. Not sure what I'm doing wrong. Private Sub Save_Click() If IsNull(Me.Caller) Then Cancel = True MsgBox "Please fill in Caller's Name." Me.Caller.SetFocus End If If IsNull(Me.Date) Then Cancel = True MsgBox "Please fill in Date." Me.Date.SetFocus End If If IsNull(Me.Reason) Then Cancel = True MsgBox "Please fill in reason for call." Me.Reason.SetFocus End If If IsNull(Me.Submitter) Then Cancel = True MsgBox "Please fill in your name." Me.Submitter.SetFocus End If On Error GoTo Err_Save_Click DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 DoCmd.Close Exit_Save_Click: Exit Sub Err_Save_Click: MsgBox Err.Description Resume Exit_Save_Click End Sub "CJ" wrote: Hi Whitney If you made the fields required in the table you should definitely see an error message if the field is left blank on your form. Try a test: Make all of the fields required in your table that need to be required and then try just navigating to a different record. Don't use your button. Unless you have your warnings turned off via code you should receive an error message. If you do not receive a message, you need to check in the code for DoCmd.SetWarnings False If some code requires the warnings to be turned off then you need to make sure that it gets turned back on with DoCmd.SetWarnings True after the code has executed. I should also have mentioned that you do need to keep the code in the forms BeforeUpdate event. BeforeUpdate checks before the record is saved and you move between records, OnClick checks when you push the button. For both events, your code would be something like: If IsNull(Me.Caller) Then Cancel = True MsgBox "Please fill in Caller's Name." Me.Caller.SetFocus End If If IsNull(Me.Phone) Then Cancel = True MsgBox "Please fill in Caller's Phone Number." Me.Phone.SetFocus End If If IsNull(Me.Address) Then etc, etc.... -- HTH CJ --------------------------------------------------------- Know thyself, know thy limits....know thy newsgroups! "Whitney" wrote in message ... Maybe that's where I'm going wrong. I have 5 fields and all 5 are required. When the user hits Save and Close, I need it to then validate that all 5 fields are populated before Saving and Closing the form. If they are not, I would like a message to appear that indicates which field is not populated and take the cursor to that field. Then to repeat this for every field that is not populated each time the user clicks Save and Close. Ultimately the form will not write any data to the table or close the form until all fields are populated. I also tried making the fields required in the table, but didn't see any difference when clicking Save and Close on the form. It might also be helpful to know that the tables are stored on a be file and the forms are on an fe file. I made the fields required on the be tables, but this did not change the user form experience. "CJ" wrote: Hi Sprinks Not that I am a code pro or anything but I would have written it as: If IsNull(Me.Caller) Then Cancel = True MsgBox "Please fill in Caller's Name." Me.Caller.SetFocus End If Also, I would put it into the OnClick event of your button before any DoCmd statements. The way that you have it written suggests that it would execute when you move from one record to the next but not when you save and close. -- HTH CJ --------------------------------------------------------- Know thyself, know thy limits....know thy newsgroups! "Whitney" wrote in message ... I tried to use this in the BeforeUpdate event, but it's not working. On my form after all information is updated the user hits the Save and Close button. Then the form closes, but no error pops up. Help! If IsNull(Me!Caller) Then MsgBox "Please fill in Caller's Name." Cancel = True Me!Caller.SetFocus End If "Sprinks" wrote: For this job, use the *form's* BeforeUpdate event. For each required control, use code similar to: If IsNull(Me!YourRequiredControl) Then MsgBox "Please fill in YourRequiredControl" Cancel = True Me!YourRequiredControl.SetFocus End If HTH Sprinks "Raj" wrote: I need help with code that will verify if certain fields have been filled in. I dont want to use field required at the table level. I want to use BeforeUpdate can someone please help me with the coding.. Thanks!!! |
#12
|
|||
|
|||
Required Fields on Form
I get the No Current Record as the last error message in the sequence of
error messages. So if I populate all fields except one, I get the error message for that field, click ok and then get another message that says "No Current Record", then click Ok and then it takes me back to the form. Also I do have a cancel button, when the user populates at least one field and then decides to cancel they get all of the error messages for the remaining unpopulated fields. They then have to click ok to each message and then the form closes, when it should just ignore the validation code and close the form. "CJ" wrote: snipI'd rather it give the message for the first unpopulated field go back to the form to populate it, then when they click save and close it gives the next unpopulated field, if anysnip Yep, me too but I haven't figured out how to do that yet. Might have to make a separate post. On the positive side, at least they know what is required and the odds of somebody leaving all of that data should be pretty slim. Users become trained as to what is expected. When does the No Current Record message show up? After the error messages when you move to another record or after the error messages after you have pushed your close button? To cancel and close the form, create a CANCEL button. Your code would be: DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70 DoCmd.Close Access does not create a new record until the user types data in. Therefore, if the form is left completely blank, no record is created so it does not need to check for invalid data. -- Thanks for taking the time! CJ --------------------------------------------------------- Know thyself, know thy limits....know thy newsgroups! "Whitney" wrote in message ... Thanks CJ for the clarification. I'm almost there, but have a few more issues. I added the code to the form_BeforeUpdate, so now if they have only populated one of the four required fields they get a message for each unpopulated field before going back to the form. I don't really like that, but willing to accept it. I'd rather it give the message for the first unpopulated field go back to the form to populate it, then when they click save and close it gives the next unpopulated field, if any. Also when all of the messages pop up, the last message is always No Current Record. I'm not sure why this is happening. My last issue, is that if the user populated one field, but then decides to cancel the form, it also gives the error messages that fields are required. How can I prevent this and just close the form? If no fields are populated it closes fine. Also if the user does not populate any fields and clicks save and close it closes the form without the error messages, this should be opposite. "CJ" wrote: First thing, the difference between fields and records: A field is each piece of data that you complete for each record. So, if you have data for Charlie Brown: Charlie would be in the first name Field, Brown would be in the last name Field. All of his data together is Charlie Brown's Record. This is important because you need to understand when Access events run. If you want the validation to only happen when the use tries to either move to the next record (Caller) or save and close the form, then you MUST have the code in the FORMS Before Update event NOT the FIELDS Before Update. Having the code in the fields event would produce the error message when the user tries to move to the next field...this is not what you want. To make sure you are putting the code in the correct event, make sure you select Form from the dropdown box at the very top of the Properties box. Also, I actually built this to make sure that it works for you, instead of trying to wing it....=D Feel free to copy and paste..... The code for your Form Before Update event should be: If IsNull(Me.Caller) Then DoCmd.CancelEvent MsgBox "Please fill in Caller's Name." Me.Caller.SetFocus End If If IsNull(Me.Date) Then DoCmd.CancelEvent MsgBox "Please fill in Date." Me.Date.SetFocus End If If IsNull(Me.Reason) Then DoCmd.CancelEvent MsgBox "Please fill in reason for call." Me.Reason.SetFocus End If If IsNull(Me.Submitter) Then DoCmd.CancelEvent MsgBox "Please fill in your name." Me.Submitter.SetFocus End If The code for your Close button should be: On Error GoTo Err_Save_Click DoCmd.Close ' You do not need to tell Access to save the data. ' It is saved automatically when the form closes Exit_Save_Click: Exit Sub Err_Save_Click: MsgBox "Unable to Save and Close" Resume Exit_Save_Click -- HTH CJ --------------------------------------------------------- Know thyself, know thy limits....know thy newsgroups! "Whitney" wrote in message news Ok I figured out the reason I wasn't getting the error message from the required table fields validation rule. I had the test fe file still referrerencing the original be file instead of the test be file where I made the table changes. Silly me. So now I'm getting the error message, which I understand but is too complicated for a user to understand. So I'm still using the vb code in order to provide a more straight forward error message, however it's not working. I wasn't sure where to put your code, as I think it might be better on click for the save button instead of checking as they move between fields. I want to give them the flexibility to populate the fields in any order without the error messages, but to validate when they click save and close. So I added the code on click, before the save and close command, see below. However I get an error message Run-time error '438': Object doesn't support this property or method. When I click Debug it points to the Cancel = True line of the IF Is Null (Me. Caller) code. Not sure what I'm doing wrong. Private Sub Save_Click() If IsNull(Me.Caller) Then Cancel = True MsgBox "Please fill in Caller's Name." Me.Caller.SetFocus End If If IsNull(Me.Date) Then Cancel = True MsgBox "Please fill in Date." Me.Date.SetFocus End If If IsNull(Me.Reason) Then Cancel = True MsgBox "Please fill in reason for call." Me.Reason.SetFocus End If If IsNull(Me.Submitter) Then Cancel = True MsgBox "Please fill in your name." Me.Submitter.SetFocus End If On Error GoTo Err_Save_Click DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 DoCmd.Close Exit_Save_Click: Exit Sub Err_Save_Click: MsgBox Err.Description Resume Exit_Save_Click End Sub "CJ" wrote: Hi Whitney If you made the fields required in the table you should definitely see an error message if the field is left blank on your form. Try a test: Make all of the fields required in your table that need to be required and then try just navigating to a different record. Don't use your button. Unless you have your warnings turned off via code you should receive an error message. If you do not receive a message, you need to check in the code for DoCmd.SetWarnings False If some code requires the warnings to be turned off then you need to make sure that it gets turned back on with DoCmd.SetWarnings True after the code has executed. I should also have mentioned that you do need to keep the code in the forms BeforeUpdate event. BeforeUpdate checks before the record is saved and you move between records, OnClick checks when you push the button. For both events, your code would be something like: If IsNull(Me.Caller) Then Cancel = True MsgBox "Please fill in Caller's Name." Me.Caller.SetFocus End If If IsNull(Me.Phone) Then Cancel = True MsgBox "Please fill in Caller's Phone Number." Me.Phone.SetFocus End If If IsNull(Me.Address) Then etc, etc.... -- HTH CJ --------------------------------------------------------- Know thyself, know thy limits....know thy newsgroups! "Whitney" wrote in message ... Maybe that's where I'm going wrong. I have 5 fields and all 5 are required. When the user hits Save and Close, I need it to then validate that all 5 fields are populated before Saving and Closing the form. If they are not, I would like a message to appear that indicates which field is not populated and take the cursor to that field. Then to repeat this for every field that is not populated each time the user clicks Save and Close. Ultimately the form will not write any data to the table or close the form until all fields are populated. I also tried making the fields required in the table, but didn't see any difference when clicking Save and Close on the form. It might also be helpful to know that the tables are stored on a be file and the forms are on an fe file. I made the fields required on the be tables, but this did not change the user form experience. "CJ" wrote: Hi Sprinks Not that I am a code pro or anything but I would have written it as: If IsNull(Me.Caller) Then Cancel = True MsgBox "Please fill in Caller's Name." Me.Caller.SetFocus End If |
#13
|
|||
|
|||
Required Fields on Form
Hi
What is the code for your Cancel button? -- Thanks for taking the time! CJ --------------------------------------------------------- Know thyself, know thy limits....know thy newsgroups! "Whitney" wrote in message ... I get the No Current Record as the last error message in the sequence of error messages. So if I populate all fields except one, I get the error message for that field, click ok and then get another message that says "No Current Record", then click Ok and then it takes me back to the form. Also I do have a cancel button, when the user populates at least one field and then decides to cancel they get all of the error messages for the remaining unpopulated fields. They then have to click ok to each message and then the form closes, when it should just ignore the validation code and close the form. "CJ" wrote: snipI'd rather it give the message for the first unpopulated field go back to the form to populate it, then when they click save and close it gives the next unpopulated field, if anysnip Yep, me too but I haven't figured out how to do that yet. Might have to make a separate post. On the positive side, at least they know what is required and the odds of somebody leaving all of that data should be pretty slim. Users become trained as to what is expected. When does the No Current Record message show up? After the error messages when you move to another record or after the error messages after you have pushed your close button? To cancel and close the form, create a CANCEL button. Your code would be: DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70 DoCmd.Close Access does not create a new record until the user types data in. Therefore, if the form is left completely blank, no record is created so it does not need to check for invalid data. -- Thanks for taking the time! CJ --------------------------------------------------------- Know thyself, know thy limits....know thy newsgroups! "Whitney" wrote in message ... Thanks CJ for the clarification. I'm almost there, but have a few more issues. I added the code to the form_BeforeUpdate, so now if they have only populated one of the four required fields they get a message for each unpopulated field before going back to the form. I don't really like that, but willing to accept it. I'd rather it give the message for the first unpopulated field go back to the form to populate it, then when they click save and close it gives the next unpopulated field, if any. Also when all of the messages pop up, the last message is always No Current Record. I'm not sure why this is happening. My last issue, is that if the user populated one field, but then decides to cancel the form, it also gives the error messages that fields are required. How can I prevent this and just close the form? If no fields are populated it closes fine. Also if the user does not populate any fields and clicks save and close it closes the form without the error messages, this should be opposite. "CJ" wrote: First thing, the difference between fields and records: A field is each piece of data that you complete for each record. So, if you have data for Charlie Brown: Charlie would be in the first name Field, Brown would be in the last name Field. All of his data together is Charlie Brown's Record. This is important because you need to understand when Access events run. If you want the validation to only happen when the use tries to either move to the next record (Caller) or save and close the form, then you MUST have the code in the FORMS Before Update event NOT the FIELDS Before Update. Having the code in the fields event would produce the error message when the user tries to move to the next field...this is not what you want. To make sure you are putting the code in the correct event, make sure you select Form from the dropdown box at the very top of the Properties box. Also, I actually built this to make sure that it works for you, instead of trying to wing it....=D Feel free to copy and paste..... The code for your Form Before Update event should be: If IsNull(Me.Caller) Then DoCmd.CancelEvent MsgBox "Please fill in Caller's Name." Me.Caller.SetFocus End If If IsNull(Me.Date) Then DoCmd.CancelEvent MsgBox "Please fill in Date." Me.Date.SetFocus End If If IsNull(Me.Reason) Then DoCmd.CancelEvent MsgBox "Please fill in reason for call." Me.Reason.SetFocus End If If IsNull(Me.Submitter) Then DoCmd.CancelEvent MsgBox "Please fill in your name." Me.Submitter.SetFocus End If The code for your Close button should be: On Error GoTo Err_Save_Click DoCmd.Close ' You do not need to tell Access to save the data. ' It is saved automatically when the form closes Exit_Save_Click: Exit Sub Err_Save_Click: MsgBox "Unable to Save and Close" Resume Exit_Save_Click -- HTH CJ --------------------------------------------------------- Know thyself, know thy limits....know thy newsgroups! "Whitney" wrote in message news Ok I figured out the reason I wasn't getting the error message from the required table fields validation rule. I had the test fe file still referrerencing the original be file instead of the test be file where I made the table changes. Silly me. So now I'm getting the error message, which I understand but is too complicated for a user to understand. So I'm still using the vb code in order to provide a more straight forward error message, however it's not working. I wasn't sure where to put your code, as I think it might be better on click for the save button instead of checking as they move between fields. I want to give them the flexibility to populate the fields in any order without the error messages, but to validate when they click save and close. So I added the code on click, before the save and close command, see below. However I get an error message Run-time error '438': Object doesn't support this property or method. When I click Debug it points to the Cancel = True line of the IF Is Null (Me. Caller) code. Not sure what I'm doing wrong. Private Sub Save_Click() If IsNull(Me.Caller) Then Cancel = True MsgBox "Please fill in Caller's Name." Me.Caller.SetFocus End If If IsNull(Me.Date) Then Cancel = True MsgBox "Please fill in Date." Me.Date.SetFocus End If If IsNull(Me.Reason) Then Cancel = True MsgBox "Please fill in reason for call." Me.Reason.SetFocus End If If IsNull(Me.Submitter) Then Cancel = True MsgBox "Please fill in your name." Me.Submitter.SetFocus End If On Error GoTo Err_Save_Click DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 DoCmd.Close Exit_Save_Click: Exit Sub Err_Save_Click: MsgBox Err.Description Resume Exit_Save_Click End Sub "CJ" wrote: Hi Whitney If you made the fields required in the table you should definitely see an error message if the field is left blank on your form. Try a test: Make all of the fields required in your table that need to be required and then try just navigating to a different record. Don't use your button. Unless you have your warnings turned off via code you should receive an error message. If you do not receive a message, you need to check in the code for DoCmd.SetWarnings False If some code requires the warnings to be turned off then you need to make sure that it gets turned back on with DoCmd.SetWarnings True after the code has executed. I should also have mentioned that you do need to keep the code in the forms BeforeUpdate event. BeforeUpdate checks before the record is saved and you move between records, OnClick checks when you push the button. For both events, your code would be something like: If IsNull(Me.Caller) Then Cancel = True MsgBox "Please fill in Caller's Name." Me.Caller.SetFocus End If If IsNull(Me.Phone) Then Cancel = True MsgBox "Please fill in Caller's Phone Number." Me.Phone.SetFocus End If If IsNull(Me.Address) Then etc, etc.... -- HTH CJ --------------------------------------------------------- Know thyself, know thy limits....know thy newsgroups! "Whitney" wrote in message ... Maybe that's where I'm going wrong. I have 5 fields and all 5 are required. When the user hits Save and Close, I need it to then validate that all 5 fields are populated before Saving and Closing the form. If they are not, I would like a message to appear that indicates which field is not populated and take the cursor to that field. Then to repeat this for every field that is not populated each time the user clicks Save and Close. Ultimately the form will not write any data to the table or close the form until all fields are populated. I also tried making the fields required in the table, but didn't see any difference when clicking Save and Close on the form. It might also be helpful to know that the tables are stored on a be file and the forms are on an fe file. I made the fields required on the be tables, but this did not change the user form experience. "CJ" wrote: Hi Sprinks Not that I am a code pro or anything but I would have written it as: If IsNull(Me.Caller) Then Cancel = True MsgBox "Please fill in Caller's Name." Me.Caller.SetFocus End If |
#14
|
|||
|
|||
Required Fields on Form
Make sure that you do not have any code in the Before Update
event for any of your fields. It sounds like there is other code that has not been cleaned up and is causing interference. If you think you need other code you need to post it so that we can see the whole picture. CJ --------------------------------------------------------- Know thyself, know thy limits....know thy newsgroups! "Whitney" wrote in message ... I get the No Current Record as the last error message in the sequence of error messages. So if I populate all fields except one, I get the error message for that field, click ok and then get another message that says "No Current Record", then click Ok and then it takes me back to the form. Also I do have a cancel button, when the user populates at least one field and then decides to cancel they get all of the error messages for the remaining unpopulated fields. They then have to click ok to each message and then the form closes, when it should just ignore the validation code and close the form. "CJ" wrote: snipI'd rather it give the message for the first unpopulated field go back to the form to populate it, then when they click save and close it gives the next unpopulated field, if anysnip Yep, me too but I haven't figured out how to do that yet. Might have to make a separate post. On the positive side, at least they know what is required and the odds of somebody leaving all of that data should be pretty slim. Users become trained as to what is expected. When does the No Current Record message show up? After the error messages when you move to another record or after the error messages after you have pushed your close button? To cancel and close the form, create a CANCEL button. Your code would be: DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70 DoCmd.Close Access does not create a new record until the user types data in. Therefore, if the form is left completely blank, no record is created so it does not need to check for invalid data. -- Thanks for taking the time! CJ --------------------------------------------------------- Know thyself, know thy limits....know thy newsgroups! "Whitney" wrote in message ... Thanks CJ for the clarification. I'm almost there, but have a few more issues. I added the code to the form_BeforeUpdate, so now if they have only populated one of the four required fields they get a message for each unpopulated field before going back to the form. I don't really like that, but willing to accept it. I'd rather it give the message for the first unpopulated field go back to the form to populate it, then when they click save and close it gives the next unpopulated field, if any. Also when all of the messages pop up, the last message is always No Current Record. I'm not sure why this is happening. My last issue, is that if the user populated one field, but then decides to cancel the form, it also gives the error messages that fields are required. How can I prevent this and just close the form? If no fields are populated it closes fine. Also if the user does not populate any fields and clicks save and close it closes the form without the error messages, this should be opposite. "CJ" wrote: First thing, the difference between fields and records: A field is each piece of data that you complete for each record. So, if you have data for Charlie Brown: Charlie would be in the first name Field, Brown would be in the last name Field. All of his data together is Charlie Brown's Record. This is important because you need to understand when Access events run. If you want the validation to only happen when the use tries to either move to the next record (Caller) or save and close the form, then you MUST have the code in the FORMS Before Update event NOT the FIELDS Before Update. Having the code in the fields event would produce the error message when the user tries to move to the next field...this is not what you want. To make sure you are putting the code in the correct event, make sure you select Form from the dropdown box at the very top of the Properties box. Also, I actually built this to make sure that it works for you, instead of trying to wing it....=D Feel free to copy and paste..... The code for your Form Before Update event should be: If IsNull(Me.Caller) Then DoCmd.CancelEvent MsgBox "Please fill in Caller's Name." Me.Caller.SetFocus End If If IsNull(Me.Date) Then DoCmd.CancelEvent MsgBox "Please fill in Date." Me.Date.SetFocus End If If IsNull(Me.Reason) Then DoCmd.CancelEvent MsgBox "Please fill in reason for call." Me.Reason.SetFocus End If If IsNull(Me.Submitter) Then DoCmd.CancelEvent MsgBox "Please fill in your name." Me.Submitter.SetFocus End If The code for your Close button should be: On Error GoTo Err_Save_Click DoCmd.Close ' You do not need to tell Access to save the data. ' It is saved automatically when the form closes Exit_Save_Click: Exit Sub Err_Save_Click: MsgBox "Unable to Save and Close" Resume Exit_Save_Click -- HTH CJ --------------------------------------------------------- Know thyself, know thy limits....know thy newsgroups! "Whitney" wrote in message news Ok I figured out the reason I wasn't getting the error message from the required table fields validation rule. I had the test fe file still referrerencing the original be file instead of the test be file where I made the table changes. Silly me. So now I'm getting the error message, which I understand but is too complicated for a user to understand. So I'm still using the vb code in order to provide a more straight forward error message, however it's not working. I wasn't sure where to put your code, as I think it might be better on click for the save button instead of checking as they move between fields. I want to give them the flexibility to populate the fields in any order without the error messages, but to validate when they click save and close. So I added the code on click, before the save and close command, see below. However I get an error message Run-time error '438': Object doesn't support this property or method. When I click Debug it points to the Cancel = True line of the IF Is Null (Me. Caller) code. Not sure what I'm doing wrong. Private Sub Save_Click() If IsNull(Me.Caller) Then Cancel = True MsgBox "Please fill in Caller's Name." Me.Caller.SetFocus End If If IsNull(Me.Date) Then Cancel = True MsgBox "Please fill in Date." Me.Date.SetFocus End If If IsNull(Me.Reason) Then Cancel = True MsgBox "Please fill in reason for call." Me.Reason.SetFocus End If If IsNull(Me.Submitter) Then Cancel = True MsgBox "Please fill in your name." Me.Submitter.SetFocus End If On Error GoTo Err_Save_Click DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 DoCmd.Close Exit_Save_Click: Exit Sub Err_Save_Click: MsgBox Err.Description Resume Exit_Save_Click End Sub "CJ" wrote: Hi Whitney If you made the fields required in the table you should definitely see an error message if the field is left blank on your form. Try a test: Make all of the fields required in your table that need to be required and then try just navigating to a different record. Don't use your button. Unless you have your warnings turned off via code you should receive an error message. If you do not receive a message, you need to check in the code for DoCmd.SetWarnings False If some code requires the warnings to be turned off then you need to make sure that it gets turned back on with DoCmd.SetWarnings True after the code has executed. I should also have mentioned that you do need to keep the code in the forms BeforeUpdate event. BeforeUpdate checks before the record is saved and you move between records, OnClick checks when you push the button. For both events, your code would be something like: If IsNull(Me.Caller) Then Cancel = True MsgBox "Please fill in Caller's Name." Me.Caller.SetFocus End If If IsNull(Me.Phone) Then Cancel = True MsgBox "Please fill in Caller's Phone Number." Me.Phone.SetFocus End If If IsNull(Me.Address) Then etc, etc.... -- HTH CJ --------------------------------------------------------- Know thyself, know thy limits....know thy newsgroups! "Whitney" wrote in message ... Maybe that's where I'm going wrong. I have 5 fields and all 5 are required. When the user hits Save and Close, I need it to then validate that all 5 fields are populated before Saving and Closing the form. If they are not, I would like a message to appear that indicates which field is not populated and take the cursor to that field. Then to repeat this for every field that is not populated each time the user clicks Save and Close. Ultimately the form will not write any data to the table or close the form until all fields are populated. I also tried making the fields required in the table, but didn't see any difference when clicking Save and Close on the form. It might also be helpful to know that the tables are stored on a be file and the forms are on an fe file. I made the fields required on the be tables, but this did not change the user form experience. "CJ" wrote: Hi Sprinks Not that I am a code pro or anything but I would have written it as: If IsNull(Me.Caller) Then Cancel = True MsgBox "Please fill in Caller's Name." Me.Caller.SetFocus End If |
#15
|
|||
|
|||
Required Fields on Form
I was missing the first docmd, so I added your suggestion. It works when one
field is populated, but now I get an error when canceling and there are no fields populated. The command or action 'Undo' isn't available now. Private Sub Cancel_Click() On Error GoTo Err_Cancel_Click DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70 DoCmd.Close Exit_Cancel_Click: Exit Sub Err_Cancel_Click: MsgBox Err.Description Resume Exit_Cancel_Click End Sub "CJ" wrote: Hi What is the code for your Cancel button? -- Thanks for taking the time! CJ --------------------------------------------------------- Know thyself, know thy limits....know thy newsgroups! "Whitney" wrote in message ... I get the No Current Record as the last error message in the sequence of error messages. So if I populate all fields except one, I get the error message for that field, click ok and then get another message that says "No Current Record", then click Ok and then it takes me back to the form. Also I do have a cancel button, when the user populates at least one field and then decides to cancel they get all of the error messages for the remaining unpopulated fields. They then have to click ok to each message and then the form closes, when it should just ignore the validation code and close the form. "CJ" wrote: snipI'd rather it give the message for the first unpopulated field go back to the form to populate it, then when they click save and close it gives the next unpopulated field, if anysnip Yep, me too but I haven't figured out how to do that yet. Might have to make a separate post. On the positive side, at least they know what is required and the odds of somebody leaving all of that data should be pretty slim. Users become trained as to what is expected. When does the No Current Record message show up? After the error messages when you move to another record or after the error messages after you have pushed your close button? To cancel and close the form, create a CANCEL button. Your code would be: DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70 DoCmd.Close Access does not create a new record until the user types data in. Therefore, if the form is left completely blank, no record is created so it does not need to check for invalid data. -- Thanks for taking the time! CJ --------------------------------------------------------- Know thyself, know thy limits....know thy newsgroups! "Whitney" wrote in message ... Thanks CJ for the clarification. I'm almost there, but have a few more issues. I added the code to the form_BeforeUpdate, so now if they have only populated one of the four required fields they get a message for each unpopulated field before going back to the form. I don't really like that, but willing to accept it. I'd rather it give the message for the first unpopulated field go back to the form to populate it, then when they click save and close it gives the next unpopulated field, if any. Also when all of the messages pop up, the last message is always No Current Record. I'm not sure why this is happening. My last issue, is that if the user populated one field, but then decides to cancel the form, it also gives the error messages that fields are required. How can I prevent this and just close the form? If no fields are populated it closes fine. Also if the user does not populate any fields and clicks save and close it closes the form without the error messages, this should be opposite. "CJ" wrote: First thing, the difference between fields and records: A field is each piece of data that you complete for each record. So, if you have data for Charlie Brown: Charlie would be in the first name Field, Brown would be in the last name Field. All of his data together is Charlie Brown's Record. This is important because you need to understand when Access events run. If you want the validation to only happen when the use tries to either move to the next record (Caller) or save and close the form, then you MUST have the code in the FORMS Before Update event NOT the FIELDS Before Update. Having the code in the fields event would produce the error message when the user tries to move to the next field...this is not what you want. To make sure you are putting the code in the correct event, make sure you select Form from the dropdown box at the very top of the Properties box. Also, I actually built this to make sure that it works for you, instead of trying to wing it....=D Feel free to copy and paste..... The code for your Form Before Update event should be: If IsNull(Me.Caller) Then DoCmd.CancelEvent MsgBox "Please fill in Caller's Name." Me.Caller.SetFocus End If If IsNull(Me.Date) Then DoCmd.CancelEvent MsgBox "Please fill in Date." Me.Date.SetFocus End If If IsNull(Me.Reason) Then DoCmd.CancelEvent MsgBox "Please fill in reason for call." Me.Reason.SetFocus End If If IsNull(Me.Submitter) Then DoCmd.CancelEvent MsgBox "Please fill in your name." Me.Submitter.SetFocus End If The code for your Close button should be: On Error GoTo Err_Save_Click DoCmd.Close ' You do not need to tell Access to save the data. ' It is saved automatically when the form closes Exit_Save_Click: Exit Sub Err_Save_Click: MsgBox "Unable to Save and Close" Resume Exit_Save_Click -- HTH CJ --------------------------------------------------------- Know thyself, know thy limits....know thy newsgroups! "Whitney" wrote in message news Ok I figured out the reason I wasn't getting the error message from the required table fields validation rule. I had the test fe file still referrerencing the original be file instead of the test be file where I made the table changes. Silly me. So now I'm getting the error message, which I understand but is too complicated for a user to understand. So I'm still using the vb code in order to provide a more straight forward error message, however it's not working. I wasn't sure where to put your code, as I think it might be better on click for the save button instead of checking as they move between fields. I want to give them the flexibility to populate the fields in any order without the error messages, but to validate when they click save and close. So I added the code on click, before the save and close command, see below. However I get an error message Run-time error '438': Object doesn't support this property or method. When I click Debug it points to the Cancel = True line of the IF Is Null (Me. Caller) code. Not sure what I'm doing wrong. Private Sub Save_Click() If IsNull(Me.Caller) Then Cancel = True MsgBox "Please fill in Caller's Name." Me.Caller.SetFocus End If If IsNull(Me.Date) Then Cancel = True MsgBox "Please fill in Date." Me.Date.SetFocus End If If IsNull(Me.Reason) Then Cancel = True MsgBox "Please fill in reason for call." Me.Reason.SetFocus End If If IsNull(Me.Submitter) Then Cancel = True MsgBox "Please fill in your name." Me.Submitter.SetFocus End If On Error GoTo Err_Save_Click DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 DoCmd.Close Exit_Save_Click: Exit Sub Err_Save_Click: MsgBox Err.Description Resume Exit_Save_Click End Sub "CJ" wrote: Hi Whitney If you made the fields required in the table you should definitely see an error message if the field is left blank on your form. Try a test: Make all of the fields required in your table that need to be required and then try just navigating to a different record. Don't use your button. Unless you have your warnings turned off via code you should receive an error message. If you do not receive a message, you need to check in the code for DoCmd.SetWarnings False If some code requires the warnings to be turned off then you need to make sure that it gets turned back on with DoCmd.SetWarnings True after the code has executed. I should also have mentioned that you do need to keep the code in the forms BeforeUpdate event. BeforeUpdate checks before the record is saved and you move between records, OnClick checks when you push the button. For both events, your code would be something like: If IsNull(Me.Caller) Then Cancel = True MsgBox "Please fill in Caller's Name." Me.Caller.SetFocus End If If IsNull(Me.Phone) Then Cancel = True MsgBox "Please fill in Caller's Phone Number." Me.Phone.SetFocus End If If IsNull(Me.Address) Then etc, etc.... -- |
#16
|
|||
|
|||
Required Fields on Form
Hi Whitney
It sounds like you have other code in place for your fields or form that is causing interference. You should have the following and only the following: The code for your Form Before Update event should be: If IsNull(Me.Caller) Then DoCmd.CancelEvent MsgBox "Please fill in Caller's Name." Me.Caller.SetFocus End If If IsNull(Me.Date) Then DoCmd.CancelEvent MsgBox "Please fill in Date." Me.Date.SetFocus End If If IsNull(Me.Reason) Then DoCmd.CancelEvent MsgBox "Please fill in reason for call." Me.Reason.SetFocus End If If IsNull(Me.Submitter) Then DoCmd.CancelEvent MsgBox "Please fill in your name." Me.Submitter.SetFocus End If For your Close button in the On Click event you should have: On Error GoTo Err_Save_Click DoCmd.Close Exit_Save_Click: Exit Sub Err_Save_Click: MsgBox "Unable to Save and Close" Resume Exit_Save_Click For your Cancel button in the On Click event you should have: DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70 DoCmd.Close You should not have any code in any of the Events for any of your fields. If you clean up any extra code that you have and you are still getting error messages, I think you should start a new thread and deal with each one individually. I think I'm out of ideas on this. -- Good Luck CJ --------------------------------------------------------- Know thyself, know thy limits....know thy newsgroups! "Whitney" wrote in message ... I was missing the first docmd, so I added your suggestion. It works when one field is populated, but now I get an error when canceling and there are no fields populated. The command or action 'Undo' isn't available now. Private Sub Cancel_Click() On Error GoTo Err_Cancel_Click DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70 DoCmd.Close Exit_Cancel_Click: Exit Sub Err_Cancel_Click: MsgBox Err.Description Resume Exit_Cancel_Click End Sub "CJ" wrote: Hi What is the code for your Cancel button? -- Thanks for taking the time! CJ --------------------------------------------------------- Know thyself, know thy limits....know thy newsgroups! "Whitney" wrote in message ... I get the No Current Record as the last error message in the sequence of error messages. So if I populate all fields except one, I get the error message for that field, click ok and then get another message that says "No Current Record", then click Ok and then it takes me back to the form. Also I do have a cancel button, when the user populates at least one field and then decides to cancel they get all of the error messages for the remaining unpopulated fields. They then have to click ok to each message and then the form closes, when it should just ignore the validation code and close the form. "CJ" wrote: snipI'd rather it give the message for the first unpopulated field go back to the form to populate it, then when they click save and close it gives the next unpopulated field, if anysnip Yep, me too but I haven't figured out how to do that yet. Might have to make a separate post. On the positive side, at least they know what is required and the odds of somebody leaving all of that data should be pretty slim. Users become trained as to what is expected. When does the No Current Record message show up? After the error messages when you move to another record or after the error messages after you have pushed your close button? To cancel and close the form, create a CANCEL button. Your code would be: DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70 DoCmd.Close Access does not create a new record until the user types data in. Therefore, if the form is left completely blank, no record is created so it does not need to check for invalid data. -- Thanks for taking the time! CJ --------------------------------------------------------- Know thyself, know thy limits....know thy newsgroups! "Whitney" wrote in message ... Thanks CJ for the clarification. I'm almost there, but have a few more issues. I added the code to the form_BeforeUpdate, so now if they have only populated one of the four required fields they get a message for each unpopulated field before going back to the form. I don't really like that, but willing to accept it. I'd rather it give the message for the first unpopulated field go back to the form to populate it, then when they click save and close it gives the next unpopulated field, if any. Also when all of the messages pop up, the last message is always No Current Record. I'm not sure why this is happening. My last issue, is that if the user populated one field, but then decides to cancel the form, it also gives the error messages that fields are required. How can I prevent this and just close the form? If no fields are populated it closes fine. Also if the user does not populate any fields and clicks save and close it closes the form without the error messages, this should be opposite. "CJ" wrote: First thing, the difference between fields and records: A field is each piece of data that you complete for each record. So, if you have data for Charlie Brown: Charlie would be in the first name Field, Brown would be in the last name Field. All of his data together is Charlie Brown's Record. This is important because you need to understand when Access events run. If you want the validation to only happen when the use tries to either move to the next record (Caller) or save and close the form, then you MUST have the code in the FORMS Before Update event NOT the FIELDS Before Update. Having the code in the fields event would produce the error message when the user tries to move to the next field...this is not what you want. To make sure you are putting the code in the correct event, make sure you select Form from the dropdown box at the very top of the Properties box. Also, I actually built this to make sure that it works for you, instead of trying to wing it....=D Feel free to copy and paste..... The code for your Form Before Update event should be: If IsNull(Me.Caller) Then DoCmd.CancelEvent MsgBox "Please fill in Caller's Name." Me.Caller.SetFocus End If If IsNull(Me.Date) Then DoCmd.CancelEvent MsgBox "Please fill in Date." Me.Date.SetFocus End If If IsNull(Me.Reason) Then DoCmd.CancelEvent MsgBox "Please fill in reason for call." Me.Reason.SetFocus End If If IsNull(Me.Submitter) Then DoCmd.CancelEvent MsgBox "Please fill in your name." Me.Submitter.SetFocus End If The code for your Close button should be: On Error GoTo Err_Save_Click DoCmd.Close ' You do not need to tell Access to save the data. ' It is saved automatically when the form closes Exit_Save_Click: Exit Sub Err_Save_Click: MsgBox "Unable to Save and Close" Resume Exit_Save_Click -- HTH CJ --------------------------------------------------------- Know thyself, know thy limits....know thy newsgroups! "Whitney" wrote in message news Ok I figured out the reason I wasn't getting the error message from the required table fields validation rule. I had the test fe file still referrerencing the original be file instead of the test be file where I made the table changes. Silly me. So now I'm getting the error message, which I understand but is too complicated for a user to understand. So I'm still using the vb code in order to provide a more straight forward error message, however it's not working. I wasn't sure where to put your code, as I think it might be better on click for the save button instead of checking as they move between fields. I want to give them the flexibility to populate the fields in any order without the error messages, but to validate when they click save and close. So I added the code on click, before the save and close command, see below. However I get an error message Run-time error '438': Object doesn't support this property or method. When I click Debug it points to the Cancel = True line of the IF Is Null (Me. Caller) code. Not sure what I'm doing wrong. Private Sub Save_Click() If IsNull(Me.Caller) Then Cancel = True MsgBox "Please fill in Caller's Name." Me.Caller.SetFocus End If If IsNull(Me.Date) Then Cancel = True MsgBox "Please fill in Date." Me.Date.SetFocus End If If IsNull(Me.Reason) Then Cancel = True MsgBox "Please fill in reason for call." Me.Reason.SetFocus End If If IsNull(Me.Submitter) Then Cancel = True MsgBox "Please fill in your name." Me.Submitter.SetFocus End If On Error GoTo Err_Save_Click DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 DoCmd.Close Exit_Save_Click: Exit Sub Err_Save_Click: MsgBox Err.Description Resume Exit_Save_Click End Sub "CJ" wrote: Hi Whitney If you made the fields required in the table you should definitely see an error message if the field is left blank on your form. Try a test: Make all of the fields required in your table that need to be required and then try just navigating to a different record. Don't use your button. Unless you have your warnings turned off via code you should receive an error message. If you do not receive a message, you need to check in the code for DoCmd.SetWarnings False If some code requires the warnings to be turned off then you need to make sure that it gets turned back on with DoCmd.SetWarnings True after the code has executed. I should also have mentioned that you do need to keep the code in the forms BeforeUpdate event. BeforeUpdate checks before the record is saved and you move between records, OnClick checks when you push the button. For both events, your code would be something like: If IsNull(Me.Caller) Then Cancel = True MsgBox "Please fill in Caller's Name." Me.Caller.SetFocus End If If IsNull(Me.Phone) Then Cancel = True MsgBox "Please fill in Caller's Phone Number." Me.Phone.SetFocus End If If IsNull(Me.Address) Then etc, etc.... -- |
#17
|
|||
|
|||
Required Fields on Form
Also, look back in your table and make sure that you
do not have Required set to yes and a bunch of data validation rules. You do not need these kinds of controls in both locations. CJ --------------------------------------------------------- Know thyself, know thy limits....know thy newsgroups! "Whitney" wrote in message ... I was missing the first docmd, so I added your suggestion. It works when one field is populated, but now I get an error when canceling and there are no fields populated. The command or action 'Undo' isn't available now. Private Sub Cancel_Click() On Error GoTo Err_Cancel_Click DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70 DoCmd.Close Exit_Cancel_Click: Exit Sub Err_Cancel_Click: MsgBox Err.Description Resume Exit_Cancel_Click End Sub "CJ" wrote: Hi What is the code for your Cancel button? -- Thanks for taking the time! CJ --------------------------------------------------------- Know thyself, know thy limits....know thy newsgroups! "Whitney" wrote in message ... I get the No Current Record as the last error message in the sequence of error messages. So if I populate all fields except one, I get the error message for that field, click ok and then get another message that says "No Current Record", then click Ok and then it takes me back to the form. Also I do have a cancel button, when the user populates at least one field and then decides to cancel they get all of the error messages for the remaining unpopulated fields. They then have to click ok to each message and then the form closes, when it should just ignore the validation code and close the form. "CJ" wrote: snipI'd rather it give the message for the first unpopulated field go back to the form to populate it, then when they click save and close it gives the next unpopulated field, if anysnip Yep, me too but I haven't figured out how to do that yet. Might have to make a separate post. On the positive side, at least they know what is required and the odds of somebody leaving all of that data should be pretty slim. Users become trained as to what is expected. When does the No Current Record message show up? After the error messages when you move to another record or after the error messages after you have pushed your close button? To cancel and close the form, create a CANCEL button. Your code would be: DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70 DoCmd.Close Access does not create a new record until the user types data in. Therefore, if the form is left completely blank, no record is created so it does not need to check for invalid data. -- Thanks for taking the time! CJ --------------------------------------------------------- Know thyself, know thy limits....know thy newsgroups! "Whitney" wrote in message ... Thanks CJ for the clarification. I'm almost there, but have a few more issues. I added the code to the form_BeforeUpdate, so now if they have only populated one of the four required fields they get a message for each unpopulated field before going back to the form. I don't really like that, but willing to accept it. I'd rather it give the message for the first unpopulated field go back to the form to populate it, then when they click save and close it gives the next unpopulated field, if any. Also when all of the messages pop up, the last message is always No Current Record. I'm not sure why this is happening. My last issue, is that if the user populated one field, but then decides to cancel the form, it also gives the error messages that fields are required. How can I prevent this and just close the form? If no fields are populated it closes fine. Also if the user does not populate any fields and clicks save and close it closes the form without the error messages, this should be opposite. "CJ" wrote: First thing, the difference between fields and records: A field is each piece of data that you complete for each record. So, if you have data for Charlie Brown: Charlie would be in the first name Field, Brown would be in the last name Field. All of his data together is Charlie Brown's Record. This is important because you need to understand when Access events run. If you want the validation to only happen when the use tries to either move to the next record (Caller) or save and close the form, then you MUST have the code in the FORMS Before Update event NOT the FIELDS Before Update. Having the code in the fields event would produce the error message when the user tries to move to the next field...this is not what you want. To make sure you are putting the code in the correct event, make sure you select Form from the dropdown box at the very top of the Properties box. Also, I actually built this to make sure that it works for you, instead of trying to wing it....=D Feel free to copy and paste..... The code for your Form Before Update event should be: If IsNull(Me.Caller) Then DoCmd.CancelEvent MsgBox "Please fill in Caller's Name." Me.Caller.SetFocus End If If IsNull(Me.Date) Then DoCmd.CancelEvent MsgBox "Please fill in Date." Me.Date.SetFocus End If If IsNull(Me.Reason) Then DoCmd.CancelEvent MsgBox "Please fill in reason for call." Me.Reason.SetFocus End If If IsNull(Me.Submitter) Then DoCmd.CancelEvent MsgBox "Please fill in your name." Me.Submitter.SetFocus End If The code for your Close button should be: On Error GoTo Err_Save_Click DoCmd.Close ' You do not need to tell Access to save the data. ' It is saved automatically when the form closes Exit_Save_Click: Exit Sub Err_Save_Click: MsgBox "Unable to Save and Close" Resume Exit_Save_Click -- HTH CJ --------------------------------------------------------- Know thyself, know thy limits....know thy newsgroups! "Whitney" wrote in message news Ok I figured out the reason I wasn't getting the error message from the required table fields validation rule. I had the test fe file still referrerencing the original be file instead of the test be file where I made the table changes. Silly me. So now I'm getting the error message, which I understand but is too complicated for a user to understand. So I'm still using the vb code in order to provide a more straight forward error message, however it's not working. I wasn't sure where to put your code, as I think it might be better on click for the save button instead of checking as they move between fields. I want to give them the flexibility to populate the fields in any order without the error messages, but to validate when they click save and close. So I added the code on click, before the save and close command, see below. However I get an error message Run-time error '438': Object doesn't support this property or method. When I click Debug it points to the Cancel = True line of the IF Is Null (Me. Caller) code. Not sure what I'm doing wrong. Private Sub Save_Click() If IsNull(Me.Caller) Then Cancel = True MsgBox "Please fill in Caller's Name." Me.Caller.SetFocus End If If IsNull(Me.Date) Then Cancel = True MsgBox "Please fill in Date." Me.Date.SetFocus End If If IsNull(Me.Reason) Then Cancel = True MsgBox "Please fill in reason for call." Me.Reason.SetFocus End If If IsNull(Me.Submitter) Then Cancel = True MsgBox "Please fill in your name." Me.Submitter.SetFocus End If On Error GoTo Err_Save_Click DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 DoCmd.Close Exit_Save_Click: Exit Sub Err_Save_Click: MsgBox Err.Description Resume Exit_Save_Click End Sub "CJ" wrote: Hi Whitney If you made the fields required in the table you should definitely see an error message if the field is left blank on your form. Try a test: Make all of the fields required in your table that need to be required and then try just navigating to a different record. Don't use your button. Unless you have your warnings turned off via code you should receive an error message. If you do not receive a message, you need to check in the code for DoCmd.SetWarnings False If some code requires the warnings to be turned off then you need to make sure that it gets turned back on with DoCmd.SetWarnings True after the code has executed. I should also have mentioned that you do need to keep the code in the forms BeforeUpdate event. BeforeUpdate checks before the record is saved and you move between records, OnClick checks when you push the button. For both events, your code would be something like: If IsNull(Me.Caller) Then Cancel = True MsgBox "Please fill in Caller's Name." Me.Caller.SetFocus End If If IsNull(Me.Phone) Then Cancel = True MsgBox "Please fill in Caller's Phone Number." Me.Phone.SetFocus End If If IsNull(Me.Address) Then etc, etc.... -- |
#18
|
|||
|
|||
Required Fields on Form
Ok one last try and then I'm giving up and sticking with the ugly validation
rule error message. I removed the validation rule from the table. All fields are set to No for required. Here is all of my current code for this form: Option Compare Database Private Sub Cancel_Click() On Error GoTo Err_Cancel_Click DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70 (with this line I don't get the error messages when cancelling with at least on field populated, but I do get an error message that says "The command or action 'Undo' isn't available. Without this line I get the required field error messages before the form closes.) DoCmd.Close Exit_Cancel_Click: Exit Sub Err_Cancel_Click: MsgBox Err.Description Resume Exit_Cancel_Click End Sub Private Sub Form_BeforeUpdate(Cancel As Integer) If IsNull(Me.Caller) Then DoCmd.CancelEvent MsgBox "Caller's name is required." Me.Caller.SetFocus End If If IsNull(Me.Date) Then DoCmd.CancelEvent MsgBox "Date is required." Me.Date.SetFocus End If If IsNull(Me.Reason) Then DoCmd.CancelEvent MsgBox "Reason for call is required." Me.Reason.SetFocus End If If IsNull(Me.Submitter) Then DoCmd.CancelEvent MsgBox "Your name is required." Me.Submitter.SetFocus End If End Sub Private Sub Save_Click() On Error GoTo Err_Save_Click DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 (without this line I get the required field error messages, but the form closes. With this line I get the extra error message "No Current Record") DoCmd.Close Exit_Save_Click: Exit Sub Err_Save_Click: MsgBox Err.Description Resume Exit_Save_Click End Sub Private Sub Undo_Click() On Error GoTo Err_Undo_Click DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70 Exit_Undo_Click: Exit Sub Err_Undo_Click: MsgBox Err.Description Resume Exit_Undo_Click End Sub "CJ" wrote: Hi Whitney It sounds like you have other code in place for your fields or form that is causing interference. You should have the following and only the following: The code for your Form Before Update event should be: If IsNull(Me.Caller) Then DoCmd.CancelEvent MsgBox "Please fill in Caller's Name." Me.Caller.SetFocus End If If IsNull(Me.Date) Then DoCmd.CancelEvent MsgBox "Please fill in Date." Me.Date.SetFocus End If If IsNull(Me.Reason) Then DoCmd.CancelEvent MsgBox "Please fill in reason for call." Me.Reason.SetFocus End If If IsNull(Me.Submitter) Then DoCmd.CancelEvent MsgBox "Please fill in your name." Me.Submitter.SetFocus End If For your Close button in the On Click event you should have: On Error GoTo Err_Save_Click DoCmd.Close Exit_Save_Click: Exit Sub Err_Save_Click: MsgBox "Unable to Save and Close" Resume Exit_Save_Click For your Cancel button in the On Click event you should have: DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70 DoCmd.Close You should not have any code in any of the Events for any of your fields. If you clean up any extra code that you have and you are still getting error messages, I think you should start a new thread and deal with each one individually. I think I'm out of ideas on this. -- Good Luck CJ --------------------------------------------------------- Know thyself, know thy limits....know thy newsgroups! "Whitney" wrote in message ... I was missing the first docmd, so I added your suggestion. It works when one field is populated, but now I get an error when canceling and there are no fields populated. The command or action 'Undo' isn't available now. Private Sub Cancel_Click() On Error GoTo Err_Cancel_Click DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70 DoCmd.Close Exit_Cancel_Click: Exit Sub Err_Cancel_Click: MsgBox Err.Description Resume Exit_Cancel_Click End Sub "CJ" wrote: Hi What is the code for your Cancel button? -- Thanks for taking the time! CJ --------------------------------------------------------- Know thyself, know thy limits....know thy newsgroups! "Whitney" wrote in message ... I get the No Current Record as the last error message in the sequence of error messages. So if I populate all fields except one, I get the error message for that field, click ok and then get another message that says "No Current Record", then click Ok and then it takes me back to the form. Also I do have a cancel button, when the user populates at least one field and then decides to cancel they get all of the error messages for the remaining unpopulated fields. They then have to click ok to each message and then the form closes, when it should just ignore the validation code and close the form. "CJ" wrote: snipI'd rather it give the message for the first unpopulated field go back to the form to populate it, then when they click save and close it gives the next unpopulated field, if anysnip Yep, me too but I haven't figured out how to do that yet. Might have to make a separate post. On the positive side, at least they know what is required and the odds of somebody leaving all of that data should be pretty slim. Users become trained as to what is expected. When does the No Current Record message show up? After the error messages when you move to another record or after the error messages after you have pushed your close button? To cancel and close the form, create a CANCEL button. Your code would be: DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70 DoCmd.Close Access does not create a new record until the user types data in. Therefore, if the form is left completely blank, no record is created so it does not need to check for invalid data. -- Thanks for taking the time! CJ --------------------------------------------------------- Know thyself, know thy limits....know thy newsgroups! "Whitney" wrote in message ... Thanks CJ for the clarification. I'm almost there, but have a few more issues. I added the code to the form_BeforeUpdate, so now if they have only populated one of the four required fields they get a message for each unpopulated field before going back to the form. I don't really like that, but willing to accept it. I'd rather it give the message for the first unpopulated field go back to the form to populate it, then when they click save and close it gives the next unpopulated field, if any. Also when all of the messages pop up, the last message is always No Current Record. I'm not sure why this is happening. My last issue, is that if the user populated one field, but then decides to cancel the form, it also gives the error messages that fields are required. How can I prevent this and just close the form? If no fields are populated it closes fine. Also if the user does not populate any fields and clicks save and close it closes the form without the error messages, this should be opposite. "CJ" wrote: First thing, the difference between fields and records: A field is each piece of data that you complete for each record. So, if you have data for Charlie Brown: Charlie would be in the first name Field, Brown would be in the last name Field. All of his data together is Charlie Brown's Record. This is important because you need to understand when Access events run. If you want the validation to only happen when the use tries to either move to the next record (Caller) or save and close the form, then you MUST have the code in the FORMS Before Update event NOT the FIELDS Before Update. Having the code in the fields event would produce the error message when the user tries to move to the next field...this is not what you want. To make sure you are putting the code in the correct event, make sure you select Form from the dropdown box at the very top of the Properties box. Also, I actually built this to make sure that it works for you, instead of trying to wing it....=D Feel free to copy and paste..... The code for your Form Before Update event should be: If IsNull(Me.Caller) Then DoCmd.CancelEvent MsgBox "Please fill in Caller's Name." Me.Caller.SetFocus End If If IsNull(Me.Date) Then DoCmd.CancelEvent MsgBox "Please fill in Date." Me.Date.SetFocus End If If IsNull(Me.Reason) Then DoCmd.CancelEvent MsgBox "Please fill in reason for call." Me.Reason.SetFocus End If If IsNull(Me.Submitter) Then DoCmd.CancelEvent MsgBox "Please fill in your name." Me.Submitter.SetFocus End If The code for your Close button should be: On Error GoTo Err_Save_Click DoCmd.Close ' You do not need to tell Access to save the data. ' It is saved automatically when the form closes Exit_Save_Click: Exit Sub Err_Save_Click: MsgBox "Unable to Save and Close" Resume Exit_Save_Click -- HTH CJ --------------------------------------------------------- Know thyself, know thy limits....know thy newsgroups! "Whitney" wrote in message news Ok I figured out the reason I wasn't getting the error message from the required table fields validation rule. I had the test fe file still referrerencing the original be file instead of the test be file where I made the table changes. Silly me. So now I'm getting the error message, which I understand but is too complicated for a user to understand. So I'm still using the vb code in order to provide a more straight forward error message, however it's not working. I wasn't sure where to put your code, as I think it might be better on click for the save button instead of checking as they move between fields. I want to give them the flexibility to populate the fields in any order |
#19
|
|||
|
|||
Required Fields on Form
I am have a problem with required fields. I read these postings and I can't
get mine to work. This is what I have done and please correct my errors. I opened my form in design view, selected properties, selected forms from the drop down menu and clicked" ..." in Before Update row. Within the code builder I wrote: The code for your Form Before Update event should be: If IsNull(Me.Caller) Then DoCmd.CancelEvent MsgBox "Please fill in Caller's Name." Me.Caller.SetFocus End If changing what I thought were fields in this example. Is "Caller" the field name in the table the form is referencing or is "Caller" the name of the text box inside the form? Once I get this to work correctly, will the error message once the form is closed? Thanks "CJ" wrote: Make sure that you do not have any code in the Before Update event for any of your fields. It sounds like there is other code that has not been cleaned up and is causing interference. If you think you need other code you need to post it so that we can see the whole picture. CJ --------------------------------------------------------- Know thyself, know thy limits....know thy newsgroups! "Whitney" wrote in message ... I get the No Current Record as the last error message in the sequence of error messages. So if I populate all fields except one, I get the error message for that field, click ok and then get another message that says "No Current Record", then click Ok and then it takes me back to the form. Also I do have a cancel button, when the user populates at least one field and then decides to cancel they get all of the error messages for the remaining unpopulated fields. They then have to click ok to each message and then the form closes, when it should just ignore the validation code and close the form. "CJ" wrote: snipI'd rather it give the message for the first unpopulated field go back to the form to populate it, then when they click save and close it gives the next unpopulated field, if anysnip Yep, me too but I haven't figured out how to do that yet. Might have to make a separate post. On the positive side, at least they know what is required and the odds of somebody leaving all of that data should be pretty slim. Users become trained as to what is expected. When does the No Current Record message show up? After the error messages when you move to another record or after the error messages after you have pushed your close button? To cancel and close the form, create a CANCEL button. Your code would be: DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70 DoCmd.Close Access does not create a new record until the user types data in. Therefore, if the form is left completely blank, no record is created so it does not need to check for invalid data. -- Thanks for taking the time! CJ --------------------------------------------------------- Know thyself, know thy limits....know thy newsgroups! "Whitney" wrote in message ... Thanks CJ for the clarification. I'm almost there, but have a few more issues. I added the code to the form_BeforeUpdate, so now if they have only populated one of the four required fields they get a message for each unpopulated field before going back to the form. I don't really like that, but willing to accept it. I'd rather it give the message for the first unpopulated field go back to the form to populate it, then when they click save and close it gives the next unpopulated field, if any. Also when all of the messages pop up, the last message is always No Current Record. I'm not sure why this is happening. My last issue, is that if the user populated one field, but then decides to cancel the form, it also gives the error messages that fields are required. How can I prevent this and just close the form? If no fields are populated it closes fine. Also if the user does not populate any fields and clicks save and close it closes the form without the error messages, this should be opposite. "CJ" wrote: First thing, the difference between fields and records: A field is each piece of data that you complete for each record. So, if you have data for Charlie Brown: Charlie would be in the first name Field, Brown would be in the last name Field. All of his data together is Charlie Brown's Record. This is important because you need to understand when Access events run. If you want the validation to only happen when the use tries to either move to the next record (Caller) or save and close the form, then you MUST have the code in the FORMS Before Update event NOT the FIELDS Before Update. Having the code in the fields event would produce the error message when the user tries to move to the next field...this is not what you want. To make sure you are putting the code in the correct event, make sure you select Form from the dropdown box at the very top of the Properties box. Also, I actually built this to make sure that it works for you, instead of trying to wing it....=D Feel free to copy and paste..... The code for your Form Before Update event should be: If IsNull(Me.Caller) Then DoCmd.CancelEvent MsgBox "Please fill in Caller's Name." Me.Caller.SetFocus End If If IsNull(Me.Date) Then DoCmd.CancelEvent MsgBox "Please fill in Date." Me.Date.SetFocus End If If IsNull(Me.Reason) Then DoCmd.CancelEvent MsgBox "Please fill in reason for call." Me.Reason.SetFocus End If If IsNull(Me.Submitter) Then DoCmd.CancelEvent MsgBox "Please fill in your name." Me.Submitter.SetFocus End If The code for your Close button should be: On Error GoTo Err_Save_Click DoCmd.Close ' You do not need to tell Access to save the data. ' It is saved automatically when the form closes Exit_Save_Click: Exit Sub Err_Save_Click: MsgBox "Unable to Save and Close" Resume Exit_Save_Click -- HTH CJ --------------------------------------------------------- Know thyself, know thy limits....know thy newsgroups! "Whitney" wrote in message news Ok I figured out the reason I wasn't getting the error message from the required table fields validation rule. I had the test fe file still referrerencing the original be file instead of the test be file where I made the table changes. Silly me. So now I'm getting the error message, which I understand but is too complicated for a user to understand. So I'm still using the vb code in order to provide a more straight forward error message, however it's not working. I wasn't sure where to put your code, as I think it might be better on click for the save button instead of checking as they move between fields. I want to give them the flexibility to populate the fields in any order without the error messages, but to validate when they click save and close. So I added the code on click, before the save and close command, see below. However I get an error message Run-time error '438': Object doesn't support this property or method. When I click Debug it points to the Cancel = True line of the IF Is Null (Me. Caller) code. Not sure what I'm doing wrong. Private Sub Save_Click() If IsNull(Me.Caller) Then Cancel = True MsgBox "Please fill in Caller's Name." Me.Caller.SetFocus End If If IsNull(Me.Date) Then Cancel = True MsgBox "Please fill in Date." Me.Date.SetFocus End If If IsNull(Me.Reason) Then Cancel = True MsgBox "Please fill in reason for call." Me.Reason.SetFocus End If If IsNull(Me.Submitter) Then Cancel = True MsgBox "Please fill in your name." Me.Submitter.SetFocus End If On Error GoTo Err_Save_Click DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 DoCmd.Close Exit_Save_Click: Exit Sub Err_Save_Click: MsgBox Err.Description Resume Exit_Save_Click End Sub "CJ" wrote: Hi Whitney If you made the fields required in the table you should definitely see an error message if the field is left blank on your form. Try a test: Make all of the fields required in your table that need to be required and then try just navigating to a different record. Don't use your button. Unless you have your warnings turned off via code you should receive an error message. If you do not receive a message, you need to check in the code for DoCmd.SetWarnings False If some code requires the warnings to be turned off then you need to make sure that it gets turned back on with DoCmd.SetWarnings True after the code has executed. I should also have mentioned that you do need to keep the code in the forms BeforeUpdate event. BeforeUpdate checks before the record is saved and you move between records, OnClick checks when you push the button. For both events, your code would be something like: If IsNull(Me.Caller) Then Cancel = True MsgBox "Please fill in Caller's Name." Me.Caller.SetFocus End If If IsNull(Me.Phone) Then Cancel = True MsgBox "Please fill in Caller's Phone Number." Me.Phone.SetFocus End If If IsNull(Me.Address) Then etc, etc.... -- |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
No fields listed on my form in form view | Nick | Database Design | 4 | October 11th, 2004 04:48 AM |
Text form fields | Jerry | General Discussion | 5 | August 24th, 2004 10:43 PM |
Default values to load up automatically in a form based on value entered in another form | Anthony Dowd | Using Forms | 8 | August 12th, 2004 08:53 AM |
Populate two+ fields on a Form, based on a pop up form's record | Michael Miller | Using Forms | 0 | June 9th, 2004 02:56 PM |
Can merge fields be entered into text form fields? | Walt | Mailmerge | 5 | May 18th, 2004 10:16 PM |