Unanswered: Problem with Access Default Navigation Buttons
I am doing a quick hack on some work-in-progress to provide a small DSS system for a client and have observed some strange behaviour in MS Access Default Navigation Buttons. The context is that I have a number of Entity Tables (Sites, Persons, Products) and the forms have a common structure. In each case, the lower half of the form is taken up with some tab pages. These pages have subforms showing various kinds of relationship material (activities, roles, etc.). These relationship records have six keys, two each for sites, persons and products (so we can show relationships between Site1 (factory) and Site2 (warehouse) for instance). I could not use the Master/Child subform link, because it would only allow primary or secondary key links or where the same key is in both fields. For this reason, I use a query with an OR clause selecting records with the parent value in either key. When I move from record to record, I have to requery the subforms to get the matching data. So far, so good, but now comes the odd bit. The navigation buttons only change records on every second click ! Both clicks produce "Calculating..." and "Form View" in the status bar, but the parent only changes on alternate clicks. The property settings are identical to other forms without the tabs and these work OK. Any advice from you gurus out there ?
The platform is Win XP and Access 2000.
Salutations from Australia.
All records are joined with dataless (i.e. Autonumber) primary keys and foreign keys. I have material in Visio, but am unsure whether it can be embedded in postable documents. Here is a try at a text version of the relevant bits of major entities and transactions..
MiddleName, Lastname, Other Data
In the Site form (for instance), the query selects all Activity (say) records where Forms!frmSite!SiteKey = Activity.SiteKey1 or Activity.Sitekey2. It joins twice to the Person table to pick up PersonCode as PersonCode1 and/or PersonCode2 and similarly for the Product table. Only the codes and data are displayed, the keys being there but invisible. The query is quite complex and I did wonder whether it might simply be a time thing, but that does not appear to be the case.
The idea is that the whole thing is built around user-defined codes which require various permutations of keys in the transaction records. The latter can then be reported by time-line or "sliced and diced" by code values. Eventually, the whole thing will be controlled by a background meta-database (the clever and proprietary bit), but for now, the poor old client will just have to remember what his codes mean (until this problem drives him scatty, of course).
I hope this helps.
Ahhh, as suspected. You're issue has to do with the design of these tables:
You effectively need to create an interim meta-data facility. It's not very hard, just a little sideways. Consider creating detail tables for Activity and Link, that will point you in the right direction.
Like you, Teddy, I thought that the problem lay with these fairly elaborate Requeries. However, the problem did not go away when I commented them out. I then looked very carefully at the form while clicking the navigation button and noticed that the SiteCode (the first field in Tab Order) was highlighted each time. Now this field had a LostFocus event which used Ucase to convert the code to upper case. As an experiment, I moved the conversion to the BeforeInsert and BeforeUpdate events (in other words the user would not see the uppercase code until the next visit - but who cares) and the problem magically went away ! I checked the event chains in some of my Access books and I think that the LostFocus event is a very tricky one, because it can be called at times other than when the user takes the cursor away.
However, before we close off this thread, I was intrigued by your references to detail tables and would like to pick your brains further if I may. I cannot find a consensus as to what we mean by detail tables, which to my mind (coming from a large database background, mainly Informix and Oracle) are the lower-order tables resulting from normalisation. As the transactional events (Activity and Link) are flat files with anonymous data fields and a list of foreign keys to entities, I do not see how they could be subdivided further, other than to have separate tables for every permutation of foreign key groups (6 factorial !). Perhaps you could give me an idea on what you had in mind.
I also considered rewriting the underlying query as a union, nominating SiteKey1 as the selector in one half and Sitekey2 in the other half. This might be slower, but might also cut down the complexity with regard to all of the lookups. Any thoughts ?
I see what you mean, but the problem is that the codes are not record identifiers but class identifiers. Thus, an activity record may have a sitekey1 (the employer location), a person key1 (an employee) and a sitekey2 (the site of work). The code could be WORKORDER. The two dates in the data could represent the timeframe for the work, a decimal field could contain the value and so forth. A link record might have an employer site (a detail record of a company record) as sitekey1, an employee as personkey1 and a code of EMPLOYEE and so forth. Multiple links between the same entity instantiations are also allowed (a person may wear multiple hats, so to speak). The codes and their fulfilment requirements are user-defined, to describe the business. As I hinted, the number of permutations of sitekey(1,2), person(1,2), product(1,2) and activity/link code (not all of which are required in every case) is quite huge.
The metadatabase is intended to assist the user by disabling unwanted foreign keys, assigning meaningful labels to the anonymous data fields of various types and so forth according to the code. I am also looking at ways of splitting up this very large table of transactions (the centre of a classic star join) according to the codes and keys in some way, somewhat as you describe, but resolving the controlling functions with the type definition processes in the hands of the user is quite difficult. I even thought of having a separate table for each code, but the thought of unions to connect 50 or 60 tables just to get a timeline was very scary !
Anyway, the metadatabase development was far beyond the timeframe that the client would countenance, so I am using his project essentially as a research tool. For this reason, I very much appreciated your interest and advice.