I am currently attempting to design a custom search screen.

I have an option group containing 3 options – Films, Distributors and Producers. On choosing one of the options I would like to have a combo box become visible. I would like to have 3 of these combo boxes available, but only becoming visible once clicked on the appropriate option. The combo’s will contain a list of the values to search on (which I have set for the row source).

My first question is how to set this to work – after update I would imagine, and what code to set these active and visible.

Once I have selected a value from the drop down list, I would then like to automatically open a new form containing the information associated with the value chosen from the given list.

I hope someone may be able to point me in the right direction, or at least let me know if this sounds feasible and an idea of the best way to tackle this. Or if somebody thinks there may be better approaches to this problem.

Thanks in advance

Graham

Need to answer the second question first in this case.

To open the second form with the related data, use your relational field.

DoCmd.OpenForm "frmWithMatchingData", , acViewNormal, "[FieldNameToMatch] = " & Me.ComboBoxName,acFormEdit

To answer the first question..

Use commands such as

If [ListBoxName] = "Distributors" Then

Me.ComboBoxNameForListBox.Visible = True

ElseIf [ListBoxName] = "Producers" Then

Me.ComboBoxNameForListBox.Visible = True

Else

Me.ComboBoxNameForListBox.Visible = True

EndIf

I hope that points you in the direction you want.

I am going to offer a slightly different scenario to the Option Group than that supplied by jwindon. In the After Update event of the Option Group try code similar to this:

Select Case Me.OptionGroupName

Case 0
Me.FileComboBoxName.Visible = True
Me.DistributorsComboBoxName.Visible = False
Me.ProducersComboBoxName.Visible = False
Case 1
Me.FileComboBoxName.Visible = False
Me.DistributorsComboBoxName.Visible = True
Me.ProducersComboBoxName.Visible = False
Case 2
Me.FileComboBoxName.Visible = False
Me.DistributorsComboBoxName.Visible = False
Me.ProducersComboBoxName.Visible = True
End Select

I have assumed that the Options in the group are 0, 1 and 2. If not, change the code accordingly.

The perverbial "skinned cat" again Jack?!?
:-)


I concur that yes, Select case is a better way.

Jack/jwindon

Many thanks for the ideas.

I have gone with the select case option, and find this to be working fine. The only little quirk I have with this is:

When opening the form, no option is selected (which is fine), but all three drop downs are visible. On selection of one of the options, this will hide two drop downs and leave open only the drop down list that corresponds with the selection.

The way I have got around this is by grouping the three lists on top of each other, although I would imagine that I could add code - to maybe the On_Open event, that would hide all drop downs, until an option is selected.

I have used a macro to perform this option on another attempt (using setvalue) and on opening the search form, again no options are selected, and no drop downs are visible until an option is selected.

I will play about to see if I can work around this one.

jwindon - can you advise on why you feel the select case option is the better way.

jwindon - I have added the code for opening the form dependant on the value in the drop down (After_ Update), unfortunately this is opening a blank form. I presume this must be down to me including an inncorrect field name, so I will also need to play around with this.

I would also like to hear your opinions on using the option group code as opposed to using a macro, and what benefits each has.

Thanks for the help so far.

Graham