bound forms vs. unbound forms

[David W. Fenton]

I suspect the biggest problem there might be in figuring out how
to properly use subforms. It does take a bit of work at the
beginning to really understand what is going on their.

I find that comment baffling. Perhaps it's because I'd already
absorbed the basic concept in Paradox for DOS, but for me when I
tried Paradox for Windows, and then Access, it was completely
intuitive to me to embed subforms and link them by the appropriate
key field(s).

I don't recall it being hard to grasp the concept in Paradox for
DOS, either, and it was *much* more complicated and less intuitive
than it was in either the first version of PDOX4WIN or in Access 2
and before.

 

[David W. Fenton]

d) Subforms provide a suburb way to display multiple related
records for the record in the main form.

I think that the single forms would be suburban, and the continuous
form more the urban form, don't you think? More people, you know...

(was that joke too lame?)

 

[Allen Browne]

Thanks, David.

Always good to know your urbane eyes are keeping us in line.

 

[Tony Toews [MVP]]

I find that comment baffling.

Whereas I came from an RPG III background on an AS/400 and S/38. So
we had similar concepts but had to code everything.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

How do you replicate the .Dirty property, or the .OnDirty event? Or
the form's .AfterUpdate event? In the few unbound forms I've created
(in the small handful of cases where there were necessary), I've
written reams and reams of messy, dirty code to replicate what bound
forms provide without an additional line of new code.

Agreed. I add buttons to say write or update. And logic that, if
they hit the close button on the form, checks to see if there are any
changed fields and warns them. Etc, etc. But in this particular
form it was well worth it due to the ability to add three transactions
at once, RT, OT and DT time entries. Making it much easier for the
data entry clerk. I think there are two or three thousand lines of
code in behind that form.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[jacksonmacd]

David,

This idea is illustrated in my article here:
http://accesstips.datamanagementsolutions.biz/subsub.htm

Great stuff, Steve. Thanks for posting.

In the "Form Footer Section", how do you link the subform with the
sub-subform? In other words, what is the content of Link Child and
Master fields in the sub-subform? Whenever I've tried something like
this, I ended up with a hidden textbox on the main form that holds the
keyfield of the current subform record, and then using that as the
Link Master field of the sub-subform. Hoping you have a more elegant
solution.


(In truth, I recently converted an A97 database into an A2003 format,
and noticed that one of my forms displayed the +/- symbols that
indicate a hidden subform. I was confused about how that appeared
until reading your article.)

 

[Steve Schapel]

Jack,

Thanks a lot for your comments.

When the sub-subform is placed in the form footer section of the
subform, its Link Master Fields property is simply set directly to the
name of the subform's Primary Key. The hidden textbox on the main form
idea is necessary for the "side by side" approach, but there is no need
for it in either of the other two approaches.

 

[jacksonmacd]

Jack,

Thanks a lot for your comments.

When the sub-subform is placed in the form footer section of the
subform, its Link Master Fields property is simply set directly to the
name of the subform's Primary Key. The hidden textbox on the main form
idea is necessary for the "side by side" approach, but there is no need
for it in either of the other two approaches.

Steve,

I must be missing something. Structure is:
tblCustomers
tblSites PK: SiteID foreign key CustomerID
tblTanks PK: TankID foreign key SiteID

1 customer -> Many Sites -> Many tanks per site.

The subform-within-a-datasheet method works perfectly. The child and
master fields for this subform are both "siteID"

For the subform in footer method, I have a mainform with Customers,
containing a subform named "frmSites" for sites. In the footer, I have
a sub-subform named "frmTanks" for Tanks. The sub-subform has these
properties:
Link child field: SiteID
Link master field: frmSites!siteID
(have also tried frmSites.form!siteID)

The sub-subform only shows the tanks for the FIRST site of the
frmSites subform. As I walk through the mainform for customers, it
keeps current with the first site. But it does not change as I select
different sites within the frmSites subform.

What do I need to change? Working in A2003 frontend, with the backend
in A97 format.

Thanks for your help.

 

[Steve Schapel]

Jack,

I will be able to look at this in more detail later. But for now... I
would have the sub-subform's Link Master Fields set to simply SiteID.

frmTanks is in the footer of frmSites, not in the footer of main
Customers form, right?

 

[jacksonmacd]

Jack,

I will be able to look at this in more detail later. But for now... I
would have the sub-subform's Link Master Fields set to simply SiteID.

frmTanks is in the footer of frmSites, not in the footer of main
Customers form, right?

D'oh! Had it in the wrong place! Moved the sub-subform into the
correct footer, and it works properly. Thanks very much.

 

[Steve Schapel]

That's cool, Jack.

I will look at editing my article to make this clearer.

 

[jacksonmacd]

That's cool, Jack.

I will look at editing my article to make this clearer.

Well, it was clear enough when I actually took the time to *read*
it...

As the acronym goes: PICNIC :-(

 

[James A. Fortune]

There were angry words on this group a couple of days ago on this topic.
I'm wondering what the consensus of other Access developers is regarding
bound forms and unbound forms.

I often use unbound forms and I create classes, like a Customer class with
properties used to AddCustomer, UpdateCustomer, etc. I will also have a
data access class which perfoms all data related tasks. I also do a lot of
VB.Net development and I feel more comfortable using a sort-of object
oriented approach. I also find that complex forms with subforms are easier
to manage when unbound. It seems easier to locate problems, by stepping
through the code. Maybe if I spent some time learning to use bound forms
better, there wouldn't be problems to solve.

I don't necessarily advocate unbound forms, that is just how I often do it.
Most projects are a mixture of more complicated unbound forms and simpler
bound forms, like popups for adding comments.

I like Access because of the reporting, easy GUI design and implementation,
the database schema tools, the query tools, etc. But not so much for bound
forms.

To be honest, I don't like to use complicated bound forms because I always
seem to end up trying to figure out the reason for some referential
integrity violation or something and I find it is easier to just write code
where I know exactly what is happening.

This certainly must be because I have not mastered the techniques of
developing in Access with bound forms and have taken the lazy way, since
advanced developers seem to prefer bound forms.

However, it doesn't take me that long to write code to do what a bound form
would do even if it worked the way I want it to.

Hopefully nobody is offended by this - I'm just curious about other views.

Bill

My advice is to use bound when you can, unbound when you must. If you
are planning on moving the application to the web, start with unbound
immediately. If you are starting with over 30 concurrent users, start
with unbound immediately. Also, if unbound is required, you usually
only need a small portion of the functionality that is programmed into
Access' bound forms. I find Access to be extremely handy, even for
situations where unbound forms dominate.

James A. Fortune
(e-mail address removed)

http://www.crn.com/software/205207950

 

[John W. Vinson]

If you are starting with over 30 concurrent users, start
with unbound immediately.

I've also found that forms bound to single-record (or occasionally few-record)
queries can give adequate results, even with fifty or so users. YMMV of
course. It's more work than your generic table-based bound forms, but rather
less work than going to completely unbound.

John W. Vinson [MVP]

 

[James A. Fortune]

I've also found that forms bound to single-record (or occasionally few-record)
queries can give adequate results, even with fifty or so users. YMMV of
course. It's more work than your generic table-based bound forms, but rather
less work than going to completely unbound.

John W. Vinson [MVP]

I agree that that plan might work. If you start with 30 concurrent
users, though, you might end up with 70 or more. It's worth trying as
long as you keep an eye on it. If you have 100 or so active jobs or
active customers the chances of having several users editing the same
record go up, as does the potential for problems. Your idea would save
a moderate amount of effort if successful.

James A. Fortune
(e-mail address removed)

 

[John W. Vinson]

I agree that that plan might work. If you start with 30 concurrent
users, though, you might end up with 70 or more. It's worth trying as
long as you keep an eye on it. If you have 100 or so active jobs or
active customers the chances of having several users editing the same
record go up, as does the potential for problems.

Yep. There you really would be best looking at Access linked to SQL/Server,
with unbound forms and all the work that entails.

John W. Vinson [MVP]

 

[James A. Fortune]

Actually moving to SQL Server would mean that the user count no longer matters
and you can easily STAY with bound forms.

That's interesting. How many concurrent users have been reported using
that configuration? If that turns out to be reliable perhaps we'll be
seeing forms on the internet soon that are bound Access style.

James A. Fortune
(e-mail address removed)

 

[Rick Brandt]

That's interesting. How many concurrent users have been reported
using that configuration? If that turns out to be reliable perhaps
we'll be seeing forms on the internet soon that are bound Access
style.

Well that configuration is how most of my apps are set up, but I have no idea
what the maximum concurrent user limit for one particular form/table combination
would be.

Not sure how that relates to "forms on the internet" since an Access form cannot
be run over the internet.

 

[Tony Toews [MVP]]

That's interesting. How many concurrent users have been reported using
that configuration? If that turns out to be reliable perhaps we'll be
seeing forms on the internet soon that are bound Access style.

One person stated he had 10,000 users world wide on an Access front
end against a SQL Server system.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[James A. Fortune]

Well that configuration is how most of my apps are set up, but I have no idea
what the maximum concurrent user limit for one particular form/table combination
would be.

Then I think I'll wait a while longer before testing those limits.

Not sure how that relates to "forms on the internet" since an Access form cannot
be run over the internet.

I was speculating about potential future products. Visual Studio has
some controls that have some data binding capabilities but I'm not sure
whether or not they were designed to deal with concurrency issues.
Visual Studio also seems to have a much better plan in place than Access
for dealing with multithreading. So it seems possible that, given
multiuser success with bound forms in Access with a SQL Server backend,
a market might exist for an Access-like product (motive) to be created
with Visual Studio (means) that would allow for bound forms on a website
and take advantage of multicore processors on the client. Using a
locking file is certainly possible. It would save me the trouble of
implementing my own collision resolution code, which I have done before.
I'm still not sure that I could trust the whole bound form on the web
concept yet.

James A. Fortune
(e-mail address removed)