Form/Subform

I think I have figured it out and in doing so realized a big problem with
the database.

Specifically the SubjId field. It is and autonumber field so subsequently
everytime I input the same subject with the auto populate or inputting all
the info manually it assigns another number. Even though the subject only
appears once in the comb box the person has several subject Id numbers
assigned to him/her. When I ask the Subform to list all occurences linked to
that subject it only lists one because each time the subject is in the
database there is a new number. My subform is based on the subject Id.

Is there anyway around this? Do I have to change the autonum on the SubjId
field? Or can I base base my subform on something else like the lastname
field?

Scott
"Dirk Goldgar" wrote:

"Srowe" wrote in message
...
Not sure if I'm totally understanding this. Where would I insert the sql
code
that you provided?

Create a new query. Switch that query into SQL view. Copy and paste the
SQL I gave you into the SQL View window. Correct any table or field names I
guessed at that are incorrect. Click the Run button to run the query. If
it runs, it will prompt you for confirmation, and tell you how many records
it added to the table.

Note: before doing this, make a backup copy of your database, just in case.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

"Srowe" wrote in message
...

I think I have figured it out and in doing so realized a big problem with
the database.

Specifically the SubjId field. It is and autonumber field so subsequently
everytime I input the same subject with the auto populate or inputting all
the info manually it assigns another number. Even though the subject only
appears once in the comb box the person has several subject Id numbers
assigned to him/her. When I ask the Subform to list all occurences linked
to
that subject it only lists one because each time the subject is in the
database there is a new number. My subform is based on the subject Id.

Is there anyway around this? Do I have to change the autonum on the SubjId
field? Or can I base base my subform on something else like the lastname
field?


I'm a bit confused about your table design. It sounds like something's
wrong with it, but I'm not yet sure exactly what. Would you mind listing
the tables you are using, with the field names and types for each table, and
indicating which fields are the primary keys of their respective tables?

Also, would you please describe, in plain language, the real-world situation
your database is intended to represent? While doing so, please describe the
relationships among the various entities that are involved.

With that information, though it may take a some time for you to write it
out, I'll be better able to advise you.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

First table is the "Complaint " table. This table contains the fields that
are relevant to the complaint.

Field: Complaintnum (autonumber)(primary key)
CsummID (number) Court summary number
StatsId (number)
SubjId (number) subject id number
RepDate (date/time) reported date
Reptime (date/time) reported time

This table is for all the information relevant to the initial complaint.

"Subject" Table. Records all information of all subjects involved in the
complaint. The entry form for this inforation is a subform of the complaint
form.

Fields:
SubjId (autonum)(primary key)
compaintnum (number)
s1status (text)
S1nme (text)
S1G1 (text)
S1G2 (text)
etc...
This table contains all the infomation pertainuing to each subject. I am
able to add as many subjects as needed to this subform. The table also
contains descriptors for each person along with address, telephone etc...

"Court" table
Fields:
Courtid (autonum)(primary key)

"Court Summary" Table
Fields:
CSummID (autonumber)(primary key)
SubjId (number)
Infonum (number)


There is a total of 40 tables contained within the database. It is an old
database used for a small Police Service that I am attempting to upgrade to
make it more user friendly and compile info from different tables on to a few
different forms so the info is easily located.

The complaint table is the table that relates to all other tables which I
believe would be a one to many relationship. The subject and complaint
tables are the main tables used and the forms associated to them are the main
forms used for day to day operations.

I also have a search form that allows me to search for each subject that
contains all of their info as well as a picture. This form is based on the
"subject" table. It is like a main subject form that has a picture and more
detailed descriptors. I am trying to put a subform on this form that will
list all of the complaints that pertain to each individual as he/she is
searched.

I could attach the whole database unfortuneatley it does contain some actual
files on my woking copy that are sensitive and con not be shared.

I can list the other tables if needed. I'm not sure if it would help or
hinder you.

Your help is greatly appreciated.

Scott

"Dirk Goldgar" wrote:

"Srowe" wrote in message
...

I think I have figured it out and in doing so realized a big problem with
the database.

Specifically the SubjId field. It is and autonumber field so subsequently
everytime I input the same subject with the auto populate or inputting all
the info manually it assigns another number. Even though the subject only
appears once in the comb box the person has several subject Id numbers
assigned to him/her. When I ask the Subform to list all occurences linked
to
that subject it only lists one because each time the subject is in the
database there is a new number. My subform is based on the subject Id.

Is there anyway around this? Do I have to change the autonum on the SubjId
field? Or can I base base my subform on something else like the lastname
field?


I'm a bit confused about your table design. It sounds like something's
wrong with it, but I'm not yet sure exactly what. Would you mind listing
the tables you are using, with the field names and types for each table, and
indicating which fields are the primary keys of their respective tables?

Also, would you please describe, in plain language, the real-world situation
your database is intended to represent? While doing so, please describe the
relationships among the various entities that are involved.

With that information, though it may take a some time for you to write it
out, I'll be better able to advise you.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

"Srowe" wrote in message
...

First table is the "Complaint " table. This table contains the fields that
are relevant to the complaint.

Field: Complaintnum (autonumber)(primary key)
CsummID (number) Court summary number
StatsId (number)
SubjId (number) subject id number
RepDate (date/time) reported date
Reptime (date/time) reported time

This table is for all the information relevant to the initial complaint.

"Subject" Table. Records all information of all subjects involved in the
complaint. The entry form for this inforation is a subform of the
complaint
form.

Fields:
SubjId (autonum)(primary key)
compaintnum (number)
s1status (text)
S1nme (text)
S1G1 (text)
S1G2 (text)
etc...
This table contains all the infomation pertainuing to each subject. I am
able to add as many subjects as needed to this subform. The table also
contains descriptors for each person along with address, telephone etc...

"Court" table
Fields:
Courtid (autonum)(primary key)

"Court Summary" Table
Fields:
CSummID (autonumber)(primary key)
SubjId (number)
Infonum (number)


There is a total of 40 tables contained within the database. It is an old
database used for a small Police Service that I am attempting to upgrade
to
make it more user friendly and compile info from different tables on to a
few
different forms so the info is easily located.

The complaint table is the table that relates to all other tables which I
believe would be a one to many relationship. The subject and complaint
tables are the main tables used and the forms associated to them are the
main
forms used for day to day operations.

I also have a search form that allows me to search for each subject that
contains all of their info as well as a picture. This form is based on the
"subject" table. It is like a main subject form that has a picture and
more
detailed descriptors. I am trying to put a subform on this form that will
list all of the complaints that pertain to each individual as he/she is
searched.

I could attach the whole database unfortuneatley it does contain some
actual
files on my woking copy that are sensitive and con not be shared.

I can list the other tables if needed. I'm not sure if it would help or
hinder you.

Your help is greatly appreciated.


Sorry it took a while for me to get back to you, Scott. Life intervenes
sometimes.

From what you've said, it seems to me that a single subject can be involved
in more than one complaint, and a single complaint can involve more than one
subject. If that's not true, tell me, and disregard all of what follows.

To represent the many-to-many relationship of Subjects to Complaints, you
need a table like the SubjectsIncidents table I described earlier. Because
I now know that one of the tables to be linked is called "Complaint", let's
change the linking table name from "SubjectsIncidents" to
"SubjectsComplaints". So the SubjectsComplaints table will have these
fields:

SubjectsComplaints
----------------------------
SubjID (Number/Long Integer)
ComplaintNum (Number/Long Integer)

Both fields must be required, and the table will have a primary key composed
of both fields. If there are any data that are related specifically to this
particular subject's involvement in this particular complaint, there should
also be fields for them in this table. So you wouldn't have fields here for
name, address, etc., since those are specific to the subject only, without
regard to the complain.

When we're done, you will no longer need the SubjID in table Complaint, nor
the Complaintnum field in table "Subject", and you'll remove them. However,
if you currently have meaningful data stored in those fields, you can't
remove them yet.

As I understand it, you have or want to have a form based on Complaint, for
the purpose of displaying, adding, and editing complaints. On this form,
you want a subform that will let you add or edit the subjects involved in
the complaint. Suppose we call that subform "sfComplaintSubjects". It
should be based on the table SubjectsComplaints, *not* on table Subject. It
will include controls for all the fields from SubjectsComplaints, but the
ComplaintNum field can be made invisible (by setting its Visible property to
No) and even set to a width of 0 so that it doesn't take up any space on the
form.

When you add sfComplaintSubjects to the Complaint form as a subform, set the
Link Master Fields and Link Child Fields properties of the subform control
to ComplaintNum. That will ensure that you only see and edit the subjects
for the current complaint, and any subject you add via the subform will be
automatically stamped with the current ComplaintNum.

On the subform, use a combo box to represent the SubjID field. Set the
RowSource property of the combo box to a query of the Subject table that
includes the SubjID and, probably a calculated field that is created from
the last name, first name, and (if appropriate) middle name. The combo
box's bound column will be the SubjID column, but you'll set that column's
width to zero so that what the user sees is the name column. If you need it
to, your combo box can also include some additional columns to allow you,
when choosing a subject, to distinguish among subjects who have the same
name.

Because the combo displays the name, not the SubjID, entries will be limited
to the subjects in the list. If I were you, I'd use the combo box's
NotInList event to ask if the user wants to create a new subject, and if so,
open a form (bound to the Subject table) to do so. I'd probably also use
something like the DblClick event of the combo box to open the Subject
Details form for the selected subject, so that the user can easily review
all the information about that subject.

For your Subject Details form (whatever you call it), you can also add a
subform based on SubjectsComplaints to show all the complaints in which that
subject has been involved. In this case, the Link Master and Link Child
Fields would be the SubjID, and the subform would be set up to hide the
SubjID field but show the ComplaintNum field.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Thanks Dirk.

Just got back from so much deserved days off. You are right about the
subject/complaint portion. A subject can have multiple entries on different
complaints. Complaints can have multiple subjects.

I'll make the changes you suggested and let you know how it goes.


Thanks again.

Scott
"Dirk Goldgar" wrote:

"Srowe" wrote in message
...

First table is the "Complaint " table. This table contains the fields that
are relevant to the complaint.

Field: Complaintnum (autonumber)(primary key)
CsummID (number) Court summary number
StatsId (number)
SubjId (number) subject id number
RepDate (date/time) reported date
Reptime (date/time) reported time

This table is for all the information relevant to the initial complaint.

"Subject" Table. Records all information of all subjects involved in the
complaint. The entry form for this inforation is a subform of the
complaint
form.

Fields:
SubjId (autonum)(primary key)
compaintnum (number)
s1status (text)
S1nme (text)
S1G1 (text)
S1G2 (text)
etc...
This table contains all the infomation pertainuing to each subject. I am
able to add as many subjects as needed to this subform. The table also
contains descriptors for each person along with address, telephone etc...

"Court" table
Fields:
Courtid (autonum)(primary key)

"Court Summary" Table
Fields:
CSummID (autonumber)(primary key)
SubjId (number)
Infonum (number)


There is a total of 40 tables contained within the database. It is an old
database used for a small Police Service that I am attempting to upgrade
to
make it more user friendly and compile info from different tables on to a
few
different forms so the info is easily located.

The complaint table is the table that relates to all other tables which I
believe would be a one to many relationship. The subject and complaint
tables are the main tables used and the forms associated to them are the
main
forms used for day to day operations.

I also have a search form that allows me to search for each subject that
contains all of their info as well as a picture. This form is based on the
"subject" table. It is like a main subject form that has a picture and
more
detailed descriptors. I am trying to put a subform on this form that will
list all of the complaints that pertain to each individual as he/she is
searched.

I could attach the whole database unfortuneatley it does contain some
actual
files on my woking copy that are sensitive and con not be shared.

I can list the other tables if needed. I'm not sure if it would help or
hinder you.

Your help is greatly appreciated.


Sorry it took a while for me to get back to you, Scott. Life intervenes
sometimes.

From what you've said, it seems to me that a single subject can be involved
in more than one complaint, and a single complaint can involve more than one
subject. If that's not true, tell me, and disregard all of what follows.

To represent the many-to-many relationship of Subjects to Complaints, you
need a table like the SubjectsIncidents table I described earlier. Because
I now know that one of the tables to be linked is called "Complaint", let's
change the linking table name from "SubjectsIncidents" to
"SubjectsComplaints". So the SubjectsComplaints table will have these
fields:

SubjectsComplaints
----------------------------
SubjID (Number/Long Integer)
ComplaintNum (Number/Long Integer)

Both fields must be required, and the table will have a primary key composed
of both fields. If there are any data that are related specifically to this
particular subject's involvement in this particular complaint, there should
also be fields for them in this table. So you wouldn't have fields here for
name, address, etc., since those are specific to the subject only, without
regard to the complain.

When we're done, you will no longer need the SubjID in table Complaint, nor
the Complaintnum field in table "Subject", and you'll remove them. However,
if you currently have meaningful data stored in those fields, you can't
remove them yet.

As I understand it, you have or want to have a form based on Complaint, for
the purpose of displaying, adding, and editing complaints. On this form,
you want a subform that will let you add or edit the subjects involved in
the complaint. Suppose we call that subform "sfComplaintSubjects". It
should be based on the table SubjectsComplaints, *not* on table Subject. It
will include controls for all the fields from SubjectsComplaints, but the
ComplaintNum field can be made invisible (by setting its Visible property to
No) and even set to a width of 0 so that it doesn't take up any space on the
form.

When you add sfComplaintSubjects to the Complaint form as a subform, set the
Link Master Fields and Link Child Fields properties of the subform control
to ComplaintNum. That will ensure that you only see and edit the subjects
for the current complaint, and any subject you add via the subform will be
automatically stamped with the current ComplaintNum.

On the subform, use a combo box to represent the SubjID field. Set the
RowSource property of the combo box to a query of the Subject table that
includes the SubjID and, probably a calculated field that is created from
the last name, first name, and (if appropriate) middle name. The combo
box's bound column will be the SubjID column, but you'll set that column's
width to zero so that what the user sees is the name column. If you need it
to, your combo box can also include some additional columns to allow you,
when choosing a subject, to distinguish among subjects who have the same
name.

Because the combo displays the name, not the SubjID, entries will be limited
to the subjects in the list. If I were you, I'd use the combo box's
NotInList event to ask if the user wants to create a new subject, and if so,
open a form (bound to the Subject table) to do so. I'd probably also use
something like the DblClick event of the combo box to open the Subject
Details form for the selected subject, so that the user can easily review
all the information about that subject.

For your Subject Details form (whatever you call it), you can also add a
subform based on SubjectsComplaints to show all the complaints in which that
subject has been involved. In this case, the Link Master and Link Child
Fields would be the SubjID, and the subform would be set up to hide the
SubjID field but show the ComplaintNum field.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Hi Dirk,

So far so good. Everything you have told me so far is working and almost
making sense. Scary I know.

I have coded the not in list event to the combo box but it does not allow me
to add the new subject. It indicates to me that the subject is not in the
list and to pick a subject from the list. It seems like a simple code but I
just can't get it to work.

Scott

"Srowe" wrote:

Thanks Dirk.

Just got back from so much deserved days off. You are right about the
subject/complaint portion. A subject can have multiple entries on different
complaints. Complaints can have multiple subjects.

I'll make the changes you suggested and let you know how it goes.


Thanks again.

Scott
"Dirk Goldgar" wrote:

"Srowe" wrote in message
...

First table is the "Complaint " table. This table contains the fields that
are relevant to the complaint.

Field: Complaintnum (autonumber)(primary key)
CsummID (number) Court summary number
StatsId (number)
SubjId (number) subject id number
RepDate (date/time) reported date
Reptime (date/time) reported time

This table is for all the information relevant to the initial complaint.

"Subject" Table. Records all information of all subjects involved in the
complaint. The entry form for this inforation is a subform of the
complaint
form.

Fields:
SubjId (autonum)(primary key)
compaintnum (number)
s1status (text)
S1nme (text)
S1G1 (text)
S1G2 (text)
etc...
This table contains all the infomation pertainuing to each subject. I am
able to add as many subjects as needed to this subform. The table also
contains descriptors for each person along with address, telephone etc...

"Court" table
Fields:
Courtid (autonum)(primary key)

"Court Summary" Table
Fields:
CSummID (autonumber)(primary key)
SubjId (number)
Infonum (number)


There is a total of 40 tables contained within the database. It is an old
database used for a small Police Service that I am attempting to upgrade
to
make it more user friendly and compile info from different tables on to a
few
different forms so the info is easily located.

The complaint table is the table that relates to all other tables which I
believe would be a one to many relationship. The subject and complaint
tables are the main tables used and the forms associated to them are the
main
forms used for day to day operations.

I also have a search form that allows me to search for each subject that
contains all of their info as well as a picture. This form is based on the
"subject" table. It is like a main subject form that has a picture and
more
detailed descriptors. I am trying to put a subform on this form that will
list all of the complaints that pertain to each individual as he/she is
searched.

I could attach the whole database unfortuneatley it does contain some
actual
files on my woking copy that are sensitive and con not be shared.

I can list the other tables if needed. I'm not sure if it would help or
hinder you.

Your help is greatly appreciated.


Sorry it took a while for me to get back to you, Scott. Life intervenes
sometimes.

From what you've said, it seems to me that a single subject can be involved
in more than one complaint, and a single complaint can involve more than one
subject. If that's not true, tell me, and disregard all of what follows.

To represent the many-to-many relationship of Subjects to Complaints, you
need a table like the SubjectsIncidents table I described earlier. Because
I now know that one of the tables to be linked is called "Complaint", let's
change the linking table name from "SubjectsIncidents" to
"SubjectsComplaints". So the SubjectsComplaints table will have these
fields:

SubjectsComplaints
----------------------------
SubjID (Number/Long Integer)
ComplaintNum (Number/Long Integer)

Both fields must be required, and the table will have a primary key composed
of both fields. If there are any data that are related specifically to this
particular subject's involvement in this particular complaint, there should
also be fields for them in this table. So you wouldn't have fields here for
name, address, etc., since those are specific to the subject only, without
regard to the complain.

When we're done, you will no longer need the SubjID in table Complaint, nor
the Complaintnum field in table "Subject", and you'll remove them. However,
if you currently have meaningful data stored in those fields, you can't
remove them yet.

As I understand it, you have or want to have a form based on Complaint, for
the purpose of displaying, adding, and editing complaints. On this form,
you want a subform that will let you add or edit the subjects involved in
the complaint. Suppose we call that subform "sfComplaintSubjects". It
should be based on the table SubjectsComplaints, *not* on table Subject. It
will include controls for all the fields from SubjectsComplaints, but the
ComplaintNum field can be made invisible (by setting its Visible property to
No) and even set to a width of 0 so that it doesn't take up any space on the
form.

When you add sfComplaintSubjects to the Complaint form as a subform, set the
Link Master Fields and Link Child Fields properties of the subform control
to ComplaintNum. That will ensure that you only see and edit the subjects
for the current complaint, and any subject you add via the subform will be
automatically stamped with the current ComplaintNum.

On the subform, use a combo box to represent the SubjID field. Set the
RowSource property of the combo box to a query of the Subject table that
includes the SubjID and, probably a calculated field that is created from
the last name, first name, and (if appropriate) middle name. The combo
box's bound column will be the SubjID column, but you'll set that column's
width to zero so that what the user sees is the name column. If you need it
to, your combo box can also include some additional columns to allow you,
when choosing a subject, to distinguish among subjects who have the same
name.

Because the combo displays the name, not the SubjID, entries will be limited
to the subjects in the list. If I were you, I'd use the combo box's
NotInList event to ask if the user wants to create a new subject, and if so,
open a form (bound to the Subject table) to do so. I'd probably also use
something like the DblClick event of the combo box to open the Subject
Details form for the selected subject, so that the user can easily review
all the information about that subject.

For your Subject Details form (whatever you call it), you can also add a
subform based on SubjectsComplaints to show all the complaints in which that
subject has been involved. In this case, the Link Master and Link Child
Fields would be the SubjID, and the subform would be set up to hide the
SubjID field but show the ComplaintNum field.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

"Srowe" wrote in message
...
Hi Dirk,

So far so good. Everything you have told me so far is working and almost
making sense. Scary I know.

I have coded the not in list event to the combo box but it does not allow
me
to add the new subject. It indicates to me that the subject is not in the
list and to pick a subject from the list. It seems like a simple code but
I
just can't get it to work.


Let's see the code you have for the NotInList event. Frequently, it's just
a matter of opening the "add subject" form in dialog mode.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Hi Dirk,

Just having some computer issues. Specifically with Office. Hopefully
rectified soon.

Scott

"Dirk Goldgar" wrote:

"Srowe" wrote in message
...
Hi Dirk,

So far so good. Everything you have told me so far is working and almost
making sense. Scary I know.

I have coded the not in list event to the combo box but it does not allow
me
to add the new subject. It indicates to me that the subject is not in the
list and to pick a subject from the list. It seems like a simple code but
I
just can't get it to work.


Let's see the code you have for the NotInList event. Frequently, it's just
a matter of opening the "add subject" form in dialog mode.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Her is the code that I have for the NotinList event on my SubjId combobox:

Private Sub SubjId_NotInList(NewData As String, Response As Integer)

Dim Result
Dim Msg As String
Dim CR As String: CR = Chr$(13)


If NewData = "" Then Exit Sub


Msg = "'" & NewData & "' Subject is not in the list." & CR & CR
Msg = Msg & "Do you want to add the subject?"
If MsgBox(Msg, 32 + 4) = 6 Then

DoCmd.OpenForm "frmCustomers1", , , , A_ADD, A_DIALOG, NewData
End If


End Sub

This gets me to my main entry form "frmcustomer1". Once I am in that form I
want to be able to add the new subject and all of their pertinant data. The
nessage I get is: "You can not add or change a record because a record is
required in the table 'complaint'

Another message pops up saying that I can not save the record an error
occurred.

This is the code that is associated to the 'frmcustomers1'
Option Compare Database

Private Sub cmdAll_Click()

Dim LSQL As String

'Clear criteria
GCriteria = ""

'Display all customers
LSQL = "select * from Subject"

Form_frmCustomers1.RecordSource = LSQL
Form_frmCustomers1.Caption = "Subject Detail Form"

MsgBox "All Subjects are now displayed."

End Sub

Private Sub cmdReport_Click()

'Open report
DoCmd.OpenReport "rptCustomers", acViewPreview, , GCriteria

End Sub

Private Sub cmdSearch_Click()

DoCmd.OpenForm "frmSearch1", , , , , acDialog

End Sub



Private Sub Form_Open(Cancel As Integer)

'Clear criteria when form is first opened
GCriteria = ""

End Sub

If you need more info let me know.

Thanks again for your help.

Scott
"Srowe" wrote:

Thanks Dirk.

Just got back from so much deserved days off. You are right about the
subject/complaint portion. A subject can have multiple entries on different
complaints. Complaints can have multiple subjects.

I'll make the changes you suggested and let you know how it goes.


Thanks again.

Scott
"Dirk Goldgar" wrote:

"Srowe" wrote in message
...

First table is the "Complaint " table. This table contains the fields that
are relevant to the complaint.

Field: Complaintnum (autonumber)(primary key)
CsummID (number) Court summary number
StatsId (number)
SubjId (number) subject id number
RepDate (date/time) reported date
Reptime (date/time) reported time

This table is for all the information relevant to the initial complaint.

"Subject" Table. Records all information of all subjects involved in the
complaint. The entry form for this inforation is a subform of the
complaint
form.

Fields:
SubjId (autonum)(primary key)
compaintnum (number)
s1status (text)
S1nme (text)
S1G1 (text)
S1G2 (text)
etc...
This table contains all the infomation pertainuing to each subject. I am
able to add as many subjects as needed to this subform. The table also
contains descriptors for each person along with address, telephone etc...

"Court" table
Fields:
Courtid (autonum)(primary key)

"Court Summary" Table
Fields:
CSummID (autonumber)(primary key)
SubjId (number)
Infonum (number)


There is a total of 40 tables contained within the database. It is an old
database used for a small Police Service that I am attempting to upgrade
to
make it more user friendly and compile info from different tables on to a
few
different forms so the info is easily located.

The complaint table is the table that relates to all other tables which I
believe would be a one to many relationship. The subject and complaint
tables are the main tables used and the forms associated to them are the
main
forms used for day to day operations.

I also have a search form that allows me to search for each subject that
contains all of their info as well as a picture. This form is based on the
"subject" table. It is like a main subject form that has a picture and
more
detailed descriptors. I am trying to put a subform on this form that will
list all of the complaints that pertain to each individual as he/she is
searched.

I could attach the whole database unfortuneatley it does contain some
actual
files on my woking copy that are sensitive and con not be shared.

I can list the other tables if needed. I'm not sure if it would help or
hinder you.

Your help is greatly appreciated.


Sorry it took a while for me to get back to you, Scott. Life intervenes
sometimes.

From what you've said, it seems to me that a single subject can be involved
in more than one complaint, and a single complaint can involve more than one
subject. If that's not true, tell me, and disregard all of what follows.

To represent the many-to-many relationship of Subjects to Complaints, you
need a table like the SubjectsIncidents table I described earlier. Because
I now know that one of the tables to be linked is called "Complaint", let's
change the linking table name from "SubjectsIncidents" to
"SubjectsComplaints". So the SubjectsComplaints table will have these
fields:

SubjectsComplaints
----------------------------
SubjID (Number/Long Integer)
ComplaintNum (Number/Long Integer)

Both fields must be required, and the table will have a primary key composed
of both fields. If there are any data that are related specifically to this
particular subject's involvement in this particular complaint, there should
also be fields for them in this table. So you wouldn't have fields here for
name, address, etc., since those are specific to the subject only, without
regard to the complain.

When we're done, you will no longer need the SubjID in table Complaint, nor
the Complaintnum field in table "Subject", and you'll remove them. However,
if you currently have meaningful data stored in those fields, you can't
remove them yet.

As I understand it, you have or want to have a form based on Complaint, for
the purpose of displaying, adding, and editing complaints. On this form,
you want a subform that will let you add or edit the subjects involved in
the complaint. Suppose we call that subform "sfComplaintSubjects". It
should be based on the table SubjectsComplaints, *not* on table Subject. It
will include controls for all the fields from SubjectsComplaints, but the
ComplaintNum field can be made invisible (by setting its Visible property to
No) and even set to a width of 0 so that it doesn't take up any space on the
form.

When you add sfComplaintSubjects to the Complaint form as a subform, set the
Link Master Fields and Link Child Fields properties of the subform control
to ComplaintNum. That will ensure that you only see and edit the subjects
for the current complaint, and any subject you add via the subform will be
automatically stamped with the current ComplaintNum.

On the subform, use a combo box to represent the SubjID field. Set the
RowSource property of the combo box to a query of the Subject table that
includes the SubjID and, probably a calculated field that is created from
the last name, first name, and (if appropriate) middle name. The combo
box's bound column will be the SubjID column, but you'll set that column's
width to zero so that what the user sees is the name column. If you need it
to, your combo box can also include some additional columns to allow you,
when choosing a subject, to distinguish among subjects who have the same
name.

Because the combo displays the name, not the SubjID, entries will be limited
to the subjects in the list. If I were you, I'd use the combo box's
NotInList event to ask if the user wants to create a new subject, and if so,
open a form (bound to the Subject table) to do so. I'd probably also use
something like the DblClick event of the combo box to open the Subject
Details form for the selected subject, so that the user can easily review
all the information about that subject.

For your Subject Details form (whatever you call it), you can also add a
subform based on SubjectsComplaints to show all the complaints in which that
subject has been involved. In this case, the Link Master and Link Child
Fields would be the SubjID, and the subform would be set up to hide the
SubjID field but show the ComplaintNum field.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

(Please see my comments in-line)

"Srowe" wrote in message
...

There are lots of things going on here causing the various problems you
report, and some that you didn't report this time, but that I see coming up.

Her is the code that I have for the NotinList event on my SubjId combobox:

Private Sub SubjId_NotInList(NewData As String, Response As Integer)

Dim Result
Dim Msg As String
Dim CR As String: CR = Chr$(13)


If NewData = "" Then Exit Sub


Msg = "'" & NewData & "' Subject is not in the list." & CR & CR
Msg = Msg & "Do you want to add the subject?"
If MsgBox(Msg, 32 + 4) = 6 Then

Your code would be self-documenting if you used the named constants that
have been defined for use with the MsgBox function:

If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then


DoCmd.OpenForm "frmCustomers1", , , , A_ADD, A_DIALOG, NewData

You are using outdated constants here. Although these constants would work,
I'd recommend this instead:

DoCmd.OpenForm "frmCustomers1", , , , acFormAdd, acDialog,
NewData

With or without the above changes, if the user chooses to add the new
customer, you want to tell Access that this has happened by setting the
Response argument of the NotInList procedure to acDataErrAdded:

Response = acDataErrAdded


End If

End Sub


This gets me to my main entry form "frmcustomer1". Once I am in that form
I
want to be able to add the new subject and all of their pertinant data.
The
nessage I get is: "You can not add or change a record because a record is
required in the table 'complaint'

I am guessing that this occurs because you still have a ComplaintNum field
in the Subject table, and you have a relationship set between that field and
the Complaint table, with referential integrity enforced, and the
ComplaintNum field in table Subject has a default value of 0. Since we
changed your table design to use table SubjectsComplaints to link Subject
and Complaint in a many-to-many relationship, you should delete the direct
relationship between Subject and Complaint, and remove the ComplaintNum
field from the Subject table. Note, though, that if you have data in that
field that you haven't otherwise captured into SubjectsComplaints, you
should take care of that first.

Why is your form is named "frmCustomer1" when it's bound to the Subject
table? I'm guessing you are adapting another database or template, and
forgot to rename the form.

Another message pops up saying that I can not save the record an error
occurred.

That would be a result of the earlier error, so curing the first should
eliminate this message.

This is the code that is associated to the 'frmcustomers1'
Option Compare Database

You ought to have a statement

Option Explicit

following the "Option Compare Database" statement. That will save you no
end of debugging headaches.

You should set the VB Editor option, "Require Variable Declaration", to
cause the editor to automatically add the "Option Explicit" statement to all
new modules you create.

Private Sub cmdAll_Click()

Dim LSQL As String

'Clear criteria
GCriteria = ""

Is "GCriteria" a global variable declared in some other module? I see no
declaration for it in the posted code.


'Display all customers
LSQL = "select * from Subject"

Form_frmCustomers1.RecordSource = LSQL
Form_frmCustomers1.Caption = "Subject Detail Form"

Do not use this syntax to refer to the form. It works, under the right
circumstances, but it is flawed. Where necessary, use

Forms!frmCustomers1

or

Forms("frmCustomers1")

However, in this case, the code is running on the form you want to refer to,
so it will be more efficient just to use the "Me" keyword:

Me.RecordSource = LSQL
Me.Caption = "Subject Detail Form"


MsgBox "All Subjects are now displayed."

End Sub

Private Sub cmdReport_Click()

'Open report
DoCmd.OpenReport "rptCustomers", acViewPreview, , GCriteria

End Sub

Private Sub cmdSearch_Click()

DoCmd.OpenForm "frmSearch1", , , , , acDialog

End Sub



Private Sub Form_Open(Cancel As Integer)

'Clear criteria when form is first opened
GCriteria = ""

End Sub

I don't see anything in the rest of the code to comment on, except that I
see you are passing the NewData from the combo box to frmCustomers1 via
OpenArgs, but I don't see you using it anywhere.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)