[cb@chrisb.eu]

Hi. I've got two main tables (Patient Details [PD] and Blood Tests [BT]) They are linked on Hospital Number, Many (BT) to one (PD), as one patient (with only one hospital number) can have more than one blood test.
The main front form is PD, with macro buttons opening other forms, all retaining the name and hospital number (=Forms!Patient Details form! Hospital No, etc) from the patient selected on the PD form.
The blood test input form, however, refuses to save without a separately entered BT Hospital Number.
Following other posts, I've tried using text boxes and patient / child sub forms to separately store this number but without luck: as far as the form was concerned, I was BT writing to BT, so they refused to write.
Both BT and PD queries (which feed each form) also store their own and each other's hospital numbers.
Any ideas?

[OBP]

Can you post a zipped copy of the database, less the data for us to look at the Data, query and form designs?

[cb@chrisb.eu]

Hi OBP.

Thanks. The BT table I referred to for brevity is actually called [FONT='Arial','sans-serif']Haemoglobinopathy Investigations (you can see why I called it BT for short). The main PD form is point 1 on the switchboard. The BT (HI) New button is the first lower button on the top of the PD form.[/font]

[OBP]

I am sure that your Relationships are not correct. I will post something tomorrow.

[OBP]

Hello, I have had a look at your database and I think that the Relationships are causing you problems and will continue to do so.
I am not sure what will work in the way of changes as I do not know how your data is structured.
You have the Hospital No. Indexed, but with Duplicates "OK", can you actually have duplicates?
If you can't have duplicates then the Hospital number should be changed to With No Duplicates and made a Key field. This can then be used to link the Patient to all of the other Patient related Tables.
If you do have duplicate Hospital Nos in the table then it can't be used to link to the other fields and you should switch to the PatientId instead.

[cb@chrisb.eu]

Originally Posted by OBP

Hello, I have had a look at your database and I think that the Relationships are causing you problems and will continue to do so.
I am not sure what will work in the way of changes as I do not know how your data is structured.
You have the Hospital No. Indexed, but with Duplicates "OK", can you actually have duplicates?
If you can't have duplicates then the Hospital number should be changed to With No Duplicates and made a Key field. This can then be used to link the Patient to all of the other Patient related Tables.
If you do have duplicate Hospital Nos in the table then it can't be used to link to the other fields and you should switch to the PatientId instead.

Hi OBP.
The Patient details table has no duplicates, as each person is unique. The HI table does have duplicates, as blood tests can be varied and / or repeated.

[OBP]

Have a look at this version.
I could make a lot of recommendations if you want them.

[cb@chrisb.eu]

Hi OBP.

Thanks. I note the star layout, which I'd tried earlier, but you've got much more organised referential integrity set up. I'll paste the data back in and see what happens next week.

I note your offer of more suggestions which I would appreciate, particularly since you've now seen the database.

Thanks again for spending your valuable time on this.
Much appreciated.

[OBP]

cb, most of the recommendations involve Form formatting and usage.
First of all it would be good if you removed the "Record Selectors" and "Dividing Lines" which are for use with forms in Continuous Mode and not really needed in Single Form mode.
You should also set the Form's "Allow Design Changes" to "Design View Only" to remove the "Properties" when the Form is open.
You do not need a normal version of the Forms and an Add Record version of the same form, you just need to open the Form in "Add" mode (see the Command Button for the Blood Tests).
I would use VBA for the Command Buttons that open forms and reports as it is more versatile then Macros.

Finally as this database is "Patient Focused", ie just about everything is related to the Patient and their Hospital Number, this database would work extremely well with a "Tabbed" mainform and subforms.
It would privide the Users with everything in one place and avoid you having to "transfer" Hospital Numbers when opening forms, as that would be taken care of by the Main/Subform links.

[cb@chrisb.eu]

Thanks, OBP.
I had hoped to re-link my full data tables, but the Relationship type stays Indeterminate, and the next message is that 'No Unique index has been found for the Referenced Field of the Primary Table (sigh). So I then tried to paste my data into each of your tables, by table or by line, but no luck so far. Any ideas?

Responses to your earlier points:
Single form: noted.
Design changes: noted.
Add forms: these were designed to come from the new patient form, and needed to be called by different macros to add rather than edit, but the Macro 'Add' command was disfunctional. That might improve given your new layout.
Command buttons: this has to be used in Access 2003, which doesn't much like VBA, IMHO.
Tabbed forms: noted, but some of these forms are fairly big.

Thanks again for your help.

[OBP]

You have to make the Patient Table's Hospital Number field Indexed and "Unique" ie "Duplicates allowed" = "No" to overcome that error message.

[cb@chrisb.eu]

Hi

That's solved the linking problem: thanks. But I'm still stumped with the original problem: on the Blood Test (BT) form that feeds both (linked) PD and BT tables, it won't write to the BT table, and therefore doesn't allow you to save the form. Mini version (with 6 real, but re-named) customers attached. If you open the Patient Details form, and try to add a new blood test form for any of the existing clients, it still refuses. Any idea why?

Thanks again for your help.

[OBP]

Mainly because you are using your form with a Macro instead of my form with VBA.

[cb@chrisb.eu]

Hiya.

This is from the form you sent back to me. The New HI form is called by VBA rather than a macro. As you'll see, I get the same problem with VBA that I get with a Macro?

Cheers

Chris B

[OBP]

Chris, I have just used the version that I sent you to add data to that form, the only thing that I had to do to get it to work was create an Interpretation record to select in the Combo.
See attached database