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

Lau wrote:
>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.

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

From: BruceM via AccessMonster.com on 16 Apr 2010 11:02
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 wrote:
>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.

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

From: Lau via AccessMonster.com on 16 Apr 2010 12:22
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.

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

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

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

From: Lau via AccessMonster.com on 16 Apr 2010 16:02
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.

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

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