Solved Carrying information from form to another and saving the record correctly (1 Viewer)

[bonzitre]

Hello everyone, it has been a while since I have done much with access and most stuff is going well except this issue I will explain.
My objects:

Home is the nav form, HomeSub is my patient search or new patient selection form, Patient Reports just has buttons to run reports, NewStaffF is for new staff info to be added, CallLogF is the form I am dealing with and PatientForm is supposed to feed it.

First, I am creating a database for nurses to use to track cancer patients. I have a Home navigation form that runs reports, adds staff members, searches for existing patients, and allows them to open the patient form to enter a new patient or the patient they searched. Those functions are (finally) working great! HOWEVER, on my Patient Form, I have a button that should open the call log form. Patients have a PK which is auto filled because our system MRN can have duplicates which I want to avoid.

I want it to where when I click that button, it opens the call log form and associates that new record with the patient who's detail page I was on.


I assume it has to do with how my tables/relationships/queries are setup but I can't figure out exactly what is wrong.



At one point it was carrying the information, but it was creating a new duplicate patient with a new Primary Key. I want it to where the call logs are linked to the patient so you can add new entries without having to add any patient info (all pulled from the patient details page) and can select the staff member who is logging the call. I also want it to show the patient information at the top of the call log screen (which was working when I was able to get it to pull info but then duplicated the records) which I am just using "=Nz([MRNcall] & " - " & [LastNamecall] & ", " & [FirstNamecall] & " - " & [DOBcall],"Untitled")" for that in a text box. Those are the fields on the right of the patient call log entry form which once it is all working, I would like just the txt box to show with the info and hide those boxes or if I don't need them get rid of them.

Oh Access Gurus, please help! Thank you.

 

[Uncle Gizmo]

In your Image calllogQ you show the call log ID from the call log table being held in the patient table, in the field call log ID. This would mean that the patient would have a link to just one call. So I don't think you should have that link.

In the call log table you have a field patient ID. This makes sense, every time you create a new call log item the call log table it will create a unique ID in the ID field and add a new record which will also contain the patient ID. Every time you make a new call log you will add the patient ID to the patient ID field.

If you need to see the history of patient call logs you query on the patient ID field in the call log table and that will give you a list of calls made/logged against that patient.

 

[Uncle Gizmo]

At the bottom of the patient data form there is a record number. Is that record number the patient ID?

 

[bonzitre]

At the bottom of the patient data form there is a record number. Is that record number the patient ID?

Yes. I plan on hiding it in the final product since it has no use being displayed, but i put it there just as a visual reference.

 

[bonzitre]

HEY! I think I got it. I was trying to make my query combining the tables which was causing issues. I set the MRN, name, and DOB boxes on the call log form to unbound and let it just carry that information from the patient form over so it will populate the textbox that identifies the patient, then I created a straight calllog query which i then bound the patient ID box from the call log query to, and carried the patient ID from the patient form to the call log form. I got rid of the CalllogID on the patient table and then created the relationship of the ID for the patient to the PAtientID of the calllog. Now when I click the call log entry button it opens, displays the name, populated the ID, and when I save it, it shows correctly. THANK YOU SO MUCH!

 

[Uncle Gizmo]

I plan on hiding it in the final product since it has no use being displayed, but i put it there just as a visual reference.

I always leave ID'S showing when developing, and sometimes, even in finished products! It makes it easier to debug when you mess something up!

 

[bonzitre]

I always leave ID'S showing when developing, and sometimes, even in finished products! It makes it easier to debug when you mess something up!

That is why I left it showing. However I didn't want to leave it because I don't want to confuse the end user.