Using a check box on a bound form to control the NotinList combo box event to take 2 controls from form, inserting into 2 fields in another table

[AccessAllAreas]

Wow!

That looks, umm, interesting.

So, I am guessing that I need to recreate a separate table with just the GOs listed in it and reference that as my table in the sSQL statement and the Set rs = statement?

Hope I understand that correctly.

On the previous (currently implemented) solution that you kindly provided, i have noticed that once one selects No from the message box (thus avoiding the originally requested action (Add or Remove name from combo) AND closes the form down manually, then re-open, then the combo displays as it should. So, I was wondering if it would be possible to basically close the record source (perhaps even the form) and have it automatically reopen at the previous record, maybe adding to my message box that this would happen? In my uneducated mind, i would have thought this could be possible. But what do i know.

Am i barking up completely the wrong tree with this idea? If so, please let me know and I will forget it! If itnis feasible, please feel free to educate me as if I am in Kindegarten!

 

[Minty]

So, I am guessing that I need to recreate a separate table with just the GOs listed in it and reference that as my table in the sSQL statement and the Set rs = statement?

No by using your existing data you are effectively recreating the combo list as a simple text list that doesn't update/change with the underlying data. Your SQL would be something like;

SELECT OverseerName From tblPublishers WHERE Overseer = -1

However, I do wonder whether the requirement to add and remove these names from a list in the form as you go is really required - as @Micron said many posts ago, you could easily handle this with a small pop up form check the Overseer box return to the main form and requery the combo. I can't imagine you would need to change them that frequently?

 

[AccessAllAreas]

Many thanks for that explanation.

I will work on this over the next week with the various solutions offered and see what works with the least loss in functionality or adding too much complexity to the end user.

 

[Minty]

Good luck - if you get stuck you know where to come

 

[AccessAllAreas]

Just to letmall know, I ended up removing the cancel event from the Select Case statement, with a message box being vbOKOnly! Then, if anyone doesn't want to follow through with selecting that person to be a Group Overseer, then they can simply click the box again, which will remove the name from the list!

Thanks for all then help on this one!