Hi:



I have two dropdown Combo Boxes on the same form, each containing 

identical info in the list, which is drawn from a single table. 

Essentially, there is a list of procedures that need to be recorded (say, 

Procedures 1 to 10), and only up to two can be recorded for each record 

(or event). Furthermore, the user can't make the same choice for the 

second dropdown as what they chose in the first dropdown beside it. The 

second one can be empty (i.e., null), but there has to be a first 

procedure recorded for any particular event.



I want to have a validation rule which checks to make sure that the value 

of the first combo box is not the same as the value of the second combo 

box (in other words, they can;t choose the same procedure for that 

record). I've tried various things witht he validation rule but can't get 

it to work. Is there a way to do this on the property sheets, or do I have 

to resort to VBA code that is triggered on an OnChange event, or something 

similar?



Thanks for any help.

For this example we have a table called tblTest with a field called

Procedure_No.  There is a form with two combo-boxes, cboBox1 and

cboBox2.  The following procedure will perform the task you want.

 

Private Sub cboBox1_AfterUpdate()

 

    cboBox2 = ""

    If IsNull(cboBox1) Or Len(cboBox1) = 0 Then

        cboBox2.RowSource = "SELECT Procedure_No FROM tblTest"

    Else

        cboBox2.RowSource = "SELECT Procedure_No FROM tblTest " & _

                            "WHERE Procedure_No <> " & cboBox1

    End If

    cboBox2.Requery

    

End Sub



John Ruff - The Eternal Optimist :-) 







-----Original Message-----

From: simonds@m... [mailto:simonds@m...]

Sent: Saturday, September 22, 2001 1:14 AM

To: Access

Subject: [access] Two Combo Boxes with identical info on one form, user

can't make the same choice in each case





Hi:



I have two dropdown Combo Boxes on the same form, each containing

identical info in the list, which is drawn from a single table.

Essentially, there is a list of procedures that need to be recorded

(say,

Procedures 1 to 10), and only up to two can be recorded for each record

(or event). Furthermore, the user can't make the same choice for the

second dropdown as what they chose in the first dropdown beside it. The

second one can be empty (i.e., null), but there has to be a first

procedure recorded for any particular event.



I want to have a validation rule which checks to make sure that the

value

of the first combo box is not the same as the value of the second combo

box (in other words, they can;t choose the same procedure for that

record). I've tried various things witht he validation rule but can't

get

it to work. Is there a way to do this on the property sheets, or do I

have

to resort to VBA code that is triggered on an OnChange event, or

something

similar?



Thanks for any help.

Two ways to address this come to mind:



1) Update the contents of the other list and remove the selected item in 

the changed list.  The earlier reply does this.



2) The other way is to use a single multiselect list, but count the number 

of items selected and limit them to two.  This method may be a little more 

managable becuase you don't have two lists that need to re-add items 

should the user change their selections.  Plus, if you ever want to 

increase the number of options that may be chosen, you don't need to add 

new lists and eat up valuable screen space.  Sorry, no code.



Ron Brown



> Hi:

> 

> I have two dropdown Combo Boxes on the same form, each containing 

> identical info in the list, which is drawn from a single table. 

> Essentially, there is a list of procedures that need to be recorded 

(say, 

> Procedures 1 to 10), and only up to two can be recorded for each record 

> (or event). Furthermore, the user can't make the same choice for the 

> second dropdown as what they chose in the first dropdown beside it. The 

> second one can be empty (i.e., null), but there has to be a first 

> procedure recorded for any particular event.

> 

> I want to have a validation rule which checks to make sure that the 

value 

> of the first combo box is not the same as the value of the second combo 

> box (in other words, they can;t choose the same procedure for that 

> record). I've tried various things witht he validation rule but can't 

get 

> it to work. Is there a way to do this on the property sheets, or do I 

have 

> to resort to VBA code that is triggered on an OnChange event, or 

something 

> similar?

> 

> Thanks for any help.