By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,028 Members | 1,309 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,028 IT Pros & Developers. It's quick & easy.

Show/Hide fields based on info in a drop down field in access 2007

P: 2
Hello,

Can anyone help me figure out how to show/hide fields based on information entered in other fields in a form in Access 2007? What I'm looking to do is: When a selection from a drop down is chosen, hidden Yes/No fields (check boxes) that apply to that specific information appear and can be updated by users. The other Yes/No fields that do not apply to the chosen selection remain hidden.

I need to be able to do this for all the individual records in the form, but I'm having trouble figuring out how to do that.

I used the code below (as an example) in the After Update event of my drop down field to try to do this, but a.) I want all the yes/no fields hidden until a drop down selection is made and this doesnt do that and b.) it worked at hiding the inapplicable fields, but it hid those fields for all the other records and I couldnt get them back:

Private Sub Disposition_AfterUpdate()
Select Case cboRecordType
Case "Selection 1"
Me.[Field1].Visible = True
Me.[Field2].Visible = False
Me.[Field3].Visible = False
Me.[Field4].Visible = False
Case "Selection 2"
Me.[Field1].Visible = False
Me.[Field2].Visible = True
Me.[Field3].Visible = False
Me.[Field4].Visible = False
Case "Selection 3"
Me.[Field1].Visible = False
Me.[Field2].Visible = False
Me.[Field3].Visible = True
Me.[Field4].Visible = False
Case Else
Me.[Field1].Visible = False
Me.[Field2].Visible = False
Me.[Field3].Visible = False
Me.[Field4].Visible = True
End Select
End Sub

Any help would be very much appreciated because trying to figure this out is driving me nuts :)
Jun 17 '10 #1
Share this Question
Share on Google+
2 Replies


Expert 100+
P: 1,233
Are the checkboxes bound to a data item? If they are not, then you have a problem because unbound fields in the detail section of a form cannot be independently controlled. It is one of the most vexing things about Access.

If you have yes/no data items in the records and they are bound to the checkboxes on the screen, then what you are doing should work.

Jim
Jun 17 '10 #2

P: 2
Thanks! Yes, the yes/no fields are bound in a seperate table. Each of the 4 options I have in the drop down has 30+ yes/no items... so I created a seperate table for each of them and linked the ID #'s to the master table. The problem I'm having with the code I'm using is:
1.) It doesnt hide the fields until I select an option from the drop down. When a record is opened, and the "Disposition" field is blank, then all of my yes/no fields should be hidden
2.) Once I select an option, it hides the right fields, but it hides it for ALL of my records. I only want the option/hide&show to apply to each individual record
3.) After I select an option and the other fields are hidden, I cannot bring them back by deleting or chaning my fields. The code needs to be able to change as the selection changes

I thought about creating a subform for each option and referencing the applicable table, which will probably make this alot easier by only having to hide the subforms, but the problem I have with my subforms is it only displays the data fields in column format. I would like it to look exactly like the rest of the form, and be able to move the yes/no fields around if I want.

Here is what the code actually looks like:

Private Sub Disposition_AfterUpdate()
Select Case cboRecordType
Case "RTV"
Me.[subRTV].Visible = True
Me.[subScrap].Visible = False
Me.[subRecondition].Visible = False
Me.[subRelease].Visible = False
Case "Scrap"
Me.[subRTV].Visible = False
Me.[subScrap].Visible = True
Me.[subRecondition].Visible = False
Me.[subRelease].Visible = False
Case "Recondition"
Me.[subRTV].Visible = False
Me.[subScrap].Visible = False
Me.[subRecondition].Visible = True
Me.[subRelease].Visible = False
Case Else
Me.[subRTV].Visible = False
Me.[subScrap].Visible = False
Me.[subRecondition].Visible = False
Me.[subRelease].Visible = True
End Select
End Sub

Do you have any suggestions on how I can perfect the code I'm using, and/or change the way subforms are viewed (not in columns)?

I swear I'm not a complete novice! This is just more complex than anything I've ever done!
Jun 17 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.