Solved

How do I make a field on an access 2007 form required?

Posted on 2011-03-19
16
1,248 Views
Last Modified: 2013-11-28
Hi Experts,

I am trying to make a field on my form a required field.  I have created the a form for the user to update.  I want to force the user to select a value from a combo box field on my form.  I have made the field a required field on the table's design view and I have put the code below in the BeforeUpdate event of the field.  When I test it, the message box appears, however, the focus is not placed back on the field that is required.  See the code below.  
******************************************************************************************************************
Private Sub ComboParty_BeforeUpdate(Cancel As Integer)
If IsNull(Me.ComboParty) = True Then
    MsgBox "This is a REQUIRED field.  Please make a selection", vbCritical + vbOKOnly + vbDefaultButton2, "REQUIRED DATA"
        Me.ComboParty.SetFocus
End If
End Sub
******************************************************************************************************************

Can anyone tell me why the focus will not return to the the ComboParty box?  I don't want the user to be able to tab down to the next field on the form if ComboParty is null.  Any help would be greatly appreciated as I am up against a tight deadline.
0
Comment
Question by:daintysally
  • 9
  • 4
  • 3
16 Comments
 
LVL 75
ID: 35173364
" I don't want the user to be able to tab down to the next field on the form if ComboParty is null."
Well ... first off, if user has not entered anything in the comb box, then the BU event will not even trigger ... so, I'm not seeing how the msgbox is going to fire at all.  This would only happen if ... there was an existing value, and the user backed it out.  And it that case, the focus *should* ... already be in the combo box.

?

mx
0
 

Author Comment

by:daintysally
ID: 35173389
Well ... first off, if user has not entered anything in the comb box, then the BU event will not even trigger ... so, I'm not seeing how the msgbox is going to fire at all.  This would only happen if ... there was an existing value, and the user backed it out.  And it that case, the focus *should* ... already be in the combo box.

mx, you are right....when I backed the value out of the field, I get the following error message:
Run-time error '2108'

You must save the field before you execute the GoToControl action, the GoToControl method, or the SetFocus method.

However, it allows the user to 'tab' over that field on the form, and I don't want it to do that.  Is there anyway to prevent the user from doing that?
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 500 total points
ID: 35173430
The message is correct.  IF ... you are in a Control BU event, then ... you cannot set the Focus to that or another control. That's just how it is.   You can do this in the Form BU event, however.

"However, it allows the user to 'tab' over that field on the form, and I don't want it to do that. "
Well ... problem is, the user may *not* ... actually tab through this required control. So, in order to meet your requirement, I would suggest doing one of two scenarios.

1)  Use the Form BeforeUpdate event to do final validation.  Now, in order to do this, you should *not* make the field Required at the table level, otherwise - you will get an error rendered in the Form Error event - before you even hit the Form BU event.

Remove the code form the combo box, and use this code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
' All control validations here
If Nz(Me.ComboParty, "") = "" Then  ' Covers both Null and Empty String cases
    MsgBox "This is a REQUIRED field.  Please make a selection", vbCritical + vbOKOnly + vbDefaultButton2, "REQUIRED DATA"
      Cancel = True  
      Me.ComboParty.SetFocus
End If

  '  another validation - if any
  ' ....
  ' Validations passed if you get this far ...

End Sub

Method 2 -  see next post - coming up ...

mx
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 500 total points
ID: 35173469
Method 2 - Using the Form Error event to trap Required fields - set at the Table level.
The downside of this method is ... you don't know which field is missing that is required, it there is more than one.  However, you could include in the message which fields are required.
The upside is ... Access does most of the work for you ... and there is less code than using the Form BU event - IF ...you have a LOT of fields to validate.

So ... it's your choice, depending on your situation.  Sometimes, say do to importing data into a table, setting a field(s) as Required is not practical.  There are other cases that come up also, in which case the Form BU event can handle these cases - when, at the end of the day - a given Field is ultimately required (at the form level).

The code looks like this (example):

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    Response = acDataErrContinue
    Select Case DataErr
       
        Case 3314   'Required Field
            mgsbox "One or more required fields are missing. Please enter ALL required fields"
       
        Case 3197, 7787, 7878   'Write conflict errors ...
             MsgBox "Another user is currently updating this record. Please go to happy hour and try later."
        Case Else
            MsgBox "An unexpected error occurred " & DataErr & "  " & AccessError(DataErr)
    End Select
End Sub

mx
0
 
LVL 75
ID: 35173481
" Sometimes, say do to importing data into a table, setting a field(s) as Required is not practical.  There are other cases that come up also"
More on this.  We get reports daily from IT. That data gets imported into a table in a db. But for example, the analyst that ultimately works the account ... is not part of the imported data.  However, business rules dictate that .... the analyst name (as well as other fields) IS required once the record is worked (edited), and ultimately saved.  

So, you can see that I cannot make the Analyst Name field Required at the table level, but it is at the Form level.  In this case, the Form BU event would be used for final validation.

mx
0
 
LVL 75
ID: 35173496
Sorry ... there is a typo in the Form Error code above:

This
        Case 3314   'Required Field
            mgsbox "One or more required fields are missing. Please enter ALL required fields"

should be

        Case 3314   'Required Field
            MsgBox "One or more required fields are missing. Please enter ALL required fields"

mx
0
 

Author Comment

by:daintysally
ID: 35173571
Thanks mx!!!  I am trying your suggestion now.
0
 
LVL 6

Expert Comment

by:AkAlan
ID: 35173756
I would code the pages Before Update event and then look at the combobox.

If IsNull(Me.ComboParty) = True Then
    MsgBox "This is a REQUIRED field.  Please make a selection", vbCritical + vbOKOnly + vbDefaultButton2, "REQUIRED DATA"
        Me.ComboParty.SetFocus
     Cancel = true   ' this stops the update
End If
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 75
ID: 35173987
AkAlan:
You should probably review what is been discussed already, because what you posted will not work.

mx
0
 
LVL 6

Expert Comment

by:AkAlan
ID: 35173999
I reviewed the post. I use the method I described all the time and iir does work. Im not at my pc now but I'll post an example tomorrow.
0
 
LVL 75
ID: 35174003
Sorry, but you cannot SetFocus to any control when you are in the BU event of a Control.  If you do, you will get runtime error 2108 ... as mentioned @ http:#a35173389

And you posted the same thing the OP has - which does not work - except you added the Cancel event.

mx
0
 
LVL 6

Expert Comment

by:AkAlan
ID: 35174007
I put the code in the forms bu event. As I said, I'll post an example db in the morning.
0
 
LVL 75
ID: 35174034
"I put the code in the forms bu event"
Sorry, I thought you were in the Control BU event. Anyway, I already posted the Form BU solution @ http:#a35173430

mx
0
 
LVL 6

Expert Comment

by:AkAlan
ID: 35175817
No problem, I work with both Access and asp.net so I shouldn't have said page, I should have said Form so it would have been more obvious. I clicked your link but it just takes me up this page a bit and I don't see a soulution.
0
 

Author Closing Comment

by:daintysally
ID: 35177762
Thank you MX....this worked wonderfully!!!
0
 
LVL 75
ID: 35177921
You are welcome ..

mx
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now