Prev: laboratory data model design
Next: Compile error: Argument not optional
From: Lau via AccessMonster.com on 13 Apr 2010 17:17
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201004/1

From: Jeanette Cunningham on 13 Apr 2010 17:45
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" <u46349(a)uwe> wrote in message
news:a67c8ee5cb70d(a)uwe...
> 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
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201004/1
>


From: Lau via AccessMonster.com on 14 Apr 2010 09:45
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.

--
Message posted via http://www.accessmonster.com

From: Lau via AccessMonster.com on 14 Apr 2010 10:53
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.

--
Message posted via http://www.accessmonster.com

From: BruceM via AccessMonster.com on 14 Apr 2010 12:54
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 wrote:
>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.

--
Message posted via http://www.accessmonster.com

 |  Next  |  Last
Pages: 1 2 3 4 5
Prev: laboratory data model design
Next: Compile error: Argument not optional