New fields based on combo box selection

[TerdFerguson]

Hi everybody! I'm a total newbie at access, just opened it for the first time yesterday so bear with me. I tried a search for my answer and found similar topics but couldn't find the exact answer.

I'm using 2002 Access on XP. In a form, I created a combo box with four different choices. Depending on the selection made I would like a corresponding field to appear. My four selections in the combo box are "Realtor", "Driving", "Mailing", or "Other". If "Realtor" is selected I want a field to appear that asks for the realtor's name and another field that asks for the realtor's phone number. If "Driving" is selected, I want a field to ask for a date. Same for if "Mailing" is selected. If "Other" is selected, I'd like the field to appear where you can enter text to explain.

I have a feeling that the answer lies somewhere under "properties" for my combo box but there is so much in there and it's all over my head. Thanks for any help!

[MGrowneyARSI]

in your properties set all of your fields to visible no then for your combo box on the after update even in properties again you'll need a good amount of code like this
If me.ComboName = "Realtor" Then
me.name of textbox.visible = True
else
if me.ComboName = "Whatever" Then
me.Whatever.Visible = True
End If
Hope thats what you were trying to do

[TerdFerguson]

Thanks for the suggestion!

I tried it but I couldn't change any of the fields to visible = no. I get an error message that reads "You can't hide a control that has the focus."

What does that mean?

[ajetrumpet]

Ferguson,

First of all, the code reads

.Visible = False

If I were you, I would write nested "If, Then, Else" statements, like the ones already posted, for all four of your choices in the combo box. I would tie each one of those statements to reference the separate text boxes on your form and use the ".Visible = Condition" property in the nested statements to display the appropriate text box on the "AfterUpdate" event of the combo. This should work fine if your typing the code in the correct manner. I'm not sure why you received the error message that you did, but it would lead me to believe that any statement you've written in the module thus far does not contain the correct syntax (sequence of script). Maybe you could post it??

[missinglinq]

I tried it but I couldn't change any of the fields to visible = no. I get an error message that reads "You can't hide a control that has the focus."

Where were you trying to set the visibilty? The instructions said to set this in the properties box! The control couldn't have focus here! In Design View, select all of the textboxes you want to control the visibilty of. Goto Properties - Form and set the Visibility Property to No. Then try your code in the combobox's afterupdate event.

You'll also have to save one of these 4 designations to each record, and use that (in the Form_Current event) to control visibilty when you return to a record.

We really do need to see the code you've got so far.

BTW, ajetrumpet, Yes/No or True/False will both work with .Visible.

Welcome to TheScripts!

Linq ;0)>

[TerdFerguson]

Thanks guys. Sorry I wasn't clear on my earlier post. I didn't try to write any code to turn visible to off. I went to properties for the field as outlined above. The mistake I made was that I wasn't in Design View. Doh! A beginners mistake for sure. I figured it out on my own last night toying around with it.

Ok, after that was fixed, I tried to write some code using the code provided in the first response as a guideline. It worked but there are some bugs that I noticed right away.

1. After making a selection from the combo box in one record, the resulting field appears as it's supposed to, but it shows up in all records instead of just the current one.

2. If I change the selection in the combo box, the field from the earlier selection remains instead of going away.

I'm not a code writer, so I need some help with that! Many thanks!

edit: oops, almost forgot to post my code that I have so far. Here ya go:

Private Sub Property_Found_By_AfterUpdate()
 
If Me.Property_Found_By = "Realtor" Then
Me.Realtor_Name.Visible = True
Me.Realtor_Phone_Number.Visible = True
 
ElseIf Me.Property_Found_By = "Driving" Then
Me.Driving_Date.Visible = True
 
ElseIf Me.Property_Found_By = "Mailing" Then
Me.Mailing_Date.Visible = True
 
ElseIf Me.Property_Found_By = "Other" Then
Me.Explanation.Visible = True
End If
 
End Sub

[TerdFerguson]

I fixed #2 with this code:

Private Sub Property_Found_By_AfterUpdate()
 
If Me.Property_Found_By = "Realtor" Then
Me.Realtor_Name.Visible = True
Me.Realtor_Phone_Number.Visible = True
Me.Driving_Date.Visible = False
Me.Mailing_Date.Visible = False
Me.Explanation.Visible = False
 
ElseIf Me.Property_Found_By = "Driving" Then
Me.Realtor_Name.Visible = False
Me.Realtor_Phone_Number.Visible = False
Me.Driving_Date.Visible = True
Me.Mailing_Date.Visible = False
Me.Explanation.Visible = False
 
ElseIf Me.Property_Found_By = "Mailing" Then
Me.Realtor_Name.Visible = False
Me.Realtor_Phone_Number.Visible = False
Me.Driving_Date.Visible = False
Me.Mailing_Date.Visible = True
Me.Explanation.Visible = False
 
ElseIf Me.Property_Found_By = "Other" Then
Me.Realtor_Name.Visible = False
Me.Realtor_Phone_Number.Visible = False
Me.Driving_Date.Visible = False
Me.Mailing_Date.Visible = False
Me.Explanation.Visible = True
End If
 
End Sub
 

I have no clue on how to fix #1 though. Maybe the answer is what missing said:

"You'll also have to save one of these 4 designations to each record, and use that (in the Form_Current event) to control visibilty when you return to a record."

I don't know what that means though. Can you give a detailed explanation?

Just to be clear, my problem now is the appearance of the fields as I move from record to record. The combo box selection is correctly saved in each record as I leave and come back but the resulting fields are not. For example, if I choose "Driving" from the combo box in record #1 then the field "Driving Date" appears. If I then move to record #2 the field "Driving Date" is still visible even though "Driving" was never selected from the combo box in this record. I hope I explained that well enough. Thanks!

[TerdFerguson]

Just wanted to bump up my question just in case it had fallen through the cracks over the weekend.

[missinglinq]

OK! You say that Property_Found_By is being saved for each record, so now, as you move from record to record, you need to check its value and set the visbility of fields accordingly.

Private Sub Form_Current()
If Me.Property_Found_By = "Realtor" Then
Me.Realtor_Name.Visible = True
Me.Realtor_Phone_Number.Visible = True
Me.Driving_Date.Visible = False
Me.Mailing_Date.Visible = False
Me.Explanation.Visible = False
 
ElseIf Me.Property_Found_By = "Driving" Then
Me.Realtor_Name.Visible = False
Me.Realtor_Phone_Number.Visible = False
Me.Driving_Date.Visible = True
Me.Mailing_Date.Visible = False
Me.Explanation.Visible = False
 
ElseIf Me.Property_Found_By = "Mailing" Then
Me.Realtor_Name.Visible = False
Me.Realtor_Phone_Number.Visible = False
Me.Driving_Date.Visible = False
Me.Mailing_Date.Visible = True
Me.Explanation.Visible = False
 
ElseIf Me.Property_Found_By = "Other" Then
Me.Realtor_Name.Visible = False
Me.Realtor_Phone_Number.Visible = False
Me.Driving_Date.Visible = False
Me.Mailing_Date.Visible = False
Me.Explanation.Visible = True
End If
 

Linq ;0)>

[TerdFerguson]

Wow, that was a quick response! Thank you missing. I implemented your code and it worked like a charm! Two more quick questions have arisen:

1. What about when the "Property Found By" Combo Box is empty or null? I need to write some code so that in this case, there are no additional fields shown. As it stands now, if I switch the comb box to empty, the fields from the last choice made remain.

2. When you open up that little window where you can write code, there is a dropdown menu in the upperleft (all the fields, form etc) and then a dropdown menu in the upper right that contains all the possible events, I'd like to learn more about all the stuff in these dropdown menus. I never would have been able to find that form_current that you suggested on my own. Perhaps there is a tutorial that explains in detail all the possible choices in these dropdown menus? What each one does etc.

[TerdFerguson]

I tried this code for when the combo box is empty but it didn't work:

ElseIf Me.Property_Found_By = "" Then
Me.Realtor_Name.Visible = False
Me.Realtor_Phone_Number.Visible = False
Me.Driving_Date.Visible = False
Me.Mailing_Date.Visible = False
Me.Explanation.Visible = False

[missinglinq]

Try:

ElseIf IsNull(Me.Property_Found_By) Then
Me.Realtor_Name.Visible = False
Me.Realtor_Phone_Number.Visible = False
Me.Driving_Date.Visible = False
Me.Mailing_Date.Visible = False
Me.Explanation.Visible = False

Linq ;0)>

[TerdFerguson]

Perfection! Thank you missing! You were a big time help. I appreciate it.

[missinglinq]

Glad we could help! As far as the questions about the code window (VBA code editor.)

Select an object from the left hand window, then the right hand dropdown box will offer all the events available for that object. To find out what each event entails, fire up Access Help (I personally whistle for Rocky the Access Hound) and enter the search term Events. Then click on Events and Event Property References and there you have it! A list of all of them. Linq ;0)>

[TerdFerguson]

Glad we could help! As far as the questions about the code window (VBA code editor.)

Select an object from the left hand window, then the right hand dropdown box will offer all the events available for that object. To find out what each event entails, fire up Access Help (I personally whistle for Rocky the Access Hound) and enter the search term Events. Then click on Events and Event Property References and there you have it! A list of all of them. Linq ;0)>

Perfect. That should be helpful in the future. Thanks again!