Formula in a form

[Guest]

I have a form that holds about 8 comboboxes. The comboboxes contain two
choices in the dropdown, "Open" and "Closed." I would like for everytime
somebody clicks on "Open," every other combobox that contains "Open" will
turn to "Closed." Please provide suggestions if you know how to do this.

Thanks.

 

[Dorothy]

I have a form that holds about 8 comboboxes. The comboboxes contain two
choices in the dropdown, "Open" and "Closed." I would like for everytime
somebody clicks on "Open," every other combobox that contains "Open" will
turn to "Closed." Please provide suggestions if you know how to do this.

Thanks.

Hi Chris. You can write some VBA that activates when the combo box
changes to open. You can go to the properties section of the combobox
and choose "Event Procedure" for the "After Update" line. Here's a
little snippet of the code.

if me.combobox1 = "open" then
me.combobox2 = "closed" and
me.combobox3 = "closed" and
me.combobox4 = "closed" and...etc.


end if

Let me know if this works for you.

Dorothy

 

[Guest]

Leave out the And. That will thow an error
If me.combobox1 = "open" then
me.combobox2 = "closed"
me.combobox3 = "closed"
me.combobox4 = "closed"
End If

The code should go in the after update event of combobox1 and you would need
similar code in the after update event of each combo box. So for combobox2
it would be:

If me.combobox2 = "open" then
me.combobox1 = "closed"
me.combobox3 = "closed"
me.combobox4 = "closed"
End If

 

[Guest]

I am actually going to go a different route with this whole situation,
however, I need to ask how to have more than one choice from the combobox
listed before the "Then" portion of the formula. For example,

If Me![Combobox1] = "open" "closed" "hidden" "etc." Then
Me![Combobox2] = "closed"

How do you get the formula to work with more choices for combobox1? Do you
put and's or or's between the choices? How do you do that?

Thanks.

 

[Guest]

A Select Case statment would be the best in this situation:

Select Case Me.ComboBox1
Case "Open"
Me.ComboBox2 = "Closed"
Case "Closed"
Me.ComboBx2 = "Open"
Case "Hidden"
....
Case Else
....
End Select

Just put the code in appropriate to each case. Case Else is used if there
is no match in the other cases. It is not required to be there; however, it
is only when you want to do something when a match is not found in the other
Case statments.
--
Dave Hargis, Microsoft Access MVP

I am actually going to go a different route with this whole situation,
however, I need to ask how to have more than one choice from the combobox
listed before the "Then" portion of the formula. For example,

If Me![Combobox1] = "open" "closed" "hidden" "etc." Then
Me![Combobox2] = "closed"

How do you get the formula to work with more choices for combobox1? Do you
put and's or or's between the choices? How do you do that?

Thanks.

Leave out the And. That will thow an error
If me.combobox1 = "open" then
me.combobox2 = "closed"
me.combobox3 = "closed"
me.combobox4 = "closed"
End If

The code should go in the after update event of combobox1 and you would need
similar code in the after update event of each combo box. So for combobox2
it would be:

If me.combobox2 = "open" then
me.combobox1 = "closed"
me.combobox3 = "closed"
me.combobox4 = "closed"
End If