Required Fields on Access Form

I am struggling with a data entry form, I have created in Access.

There are about 10 fields on the form that are required, they range from combo boxes, text fields and memo fields. The rest is not required information. In the table i have all of the feilds marked as required. Right now the error message is pretty generic, saying "The table doesn't allow for a null value" I want the error message to tell me which feild it is not letting me submit? What is the best way to do this? Through code? In the table? How can I get this set-up?
SueMosAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Chuck WoodConnect With a Mentor Commented:
You can use the OnExit event to trigger a check of the txt field (or memo field). Also you can use the same code when you click a command button.

Private Sub txtName_Exit(Cancel As Integer)
    If IsNull(txtName) Or Len(Trim(txtName)) = 0 Then
        MsgBox "You MUST supply a Name"
        txtName.SetFocus
    End If
End Sub

Open in new window

0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Try this

Private Sub Form_BeforeUpdate (Cancel As Integer)

    If Nz(Me.SomeField1,"")="" Then
           Msgbox "SomeField1 is required
           Cancel = True
           Exit Sub
    End If
     
 '  same code for the other fields


End Sub

mx
0
 
jparulCommented:

Here's one recommended solution using VBA
Create an array like this:

CheckNamS = Array(Me.chkNamc, me.txtField1, me.opttest, me.cboTest2)
vNamString = "" 
For Each vNamName In CheckNamS
    If vNamName.Value = -1 Then
        vNamString = vNamString & vNamName.Tag & ", "
    End If
Next
Msgbox("following fields are missing" & vNamString)

0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
SueMosAuthor Commented:
Should I put it on the "OnClick" for the save button?? Or just the general "BeforeUpdate" of the form?
0
 
Chuck WoodCommented:
Either one will work but I would use the OnClick event of the Save button.
0
 
SueMosAuthor Commented:
It errors 'variable not defined' and highlights the cancl = true line?
0
 
SueMosAuthor Commented:
cancel
0
 
SueMosAuthor Commented:
Sorry, I didn't see all of my options, let me try something else.
0
 
SueMosAuthor Commented:
CheckNamS gives me a varibale not defined...
0
 
SueMosAuthor Commented:
It works!! Thanks!!
0
 
jparulCommented:
declare CheckNamS as variant
dim CheckNamS as variant
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"You can use the OnExit event to trigger a check of the txt field (or memo field). "

The best place to do this is the Before Update event of the control (or the Form).  On Exit is going to trigger that code *every* time you exit the control - even if a user is just tabbing though the control and the control already has correct data, thus running the code needlessly.  The BU is intended for validation of this nature.

Also, using the BU of each control *does not* cover the case when the user never actually enters the control.  This is why I suggested the Form's Before Update - which will prevent a save from occurring if any of the validations failed.

I highly recommend you use the Form BU instead of each Control's On Exit or Before Update.

mx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.