limiting entries in a subform combo box

[Zebble]

I'm developing a small budgeting application for a non-profit and one part of
the application is driving me nuts!

I have a combo box in a form to select an employee. I have a subform (a
data table) that allows the user to identify specific grants (via another
combo box) to be used to pay that employee and enter an amount.

My problem is that the grants available in the second combo box need to be
limited to those grants assigned to the employee's department (not all
departments derive their funding from every grant). I have added a WHERE
clause to the query that builds the grant combo box and it appears to work
for the first employee I select. Subsequent employee selections (from a
different department) either provide the same list of grants (not the grants
unique to the new department) or a blank combo box.

I've tried using a requery macro (to requery the grant combo box) in the
AfterUpdate property of the staff selection combo, but either get an error
that the grant combo box is not part of the form or get the same results as
before.

I'm running Access 2003 and would like to handle this in the simplest manner
possible. I'm not proficient at VBA, but do follow instructions well. I'm
more comfortable with macros, but know there are limitations.

Any advice would be welcome.

 

[Al Campagna]

Zebble,
Sounds like that Grant combo doesn't realize that the Employee combo has
changed.
On my website (below) I have a sample A97 and A2003 file called "Synched
Combos."
It will show you how to Requery the dependent combo whenever cboEmployee
updates... using VB in the form module.
Very simple, once you've seen it...
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Zebble]

Al:

I actually got it to work just before I got your response. Turns out I had
two issues (1) not using VBA and (2) believing what I read in the online VBA
help file. I figured that I needed to provide the full syntax of the grants
combo and use a "." to separate the objects in the syntax string vs the "!"
used in the VBA help example.

All is well now. The sun is shining, the birds are singing, bunnies are
frolicking and I move on.

Zebble