473,287 Members | 1,880 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Required Fields in a Form

I am pretty good with Access, but do not understand VBA. I have
researched this topic and see only VBA answers, so I hope someone can
help with my specific question.

I have 2 fields for an end-user that must be filled in. I want an error
message for each field. The forms are already partially filled in, and
a user needs to select which record to go to. There are fields for them
to fill in their initials [REVIEWER] and date [REVIEW DATE], along with
other fields. It is these two fields I need to make mandatory, and need
error messages if these are not filled in. I don't know if the would be
a 'Before Exit' or 'Before Update' or which event to attach this to.
Also, if the code could place the curser into the missing field (or the
[REVIEWER] field if both are missing), that would be great.

Thanks for your help.

Nov 13 '05 #1
2 39014
bufb...

Use the ValidationRule and ValidationText properties. For example, for
your txtReviewer control, you could use "Not Is Null" and "Enter your
initials".

This handles the case where the user enters bad data into a field. But,
what if the user skips the field altogether? To catch this, you will
need some event code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If ValidateRecord = False Then
Cancel = True
End If
End Sub

Also a routine:

Private Function ValidateRecord() As Boolean

ValidateRecord = False

If IsNull(txtReviewer.Value) Then
MsgBox txtReviewer.ValidationText
txtReviewer.SetFocus
Exit Function
End If
If IsNull(txtReviewDate.Value) Then
MsgBox txtReviewDate.ValidationText
txtReviewDate.SetFocus
Exit Function
End If

ValidateRecord = True

End Function

ValidateRecord checks your required fields, and displays the
ValidationText when one is missing or incomplete, and sets focus to the
offending control. Your VBA tests in ValidateRecord will need to be the
equivalent of what you placed in ValidationRule for each control.

See Access Help on "ValidationRule" for more.

-Ken

Nov 13 '05 #2
<bu*****@yahoo.com> wrote :
I have 2 fields for an end-user that must be filled in. I want an error
message for each field.


I've provided two options which work for me rather well; hopefully you can
make use of one them.

Cheers,

Dave

Option One

'------------ Code Begins ------------
Private Sub Form_BeforeUpdate(Cancel As Integer)

' Ensure required fields are filled.
' Required fields, which are empty: show a customized message box to that
effect,
' and return the focus for user input.
' Check that a Category has been chosen before proceeding.

If IsNull(Me.cboCategories) Or Me.cboCategories = 0 Then
Call MsgBox("You must select a Category from the list provided, or " _
& vbCrLf & "enter a new category using the ""Edit Categories"" button
on the main menu!" _
, vbExclamation, "ENTRY REQUIRED")
Me.cboCategories.SetFocus
Exit Sub
End If

' Check that a Sub-Category has been chosen before proceeding.

If IsNull(Me.cboSubCategories) Or Me.cboSubCategories = 0 Then
Call MsgBox("You must select a Sub-Category, or choose Null from the
list provided." _
& vbCrLf & "" _
& vbCrLf & "If ''Null'' or a desired value is not available from the
list provided, you " _
& vbCrLf & "can create a new one by using the ''Edit Categories''
button, and " _
& vbCrLf & "add a new sub-category to a main category heading." _
& vbCrLf & "" _
, vbExclamation, "ENTRY REQUIRED")
Me.cboSubCategories.SetFocus
Exit Sub
End If

' Check to ensure a Question or Title has been entered prior to proceeding.

If IsNull(Me.txtQuestion) Or Me.txtQuestion = "" Then
Call MsgBox("Question is a required field!" _
& vbCrLf & "" _
& vbCrLf & "Please enter a question or title for future reference" _
& vbCrLf & "" _
, vbExclamation, "ENTRY REQUIRED")
Me.txtQuestion.SetFocus
Exit Sub
End If

End Sub

'------------ Code Ends ------------
Option Two (Note, this option does not return the user to a specific
field...modify as your see fit)

'------------ Code Begins ------------
Private Sub Form_BeforeUpdate(pintCancel As Integer)
' Check for Null values, and get confirmation each time a record is changed.

Dim strMissing As String
Dim strCRLF As String

' line feed
strCRLF = Chr(10) & Chr(13)
' 1st field value is null?
strMissing = IIf(IsNull(Me.txtSurname), "- Surname" + strCRLF, "")
' 2nd field value is null?
strMissing = strMissing & IIf(IsNull(Me.txtGivenName), "- Given Name" +
strCRLF, "")
' 3rd field value is null?
strMissing = strMissing & IIf(IsNull(Me.cboGroup), "- Group" + strCRLF,
"")
' 4th field value is null?
strMissing = strMissing & IIf(IsNull(Me.cboGrpFilter), "- Group Filter"
+ strCRLF, "")
' 5th field value is null?
strMissing = strMissing & IIf(IsNull(Me.cboElement), "- Element" +
strCRLF, "")
' 6th field value is null?
strMissing = strMissing & IIf(IsNull(Me.txtSortNo), "- Sort Number" +
strCRLF, "")
' Any required field was null?
If strMissing <> "" Then
' No update
Cancel = True
' ask user is (s)he wants to enter data or cancel and close form
If MsgBox( _
"Required information is missing: " & strCRLF & _
strCRLF & _
strMissing & _
strCRLF & _
"Do you want to enter the missing data?", vbYesNo, _
"Data missing") = vbNo Then
Me.Undo

End If

End If

End Sub
'------------ Code Ends ------------
Nov 13 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Spanky | last post by:
I am looking for some help with the following. I have a form with many fields. I have a table in the form that I need to make all the fields within the table required. Basic validation, just...
2
by: Mark Creelman | last post by:
Hi: I am relatively new to ASP. I prefer Perl, but need to do this form to e-mail sipt for a web page, See Example of script below that works fine. I want to add the feature to this where it...
14
by: Oleg | last post by:
Hello there: I've been trying to create two different sets of required fields in one form and to use a radiobutton as sort of a switcher between these sets. In my HTML form there are two...
2
by: Cliff R. | last post by:
Hello, I have a form that has a few required fields and also an "agree to terms" checkbox that must be required. I have used Javascripts for both functions individually, but I need a little help...
3
by: Orchid | last post by:
Hello All, Hope someone can help me on my required field problems. I have a form base on a table for users to input new Employees. There are 4 fields that cannot be Null when entering new...
1
by: swingingming | last post by:
Hi, I made a form based on one table that has several required fields. If in the form, I leave some of them blank and close the form using 'X', I get 2 warning messages, 1st is "... field is...
7
by: Tom van Stiphout | last post by:
I want to indicate requiredness by setting the background color of the control. Ideally I would call a sub in Form_Load to just do its thing. Below is what I have so far, but I realize this is of...
1
by: sdavis1970 | last post by:
I am working on an Access 2002 database where one of the tables has five required fields making up the key. There is a form that is linked to this table which is used for adding new records. ...
7
by: samdev | last post by:
I have set up a table with a few fields that are set to required....all work fine except the fields that are Lookup from another table. What am I forgetting to set? Thanks!!
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.