Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Required Fields on Access Form

Posted on 2009-05-04
12
325 Views
Last Modified: 2013-11-27
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
Comment
Question by:SueMos
  • 6
  • 2
  • 2
  • +1
12 Comments
 
LVL 75
ID: 24298559
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
 
LVL 16

Accepted Solution

by:
Chuck Wood earned 500 total points
ID: 24298583
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
 
LVL 6

Expert Comment

by:jparul
ID: 24298608

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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:SueMos
ID: 24298656
Should I put it on the "OnClick" for the save button?? Or just the general "BeforeUpdate" of the form?
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24298703
Either one will work but I would use the OnClick event of the Save button.
0
 

Author Comment

by:SueMos
ID: 24298761
It errors 'variable not defined' and highlights the cancl = true line?
0
 

Author Comment

by:SueMos
ID: 24298783
cancel
0
 

Author Comment

by:SueMos
ID: 24298797
Sorry, I didn't see all of my options, let me try something else.
0
 

Author Comment

by:SueMos
ID: 24298813
CheckNamS gives me a varibale not defined...
0
 

Author Closing Comment

by:SueMos
ID: 31577715
It works!! Thanks!!
0
 
LVL 6

Expert Comment

by:jparul
ID: 24298844
declare CheckNamS as variant
dim CheckNamS as variant
0
 
LVL 75
ID: 24300172
"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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sum in Split Form 17 28
ADODB problem 20 38
How to force the Combo box to refresh when setting its first column (col 0) from another Form. 6 34
Access Schema 6 26
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

829 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