Sorry about the repost but need help

[fordrules01]

Originally posted in form coding but no response and i need to sort this out
asap. Any help greatly appreciated.


Hi,

2 questions to ask.

1) I have a CRM database that tracks all correspondance to a specific
contact through "ContactID". These contacts are then linked through joining
tables to both the centres and councils they work at. I have 3 main forms set
up for "Contacts", "Councils", "Centres". Each of these currently has a
number of subforms which show the relationships between the 3 entities and
the specific one i'm interested in is the "Correspondance_Subform".
Correspondance is only linked to ContactID however i want to display all the
correspondance for a council even if its through more than one contact.

I'll talk in terms of 2 forms here just to make it easier and only deal with
the "Council" and "Contacts" form. I have the "Council correspondance
subform" linked to the "CouncilContacts_Subform". The "Council
correspondance subform" currently shows only the correspondance for which
ever record is active in the "CouncilContacts_Subform". Is there a simple way
to get it to display all correspondance for all contacts listed in the
"CouncilContacts_Subform"?

2) Dealing with the same 2 forms. I want the user to be able to add
correspondance with a council straight from the council subform. Where i run
into problems if the contact they are making it with is not currently in the
ComboBox that lists all our contacts. I have set up a command button that
opens the a new contact form but i want it to automatically link it to the
council through the "ContactsAtCouncil_Subform" so the user does not have to
type this in.

All help greatly appreciated.

Cheers

 

[Joe]

hey ford...
"Is there a simple way to get it to display all correspondance for all
contacts listed in the "CouncilContacts_Subform"?"
Simple? - depends on you, I would say you would probably have to create or
change the current query that is driving the subform to get it to display the
required info and setting it to 'Continous Form', for example.
#2) Yes, this can all be done programmatically, but will depend on how the
relationships are setup.
In both cases there will probably be a bit of coding to be done, but if your
comfortable there shouldn't be too many problems, just the time investment.
Don't know that this really helps, but let me know.

 

[fordrules01]

Thanks for the response joe,

Yeah easy was probably the wrong word. I'm a begginer-intermediate access
user and have probably bitten off a little more than i can chew with this
project but i've spent so long on it now i just want to finish it. Below are
further details that i hope might help understand my problem.

1) The query the subform is based on is below:
SELECT qryCorrespondance.ContactRefNum, qryCorrespondance.SCCStaffID,
qryCorrespondance.ContactID, qryCorrespondance.CDate,
qryCorrespondance.CTime, qryCorrespondance.ContactMethod,
qryCorrespondance.Project, qryCorrespondance.FollowUpReq,
qryCorrespondance.FollowUpCompleted
FROM qryCorrespondance;

The Link Child: ContactID
Link Master: ContactID (Textbox in main form that draws contactID from
ContactsAtCouncil_Subform) I'm fairly sure this is the problem as it can only
display 1 variable at a time but it was the only method i could think of.

The subform is set up as a datasheet. Would it stull be required to be set
as a Continuous form?

2) Any chance of some help with the code? Have learnt the bare basics of VBA
so wouldn't really know where to start with this.

Thanks

 

[Joe]

Hey Ford,
In the original post, you wrote ... i want to display all the
correspondance for a council even if its through more than one contact.
In the second you wrote... The query the subform is based on "...FROM
qryCorrespondance"
Without knowing the tables, relationships and what qryCorrespondance is
querying it would be near impossible for me to give you any good code.
A couple things to try...
1) make a copy of the "qryCorrespondance" and try adjusting it to get the
desired results - probably the easiest way to go about it or...
2) try creating a new query in design view and refining it to what you need.
Think about this...
How do you determine what 'Council' a 'ContactID' belongs to?
Make a simple query that shows all records for those 2 fields.
Then add the "Correspondance" field and see what you get.
If you don't get the results you want, you may have to investigate the
relationships of the tables.
If you do get the results you want, start adding in the other fields you
need one at a time and check the query at each step.
If you're really confused and ready to throw your keyboard through your
monitor, contact me off-list at jlapointe at valmacind dot com.
Joe