Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 344
  • Last Modified:

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?
0
SueMos
Asked:
SueMos
  • 6
  • 2
  • 2
  • +1
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
Chuck WoodCommented:
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
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 MVP, Access and Data Platform)Commented:
"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

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 6
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now