Help with Development of CRM Model

[mike60smart]

I cannot understand why you don't just use the standard Main Form / Subform as follows to
control which Persons belong to which Organisation:-

Main Form based on tblPerson and then a
Subform based on tblOrganisationPerson
with a Lookup to select Organisation and
a Lookup to selectJobClassification

 

[HillTJ]

@jdraw , my plan is to generate a unique autonumber PartyID in frmParty, then use this value as the PK in either tblPerson or tblOrganisation(i changed the name as you suggested). On each form i use the before_update event to validate data as usual. The problem is that if a record is not saved in either frmParty or frmOrganisation, then there has still been a record created in frmParty because its created first. It would be good to create hat record last & after the data has been validated but i don't know how. Thus, in such an instance i need to delete that entry in frmParty as its orphaned. I currently use the form_close event to check whether there is an entry in firstname, lastname, or dob, but i believe this is incorrect logic as the form_validate routine confirms required fields as well.

Also if you try to add another copy of a record that already exists, then the msgbox pops up but afterwoods an access system message pops up. I don't know why.

Maybe i've put the cart in front of the horse?, and i'd be happy to accept a better way.

Hope i made sense. Cheers

 

[HillTJ]

@mike60smart, could do it your way but not all people belong to an organisation. There may be a person who belongs to an organisation, but that person may have children (who may belong to an organisation at some point). My plan is to be fully inclusive of all family relationships. Thats my logic anyway!

 

[HillTJ]

@MajP , had a go with your recommendation as per post #7, but came up with an issue where PartyID is used as the Fk in other table relationships. How do you suggest I handle these? I've uploaded a copy of my work in progress so that you can see what I mean. I've generated 2 forms frmParty_Person & frmParty_Organisation along the lines you recommended. Appreciate the help.

 

[jdraw]

"I've uploaded a copy of my work in progress so that you can see what I mean."

Nothing attached??

 

[HillTJ]

Didn't Upload. See attached

 

[HillTJ]

@jdraw, I saw that too. See post #26. My Bad!

 

[HillTJ]

All fixed. Using Before_Update event to validate data & if validated successfully, using if me.newrecord to run the code to make the new record in frmParty, then using the dmax to find the max partyID PK which is then saved as either frmPerson or frmOrganisation PK. Thanks to those whom contributed. Cheers.

 

[TadesaeN]

I admire your determination! I remember when I was tackling a similar challenge, trying to link different tables in my database seamlessly. It can definitely be a head-scratcher at times!

Regarding your form not populating, have you checked your query or data source? Sometimes a small glitch there can throw everything off. Double-checking your logic and code step by step might help uncover any issues.

 

[HillTJ]

@TadesaeN , all fixed. Project is progressing well. Got login, user access rights, attachments & working on assigning tasks & reminders at the moment. Most comprehensive CRM i've seen. Able to "drill down" to related organisations & related personnel, assign personnel to job roles within organisations etc. Aim is to make it "universal" so that it works across different industries with their unique job roles.

Got to 'get outside your comfort zone' to learn new stuff.

Pretty happy with he progress at the moment (but can change without notice!).

See my original post for reference to an excellent treatise on the concept. I develped mine from this concept.

Cheers

 

[HillTJ]

@Pat Hartman , did you have a look at the table design developed in the reference on my original post. My database is derrived from a modified version of this. I have standardised lookup tables where possible and flag them as either 'organisation' or 'personel' related. Upon reflection, yes i probably should have used a single table for both 'organisational' & 'personnel' records. I might even have a go. Problem is i've invested quite a bit of time into this model, but the upside is quite a bit should be reusable. I wish you hadn't said anything., now you"ve got me thinking. Out of the comfort zone i go again....

 

[HillTJ]

@Pat Hartman , thankyou for your input. I'll have a good hard look at my design. The original design l adapted (link in post 1) made sense to me, but I understand what you are saying. I might make a copy of my database and modify it to a single entity table design. I just don't relish the work though...Outside my comfort zone I go again.