Dynamic ComboBox RowSource

[Guest]

I have a hierarcial structure that I want to have uses select from. ComboBox1
is Area1 then ComboBox2 is Area2, where Area 2 is defined as those areas that
have Area1 as a parent.

This is in Access2003 ADP application using SQL Server 2005 as the backend.

I have unsuccessfully define the RowSource for Area2 as:
select distinct ProgramArea2 from GiftsProgramArea where ProgramArea1 = +
Me.ProgramArea1

It seems that the whole liine is sent directly to SQL Server and does not
resolve the local reference first.

I have succussfully implemented this with the Area2 control's OnEnter event
where I set the control's RowSource by creating a string.

2 QUESTIONS:

1) Is there a way to do this with the control's RowSource attribute directly
without using an event procedure?

2) If I release the final applilcation to a file share and have each user
connect to a common ADP application, will concurrent users interfer with each
other as the event procedure updates the control's RowSource, or does each
user have their own local dataspace for this information?

Thanks,
John

 

[Robert Morley]

1) Is there a way to do this with the control's RowSource attribute directly

without using an event procedure?

I believe you can use a Stored Procedure, but I've always used an Event Procedure for this, personally, because it's what I'm
familiar with. Sylvain Lafontaine will no doubt be along shortly to tell you how to do it with a Stored Procedure, as he seems to
know just about everything about Access.

2) If I release the final applilcation to a file share and have each user
connect to a common ADP application, will concurrent users interfer with each
other as the event procedure updates the control's RowSource, or does each
user have their own local dataspace for this information?

They'll all get their own space, as each form is opened locally on the user's instance of Access, and never saved to the central
ADP.

That said, it's *highly* recommended that you distribute a copy of the ADP to each user instead, and have them install it on their
local machine. It'll be much faster for them to open, and won't generate a warning message when two users open the same ADP
concurrently. This has the inherent drawback of requiring some update mechanism if you need to change the ADP frequently, however.
But that's not particularly hard to design if it comes to that.



Rob

 

[Guest]

Thanks Rob, this is what I expected, but I wanted to confirm before going too
far down this path.