If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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) |
#12
|
|||
|
|||
Form/Subform
"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) |
#13
|
|||
|
|||
Form/Subform
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) |
#14
|
|||
|
|||
Form/Subform
"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) |
#15
|
|||
|
|||
Form/Subform
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) |
#16
|
|||
|
|||
Form/Subform
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) |
#17
|
|||
|
|||
Form/Subform
"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) |
#18
|
|||
|
|||
Form/Subform
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) |
#19
|
|||
|
|||
Form/Subform
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) |
#20
|
|||
|
|||
Form/Subform
(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) |
Thread Tools | |
Display Modes | |
|
|