2 fields cannot equal each other

[mrk2]

I have two pull down menu's one called presenter and the other teachers assitants. these two drop down menus offer the same selction in there menus, from a list of staff members. I would like to have it so the database will not allow the same person to be selected in the presenter and teachers assitant field. I would also like to have it so a person can only be selected once in the teachers assitants field. I would assume that I could use the valiadation rule for to accomplish this task. Any advice is appreciated.
thanks

 

[tsumrall]

using the after update event, check to see if the 2 combo boxes are the same like ...

if me.combo1 = me.combo2 then
msgbox "scold the user"
me.combo1 = null
me.combo2 = null
end if

as for the "only be selected once in the teachers assitants field"

try something like this on the same after update event

if dcount("*", "yourtablename", "[staff_id]=" & me.combo2) > 0 then
msgbox "scold the user"
me.combo2 = null
end if

You may need to add more critera to the where clause like date or event depending on the way you are storing the data.

 

[mrk2]

How do I distinguish between the first combo box and the remaining combo boxes? there can be an unlimited amount of combo boxes. See below.

if me.combo1 = me.combo2 then
msgbox "scold the user"
me.combo1 = null
me.combo2 = null
end if

 

[mrk2]

what I meant to say is that is a conintuous form. there is not combo box a and combo box b.How do I tell access to take the current combo box data and look through the rest (if any) for like data?

 

[mrk2]

if me.combo1 = me.combo2 then
msgbox "scold the user"
me.combo1 = null
me.combo2 = null
end if

How do I refernce a combo box outside the current form. Say combo box 2 is outside the form which im placing this code. Would it be something like Forms!Teachers!TA ??
PLEASE HELP.
THANKS

 

[tsumrall]

Forgive me. I read menu as not really a menu. Are you really using menus? These menus populate data on your form?
or does menu mean drop down boxes(combo boxes) to pick from?

A teachers assitants can only be selected once ever?
A presenter can only be selected once ever?

or

A presenter can not be the teachers assitants ever?

Basicly for any presentation a Presenter can not equal teachers assitants?

Give me a bigger picture. Describe what data is collected in what table. Describe the rows of your conintuous form.

 

[mrk2]

Sorry about the confusion. yes when I say menu I mean combo box.

To be more clear. I have 2 subforms one called presenter and one called teacher assitants. These two subforms are linked to the main form by course ID. Therefore the data stored in the tables for the two forms consist of course ID's and names of presenters or ids and name of assitants(two seperate tables of course). The way I have it setup now , the user can only select one presenter (which logical makes since). On the other hand the user can select multiple assitants for each course. What I would like to do is only allow each employee to be selected once during each occurance of a course. Obviously people cant do two things at once, so I think this makes alot of sense. To accomplish this I want to block the presenter combo box from being equal to the assitants combo box value (in the other subform). I then want to prohibit duplication of values in the assitants combo box. But again, this is only for this one course occurance. I do want to be able to select the same "presenter" or "assitant" for another course. I hope this helped clear things up a little.
Thanks
mrk

 

[tsumrall]

So we have 2 checks to make.

First check is: are the two combos the same. If they are then msg the user and null the combos out. But we need to check both combos becuase you may not be able to tell which the user clicks first. Note that this is a real time check not a history test so we don't have to go looking thru records to see if there is a match. We stop the match before it happens.

sub combo1_afterupdate()
if me.combo1 = me.combo2 then
msgbox "scold the user"
me.combo1 = null
me.combo2 = null
end if
end sub

sub combo2_afterupdate()
if me.combo1 = me.combo2 then
msgbox "scold the user"
me.combo1 = null
me.combo2 = null
end if
end sub


Next we need to check to see if the same assistant has been picked twice. Now we need to look thru records to see if an assistant has been picked matching combo2 and courseID.

So combo2 afterupdate grows to include ...

if dcount("*", "teacher assistant", "[courseID]=" & me.courseID & " and [staffID]=" & me.combo2) > 0 then
msgbox "scold the user"
me.combo2 = null
end if