Absolute Field Validation
in [Forms]
|
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 |