Use Subform or Mainform?

  • Thread starter Thread starter Jim Franklin
  • Start date Start date
J

Jim Franklin

Hi,

I have three stats tables, each with one field and one record, which are
being constantly updated by various other parts of my application. Values
are always updated programmatically, with no possibility for user to add or
delete records. A single select query QueryX pulls all three values into one
recordset using cartesian joins.

I also have an unbound main console form, which has a number of subforms
showing various data and command buttons etc.. I want to show the three
stats values at all times on this form. Originally I created a subform,
whose RecordSource is QueryX. However, it occurred to me that I could simply
make my main form bound to the query instead, as only one record is returned
at all times. This would save the need to have an extra subform.

Can anyone tell me which is the best way to do this?

Incidentally, I have used 3 stats tables instead of one table with 3 fields,
as its a multi-user application and am I worried about record-locking issues
as different users in different parts of the system will constantly be
updating the three fields. Again, can anyone tell me if this is the correct
way to do this?

Thanks for any advice,

Jim F.
 
On your first question, subforms are definitely more resource-intensive than
just main forms, so I'd recommend not using a subform if you don't have a
need.
Do remember to lock (and disable, if you like) the bound textboxes, so that
your users can't change them manually.
You'll probably want to set your mainform's AllowAdditions and AllowDeletes
properties to NO, just for additional security, and set its Cycle property
to CurrentRecord.

As for your 3 tables, I hope others will chime in here, as it's a bit beyond
the area where I can speak with much confidence. My experience, however,
has been that executing a SQL statement against an existing table takes
extremely little time. If Access tries to execute such a statement and
finds the record locked, it waits a short time and tries again. Both the
number of update retries and the update retry interval can be set in Tools -
Options - Advanced. You could try juggling these a little if you do get
record locking issues.
In this case, too, I'd personally tend to prefer the simpler approach of a
single table. But it's also true that with today's processor speeds and
memory sizes, many of the resource utilization issues which were critical a
generation or even a decade ago are no longer worth the cost in programming
effort.

HTH
- Turtle
 
Back
Top