Form Architecture In Multi-User Environment

[gray]

Hi

Can I pick the brains of you Access experts out there with regard to forms design in multi-user environments please?

Rather then 'bound' most of my forms are populated by use of strings bulit in VBA and then applied to respective recordsources. I've done this where JOINs are required and especially so where use of Junction tables is needed to relate data in many-to-manys.

What puzzles me is that when UserA has a form open and UserB makes a subsequent update, the updates sometimes appear 'automagically' in UserA's display and sometimes don't? I'm guessing that the complexity of the JOINs used by the Forms which dictates this?

The danger here is that as soon as a recordsource is applied in a multi-user environment it is effectively out-of-date (until requeried). Tricky on a busy helpdesk...

I know, of course, that this is a problem with all DB technologies but how do you experts tackle this in Access please? Do you have timers running to requery forms?? Or I am I missing something with, say, referential integrity rules? Or maybe there is some way I can bind a form to it's 'primary' table and use form filters to emulate my SQL... my JOINs are quite complex though and I'm not sure they can be used as filters?

Thanks for any pointers you can give me...

 

[BobMcClellan]

Hi

>>Rather then 'bound' most of my forms are populated by use of strings bulit in VBA and then applied to respective recordsources. I've done this where JOINs are required and especially so where use of Junction tables is needed to relate data in many-to-manys.
What puzzles me is that when UserA has a form open and UserB makes a subsequent update, the updates sometimes appear 'automagically' in UserA's display and sometimes don't? I'm guessing that the complexity of the JOINs used by the Forms which dictates this?
--If the controls are unbound and you populate them from an event via vba.. then you will not see a change until another event updates the unbound controls. You should never see an unbound control "automatically" update from a data update in a table. That's the point of the unbound control. You can change it with complete control when you want to via code logic. Regardless of how complex the underlying query is that ultimately is used to get the data to populate the control.. it should not change until another event is triggered that will update it.

hope this is clear.
hth,
..bob

 

[gray]

Hi Bob

Thanks for your reply... I thought that was meant to be the case but as an experiment I populated a form using a relatively simple join... thus:

SQLLINE = "SELECT TBL2.Selected As Selected,
TBL1.Unique_No,
TBL1.Name,
TBL1.Address_Line1,
TBL1.Address_Line2, etc etc
FROM Addresses AS TBL1
LEFT JOIN Addresses_to_Contacts AS TBL2 ON TBL1.Unique_No = TBL2.Addresses_Unique_No
WHERE TBL2.Contacts_Unique_No = nnnn"

Applied with:
my_form.recordsource = SQLLine

Then with my_form open, when I go directly into the Addresses table and make a change to, say, an address line, my_form is magically updated with the new details : this happens as soon as I move to another record in Addresses (thus commiting te change). I don't even need to move focus to my_form?

However, if I add a UNION SELECT into the SQL then my_form does not get updated automatically??

With whichever way it works, do you run a requery on a timer for open forms or do you supply a 'refresh' button for your users?

Thanks

 

[BobMcClellan]

Gray.. we are not on the same page. Is your control truly an unbound control?
If it is.... the only way for it to get updated is if you have code updating it.

I NEVER use timers. I use events to populate controls and events to refresh subforms that are in datasheet view. I use only .adp projects and I change the input parameter property from an event when I need the data refreshed. the stored proc gets the new inputparameter and the data is refreshed. likewise if you use ado to get the data from sql the return param has the data that gets mapped to the unbound control.

hth,
..bob

 

[gray]

Hi Bob

Yes, my controls are indeed bound (using ControlSource) to:

TBL1.Unique_No,
TBL1.Name etc etc

of the form's recordsource, the recordsource being set to the SQL above.

So although my Form is not bound as such, the controls are.... (incidentally, by a form being 'bound' I mean the form recordsource is set to some SQL and not simply set to a Table name - Hope that's the correct meaning?).

So I guess that's why the controls get auto updated but it still leaves me womdering why that happens on a form with a simple JOIN but not a more complex one. I've noticed similarly that some forms get an updatable recordset and some none-updatable (also dependent , I suspect, upon JOIN complexities?).

Thanks and yes... "hth" .. you experts' replies always help.. I learn a ittle more each time I logon

 

[vbaInet]

Not completely related to your question (but relevant), here's a link regarding read-only queries:

http://allenbrowne.com/ser-61.html

You will find that a UNION query is amongst the list of queries that are not updateable.

All a union does is pull the records from one table and pull from the other, then joins them. It doesn't keep a reference of what tables the records are coming from. In other words, it is not connected to the tables you've used in the Union. Which is why you find that UNIONS are mostly used in reports. They must be requeried for the records to be pulled in again. So if you want "automatic" updates don't use a Union.

 

[gray]

Hi Vba1net

Thanks for the explanation... and I'll have a read on Allen's link - cheers!