Format textboxes/labels based on value in combo box

[secretary]

I am developing a membership database which includes individual members and couples.

The member entry form includes a "1st Member" and "2nd Member" section for personal information. When a combobox on the form is set to GENERAL/SINGLE, HONORARY/SINGLE, or SENIOR/SINGLE, I want the entire "2nd Member" section of the form to be either invisible or greyed out (disabled).

I've attempted this with conditional formattingon one of the textboxes in the "2nd Member" section as a test. I've even converted a few labels into textboxes that act like labels (since cond. formatting only works on controls) in case I would have to apply the same formatting later:

IIf (
[listMemberType] Like "*SINGLE", [lab2ndMember].Visible = False, [lab2ndMember].Visible = True )

This doesn't work, thought. I realize that conditional formatting may not even be the best approach. Any assistance would be greatly appreciated!

~Secretary

[June7]

I don't think conditional formatting in one control can set properties of another control. Use VBA. Set the the 2nd Member combo as invisible then in code for the AfterUpdate event of 1st Member combo have code to make 2nd visible or invisible per 1st selection. Also, same code in form OnCurrent event to set the 2nd combo when opening to existing record.

[secretary]

I've never used VBA before. What's the best resource, and how do I get started?

[June7]

Access Help has an introduction to coding. Search for VBA in the Help, should see result 'Introduction to Access Programming', start there.

And here is a reference book: Access™ 2007 Programming by Example with VBA, XML, and ASP by Julitta Korol, Wordware Publishing, Inc.

[boblarson]

And here's another free resource:

Access 2007 VBA

[secretary]

I'm having trouble with this. I've written the following event procedure:

Private Sub listMemberType_AfterUpdate()

If listMemberType.Value Like "*FAMILY" Then Show2ndMember

End If

End Sub

where

Private Sub Show2ndMember()

lab2ndMember.Visible = True

labSalutation2.Visible = True

comSalutation2.Visible = True

labFirstName2.Visible = True

txtFirstName2.Visible = True

labLastName2.Visible = True

txtLastName2.Visible = True

labBirthDate2.Visible = True

txtBirthDate2.Visible = True

End Sub

The thing is, I don't want the second half of the form visible when I select a control, I want it to become visible/invisible because of the value in that control (combo box).

[boblarson]

It would be much easier if you had your table structure properly normalized instead of having repeating fields.

But to use what you've got, you would need to call Show2ndMember in the form's On Current event as well as in the After Update event. But you need to modify Show2ndMember to have the If instead of in the after update event:

Private Sub listMemberType_AfterUpdate()
 Show2ndMember
End Sub

Private Sub Show2ndMember() 
Dim blnTF As Boolean
    If Me.listMemberType.Value Like "*FAMILY" Then  
        blnTF = True
    End If
  Me.lab2ndMember.Visible = blnTF 
  Me.labSalutation2.Visible = blnTF 
  Me.comSalutation2.Visible = blnTF 
  Me.labFirstName2.Visible = blnTF 
  Me.txtFirstName2.Visible = blnTF 
  Me.labLastName2.Visible = blnTF 
  Me.txtLastName2.Visible = blnTF 
  Me.labBirthDate2.Visible = blnTF 
  Me.txtBirthDate2.Visible = blnTF 
 
End Sub 

[secretary]

Wow thanks that helped!

Since I'm dealing with a list, I'm curious if there is a way to Show2ndMember when I select the right MemberType just by clicking the arrows i.e. Is there an event where I can call Show2ndMember when the up/down arrows on the list box are clicked? At this point, I have to either click on the list field or arrowkey through the choices to show/hide 2nd Member info.

You mentioned repeated fields, and I actually thought about that as I was typing up the code: is there any way to change a single property for multiple controls at once so that the code's a bit more elegant?

[boblarson]

Wow thanks that helped!

Since I'm dealing with a list, I'm curious if there is a way to Show2ndMember when I select the right MemberType just by clicking the arrows i.e. Is there an event where I can call Show2ndMember when the up/down arrows on the list box are clicked? At this point, I have to either click on the list field or arrowkey through the choices to show/hide 2nd Member info.

You could use the listbox's KeyDown event (which means when a key is pressed) and use

If KeyCode = 38 or KeyCode = 40 Then
   Show2ndMember
End If

You mentioned repeated fields, and I actually thought about that as I was typing up the code: is there any way to change a single property for multiple controls at once so that the code's a bit more elegant?

Not really. You can use code to shorten up the code by setting the tag property of the control and then iterating through it, like setting the tag of the fields you want to display if 2nd member is selected you can set the tag to something like "2ndMem" without the quotes and then use

Dim ctl As Control
Dim blnTF As Boolean

    If Me.listMemberType.Value Like "*FAMILY" Then  
        blnTF = True
    End If
 
For Each ctl In Me.Controls
   If ctl.Tag = "2ndMem" Then
      ctl.Visible = blnTF
   End If
Next

[secretary]

You could use the listbox's KeyDown event (which means when a key is pressed)

I'm actually interested in the up/down arrow buttons that are part of the listbox, not the arrow keys.

Thanks for the tag tip though!

[boblarson]

I'm actually interested in the up/down arrow buttons that are part of the listbox, not the arrow keys.

Thanks for the tag tip though!

Can you post a screenshot of what you are talking about with up/down buttons? I think it is just the scroll bar you may be talking about and there is nothing you can do with those.

[secretary]

You're right, it was just the scroll bar. The list is so small that it only appears as two arrows because the bar is so tiny. That's fine.

In an unrelated note, I'm trying to set the DefaultValue of a textbox with a variable

Private Sub AutoFillLastName()
Dim strLastName1 As String
strLastName1 = txtLastName1
txtLastName2.DefaultValue = strLastName1
End Sub

I keep getting the #Name? error. I have a feeling it has something to do with improperly defined strings. The correct name appears in the property sheet for txtLastName2 under Default Value, but it only displays properly in the form when I add "" around the name in the property sheet. What's the code missing?

[boblarson]

Private Sub AutoFillLastName()
Dim strLastName1 As String

strLastName1 = Me.txtLastName1
Me.txtLastName2.DefaultValue = strLastName1
End Sub

However, setting the default value isn't going to help you here. Once the record has been started, it will only be applicable to the NEXT record that gets started.

[secretary]

Thanks, Bob!

[secretary]

Dim ctl As Control
Dim blnTF As Boolean
    If Me.listMemberType.Value Like "*FAMILY" Then  
        blnTF = True
    End If
 
For Each ctl In Me.Controls
   If ctl.Tag = "2ndMember" Then
      ctl.Visible = blnTF
   End If
Next

Quick question about this piece of code. If I were to append additional tags to any of the controls, how would that change the line above. In this case, I now have

2ndMember;UsualBackColor=16777215

as the tag. Thanks!