Absolute Field Validation

[Lau via AccessMonster.com]

My main form is based on tbl_household_info which stores contact information
and subform is based on tbl_family_member which stores name, gender,
birthdate, etc. On the subform, I have a field called relationship that can
be any of these values:

Head of HH
Husband
Wife
Son
Daughter

What I would like to happen is that one of the family members must be set to
Head of HH before letting the user go to another household. In other words,
when pressing the backward or forward navigation button of the main form, it
needs to check to make sure Head of HH is selected for current family. So
far, I have tried the codes below, but they do not work. How can I accomplish
this task? Thanks.


*** AA is the code for Head of HH

Private Sub Form_Current()
If Me!house_num <> Null Then
If DLookup("[household_id]", "tbl_family_member", "[household_id] = " &
Me!sbf_family!household_id & " AND Me!sbf_family!relationship_id = 'AA'")
Then
'Do nothing
Else
'if no household is chosen, prompt to select one
MsgBox ("You must select a family member to be a Head of Household."),
vbOKOnly
Cancel = True

End If
End If
End Sub

 

[Jeanette Cunningham]

Put that code on the BeforeUpdate event of the form.
If you have a close button, put this code on the close button's click event.

If Me.Dirty = True Then
Me.Dirty = False
End If

The 3 lines of code above will make the before update event run when the
close button is clicked.

Note: I haven't checked the code that begins with

If Me!house_num <> Null Then.......

I am assuming that your code has no errors.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Lau via AccessMonster.com]

I do not have a close button on the form.

I tried your suggestion about moving the code from OnCurrent to BeforeUpdate
and it still does not work. I don't know if this sparks anymore ideas, but
I'm using Access 2003 with 2000 file format.

Thanks.

 

[Lau via AccessMonster.com]

When I put the statement below on BeforeUpdate of the subform, I got an error
message
If Me!house_num <> Null Then.......

I then replace with
If Forms!frm_household_info.house_num <> Null Then

There's no more error message; however, it does not work as I have hoped.

Thanks.

 

[BruceM via AccessMonster.com]

Try:
If Not IsNull(Me.house_num) Then ...

Also, you aren't checking for a value. If there are no records that match,
DLookup returns Null. Your expression as written is essentially "If Null
Then ..." when there is no head of household, or "If 125 Then ..." if there
is a head of household for household_id 125. Neither can be evaluated.

In such a situation I generally use DCount and check whether the result is
greater than 0:

If DCount("[household_id]", "tbl_family_member", "[household_id] = " &
Me!sbf_family!household_id & " AND Me!sbf_family!relationship_id = 'AA'")

0 Then...

You could use DLookup, with something like this:

If IsNull(DCount("[household_id]", "tbl_family_member", "[household_id] = " &
Me!sbf_family!household_id & " AND Me!sbf_family!relationship_id = 'AA'")
Then ...

Did you compile the code (Debug >> Compile)? I suspect not, because I don't
see how an If statement without "Then" would have compiled. You should
always compile code before trying to run it. Also, be sure Option Explicit
is at the top of the code window, directly below Option Compare Database. To
be sure Option Explicit always appears, in the VBA editor go to Tools >>
Options. Click the Editor tab, and check "Require variable declaration". I
don't think undeclared variables are a problem here, but it's best to avoid
them.

 

[Lau via AccessMonster.com]

I took your suggestions and used Dcount as indicated. However, I was not
able to trigger BeoreUpdate event. I moved the code to OnCurrent and now
with Dcount statement highlighted, I receive an error "Run time '2001'. You
canceled the previous operation."

Thanks.

 

[Jeanette Cunningham]

In Access, the before update event triggers when you close the form or move
to a different record or create a new record.
However, if the form is not dirty, before update will not do anything to
ensure that there is a head of household.
There is a bug when you put your own close button the form - you need to use
the code for
If Me.Dirty = True Then
Me.Dirty = False
End If

to trigger the before update event when user clicks your close button.

Step1 is to make sure your DCount code is working correctly.
Step 2 is to put that code in the before update event and make sure that the
before update event is triggered when the form closes or moves to a
different record, or user clicks out of subform on to main form.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Lau via AccessMonster.com]

Below are my latest codes on the main form. They do not seem to trigger the
event. Anybody spots any mistake? Thanks!

**********************
BeforeUpdate event
**********************
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not IsNull(Me!house_num) Then
If DCount("[household_id]", "tbl_family", "[household_id] = " & Me!
sbf_family!household_id & " AND Me!sbf_family!relationship_id = 'AA'") > 0
Then
MsgBox ("Testing - You are dandy!"), vbOKOnly
Else
'if no household is chosen, prompt to select one
MsgBox ("You must select a family member to be a Head of Household."),
vbOKOnly
Cancel = True
End If
End If
End Sub

****************
OnDirty event
****************
Private Sub Form_Dirty(Cancel As Integer)
If Me.Dirty = True Then
Me.Dirty = False
End If
End Sub

 

[BruceM via AccessMonster.com]

If you update the data on the form, then exit the record by using the built-
in navigation buttons, attempting to close the form, or by other means,
Before Update should run. However, it will only run if the data have been
updated. If you don't change anything, there is nothing to update.

If you have your own Close button or navigation buttons, use Me.Dirty = False
as the first line of code. There is no point to putting the code in the
form's Dirty event. I don't use that event much, but I believe it runs as
soon as you "dirty" the record (by typing, for instance). I think that if
you save the record in the form's Dirty event you will do so after every key
stroke, combo box selection, etc. Probably not what you want.

If you use the built-in Access buttons only there is really no place for the
Save code (at least not for record navigation), but then again it is not
necessary if using the built-in buttons. It isn't always necessary when
using your own navigation and close buttons, but there are some bugs, so I
routinely force a save.

Jeanette showed one way of doing this:
If Me.Dirty = True Then
Me.Dirty = False
End If

I tend to use:
Me.Dirty = False

I have heard that Jeanette's way is faster, but whatever difference there is
between the two is very slight.

Below are my latest codes on the main form. They do not seem to trigger the
event. Anybody spots any mistake? Thanks!

**********************
BeforeUpdate event
**********************
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not IsNull(Me!house_num) Then
If DCount("[household_id]", "tbl_family", "[household_id] = " & Me!
sbf_family!household_id & " AND Me!sbf_family!relationship_id = 'AA'") > 0
Then
MsgBox ("Testing - You are dandy!"), vbOKOnly
Else
'if no household is chosen, prompt to select one
MsgBox ("You must select a family member to be a Head of Household."),
vbOKOnly
Cancel = True
End If
End If
End Sub

****************
OnDirty event
****************
Private Sub Form_Dirty(Cancel As Integer)
If Me.Dirty = True Then
Me.Dirty = False
End If
End Sub

 

[Lau via AccessMonster.com]

Yes, I use the default built-in navigation buttons. Also, I update records
and page back-and-forth, but still it just goes through one record to another
like there is no BeforeUpdate event. As you can see, I even put the
statement

MsgBox ("Testing - You are dandy!"), vbOKOnly

to test for condition that meets the criteria. Nothing happens. I find it
hard to believe myself.

Thank you.

 

[BruceM via AccessMonster.com]

ARe you placing the code in the correct form's Before Update event? If the
HH field in question is in the subform, that is where the Before Update code
needs to be.

Place the following in the Before Update code of the form and the subform:

MsgBox Me.Name & " is updating"

I too find it hard to believe the Before Update event is not running when you
update a record. If it truly is not, try creating a new form. You can use
autoform, or otherwise make a quick and simple form, and test the Before
Update event there.

 

[Jeanette Cunningham]

Here is an issue with your code:

****************
OnDirty event
****************
Private Sub Form_Dirty(Cancel As Integer)
If Me.Dirty = True Then
Me.Dirty = False
End If
End Sub



This code:
If Me.Dirty = True Then
Me.Dirty = False
End If

Goes in the form's before update event.
It will not work for you when you put it in the Dirty event.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Marshall Barton]

Here is an issue with your code:

****************
OnDirty event
****************
Private Sub Form_Dirty(Cancel As Integer)
If Me.Dirty = True Then
Me.Dirty = False
End If
End Sub

This code:
If Me.Dirty = True Then
Me.Dirty = False
End If

Goes in the form's before update event.
It will not work for you when you put it in the Dirty event.

But, but, but the form's BeforeUpdate event will not be
triggered unless the form is dirty, so dirty is guaranteed
to be true. I.e. that code in the form's BeforeUpdate event
is redundant and can be omitted.

 

[Lau via AccessMonster.com]

Because I do not have a customed Close button, I removed OnDirty event. Then
I put BeforeUpdate event codes on the subform and at the same time I
discovered a mistake in Dcount statement. Notice I had the part "Me!
sbf_family!" inside the quotation in second part of the condition below.

If DCount("[household_id]", "tbl_family", "[household_id] = " & Me!sbf_family!
household_id & " AND Me!sbf_family!relationship_id = 'AA'") > 0 Then

I should have had this.

If DCount("[household_id]", "tbl_family", "[household_id] = " & Me!
household_id & " AND relationship_id = 'AA'") > 0 Then

Now it's triggering the BeforeUpdate event, but it happens to every family
member. I only want to check for Head of HH when I go to another family.

Thanks.

 

[Lau via AccessMonster.com]

In the relationship field of the subform, I have an OnChange event to reset
all family members' relationship when a new family member is set to Head of
HH.

Thanks.

 

[BruceM via AccessMonster.com]

Argh! <smacking hand to forehead>

I should have seen that. The Me prefix only works in VBA, not within quotes
in a SQL string, which is essentially what you have with the Where condition
in DCount or any domain function (DLookup, etc.).

Are you getting the test message box when the HH has been previously selected?
If so, you could check for equivalency to 0:

If DCount("[household_id]", "tbl_family", "[household_id] = " & Me!
household_id & " AND relationship_id = 'AA'") = 0 Then
MsgBox "You must select a family member to be a Head of Household."
End If

You could also keep the expression you have, and put nothing in the Then:

If DCount("[household_id]", "tbl_family", "[household_id] = " & Me!
household_id & " AND relationship_id = 'AA'") = 0 Then
' Do nothing
Else
MsgBox "You must select a family member to be a Head of Household."
End If

However, in either case users will be pestered with a message until they
create the HH record.

If it was me I would do something like this in the subform Current event:

If Me.Recordset.RecordCount = 0 Then
MsgBox "Please enter the Head of Household name first",vbInformation
Me.Relationship_id.DefaultValue = "AA"
End If

This should set Relationship_id to AA for the first subform record in each
family group. The user can change it, but the reminder should go away if HH
is selected.

It may be possible to check the subform records all at once in the subform
control's Exit event, but I would have to experiment with that, and I really
can't take the time just now.

Because I do not have a customed Close button, I removed OnDirty event. Then
I put BeforeUpdate event codes on the subform and at the same time I
discovered a mistake in Dcount statement. Notice I had the part "Me!
sbf_family!" inside the quotation in second part of the condition below.

If DCount("[household_id]", "tbl_family", "[household_id] = " & Me!sbf_family!
household_id & " AND Me!sbf_family!relationship_id = 'AA'") > 0 Then

I should have had this.

If DCount("[household_id]", "tbl_family", "[household_id] = " & Me!
household_id & " AND relationship_id = 'AA'") > 0 Then

Now it's triggering the BeforeUpdate event, but it happens to every family
member. I only want to check for Head of HH when I go to another family.

Thanks.

 

[BruceM via AccessMonster.com]

I'm not following this. You could check to see there is only one HH, but you
would do that in the subform's Before Update event. Not sure what OnChange
event you are referring to. How are you resetting? Does it work as intended?

 

[Lau via AccessMonster.com]

I had OnChange event some time ago and it’s been working fine. I just want
to mention so you are aware of it. What it does is when a family member is
assigned to Head of HH, the relationships for all family members reset to
blanks, except the one with Head of HH.

As now, the event requires all family members to Head of HH (assuming that I
remove the OnChange event). Yes, I’m looking for an event that checks
â€subform records all at once†to make sure there is only one Head of HH per
family.

Thanks.

 

[BruceM via AccessMonster.com]

The only thing I can think of as to why the validation code does not work as
expected is that you do this:

Dim strWhere as String
strWhere = "[household_id] = " & Me.household_id & _
" AND relationship_id = 'AA'"
Debug.Print strWhere

If DCount("[household_id]", "tbl_family", strWhere) = 0 Then
' Do nothing
Else
MsgBox "You must select a family member to be a Head of Household."
End If

First, be sure you can compile the code.

After running the code, press Ctrl + G to open the immediate VBA Editor
window. You should see strWhere printed. The code won't work if
Household_id is a text field. It sounds as if you are sure you are storing
AA for Head of Household, but it would be worth checking the string.

As for checking all records at once, you could try the subform control's Exit
event. I don't know if it will work, but it's all I can think of.

 

[Lau via AccessMonster.com]

BeforeUpdate event does get triggered and it does to every family member,
which is not what I want. I will take a look at Exit event.

Thanks.