Why don't the database relationship fields display correctly in the form?

[sueb]

My original database has a single table which contains Patient information and up to 3 "slots" for procedures requested by the doctor. I am working on a split version that contains two tables: Patients and Procedures. At first, when I split the original database, it contained no AutoNumber field, so I simply allowed the relationship to be based on the patient's chart number (which is unique). I've made my new forms based on those new tables, and everything has been great.

My goal all along, however, was to use an autonumber to identify the patients and as the one-to-many join field, so today I added an AutoNumber field to the original database, reworked the join in my two tables (and deleted the current data, of course), then exported the single table into Excel and shoved things around so that I could import into the two joined tables (using the same steps I've been using for the past several weeks, which have worked perfectly).

Now, however, the patient demographic data (chart number, last name, etc.) is being displayed incorrectly in my forms: all the fields are pushed one place "to the right", as though the AutoNumber field is shoving them aside, even though it has no place on any of the forms. When I look at the data in the datasheet, everything looks okay; no field names were changed; and in the forms, the Properties of each field indicate that they are pulling from the correct table field. The data being displayed in the "Chart Number" field (which is the "first" one following the autonumber "Patient Index" field) looks to be a random, single-character value.

I can't figure out what's happened. Any ideas will be greatly appreciated.

[ADezii]

sueb, the only way that I feel we can get an idea of exactly what is/is not going on, is by looking at the actual Database itself, along with a detailed explanation of all the components involved. Can you Upload a scaled-down Version of your DB in Access 2003 format?

[sueb]

Okay. I'll do that on Monday when I'm back at work. Thanks!

[ADezii]

Just be sure to include the specifics inidicating what, exactly, the problem is, and the Objects involved.

[sueb]

Okay, I have a version ready to upload, but .mbd is not listed as a valid file extension for attachments. How do I upload this?

Edit: Never mind. I'm zipping them.

[sueb]

I'm attaching a .zip that contains my front end and a greatly reduced back end. Although there seem to be patient's names in the data, these names are all made up, so confidentiality is not being breached.

You'll see that on the forms "IURs: Enter New" and "IURs: View/Edit Existing" the data does not show up in the correct fields. On the forms "Patients" and "IURs: subform", it does. The two forms on which it is incorrect are the ones that are displaying data from two tables, across their join; the two forms on which it is correct each display data from a single table. I'm sure that's part of the answer, but I can't figure out what's really going on, especially since before I introduced the AutoNumber, all these forms behaved correctly.

[Bruce Callander]

sueb - I had a quick look at the database. It really needs to be normalised into separate tables. By putting so much into the IUR table you are making it difficult to manage and not gaining the benefits of a relational database. For example there is no need to hard wire a maximum number of three telephone calls against each patient. Make telephone calls a separate table and link them to patients via the unique patient ID. Also, if you ever want to add VBA coding it will be easier if your field names don't have spaces - use an underscore instead of a space. I believe that you need to review of your database structure, otherwise you are likely to encounter an unending string of niggling faults.

[sueb]

I am in the process of normalizing the structure (you should have seen it before), and in fact, in this case, 3 call is exactly the limit, for internal process reasons that have nothing to do with the problem I'm having.

I have many years of database experience, but I am maintaining this database in my spare time and have to carefully manage the changes I make to it so that it is NEVER down.

** Edit **
Argumentative comments removed.

[sueb]

Bruce, I apologize for the tone of my original reply to you (and, NeoPa, I thank you for editing it). There is no excuse for my responding to you that way; this site has been immeasurably helpful to me. I can only plead the pressure of my deadline and the lack of support here at work.

Again, I'm sorry. Thanks for taking the time to look over what I uploaded.

[MMcCarthy]

@sueb

I'm not sure what is going on. I've opened the two forms you say you are having a problem with but both appear fine to me. The Chart Number field on the subform is displaying the value in the Chart number field on the table.

Try running a refresh on the linked tables using the linked table manager and see if that sorts out your problem.

Otherwise I'm not sure what is going on.

[ADezii]

What is immediately apparent to me is the following:

1. The Primary Relationship between the Patients and IURs Tables as defined in the Back End Database(IURSTATUS_BE.mdb) is based on [Patient Index], namely:
   

   Patients.[Patient Index]{1} ==> IURs.[Patient Index]{MANY}

2. On the Front End DB, the Parent <==> Child Linkage between the Main and Sub-Forms is based on the [ChartNum] Field, resulting in a misalignment. Change the Linking Fields to [Patient Index].
3. The Patient Indexes of 31406 and 31392 in the Patients and IURs Tables refer to different Chart Numbers, which they shouldn't.

[sueb]

The combined answers of both MMcCarthy and ADezii fixed this right up! You were exactly right, ADezii, about the incorrect linkages, but just before setting out to fix that problem manually I thought I'd try MMcCarthy's linkage refresh suggestion. Turns out that the Link Manager took care of correcting the misalignment!

I guess I assumed that "link means linked", and wouldn't have thought about having to refresh them. However, it makes sense that a change like this might require a little extra boost.

So, thanks again, everyone! I think I'll have this thing completely ready for the deadline of next Monday, thanks to this last important step. I still want to gradually break up the IUR table some more, but this was the big hurdle.

(Oh, and, ADezii? That was some amazing data review you did, catching the mismatch in the Chart Numbers! Thankfully, that was a result of my hacking away a little too hurriedly in my attempt to whittle this down to a size more appropriate for uploading, but I did go back and check that all those numbers lined up right.)

[ADezii]

Glad that it all worked out for you.

[NeoPa]

SueB:
Bruce, I apologize for the tone of my original reply to you (and, NeoPa, I thank you for editing it).

Sue, I have come across your posts on many occasions and have always found it to be a pleasant exerience. I was somewhat surprised to see your post earlier, but your apology and explanation (we all have difficult days so I hope we can sympathise) followed swiftly and gracefully, so I'm happy with the outcome.

PS. I'm glad to see that ADezii and Mary have helped you to resolve your problem between them.

[Bruce Callander]

SueB - I appreciate your apology but I felt pretty bad when I looked again at my contribution, which in retrospect I agree was completely unhelpful. One lives and learns ...