Please, Help me, how to solve this? (1 Viewer)

has_access · Aug 15, 2018

Hi all
I have a database with 2 tables (Patients and Visits) , with one-many relationship (1 patients ---> to many Visits)

in Patients table, we have Patient_ID, Patient_name
visit table, we have visit_ID, visit_date , visit_description

I want to add this procedure:

when I enter a new patient's name in the add new patient form , it check whether this name is present in the patients table, if it was present, then it Automatically open a new form showing that patient info and visits.
Thank you in advance.
Has essa

Ranman256 · Aug 15, 2018

The tVisit table needs the field, PatientID.

The form wizard should be able to do this.
Build a single record form for patient, frmPatient1
Add a sub form for visits, bound on the PatientID.

Another form ,continuous, to show all patients.
An unbound text box for the name, in the header.
User enters name in the txtBox,it filters found names.

Start with the continuous form,
Search on name,
If found ,click Edit btn to view that 1 patient.
If not, click the New button (use button wizard)

Code:

Sub txtFind_afterupdate()
If IsNull(txtFind) then 
    Me.FilterOn= false
Else
Me.Filter= [lastName] ='" & txtFind & "'"
Me.FilterOn= true
End if
End sub

Sub btnEdit_click()
Docmd.openForm "frmPatient1",,,"[PatientID]=" & me.ID
End sub

CJ_London · Aug 15, 2018

you need a patientFK in the visit table to be able to link as you require

in the afterupdate event of the textbox where the user enters the patient's name (or perhaps a 'find' button click event) some code similar to this

Code:

if dcount("*","tblPatients","PatientName='" & me.entryBox & "'")<>0 then
    docmd.openform "ExistingPatients",,,"PatientName='" & me.entryBox & "'"
end if

All names made up - replace with your names

However I don't think you have thought this through enough - what if the user spells the name incorrectly?, or was originally set up differently?

has_access · Aug 16, 2018

Thank you so much, Ranman265 and CJ_London , I appreciate you help, Thanks again.

Mark_ · Aug 16, 2018

has_access said:
when I enter a new patient's name in the add new patient form , it check whether this name is present in the patients table, if it was present, then it Automatically open a new form showing that patient info and visits.
Thank you in advance.
Has essa

Just to be clear, you won't allow more than one patient with the same name? So if you have Hans Bach come in, but he's not the same Hans Bach you are use to seeing, how will you differentiate?

has_access · Aug 16, 2018

Dear Mark, Its pritty easy, I use the full Name (patient's name , his father and grandfather) , and also his age and address are also included in my database. when another patient came with similar full name ,I simply add his Family name also. Is it clear.

jdraw · Aug 16, 2018

You can use your additional info to confirm/verify a Patient's identity, but I would advise you to use PatientID (the PK of the Patient table) as a FK in your Visits table. And because a Patient may have multiple visits, you would include VisitDate to isolate a specific Patient's Visit.

You haven't said anything about your Primary Keys. And it appears you haven't shown all fields in your tables. That info would help readers provide more focused responses.

CJ_London · Aug 16, 2018

I use the full Name (patient's name , his father and grandfather) , and also his age and address are also included in my database.

so to be clear from what you are saying - to ensure you don't assign to the wrong patient, your visit record will have full name, father, grandfather, age and address?

has_access · Aug 16, 2018

Dear friends, yes a lot of fields are included in my tables so that all the important info are recorded, but sorry I don't mention all of it to simplify my question.
Thank you all.

has_access · Aug 16, 2018

jdraw said:
You can use your additional info to confirm/verify a Patient's identity, but I would advise you to use PatientID (the PK of the Patient table) as a FK in your Visits table. And because a Patient may have multiple visits, you would include VisitDate to isolate a specific Patient's Visit.

You haven't said anything about your Primary Keys. And it appears you haven't shown all fields in your tables. That info would help readers provide more focused responses.

Yes, I use the id patients as a PK and as a FK in visits table.
my database works just fine, but I try to shape it and refine it to simplify the working with it and to save time and minimize errors.
Thank you dear.

Cronk · Aug 16, 2018

In a case like this, the user should have a surname entry text box which then progressively shows in a list box a shorter number of patient names and addresses, phone numbers etc, as more of the surname is typed in. Then double click on the line showing the particular patient to open the patient record, or click a New button to add a new patient.

jdraw · Aug 16, 2018

has_access,
You can overview a description of the database, but it is important to show your tables and relationships especially when your question relates to table designs and interaction.
Saving time is in the "eye of the beholder"; when in doubt - opt for clarity and completeness.
Too many posts, not yours specifically, rely on readers' best guess/interpretation and that often leads to a long, long thread before the real requirement is unmasked.

Again, overview description and graphic of tables and relationships (in zip format) is a good approach.

Please, Help me, how to solve this? (1 Viewer)

has_access

New member

Ranman256

Well-known member

CJ_London

Super Moderator

has_access

New member

Mark_

Longboard on the internet

has_access

New member

jdraw

Super Moderator

CJ_London

Super Moderator

has_access

New member

has_access

New member

Cronk

Registered User.

jdraw

Super Moderator

Similar threads

Users who are viewing this thread