If value is false, hide values in other fields (1 Viewer)

[Shfp986]

Hello,

I am trying to design a database while working from home...which is proving a little tricky!

The database is to do with event and dinner attendance. I am sure I have made quite a few mistakes. I have one table which records information about the "member". In this table I have details about their spouse or partner. On the form I have used YES/NO for attendance of certain dinners.

My query is pulling through the right information, but there is one major problem. Should the "member" not attend the dinner, but the spouse/partner attend, I do not seem to be able to hide information about the member. Do you have any suggestions on how this could be done?

Many thanks in advance! And apologies for my ignorance about Access.

 

[Dreamweaver]

If member the employee who added the record?

 

[theDBguy]

Hi. Without seeing your table structure, I can't tell if it needs to be adjusted. However, you might be able to use the IIf() function to hide the member data who did not attend. Hope that helps...

 

[Micron]

I have one table which records information about the "member". In this table I have details about their spouse or partner.

I would say that is your problem. If the spouse is a member, it is another member record. If they are not a member per se, then you need a table for associates. I'll bet you have both in this table, and more than likely, spread across fields rather than records (rows). If you didn't study normalization before starting out, that was your first boo boo.

 

[The_Doc_Man]

On the form I have used YES/NO for attendance of certain dinners.

IF you are talking about "hiding information" on the form then the usual method for doing this depends on how/where you select the YES or NO for each person. If that is a check box, for example, the best place is EITHER the check-box's _Click event or the _LostFocus event. The way to hide information is either to make the individual controls not visible or to disable the individual controls. Either way is totally legit and is a matter of appearance - which one you like more.

TextBox.Visible property (Access)

Office VBA reference topic

docs.microsoft.com

TextBox.Enabled property (Access)

Office VBA reference topic

docs.microsoft.com

CheckBox.Click event (Access)

Office VBA reference topic

docs.microsoft.com

Let's say for the sake of argument that you have a checkbox called "MbrAttend" - which will be either true or false, checked or not checked. And let's say that for sake of argument that you chose the _Click routine to do this. In your form, you would define a MbrAttend_Click event by selecting the control (while still in design mode). Make the properties visible (it's in the ribbon to do that). Select the Events tab from the properties control. Left-click in the box next to OnClick, select [Event Procedure]. Now click in the box [...].

You will be in the VBA code segment of the interface, more specifically in the Form's Class Module. The button wizard would build something similar to this:

Private Sub MbrAttend_Click()
    On Error Goto MbrAttend_Error
(blank space)
MbrAttend_End
    Exit Sub
MbrAttend_Error:
    (some error handling code)
    Resume MbrAttend_End
End Sub

You would add the following types of lines. Let's say you wanted to enable or disable some controls on the form. Remember that in this suggested design, the check-box is checked if someone DID attend! Add these lines in the blank space that I indicated in the _Click routine, one for each control you wanted to enable or disable. I'm making up names just to have a few to show how you would do it.

[MemberFirstName].Enabled = MbrAttend
[MemberLastName].Enabled = MbrAttend
[MemberIDNumber].Enabled = MbrAttend

This will have the effect of toggling the listed controls based on the state of the MbrAttend checkbox. Putting it in the _Click event makes it happen when you click inside the check box.