VBA needed for add New Note in form

[MarcCastignani]

Hi Guys,

Hoping you can help me.

table A called residents
table B called ProcessNotes

the relationship is the following, one to many.

Residents PK: ProcessNoteID
ProcessNote FK: ProcessNoteID_FK

I have a list box with notes for clients. on double click it opens a new form with the notes, from the list box.
Now I Have created a button for new record, the problem is that I have to manually enter the Key fields unlike when i add the records in the child table ProcessNotes and it automatically assigns the FK Field hence One to many. How would i do that on the form with the New Button?

Thanks appreciate the help

 

[MajP]

A screen shot of this would help explain. Not really sure how this is set up. A screen shot of your relationship window would help because I do not get your relationship notation. I assume the Note has an FK to a resident. When you click the New note button can you pass the Resident ID in open args? Then in the new note forms before insert event set the Resident ID fk. This relates the new note to that resident.

 

[MarcCastignani]

 

[MarcCastignani]

Thanks for the reply!, heres the relationships

 

[MajP]

Not sure that makes any sense.
The notes table should have a ProcessNoteID PK, and an ResidentID_FK to a resident.
So an employee can have many residents (whatever that means). And a resident can have many notes.
Get rid of processNoteID in residents table. Residents table needs a ResidentsID_FK

 

[MarcCastignani]

Sorry im very new at this so please bear with me, please see attached

 

[MarcCastignani]

Is that right now?

 

[Gasman]

Is that right now?

I would say No?
Employees would have a Primary key EmployeeID
Residents would have a Primary key ResidentID
ProcessNotes would have a Primary key ProcessNotesID

Thiose PKs would then become Foreign keys in their respective tables, depending on your relationships.

At least that is how I would do it.

 

[MajP]

Not quite. Every table needs a PK. Normally that is very similar to the name of the table

ProcessNotes
-ProcessID ' Primary key uniquely IDs a note.
-ResidentID_FK' a note relates to a resident. Many notes can have the same resident
-note fields

Residents
-ResidentID ' PK for a resident
-EmployeID_FK ' FK to an employee. An employee can have many residets.

Where this could get more complicated is when a Note is not unique, but reused. If you had common notes and you pull from a list of common notes
Inspection Complete
Final Payment Recieved

 

[MajP]

The reason I am not sure if this is more complicated is why there is a listbox and a pop up.
Normally this is done with a subform.
MainForm is Resident and subform is notes.

Now in your case where an Employee has Many residents and a Resident has many notes then the form could get complicated with nested subforms.
Main Form Employee
Subform Resident
SubSubForm Note

There are ways to do this cleanly. But if you want continuous subforms this is harder

 

[MarcCastignani]

Please see attached. Iv changed Name residents to clients. Im sure iv just been trying to do things to complicated. but im sure that the relationships are right now. let me know if thats better, then sub forms is the way forward now. thanks so far

 

[MajP]

That looks correct.
If your form design was simply Resident as main form and Notes in a subform this could be done very simply in a subform without any code.
The form is still doable with Employees, Residents, and Notes it just depends how you want to show this and how much information to show.

You could do this with cascading combos in the header. Pick an employee from combo 1. Then combo 2 would have only those residents related to that employee. Then the form is notes.
If you do not have a lot of employees and residents you could have a two column list box on the left side of the form. First column is Employee, second is Client. The right side of the form is a subform for adding notes to whatever resident is selected.

You can do this nested subforms, but I find those confusing. This requires not fancy code though.
Main form employees
Subform in single form view with residents
subsubform in continuous form or single to add notes.

 

[MarcCastignani]

Thank you so much for your help, i really appreciate your time and effort.

Have a blessed day!