Help with form design needed (Really struggling :-) )

[Tony Williams]

I’m not sure if this is a design problem or just me not able to think this
through. I’ve struggled for a couple of days and not sure where to go from
here.

Background
I have a table (tblindividual) which stores the names of officers of an
association. Fields are pretty standard, first name surname business address
and so on.
I also have a table where I want to store details of complaints
(tblcomplaints). Fields in here hold such things as the complainant, what
type of complaint etc. I want to build a form that enables us to store the
details of the complaint and who they are complaining against, ie which
officer and then create some regular reports. Sounded pretty easy to me at
first.

When the complaint comes in, we need to log it in a form, presumably based
on tblcomplaints and then on the same form be able to record the details of
the officer concerned, presumably on a subform. The main form and subform
would be linked with the Primary key (CID) on the tblcomplaints and a foreign
key say txtCID from the tblindividual. However I want to limit the amount of
keying that is done and so I want to automate as many fields from the
tblindividual as I can. But how? I had thought about firstly creating a
combobox with the list of names of the officers so that the user can choose
the relevant one and then whichever was chosen would populate the additional
relevant fields on the form. But if the combobox showed a list of names, made
up of first name and surname, what do I have as the control source because
without the control source I don’t seem to be able to populate the extra
fields?

Also when I come to write the reports if the name hasn’t been stored and the
extra fields populated how can I reflect all the data in the report.

I’m sure there must be an obvious answer to this and it’s probably staring
me in the face but I’m now getting too close to it to see what it is!

Would really appreciate some guidance on this, as I said I thought this
would be easy.
Thanks
Tony

 

[KARL DEWEY]

I think you need it the other way around - Officer/Compliant Form/Subform.
But if there might be cases where there multiple officers on the single
complaint then you need three tables.
Officer, Compliant, and OfficerComplaint as a junction table. Set a
one-to-many relationships from Officer to OfficerComplaint and Compliant to
OfficerComplaint.
Create two forms and use same subform for both. The Master/Child links of
form/subform to use OfficerID for first and ComplaintID for second.
Use a combo to select complaint on subform of Officer-Complaint and on the
Complaint-Officer the combo on subform select officer.
Populate the Officer table with all names, rank, etc.

 

[Tony Williams]

Thanks for the roadmap Karl that's given me some ideas as to where to start.
What would be the source and content of the subform in your example?
Thanks again
Tony

 

[KARL DEWEY]

I was wrong when I said to 'use same subform for both.'
One will be using junction table to show complaints of officers and other to
show officers involved in a complaint.
Use queries that have the junction table and the complaiant or office table
based on which it is.

 

[Tony Williams]

Thanks Karl I think I've got the idea now. Your email gave me a Eureka moment
at 3am last night!!!
Thanks Tony