Access Database

Hello,
I have a database created in Microsoft Access 2007. We have a form we use as the front interface and require people to fill out the fields within the form. However, not everyone fills out the form properly. Therfore, I wanted to make all the fields required and if they leave a certain field, I wanted Access to display the reason for not submitting the form. In other words, "Cannot complete because Last Name field is Blank"

However when I turned on the make field required option thru the table - design view, I received an error message of data integrity rules. I save the database and it appears to fuction worse than it did before.

How can I make the fields required and prompt user with the appropriate message if left blank ?
Tony4145Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
that will be annoying for the user, but if you want it
use the On Exit event of the control to check if the field is empty or not

another preferable way is to use  before update event of the form

private sub form_beforeUpdate(cancel as integer)
Dim sCtlName As String, NullCtl As String, i As Integer
sCtlName = ""
Dim ctl As Control
For Each ctl In Me.Controls
    If ctl.ControlType = acComboBox Or ctl.ControlType = acTextBox Then
            If IsNull(Me(ctl.Name)) Or Len(Me(ctl.Name)) = 0 Then
                sCtlName = sCtlName & ctl.Name & ";"
                Else
               
            End If
    End If
Next ctl
If Len(sCtlName) > 0 Then
    NullCtl = Mid(sCtlName, 1, InStr(sCtlName, ";") - 1)

    msgbox ""Cannot complete because " & NullCtl & " field is Blank"
    Me(NullCtl).SetFocus
    Exit Sub
    Else
   
End If

end sub
0
Rey Obrero (Capricorn1)Commented:
if there are fields that you don't require to be filled out, use the Tag property of the controls

place Required to the tag property of the fields that must be filled in and modify the codes above

private sub form_beforeUpdate(cancel as integer)
Dim sCtlName As String, NullCtl As String, i As Integer
sCtlName = ""
Dim ctl As Control
For Each ctl In Me.Controls
    If ctl.ControlType = acComboBox Or ctl.ControlType = acTextBox Then
        if ctl.Tag="required" then
            If IsNull(Me(ctl.Name)) Or Len(Me(ctl.Name)) = 0 Then
                sCtlName = sCtlName & ctl.Name & ";"
                Else
               
            End If
        end if
    End If
Next ctl
If Len(sCtlName) > 0 Then
    NullCtl = Mid(sCtlName, 1, InStr(sCtlName, ";") - 1)

    msgbox ""Cannot complete because " & NullCtl & " field is Blank"
    Me(NullCtl).SetFocus
    Exit Sub
    Else
   
End If

end sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SheilsCommented:
Hi Capricorn,

I guess you are assuming that the naming convention is using a single letter prefix. How about inserting the name that a lay person will refer the field as (eg: Age, Address, Date) in the tag and then use that to create the NullCtl string.

Guess a thought,Tony, no points required for this contribution.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You could also use the BeforeUpdate event of each Control to manage this, which can sometimes make a better UI experience for the user, depending on what your app does. This can sometimes allow you a bit more control over what message you display, since you can show different messages for the CustomerName field than you do for the CheckAmount field (examples, of course, but hopefully you get the idea).

Of course, you'd also need to implement cap's suggestions, since the BU control event only fires if the user actually interacts with that control.
0
Rey Obrero (Capricorn1)Commented:
sb9
<I guess you are assuming that the naming convention is using a single letter prefix.>

what makes you think about that?
0
Tony4145Author Commented:
Particular Capricorn and others, I am new to Access 2007 and I am maintaing a database that was already created. I view your responses because could you guys break this down more step for step intermideate level pls. Thx guys
0
Rey Obrero (Capricorn1)Commented:
the comment posted is just a simple code that utilize the form's beforeUpdate event..

open the form in design view
hit F4

this will show the Property sheet of the form

click on the Event Tab

you will see  Before Update, select Event Procedure and clikc on the (...)

that will take you to the VBA window where you will place the codes posted above...












0
SheilsCommented:
Capricorn,

Actually I did misread part of your code, it will give the entire name of the control. But still if if the developer is using prefixes in their naming convention or abbreviated control name it may be confusing to the lay person. Eg I run your code on a sample db (with all the control set to null) and I got:

sCtlName = txtDatePlayed;cboVisitingTeam;cboHomeTeam;cboLocation;txtGamesID;
NullCtl = txtDatePlayed

Using "Date Played" in the tag would be less confusing to the lay person.

Also:

Why use the instr  function if you only want to pick up the first null value control. I run the following your code:

Dim sCtlName As String, NullCtl As String, i As Integer
sCtlName = ""
Dim ctl As Control
For Each ctl In Me.Controls
    If ctl.ControlType = acComboBox Or ctl.ControlType = acTextBox Then
        If IsNull(Me(ctl.Name)) Or Len(Me(ctl.Name)) = 0 Then
            sCtlName = sCtlName & ctl.Name
            
            
            If Len(sCtlName) > 0 Then
                NullCtl = sCtlName
                
                MsgBox "Cannot complete because " & NullCtl & " field is Blank"
                
                Debug.Print "sCtlName = " & sCtlName
                Debug.Print "NullCtl = " & NullCtl
                
                Exit Sub
                Else
            
            End If
                       
        End If
    End If
Next ctl

Open in new window



 and got

sCtlName = txtDatePlayed
NullCtl = txtDatePlayed

My guess is that your intention was to strip the last semi colon. As I said I am not after any points in the question (I am merely trying to understand the thinking of a more accomplished expert than myself) so I will let you deal with this.

I also note that you have an extra inverted comma at the start of the msgbox string that needs to be deleted.


 
0
Rey Obrero (Capricorn1)Commented:

<Using "Date Played" in the tag would be less confusing to the lay person.>

why will you use  "Date Played" in the tag?

if the field/s is/are required, in the design view of the form select all the controls and enter in the Tag Property  Required  or any word you want and use that to check if the control have that value in the Tag property

       if ctl.tag="AnyWord" then
0
SheilsCommented:
Want I am suggesting is to avoid having a message like

Cannot complete because txtDatePayed field is Blank

or

Cannot complete because txt_DtePld field is Blank

The only way I can think of accomplishing that is to insert that lay name of the field in the tag. And yes if you only put the "lay name" in the tag of fields that are required then something like:

 If len(ctl.tag)>0 then

sCtlName = sCtlName & ctl.tag

should work.

0
Rey Obrero (Capricorn1)Commented:
sb9,
the codes posted is generic, you will never see those kind of messages in my applications.
and, this is not the only condition where i utilize the Tag property of the control.

you can use the codes the way you want it, hope you benefit from it.
0
SheilsCommented:
Capricorn,

I am surely benefiting from following this post. I will butt out know so as to not confusing the asker.

Cheers

SB
0
Rey Obrero (Capricorn1)Commented:
Tony4145,

i hope that you realized that your accepted comment was just copied from my post at http:#a34478055
0
SheilsCommented:
Those points should go to Capricorn. I was just making some side comments
0
coolleomodCommented:
Starting the auto-close procedure on behalf of the Question Author, to implement the recommendations from the participating Expert(s).

CoolLeoMod
Community Support Moderator
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.