Check fields when user changes tabs

[Lloyd]

I have a form with 7 tabs. On my second tab is where the user enters
information to a person block that has name, address, date of birth, etc. On
this form it can have multiple person records.

Each person record has a person type and a case type. Those 2 fields are
critical to determining what other fields within that person type needs to be
completed.

For example if persontype = victim and casetype = murder, then the user must
complete field5, field6, and field7, but only within that record, not the
other person records.

What frequently happens is the user doesnt complete all the fields they need
to on the person block. They either go onto a new person block or they
change tabs.

What I'm trying to do is that if the user changes tabs and something is
missing from a person block, I need to check each person block but based on
the person type and case type fields and if the user has a null value in one
of the required fields, the code will switch back to tab 2 and highlight the
fieds in red for that specific record.

In my first attempts of writing this code, I found that the background color
gets changed in for example the last name field for all the records, and not
just the record I want.

I'm really kinda lost on how to do this, it sounds complex and maybe it
is..but it seems like I should be able to somehow look at each record and
apply a specific set of rules to that record based on the persontype and
casetype and then say which fields were missed and highlight those fields for
that record only, then check the next record and do the same.

Is this possible? Any suggestions on how to proceed?

thanks!

 

[TonyT]

Hi Lloyd,

Not sure I fully understand what you are trying to accomplish here, you say
you can have multiple 'person blocks' on your tab. This suggests to me that
this tab contains a subform in continuous layout, or more than one subform if
not continuous.

If you do have more than one subform, you can use the subforms before update
event to check all the data entered and prompt the user to add the missing
fields, preventing them from leaving it until the data is entered or it's
cancelled.

If the subform is a continuous subform, you can again use the before update
event to prevent the user from leaving the form until it's been filled in to
your satisfaction, again by testing the fields data depending on what
combinations are required. You won't be able to just change the fields colour
on a continuous form without all the fields changing at the same time unless
you get creative with the conditional formatting property, but this will
still allow people to exit the form without completing all the fields if all
you are doing is changing a background colour.

If the tab control doesn't have any subforms on it, then you can use the tab
controls on change event (the whole tab control, not just the page) and again
prevent the user from changing tabs until the required fields have been
filled in.

If you describe the actual form and tab layout, and what fields you have
that must be filled in and in what combination, we may be able to give more
specific help

TonyT

 

[Lloyd]

TonyT,

Sorry for the confusion, yes on the person tab, the form is continuous and
it is a subform. But not all fields are required. What happens is a user
will switch to the next tab to do the data entry and misses some of the key
fields. I want to stop the user from changing tabs. On my first tab (which
is not a subform or continuous) I use the below code. This code works fine,
but when I tried to adapt it to the person tab (tab 2), It doesnt work well
in that it highlights the same field for each continuous record and the rules
for each record may be different based on the persontype and casetype fields
the combination of persontype and casetype dictate which fields are required
and which ones are not.

If TabCtl4 > 0 Then

Dim CkCtls As New Collection

CkCtls.Add "DRNo"
CkCtls.Add "DateOccd"
CkCtls.Add "TimeOccd"
CkCtls.Add "CaseType"
CkCtls.Add "Dispo"
CkCtls.Add "Location"
CkCtls.Add "City"
CkCtls.Add "Division"
CkCtls.Add "Detective1"

Dim var As Variant

For Each var In CkCtls
If IsNull(Me(var)) Then
'Me(var).BackColor = 11468799 ' light yellow

MsgBox var & " Field MUST be completed", vbCritical +
vboko, "You left fields empty"
Me(var).BackColor = 255 ' Red
Me!TabCtl4.Value = 0

Else
'MsgBox "no fields are null"
'Me(var).BackColor = 16777215 ' white
End If
Next


Else
End If

 

[TonyT]

The problem is that each row of the continuous form shares the same controls,
hence they all change together.

You will have to apply the checks in the beforeupdate event of the subform.
This will be triggered each time a user tries to leave an individual record
(a row) IF they have updated it. You can use conditional highlighting, but it
sounds like you will run out of options (3 and a default per control). You
can still use the tab controls on change or click event to confirm the
correct data is added, but you will have to check the recordset for the
correct values rather than just control names.

Is the subform a linked child of the parent form showing multiple records
from a child table? If so you can check for the existance of all the correct
fields in the main forms before update event, but this has some limitations,
such as still not being able to highlight the erroneous record on the subform
without much coding.

From what I've read so far, it sounds like a re-design of the form or
possibly the underlying data structure would be an better all round approach
than having to implement so much code to acheive the desired results.

If you can detail what tables are involved, which fields are required under
what circumstances we may be able to offer more specific suggestions,
although that might be better started as a new thread as many people won't
read down a long thread too far.

TonyT