Link to home
Create AccountLog in
Avatar of justinmoore14
justinmoore14Flag for United States of America

asked on

Required Fields Access 2010

Hello Experts,

I have an Access Database that has around 26 fields that need data entered into them. This is a two part question.

First, how can I make sure that data is entered into each field and if it is not the user is prompted with an msg box and when they pick the appropriate answer it takes them to the field that they left blank. For example, if they fill out 25 of the fields and leave 26 blank when they click on the button to “Add Record” they get a message boxes that tells them they haven’t filled out all the fields and when they make the appropriate
selection it takes them to field 26 to be filled out.


Second, if I have the required field turned on in the table; then when that field is left blank and the user hits the “Add Record” button I get the following error message (Run-Time error “2105” You can’t go to the specified record) obviously because that field was left blank. My question is where can I change the text of this message or can you not??


Justin
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

"My question is where can I change the text of this message or can you not??"
That error is raised in the Form Error event, when you can trap it.  For example:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    Response = acDataErrContinue
    Select Case DataErr
        Case 2105
             Msgbox "A required field is missing - get your act together!"
        Case 3022
            MsgBox "You entered a duplicate record"
        Case Else
            MsgBox "An unexpected error occurred " & DataErr & "  " & AccessError(DataErr)
    End Select
End Sub


mx
As far as question # 1 ... you can either use the Form BeforeUpdate event and test each field, and if missing, set the focus to that field.  

But that's a LOT of tests.  Instead, I would set the Required property for those field to Yes at the table level (related to your #2 question), and again use the Form Error event to trap the error ... then display a message something like "One of more of the following fields are missing ... <a list of required fields here > ... "   and let the User figure out what field(s) is missing.  However, with this scheme, you can't really set the focus since you don't know the exact field like you would using the BU event.  But ... Access basically does all the work for you in this scheme.

mx
Avatar of justinmoore14

ASKER

Hello DatabaseMX,

Thank you for your very quick responses!


First Response,

Can you give me a For Example of where the code would be placed?? P.S. I like your (get your act together!) response I think I may leave that one in there LOL.


Second Response,

Option number one. Could you elaborate (If you don't mind) a little more on what you mean by (But that's a LOT of tests)?? Could you also give an example of what the code would look like if I end up choosing to go this route?




Justin
"Can you give me a For Example of where the code would be placed??"
Well ... that's what I posted ... that is the example.  That code (minus the first and last line) is what goes in the Form Error event - see attached screenshots.  #2 shows the end result.

Standby for a Before Update example ...
Capture1.gif
Capture2.gif
DatabaseMX, I didn't see the "On Error" field sorry about that. Thank you for clearing that up for me.

When I paste the code that you posted above save changes, and go back to the forum. When I hit the "Add Record button" I get the same error message again that I was getting before (Run-Time error “2105” You can’t go to the specified record). If I click on "End" it takes me back to the form. When I click on the "Add Record" button again I get the same error message. Now when I click on "End" it takes me back to the form. Now when I click on "Design View" I get the following messages in this order (An unexpected error occurred 3314 You must enter a value in the "|" field.
) I click "OK" and get (An unexpected error occurred 2169 You can't save this record at this time.@Database Name may have encountered an error while trying to save a record If you close this object now, the data changes you made will be lost. Do you want to close the database object anyway?@@20@@@2). Click "Ok" and it takes me to design view. Any ideas??


Justin
Can you post exactly what you put in the Form Error event ?

mx
Private Sub Form_Error(DataErr As Integer, Response As Integer)
    Response = acDataErrContinue
    Select Case DataErr
        Case 2105
             MsgBox "A required field is missing - get your act together!"
        Case 3022
            MsgBox "You entered a duplicate record"
        Case Else
            MsgBox "An unexpected error occurred " & DataErr & "  " & AccessError(DataErr)
    End Select
End Sub
Can you:

1) Compact & Repair (*** to shrink the size),

2) Zip up the MDB (*** to further shrink the size)

3) Attach the file for upload here (using the 'Attach File function below) ... removing any sensitive data of course.

4**** And please give a clear explanation of exactly how to reproduce the problem or what you are trying to do.

How to upload:
https://www.experts-exchange.com/Community_Support/General/A_2790-How-do-I-attach-a-file-at-Experts-Exchange.html

mx
Unfortunately,

There is just no way that I can post the database online, or remove the sensitive data (Would take way to long). If you really are needing the database I can create another dummy one but will take some time. What would you like me to do??



Justin
Does the Add Record button have code?  If so, post it.   Or is this the Access add record button?

On the "Add Record Button" on the "On Click" there is a "Event Procedure" with the following code below.


Private Sub Command63_Click()

DoCmd.GoToRecord , , acNewRec

End Sub




Justin
I don't think this error is occurring because a Required field is blank, because that would case error 3314 to occur.  If you fill in that field, do you still get the error 2105 ?

And as a Test, what happens is you do this:


Private Sub Command63_Click()
On Error Resume Next ' ******************* add t his
DoCmd.GoToRecord , , acNewRec

End Sub
If I fill in the required field it adds the record like it is suppose to. If I don't then that is when I get the error, but here is the weird thing.

In the table this form pulls from there is the default "ID Field" that Access uses and it is set as the Primary Key. This field is "Not" on the form because it is not needed. Now at one point in time there is another field in the table called "Record Number" which was set as the Primary Key for some length of time. This field "Is" in the form. However, before I ever posted this question on EE the "Primary Key" was changed back to the "ID" field because the Primary Key did not need to be on the "Record Number" field after all.

Here is the weird part. When I go to the form that is in question and I click on "Add Record" it does nothing (Obviously because there is nothing to add). If I go to the "Record Number" field and add the next number in line and then hit "Add Record" that is when I get the (Error 2105). Now if then I go to the required filed (Will say Test) and enter the data there and then hit "Add Record" it adds the record like it is suppose to. I only get the (2105) error on the "Record Number" field. If I start to add data in one of the other fields first say "Test2" (Not a required field) when I hit the "Add Record" button it does nothing (No Error Message) (I assume because the "Test" required field has no data in it) because as soon as I go up there and enter data and hit add it adds the record.


Now when I add the following code that you posted above to the "Add Record" button it prevents me from getting any type of error message. I don't even get the 2105 error message. Instead if I create the scenario I have been describing above and I hit the "Add record" button it just does nothing this time, but if the "Test" field has data in it then the record is added.



Justin
I kind of really need to see a db that exhibits this issue.  That would save a load of time here.

mx
Ok, I created a demo database which is exhibiting the issues we have been talking about.

First

--Open the database and click on "Add Data"
--Now click on the "Record #" page
--In the "Record Number (Ex.1,2,3,etc..) box enter the next sequential number Ex. 4
--Now click the "Add New Record" button and you will get the "2105" error message I was talking about.
--Now go up to "State" (The only required field) and choose a "State" from the list Ex. TX.
--Now hit the "Add New Record" Button and you will see it adds the record without any issue.


Second

--With the Database still open
--Now click on the "Record #" page
--In the "Record Number (Ex.1,2,3,etc..) box enter the next sequential number Ex. 4
--Now click the "Add New Record" button and you will get the "2105" error message I was talking about.
--Click on "End" and it will take you back to the Form.
--Now in the top Left-hand corner click on the "Design" button and you will get the following error message (An unexpected error occurred 3314 You must enter a value in the "|" field.)
--Click on "Ok" and  you will get the following error message (An unexpected error occurred 2169 You can't save this record at this time.@Database Name may have encountered an error while trying to save a record If you close this object now, the data changes you made will be lost. Do you want to close the database object anyway?@@20@@@2).
--Click "Ok" and it will take in Design View of the Form.

Third

Note that in the code below I have commented out the following line so you can see the error message. If you un-comment that line then you want get the error message anymore it just want do anything.


Private Sub Command63_Click()
'On Error Resume Next ' ******************* add t his
DoCmd.GoToRecord , , acNewRec

End Sub




I hope this helps.



Justin

Demo.mdb
ASKER CERTIFIED SOLUTION
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Excellent...That worked perfectly. Now the only thing left was my second question. You said (Standby for a Before Update example ...) were you ever able to work up an example?? I know you have been busy helping me with this issue and it is best to take one issue at a time. I am not Rushing you by any means. Just asking. I greatly appreciate your help and patients with me thus far.


Justin
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Ok, that work, but I am a little confused now sorry :(  I get both the error messages now (This I understand why), but here is what I am confused about.

I am wanting to see if there is a way that after you get the error message and click on "OK" it takes you to the field you missed or highlights them in red. I believe you said that this required a LOT of testing, but am not sure how or even if it is possible to do this?? What I am confused about is weather it is better to use the "Before Update" event and have it go to the missing field (If that is even possible) or use the "On Error" event?



Justin
ok ... here is one way:

Private Sub Form_BeforeUpdate(Cancel As Integer)
   
    If Nz(Me.txtField1, "") = "" Then
        Cancel = True
        MsgBox "Field 1 is required.  Get it together!?"
        Me.txtField1.SetFocus
        Exit Sub
    End If
   
    If Nz(Me.txtField2, "") = "" Then
        Cancel = True
        MsgBox "Field 2 is required.  Get it together!?"
        Me.txtField2.SetFocus
        GoTo Form_BeforeUpdate_ValidationFailed
    End If
   
    If Nz(Me.txtField3, "") = "" Then
        Cancel = True
        MsgBox "Field 3 is required.  Get it together!?"
        Me.txtField3.SetFocus
        GoTo Form_BeforeUpdate_ValidationFailed
    End If
   
    ' and so one for all the fields that are required

   
    ' Validation passed if we get here
    MsgBox "Nice going, you entered ALL required fields. Kudos!"
   
Form_BeforeUpdate_Exit:
    Err.Clear
    Exit Sub

End Sub

mx
When I run the code above (With the txtField's) replaced with the appropriate field names I get the following error message.

Compile Error:
Label not defined


When I hit debug it has the first line listed below highlighted in yellow and the line listed under it highlighted in blue. Any ideas?? What label did I forget to define??


Private Sub Form_BeforeUpdate(Cancel As Integer)


GoTo Form_BeforeUpdate_ValidationFailed




Justin
post the exact code you have now.

mx
Private Sub Form_BeforeUpdate(Cancel As Integer)
   
    If Nz(Me.Combo0, "") = "" Then
        Cancel = True
        MsgBox "Field 1 is required.  Get it together!?"
        Me.Combo0.SetFocus
        Exit Sub
    End If
   
    If Nz(Me.Text56, "") = "" Then
        Cancel = True
        MsgBox "Field 2 is required.  Get it together!?"
        Me.Text56.SetFocus
        GoTo Form_BeforeUpdate_ValidationFailed
    End If
   
'    If Nz(Me.txtField3, "") = "" Then
 '       Cancel = True
  '      MsgBox "Field 3 is required.  Get it together!?"
   '     Me.txtField3.SetFocus
    '    GoTo Form_BeforeUpdate_ValidationFailed
    'End If
   
    ' and so one for all the fields that are required

   
    ' Validation passed if we get here
    MsgBox "Nice going, you entered ALL required fields. Kudos!"
   
Form_BeforeUpdate_Exit:
    Err.Clear
    Exit Sub

End Sub
sorry ... forgot to remove some code form previous example:


Private Sub Form_BeforeUpdate(Cancel As Integer)
   
    If Nz(Me.Combo0, "") = "" Then
        Cancel = True
        MsgBox "Field 1 is required.  Get it together!?"
        Me.Combo0.SetFocus
        Exit Sub
    End If
   
    If Nz(Me.Text56, "") = "" Then
        Cancel = True
        MsgBox "Field 2 is required.  Get it together!?"
        Me.Text56.SetFocus
        '         GoTo Form_BeforeUpdate_ValidationFailed  ** comment OUT
    End If
   
'    If Nz(Me.txtField3, "") = "" Then
 '       Cancel = True
  '      MsgBox "Field 3 is required.  Get it together!?"
   '     Me.txtField3.SetFocus
    '    GoTo Form_BeforeUpdate_ValidationFailed    GoTo Form_BeforeUpdate_ValidationFailed  ** comment OUT
    'End If
   
    ' and so one for all the fields that are required

   
    ' Validation passed if we get here
    MsgBox "Nice going, you entered ALL required fields. Kudos!"
   
Form_BeforeUpdate_Exit:
    Err.Clear
    Exit Sub

End Sub
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
No Problem.


I think that is going to do what I want, but I have one small issue. Now I am getting the msg box from the code above and then right after that I get the msg box from the "On Error" code. Is there anyway to make it so that I only get the one message box?? If I take the code away for the "On Error" then the error messages aren't getting trapped anymore and I am getting the 2105 error after the one from the code above.


Justin
Humm.  Kind of a Catch 22.  When attempting to leave the for the new records, Access attempts to Save the record, thus the BU event is triggered.  However, Cancel = True should ... keep the 2nd message from happening, which is a direct result of the required field(s) missing.  In fact, I'm surprised that one doesn't occur first ... !

Really what you need to do is ... keep the New Record button grayed out until all fields are filled in.  And you need a Save button that explicitly saves the record, and then the New record button is enable.

But, all of this takes special code ... and is a bit beyond this Q ...

mx
That is a great idea and I have never thought of that before. That would actually fit my situation better, and I have learned some new ways to do things with the other code you have provide that I did not know how to accomplish before. Thank you very much for that. Always glad to learn new things.


Now last question, because I agree that (Even though it is what I really need to accomplish) it is really not what I asked in the original question and may confuse others who read this later on. My question is I will be glad to open up a new question asking how to do that but couldn't begin to guess what would be the proper way to word it as not to confuse others. My aim is to helps others who may run across this later on and want to know how to accomplish this and a question they can't find or can't understand is, well, a useless one. Any ideas would be greatly appreciated and I will get that question started??



Justin
Humm ... how to word the Q.  Less is more for sure. Break up into small paragraphs.

Basically, you have a Save button and New Record button.  Both s/b disabled until all required fields are present. Save is enabled once all are present. After Save, then enable New record and disable Save.

You can create a Function on the form that you call from each control's AfterUpdate event.  This function checks all required controls - basically same test as in BU code, but w/o messages.  If all present, Save is enable, otherwise, Save is disabled.  Then ... you won't even need the BU code above.  You will still need Form Error, because ...  IF ... user enters data in a required field, goes to a couple of others, then comes back to that field and backs out the entry, error 3314 will still occur ...so, you still want that message "This field is required".

This is the basic idea.  Oh ... and a Cancel button would be nice also :-)

mx
You basically see this concept all the time on web sites.  Some button is not enable until ALL required fields are entered.  I've been doing this for years in my apps.  

mx
Thank you for all of your help! :-)


I have also asked the other question and called it (Disable Button Until All Fields Are Entered Access 2010).


I hope you have a Happy and Great New Year!



Justin
You are welcome ...

Happy Ho to you too !

mx