VBA code for form to pull data from another table and enable/disable button

[EDNYLaw]

Hello, I'm relatively new to Access (and the forum) and I was wondering if someone could give me a hand with some VBA Code and procedural stuff. I've created a form to enter customers into workshops. The form is called Attendees and the information entered into this form is recorded on to the table Attendees by clicking the Add Customer button (just an add new record button). In addition to several textboxes I have two combo boxes, one of which pulls the workshop names from a table called Workshops. What I would like to do is on the Workshops table I have 3 fields/columns (ID, Workshop, Open), when a workshop has reached it's limit I'd like to be able to change the corresponding Open field to Closed. Basically, all workshops will start as Open and when they reach their maximum, the Open value will be changed to closed.



What'd I'd like to happen (and consequently where I need the programming help) is I'd like the combo box (AfterUpdate I'm assuming) to have an event that if a workshop is selected that has a Closed status, the Add Customer button is disabled. I did a whole bunch of Googling yesterday but couldn't find the answer. I'm not sure how to code VBA to look up a value from another table and disable the button accordingly.

So for example: In the workshops table I have a workshop (field) called Resume Improvement - 8/2/12 (value) and that workshop has a limit of 25 people. In my Open (Field/Column), next to that workshop it says Open (value), when the limit is reached I will change the open to closed and at that point if someone were to select Resume Improvement - 8/2/12 from the combo box the Add Customer button would be disabled, thus disallowing them to add the record (or at the very least make it more difficult, I dealing with extremely computer illiterate people for the most part so security's not top priority).

Based on my Googling I'm thinking the VBA code should be something like

If Me.Workshops.Column(2) = True then
Me.Add_Customer.Enabled = True Else
Me.Add_Customer.Disabled = True

Or something like that, but as I said I'm fairly new at Access and completely new to VBA. Sorry for the length but I wanted to provided as much info as I could. Any help would be greatly appreciated, THANKS!

EDIT: If anyone knows how to code the form to disallow the addition of a new record until all fields are filled in/selected that'd be super helpful too!

[amrut]

I would suggest to change the row source of combo Workshops with a condition [Open]=True and after adding a new attendee , check for the number of attendees and change [Open]=False when the limit is reached. Using this approach, only the "open" workshop's name will appear in the combo box.

[EDNYLaw]

That sounds like a great idea, being that I'm still relatively new to Access, can you tell me how and where to add this to the Row Source? I'm not exactly sure how to add a condition to a row source.

Edit: Nevermind, figured it out. The Criteria under the Attendees Query under [Workshops] Workshop is [open]=true. AWESOME, thank you so much! This totally saves me a whole bunch of hassel from employees who like to disobey limits

[amrut]

Glad to know it worked! Pl mark as "Solved"