[cb@chrisb.eu]

Hi.

Apologies for the delay: I was on a different (non-Access!) job for a few days. I carefully saved your 11 July attached file in a new folder, opened it, opened Patient Details, pressed the new blood test button, added a short sentence to the (HI) notes field, and tried to save it.

Hey presto: same message.

Sorry! Any more ideas?

Chris B

[OBP]

The reason that the VBA is not working and you get the same error is because your HI form has not got the original change that I made.
You have

"=[Forms]![Patient Details Form]![Hospital No:]"

as the Control Source for the Hospital No.
You must change the Control Source to Hospital No. to allow the VBA to write to it.

The attached database is the one that you posted on the 8th, I have added the VBA and corrected the Hospital No. Control Source.
I have just successfully added 2 new HI records to the database for Patient record 6, the others won't work because they do not have the correct "format".

[cb@chrisb.eu]

Hi OBP, and thanks.

Do you have any idea why the HI Interpretation field insists on being filled in?

Chris

[OBP]

You must have the Table's Field Property "Required" set to "Yes".

[cb@chrisb.eu]

Hi OBP, and thanks again for your work on this so far.

One more query: many of our patients are related to each other, and we need to check that relatives have been tested, as Sickle Cell and Thassalemia are family-based conditions. So I've created a revised Family table consisting of ID / Patient Hospital No / Relationship to patient (eg Dad Mum Uncle etc) and Relative's Hospital No.

The next step was to look up the Relative's Hospital No, and show their Name and Test History. But if ask the (linked) Patient details or HI tables for information on the relative's Hospital niumber, the querry gives me info on the patient, not his relative.

I think there's a step I've missed. Could you be kind enough remind me what that might be? Thanks. Chris B

[OBP]

Chris, if you are using a Query then the Query Criteria must use the "Relative's Number" and not the Patient's number. Do you have a seperate Relations table?
That would be a good way to do it, in the Table you would have the Patient's Hospital Id and the relative's Hospital ID thus linking them together.
You could then list those "linked" relative IDs to show their details test etc.

[cb@chrisb.eu]

Cheers.

Just one more question (I hope).

My queries are reporting duplicates where none exist in the table. I thought this might be due to a linked multi-hospital number table, but one of the repeated fields is my N000000 test field, which I know is in only one record.

Any ideas?

Thanks

Chris B

[OBP]

When you say "My queries are reporting duplicates", does that mean normal Select Queries are producing Duplicate Records?
If the answer is Yes, then it is most likely because the Query contains more than one Table. To overcome this try setting the Join between the tables to "All records from the Patient Table and only those matching " from the other tables.

[cb@chrisb.eu]

Hello again OBP.

Just going through my 'things to fix' on the database before my client comes back from holiday.

The Patient table has a GP field, which is connected to the GP list table. The GP list table is sorted by GP alphabetically.
The field in the patient table is not sorted alphabetically, and asking it to be so by means of the right hand button does nothing. (Probably a good thing, since the Patient Table itself needs to be sorted by Hospital Number)
Therefore the field in the Patient Form is not alphabetical, but needs to be, especially when signing up new patients.

Any ideas?

Chris

[OBP]

The GP Field is a "Lookup" Combo box based on the GP List table. I never use them as they are too restrictive, (I know Jim would disagree ), I much prefer to create a Query on the Table, sort the name field(s) and then use that for Forms Record Sources and Combo Boxes record sources using the Combo Wizard.
Note I say name Field(s), it is good practice to have First and Last names, they can always be Concatenated in the Query for Display purposes.

[cb@chrisb.eu]

Hi OBP.
Query created, sorted and combo box linked to that.
Success.
Thanks.

[cb@chrisb.eu]

Hi again Tony

I'm calling sub forms with the normal DoCmd:

DoCmd.OpenForm "FormName"
Forms![FormName]![Hospital No:] = Me.Hospital_No_

but it opens the form with one example for that Hospital No, and then when I select the arrows, shows me all the other people's records. How do I get it to show me just the record(s) for that one hospital number?

Cheers
Chris B

[OBP]

Chris, you can do this in 3 ways, you can make the Subform's Query Criteria based on your Hospital Number, which is the method that I usually use.
You can set the new Form's Filter to the Hospital Number using the OpenArgs to pass it to the Form.
You can change the Forms Underlying Query by creating a VBA SQL statement, but that is quite hard to do.

[OBP]

Chris, you can do this in 3 ways, you can make the Subform's Query Criteria based on your Hospital Number, which is the method that I usually use.
You can set the new Form's Filter to the Hospital Number using the OpenArgs to pass it to the Form.
You can change the Forms Underlying Query by creating a VBA SQL statement, but that is quite hard to do.

[cb@chrisb.eu]

Hi again Tony

Thanks for that. It worked well for the Blood Test form, but has gone loopy on Appointments. Screen shots (and VB text) attached.