Enforcing data entry

[deere8585]

Dear all,

I am a fairly new user to access, I think I understand the very basic principles but this is the first time I have tried to put them into practice!

I am trying to build a customer complaints data base-The main table holding data on 'active' complaints with an Entry, Analysis & Follow up form for various levels of user to enter data into this table.

The very end process in this would be for the person responsible for complaints to mark a record as completed and hence 'inactive'.

To this end in the Follow up form I have a complaint status field which in the ative complaints table is Yes/No format where the display control for the form is a check box.

What I am looking to achieve is that when someone attempts to click in the check box to mark the record complete if a number of other fields within the table have no value (e.g. completion date/employee who delt with complaint etc) then a message box will appear stating which fields need data entering into them & leave the user unable to mark the record as complete until these fields have the required values.

I thought perhaps that by building an event into the check box properties (in the on click event) may be one way to solve the issue-however my knowledge of expression building/vba-and the general language of these commands is nill!

Could anyone please spare a moment to help me?

Many thanks for any/all help received

 

[RuralGuy]

I think you should keep the CheckBox DISABLED until all of your conditions are met. You can check these conditions in a procedure of its own and call it from the Current event of the form and the AfterUpdate event of each of the controls involved in the condition.

 

[deere8585]

Right so if I understand correctly I have now switched the data property of the check box 'Enabled=No'.
I then get a bit lost on what you mean by calling a procedure from the current event of the form.
And then finally would have to put in an After Update event into all the fields that I wish to be completed before the status check box would enable?

Am I on the right lines here? If so could you please break down into laymans terms how I would go about this?

Many thanks for your patience in teaching a novice

 

[RuralGuy]

First create a Function in the class module of your form (the code module of the form) that checks all of your involved control for proper completion and return True of complete and False if incomplete.

 

[deere8585]

Right after 2mintues of searching (always best to look before you ask being my new motto) I think I have found what you the event controls you mean.

In the form properties box-events tab-On Current
&
In the fields I wish to be conditional properties box-event tab-After update?

So if I have the property of the status field check box Enabled=No I now need to build a procedure/event to check that these conditions are met-am I correct in these assumptions?

If so could someone please detail how I would write said procedure?

 

[deere8585]

Right sorry to be a bore but I think I understand the principle of what you are saying but the knowledge of how to do that fails me-how would I go about creating a function in the code module of my form?

 

[RuralGuy]

Pick a clear spot in the code and start typing:
Private Function WhatEverNameYouWant() As Boolean

 

[deere8585]

Opened the form properties-event tab-on current clicked on event procedure and chose Microsoft Visual Basic and entered-

Private Sub Form_Current()
Private Function Form_Check() As Boolean

Forgive my lack of understanding but what will this do for me? or do I need to add more to make this a function? VBA skills non existant at present but would love to learn!

 

[RuralGuy]

You do *not* want:
Private Sub Form_Current()
in the module right now. Delete it.

 

[RuralGuy]

After you type:
Private Function Form_Check() As Boolean
and hit a return then Access should put in:
End Function

 

[deere8585]

Right I'm with you so far-is that the end or is there more to add before I exit this function?

 

[RuralGuy]

What are the name of the controls that *must be completed and what would their verification look like in english.

 

[deere8585]

The 2 I would like initially would be-

Action Taken field-this is a date (dd/mm/yyyy) field-the verifcation would be that the user would have to enter a date that is equal to or greater than the date in a sperate date field (that records the date of the initial record entry)

Action Taken By field-This is a drop down box that lists employee names from the employees table-the vertification would be that the user would have to select a name from the list.

Once again may thanks for any/all help received

 

[RuralGuy]

Within the function put:

If Len([Action Taken By] & "") = 0 Then
   MsgBox "You must select a name"
   ThisFunctionName = False
   Me.[Action Taken By].SetFocus
End If

...for one of the tests.

 

[deere8585]

Thank you for that- I thought that an on click event procedure with an If function would be a more basic way of achieving the desired effect-however with my very very limited knowledge on VB & code I'm struggling to achieve this- my if so far looks like this

Private Sub Status_Check()
If (Me.Action_Taken_Date) <= (Me.RecordDate) Or Null Then
MsgBox "Please Enter Action Taken Date"
Else: End If
End Sub

This seems to have no effect-when I attempt to click the check off box for a record with no value it allows this with no message box appearing

Could you please point out the mistakes in my code or if this is just not possible to achieve via this method?

 

[RuralGuy]

Try this for size:

Private Sub Status_Check()
   If Me.Action_Taken_Date <= Me.RecordDate Or IsNull(Me.Action_Taken_Date) Then
      MsgBox "Please Enter Action Taken Date"
   End If
End Sub

 

[deere8585]

Thanks that seems to work-when I attempt to 'click' the status check box a Message Box appears with the appropriate message.

However when I click ok on the message box the status check box is still marked complete.

Is there some additional code I could add to prevent this-for example you attempt to mark the check off box as complete & if the If statement criteria are not met then the focus is set to the action_take_date field whilst leaving the status check box un-marked?

 

[RuralGuy]

How are you calling the Status_Check() code?

 

[deere8585]

As an On Click Event-would it be better another way perhaps?

 

[RuralGuy]

The click event is fine but if your conditions are met then you need to set the controls the way you want them in the same code.