In Microsoft Access, how would you create a pair of fields (call them 1A and 1B), such that 1A is a yes/no field and 1B is a text field which must have data if 1A=Yes and must be blank is 1B=no, as in the following:
**1A:** Did the mama see anything? [ ] Yes [ ] No
**1B:** If yes, what did the mama see? _______________________
Importantly, I would like it so that changes to either field cause the relevant conditions in the other field to be evaluated as well. Thus, if you initially set 1A = Yes, enter data in 1B, and then change 1A to No, then 1B will be blanked out. Likewise, I would like it to require an entry in 1B whenever 1A=Yes.
If I am not mistaken, the above means that the conditions must be evaluated at the Before Update event. How do I tell Access to do so and to do so at that time? Or maybe there is a better way to capture these formal requirements altogether?
Hi. I can think of a lot of ways of going about this sort of thing. One possibly good idea is to enforce the restriction as a table-level validation rule - under the properties for the table design view, you can enter something like this for validation rule:
[didsee]=No Or ([seewhat]<>"" And “seewhat” Is Not Null)
For the actual changes to the form, I would tend to work with other events than the before_update, but maybe that’s because I’m still not really that clear on what it does and how. Try something like this in the form module:
Private Sub didsee_Click()
If didsee.Value = True Then
seewhat.Enabled = True
Else
seewhat.Value = “”
seewhat.Enabled = False
End If
End Sub
Private Sub Form_Current()
If didsee.Value = True Then
seewhat.Enabled = True
Else
seewhat.Value = “”
seewhat.Enabled = False
End If
The data that you enter into an Access form that’s linked to a table does not actually update the table record until the update event for the form fires. This can be forced in the VBA code for the form, and the event will also fire when the user navigates to another record or attempts to close the form. So in theory, you can enforce data validation in the VBA code by putting validation requirements in the BeforeUpdate event and preventing the updated record from saving with invalid data.
999 times out of a thousand, it’s better to enforce data validation on the Jet side, and rely on the VBA only to make it easy for the user to see what they need to do to enter a valid record, exactly as the sample you posted did. The one thing I would add is some error handling–if the user in this case decides not to enter anything in seeWhat even after it pops up in their face, the form will throw a very user un-friendly error message when they exit and it tries to save data that doesn’t meet the validation rule.
It seems to me as though the Yes/no field 1a is (in terms of data structure design) redundant - it can be calculated based on whether or not there is anything in field 1b.
Couldn’t/shouldn’t it just be replaced by the single question: “What, if anything, did the mama see?”
OK, but I would maybe see if there was a way to make the checkbox an unbound control on the data entry form, just controlling the availability of the textbox bound to field b1 for the current record, otherwise you’re still going to get inconsistent data because of:
[ul]
[li]People checking the box and leaving b1 blank[/li][li]People checking the box, writing something in b1, then unchecking the box[/li][/ul]
It’s generally a bad idea to store the same data twice, or store a value that has a consistent calculated relationship with something else already stored. There may be exceptions to that, but this doesn’t seem like one of them.
If this is only one example of a list of other similar structures in the same table, then it starts to point at a normalization problem - and it might be better to store the assortment of attributes and values relevant to each record as multiple rows in a child table.
