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

Tech-Archive recommends: Fix windows errors by optimizing your registry

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.

--
Steve Schapel, Microsoft Access MVP


Peter Stone wrote:
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.
.

Quantcast