Requery subforms that don't have a Master/Child relationship

[Guest]

Novice

I have a form (frmTable) with a large number of pages in tabs. Each page
contains a subform (fsubSelect, fsubText, fsubHousekeeping, etc.). All
(except one) of the subforms are based on the Main table and so DON'T have a
master/child relationship with frmTable.

Is there a way to requery frmTable without having to requery every subform
separately?

Thank you

Peter

 

[Steve Schapel]

Peter,

In my experience, your design here is very unusual. And based on the
information you have given so far, it is more complicated than it needs
to be. What is the purpose of the subforms? Why not just base the
whole form on the main table, and put the required controls directly
onto the tab pages?

It is also not clear what the Requery is supposed to do. Do you mean
that the main form has controls bound to the same fields as have been
edited in the subforms? And then after the editing in the subform, you
want this changed data to be shown on the main form? Would it be
possible to give a specific example of what you need to achieve?

 

[Guest]

Thanks Steve--you've helped me before.

I will lay out all the facts-I hope you can follow. I have dreams (or are
they nightmares) about this at night.

The Access database will be the front end for a database (MySQL) driven
Website.

The database requires approximately 35 forms. All the forms need to display
the fields from the main table (tblMain). These fields are displayed on the
form header and 9 tabbed pages/subforms (fsubSelect, fsubText,
fsubHousekeeping, fsubMaps, etc.).

But each of the 35 forms also has to display fields from other tables on
more tabbed pages. I have joined these other tables to tblMain in separate
series of 1 to 1 relationships.

Sometimes the fields from the other tables appear on many of the forms
(e.g., fsubOpeningHours, fsubStreetAddress, etc.) and sometimes the
fields/table appear on just one form. Each of the 35 sets of tables will be
joined to a unique table that denotes the record type (e.g., tblRestaurant,
tblHotel).

I have an alternative version of the form that works conventionally (whole
form based on the main table as you suggest), but I don't want to modify 35
forms every time I make a design change, so I put the tabbed pages of the
main form (tblMain) onto separate subforms.

An alternative is to split the tblMain into 10 tables corresponding to the
form header + 9 pages and use conventional Master/Child subforms.

Furthermore, I haven't worked out whether I need to:
(1) base each of the 35 main forms on each of the 35 unique tables (e.g.,
frmRestaurant) or
(2) base the 35 main forms on the tblMain and put the 35 unique tables (that
denote record types) on subforms (e.g., frmMain, fsubRestaurant).

If the method (2) is possible, I can base all the 35 forms on tblMain and
don't need to split tblMain. POTENTIAL PROBLEM with method (2): on some
occasions users need to open 2 or 3 forms at once.

I hope this explanation covers all the bases.

I asked a question about this a month or so ago and didn't get an answer and
so I developed the two concepts in tandem.

Your suggestions are very welcome.

Thank you

Peter

 

[Steve Schapel]

Peter,

My first question is whether you are on the basic right technology here.
If you are talking about a website, how are you going to get these
forms up there. It is fine to use an Access (JET) database as the data
store on a website, in fact I can't think why you would use MySQL. But
as a frontend, Access applications are essentially desktop. If you want
a web frontend application, you should probably be looking at ASP.Net or
some such.

As for the table and from design, I will need more time to think about
that. But on the face of it, I can see no reason for all the
fragmentation. Generally speaking, two forms/subforms open at the same
time based on the same table is asking for trouble. Generally speaking,
except in very special sub-classing scenarios, 1:1 table relationships
are not valid design choices. Generally speaking, more than one subform
based on different parts of the same table would not make much sense.
Your project may be different, but at this stage, to be honest, I can't
see it.

 

[Guest]

Hi Steve,

I don't need the forms on the Website. I plan a desktop database in Access
and then export the relevant fields of the database into MySQL. Here's a
synopsis of the advice that I got from a varity of sources before I started:
1. As a Web application, Access can handle 5-25 simultaneous connections and
then it will drag or even fail totally.
2. Use Access to define the GUI for the in-house portion.
3. For the Website, use PHP and MySQL. MySQL has a few flaws, but is
evolving and improving (and was slightly preferred over ASP.net for a variety
of reasons).
4. Upload the Access table to MySQL--it takes 2-5 seconds and runs
flawlessly (there is free software that does this).
5. Consider a custom-built GUI in 1-2 years, when I have ironed out the
kinks in a live situation.

Although I probably won't be using it, there is off-the-shelf software that
migrates Access tables AND forms to MySQL.

As for the fragmentation: I may have a table that will appear on 10 of the
35 forms, and another table that appears on 8 of 35 forms, and another on 2
of 35 forms, etc.

I can put most (all) of this into the main table, but it will mean lots of
empty fields. in that table.

The 35 forms are a tool to build the Website database. While the Website
database needs to handle heavy traffic, the Access db may only have 3-6 users
max.

If you still have doubts, I may have to somehow show my table relationships.

I actually set out to build this from scratch, but despite the extensive and
detailed documentation I provided, the programmer produced an unusable mess.
The investors in my project then suggested I build the app. I was an
analyst/programmer many, many years ago working on large long-term projects
and so I have no problem defining what I want. Executing is a little more
difficult. The machine language that I programmed in is as dead as Latin and
so I'm learning as I go.

Thanks

Peter

 

[Guest]

I need to base my subforms on select queries on the main table.