Subform opening as new record (1 Viewer)

[Kuleesha]

I have attached my database below.
The frmPatients for has a toggle which brings up frmVisit. This form had a subform under investigation results tab page. I have set the frmVisit to automatically create a new visitID when i type in details.
When I enter data into the subform I need this to go under the same visitID as the parent form. But this data is recorded under a vew vistID instead.
What am I doing wrong ?

 

[Pat Hartman]

Dirtying a record before the user enters any data is poor practice. If you want to automatically fill in data for new records, put that code into the form's BeforeInsert event. This event runs ONLY for new records and runs immediately after the user enters the first character in some control - i.e. the user actually wants to add a record. The added advantage is that the code runs for EACH new record the user adds. You especially don't want to run code in the click event of a tab control. What if the user doesn't actually want to add a record but wants to just look at something?

 

[Kuleesha]

Thanks for your suggestion. I am creating my first database and your suggestions are extremely helpful.
I changed the code i had set to execute on clicking tabcontrol as a Sub Form BeforeInsert event.
But the primary problem of inability to enter data into the same visitID on the subform still persist.
I have attached the modified version below.

 

[Pat Hartman]

1. You need to use the Where argument of the OpenForm method when you open the visit form to open to the correct patient record if it exists.
2. The master/child links of the subform on the visit form are incorrect. This is an example where using ID as the PK for all tables comes back to bite you. The link should be on PatientID.
3. The investigation results subform has no navigation control so you can never add additional records. Also, there isn't any field that to me indicates you would have multiple values so I don't know what to do about this.
4. Mushing multiple values into a single field violates first normal form so what you are doing on Past Mx Hx is wrong. You need an additional table and each of those items should be stored in a separate row rather than all mushed into a single column. You are never going to be able to perform any analysis of whatever these things are. Your abbreviations mean nothing to me so I don't know what the data is. The values you are stuffing in look like diseases or symptoms. But the meaning of "Past Mx Hx" escapes me.

 

[Kuleesha]

Thank you for your comments.
1 & 2 - problem solved. Thanks
3. I am not planning to enter new records directly into investigation results subform. It is meant to be used only for the record indicated in the parent form
4. I actually do not want to analyse this data. I just need to keep record and print it out as a report for each and every record seperately (each record is for a patient and i just need the details on a single visit record to be printed at one time)
PastMxHx means past medical history

 

[Pat Hartman]

1&2 Glad that solved the problem.
3. if there is a 1-1 relationship between the two tables, you should probably eliminate the second table and put all the data in a single table.
4. "I actually do not want to analyze this data.". Today. But poor practice is poor practice and rarely easier enough to develop to justify creating an inferior design. There is no reason to ever collect data you don't want to analyze at some level.

 

[mjdemaris]

Have you finished this project?

If not...I would use a normal button to open the visit form, pass the patient ID to that form in the open command. Delete the extra patient ID and visit fields on the investigation sub form. And calculate the age from the date of birth.