Help with Development of CRM Model (1 Viewer)

[HillTJ]

Friends, I’ve started a new project which is to be a CRM system along the lines of that published by CRM Design Tutorial. I like the flexibility that this design provides. I have built the database tables and relationships. A design consideration is the 1:1 relationship of PartyID to both tblOrganisation & tblPerson to tblParty. That is, a unique PartyID is applied as the PK in both said tables. This is new to me & I'm having a bit of trouble!.

So, I’ve made a form ‘frmMain’ onto which I have two buttons, to add new Organisations or People respectively. My intention is when either button is depressed, then a record is created in tblParty & that partyID is passed to the form as an openarg. The default value of the PK is then set as the value of the openarg upon close of either form.

Upon closing the form (either frmEstablishment or frmPerson) the new record is saved and the PK is reflected on both tblParty and the form respectively. Happy Days!. When I close either form, my intent is to determine whether the record has any entries or whether it is merely closed without adding data. In the latter case, then I need to delete the related entry in tblParty.

Currently, frmEstablishment is not being populated & I don’t know why.

This may be a tall ask, but I’d appreciate assistance in reviewing my logic & checking whether my code makes sense. If you have any recommendations, then I’d really like to hear them.

 

[jdraw]

A few comments after a very brief look at your database.
-Have you vetted your model with some sample data and sample transactions? I ask because you are into physical database, and may not have "verified intended logic". (see this for additional info)

In PartyDelete in module ModDeleteParty, the code below applies to an add, not a delete, so I'm wondering how much vetting/testing has been done.

If db.RecordsAffected = 0 Then
        MsgBox "No New records were created by the above query?"
    End If

PartyEmailId is PK of tblPartyPhone?? Also PartyEmailId is PK of tblPartyContact??

-Why use Establishment when you have referred to Organization? Is there some rationale?

-When adding and/or deleting records, you may wish to created a log for audit purposes?
We don't know your intent ,but in a CRM audit logging may be important.

I commented your Form Close code on frmEstablishment and on frmPerson.
'If Me.Dirty Then
' DoCmd.RunCommand acCmdSaveRecord
' Else
' Call DeleteParty(Me.OpenArgs)
' Me.Undo
'End If

I made a change to your frmEstablishment in OrganisationName_AfterUpdate()

If Me.OrganisationName <> "" Then
        DoCmd.RunCommand acCmdSaveRecord
    End If

and to frmPerson in BeforeUpdate

If Len(Me.CurrentLastname & "") > 0 And _
    Len(Me.CurrentFirstname & "") > 0 Then
    DoCmd.RunCommand acCmdSaveRecord
End If

And ran(added) a record for each Party type. Below is the sequence of procedure flow starting on frmMain

TrackProcReviewLogForm TrackProcReviewLogForm

Id	ObjectName	Event_ProcName	sysDateAdded
1​	JP	*** start after adding debug info UserChkPoint***	25-Nov-23 9:51:37 AM​
2​	frmMain	BttnEstablishment_Click()	25-Nov-23 9:51:43 AM​
3​	ModAddParty	AddParty	25-Nov-23 9:51:43 AM​
4​	frmEstablishment	Form_Open(Cancel As Integer)	25-Nov-23 9:51:45 AM​
5​	frmEstablishment	OrganisationName_AfterUpdate()	25-Nov-23 9:51:58 AM​
6​	frmEstablishment	Form_Close()	25-Nov-23 9:52:01 AM​
7​	frmMain	BttnPerson_Click()	25-Nov-23 9:52:07 AM​
8​	ModAddParty	AddParty	25-Nov-23 9:52:07 AM​
9​	frmPerson	Form_Open(Cancel As Integer)	25-Nov-23 9:52:09 AM​
10​	frmPerson	CurrentLastname_BeforeUpdate(Cancel As Integer)	25-Nov-23 9:52:36 AM​
11​	frmPerson	Form_Close()	25-Nov-23 9:52:46 AM​

 

[HillTJ]

@jdraw , thanks a million. It's great to have an 'outsider' review the logic & yes it's a little rough at the moment. I'll take onboard the changes you recommend, make some 'trial' data entry forms & prove the concept before i invest too heavily..Appreciate it. I don't see the amended file attached?

 

[HillTJ]

@jdraw , can you please check for me the logic in the case that a user opens either frmPerson or frmEstablishment then closes without entering a record. In these instances a new record is made in frparty that i attempt to delete (hence, the deletemodule). Appreciate it.

 

[jdraw]

@jdraw , can you please check for me the logic in the case that a user opens either frmPerson or frmEstablishment then closes without entering a record. In these instances a new record is made in frparty that i attempt to delete (hence, the deletemodule). Appreciate it.

No problem.
I opened frmMain, clicked Establishment, then close frmEstablishment. Then went back to frmMain, clicked Person, then closed frmPerson.

Here is the sequence of procedures(logic) that occurred:

TrackProcReviewLogForm TrackProcReviewLogForm

Id	ObjectName	Event_ProcName	sysDateAdded
1​	JP	*** Star no data enry in frmEstab/Person UserChkPoint***	25-Nov-23 6:23:30 PM​
2​	frmMain	BttnEstablishment_Click()	25-Nov-23 6:24:04 PM​
3​	ModAddParty	AddParty	25-Nov-23 6:24:04 PM​
4​	frmEstablishment	Form_Open(Cancel As Integer)	25-Nov-23 6:24:07 PM​
5​	frmEstablishment	Form_Close()	25-Nov-23 6:24:10 PM​
6​	JP	*** clicked est then closed frmEstablishment UserChkPoint*	25-Nov-23 6:25:53 PM​
7​	frmMain	BttnPerson_Click()	25-Nov-23 6:25:56 PM​
8​	ModAddParty	AddParty	25-Nov-23 6:25:56 PM​
9​	frmPerson	Form_Open(Cancel As Integer)	25-Nov-23 6:25:59 PM​
10​	frmPerson	Form_Close()	25-Nov-23 6:26:03 PM​
11​	JP	*** after clicking Person, then closing frmPerson UserChkP	25-Nov-23 6:26:36 PM​

You get a record in tblParty because of the code behind the form-actually the button click for Establishment/Person.

You Call AddParty and create a Party record. And, because you close the frmEstablishment (or frmPerson) without adding data, you get:
--the record in tblParty each time you click either button on frmMain

And, since the criteria for saving an Establishment/Organization record, or for saving a Person record was not provided, then no record is saved for tblOrganization, nor tblPerson.

 

[HillTJ]

@jdraw, works every time to populate a PartyID to either table (and I've renamed to 'frmOrganisation' as you correctly picked up). But, if a user were to close either form without entering any data by just clicking close at a new record, then a record has already been made in tblParty. My intent is to run code 'DeleteParty' in that instance so that there is no unrelated record in tblParty. I just don't know how to trigger the code to run. Is there a more sensible method?

 

[MajP]

I do not think 1 to 1 relationship makes sense. Or at least I would not do it that way.

I would simply give each table its unique PK (PersonID and organizationID) then the foreign key party fk (PartyID_FK) .
You can index PartyID_FK as unique and required to ensure that no person or organization exists with out a party and no party can be assigned more than one person or organization.
The difference is subtle, but I think more logical to what this is really doing.

 

[HillTJ]

@MajP, yes will mock up your recommended design & have a go. With my model, a problem i foresee is when a user opens either frmOrganisation or frmParty, then closes the form without entering data. My code has already added a new record to tblParty at that point. I have a detete routine that works from the immediate window that deletes the most recent record in frmParty, but it's unclear to me how to trigger it with a form event. Any help would be appreciated.

 

[Gasman]

Can't you use the same logic from your other thread?

Use tempvars to open a related form

Mind you, I see that Chat GPT has removed my error handling and replaced it with its own, which is bloody annoying! I'm arguing with it at the moment, trying to get it to use my preferred error handling, but it's like talking to a brick wall sometimes! Chat GPT made a good suggestion in that...

www.access-programmers.co.uk

 

[jdraw]

HillTJ,
As you now know, you are getting the new Party record(s) because it is created when you click either the Establishment or Person button on frmMain. So the logic to prevent same is to not add the Party record on frmMain. Don't add a record until you have validated the criteria for a new record. As MajP said, 1-to-1 relationships are quite rare in relational database--usually reserved to handle "sensitive data to a limited group of users".
As before, I'm suggesting you spend time vetting the model before getting too deep into physical database.

 

[HillTJ]

@Gasman , you are correct. This project was what generate the other thread that you refer to. I have made progress but intend to make sure that the the code is correct before I invest too much. @jdraw , yes I agree with you last post. Thanks to contributors to this thread, I have an idea to try. I usually use autonumber Pk's, this is a different logic to me that I have to get my head around.

 

[HillTJ]

All, I really am stuck!. I've read every response to this thread & I've unsuccessfully tried everything that i can. Currently, upon selecting the control 'Organisation' on frmMain a record is inserted into tblParty, and the corresponding form opens & the default PK of that form is passed the value of the PK from tblParty. And the same happens if the 'Organisation' control is selected. However, i cannot get the before_Update event to fire to validate entries. The same for 'CurrentLastname' & CurrentFirstName on frmPerson. I do not understand it.

If someone could take the time to have a look at my logic & be really grateful. I don't wish to wear out my welcome & appreciate any help you can provide. If I'm totally off track then I'd appreciate rerouting!

I've attached an updated version of the application.

 

[GaP42]

Your before update on both Organisation the Persons forms are working - the Persons form does not have any data validation rules.
Some sample validation on the Person form placed in the before update event below. The validation on the organisation name is just to test if a name is there. DO you want to test for duplicate name for eg?

If Len(Trim(Me.CurrentFirstname)) < 1 Or IsNull(Me.CurrentFirstname) Then

    MsgBox "The First name was not entered" & vbCrLf & _
            "A first name must be entered for every person record", vbInformation, "Data Entry Error"
    Cancel = True
    Me.CurrentFirstname.SetFocus
    Exit Sub
End If
If Len(Trim(Me.CurrentLastname)) < 1 Or IsNull(Me.CurrentLastname) Then

    MsgBox "The Last name was not entered" & vbCrLf & _
            "A first name must be entered for every person record", vbInformation, "Data Entry Error"
    Cancel = True
    Me.CurrentLastname.SetFocus
    Exit Sub
End If

If IsNull(Me.DOB) Then
    MsgBox "No date of birth recorded", vbInformation, "DOB Mandatory"
    Cancel = True
    Me.DOB.SetFocus
    Exit Sub
End If

If IsNull(Me.ActivePerson_Y_N_) Then
    MsgBox "The is active stus has not been set", vbInformation, "IsActive Flag not set"
    Cancel = True
    Me.ActiveControl.SetFocus
    Exit Sub
End If

 

[jdraw]

HillTJ,

Do you have a list of what information on a data entry form MUST be present? And for each of those are there other criteria---things like DOB, City, State, ZipCode, IsActive, Picture.....? Do you need a logic check to see if this Person or Org exists, and if so, are there further validations required? Are or could your Establishments be part of other Establishments -- is the ABC_ACME corp part of/subsidiary of the XYZ Manufacturing? Do you care? Do you have a business rule governing that?
Determining this can involve test cases against your data model. Often good to vet your model with test cases with data containing errors and good data/without errors. As you get more into testing, it is often better to get others involved with test data/cases --we all have a self-interest to have things work as expected(according to spec), but may have a bias (unintended) when testing our own code.

 

[HillTJ]

Gents, orgnisation must be unique & @jdraw , i do need to build in the ability to capture a company being a subdivision of another. I hadn't thought of it!. In terms of persons, there cannot be duplicate names. Had intended to dlookup for duplicate firstname+lastname+DOB & make sure these are unique. @jdraw , never a truer word said about "self-testing".
@GaP42 , i don't see the before update event firing. That is , the message box appearing upon closing frOrganisation without entering any data. Do you? My intention is not to save the form without a valid entry, and if the user accidentally opens the form to close it without saving & delete the corresponding new record generated in tblParty (otherwise its orphaned).

Definately appreciate your help.

 

[HillTJ]

Put the validation code in the frm_BeforeUpdate event of frmPerson. @GaP42 , My bad. I open and display a blank form, then can close the blank form and obviously, no record has been generated so no before_update event. Amazing what a nights sleep & a clear head can do! Clearly it's working as envisaged. Appreciate it.

 

[Gasman]

Perhaps, take a step back, pause, take a breath, and think of what is needed, but broken down into small steps?
Then address, one at a time.?

 

[HillTJ]

@Gasman , spoken like a true professional.

 

[HillTJ]

So, I've made progress & added code to prevent duplicate firstname+lastname+Dob on frmPerson & it works Fine. But I think I have a 'logic' problem. I'm using the onclose event of that form to trigger the deletion of the corresponding record in tblParty when the user leaves either firstname or lastname or DOB blank. In that instance, the record is not saved in tblPerson & the code DeleteParty is run to remove the corresponding entry in tblParty. Firstname, Lastname & DOB should never be blank in the firstplace as I'm screening out these inputs with the before update code!. What event can I use to call 'DeleteParty' when a record is not saved? It's difficult to explain, but i suspect I'm using the wrong event to trigger the DeleteParty code, but I don't know which i should use. I've attached the latest version. Please note the additional error message that pops up when you attempt to save the same record twice.

Appreciate any help.

 

[jdraw]

You are still adding a PartyID when you click a button on frmMain.
When you add a duplicate person, you get an error message, BUT the form remains populated. Should you be clearing the data and advising the user to add a different person and/or should you have a way to exit that form?

Why exactly are you adding a Party record before you have validated the info for the entity you are adding. I would think you would only add a party record when you add a vetted org or person.

You are into physical database (the HOW to do what you want) but you have not clearly identified WHAT you want or should be done.