Search results

@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...

@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...

@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...

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...

@Gasman , spoken like a true professional.

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...

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...

@Gasman, I did read Pats reply. I'm getting myself in a knot as my project is driving me nuts. My other thread is below. I appreciate all you do & are mindful of not driving you nuts too! https://www.access-programmers.co.uk/forums/threads/help-with-development-of-crm-model.329504/

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...

@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...

@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...

@Pat Hartman , sorry Pat I said it back to front. You make total sense.

@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...

@Pat Hartman , and other contributers. Thanks & thanks to all. I understand now. Will make the changes you recommend & make sure all my forms reflect this practice in future. That is, 'Before_Update' to validate data and/or back out & 'After_Update' to add FK after the data has been validated...

@spaLOGICng , thanks. What you said makes sense to me. Will review my code. Cheers

@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 , 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?

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...

Thanks. I appreciate it. Trying to learn a bit more sql.

All, The 'PartyID' passed to the where statement is causing me grief. the value is not being passed, rather the text 'PartyID' is being passed. I suspect its simple syntax issue but I haven't figured it. Appreciate some help. Public Function DeleteRecord(PartyID As Long) On Error GoTo...