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 |
#1
|
|||
|
|||
Subform of a Subform
I have several years experience with Access queries and other
reporting uses but very little experience with forms designed for inexperienced users. I am using Access 2002 with a database with the following: Tables Fields Classes ClassNo ClassName StuID StartDate EndDate Students StuID Name Address Events EventNo StuID EventDate EventCode Comments I want to set up a form that allows inexperienced users to review related data from the above three tables. I want the main form to be tied to the Classes table and allow the user to select from a combo box the ClassNo to be viewed. Then, I want all the students in that ClassNo to be presented in a subform in datasheet view. When the user is on a particular student's record in that subform, I want another subform below it to present a subform of events for that student in a datasheet view. I also want the user to be able to filter the events in the last subform to only show those with particular EventCodes which the user selects with a combo box. Is it possible to do what I want? Can someone outline the steps I need to take? Will any coding be required to do this because my understanding of Access modules and code is extremely limited. Alan |
#2
|
|||
|
|||
Subform of a Subform
Hello Alan.
"Alan" wrote: I have several years experience with Access queries and other reporting uses but very little experience with forms designed for inexperienced users. I am using Access 2002 with a database with the following: Tables Fields Classes ClassNo ClassName StuID StartDate EndDate Students StuID Name Address Events EventNo StuID EventDate EventCode Comments As an also experienced database developer, let me ask you this: Instead of storing a StuID in the Classes table, wouldn't it be much better to create an additional table ClassesStudents for a many-to-many relationship between Classes and Students that contains the two columns ClassNo and StuID? For convienience, I suggest (although many people and MVPs hate this) to make the StuID in that table a lookup field that displays the Name column from the Students table (and stores StuID in a hidden column). I want to set up a form that allows inexperienced users to review related data from the above three tables. I want the main form to be tied to the Classes table and allow the user to select from a combo box the ClassNo to be viewed. Create the main form based on the Classes table displaying all the information that is desired (ClassName, STartDate, EndDate). The create a ComboBox in the form header that allows the user to go to the selected class (Third option in the ComboBox wizard). Then, I want all the students in that ClassNo to be presented in a subform in datasheet view. Create a subform in the main form that is based on the ClassesStudents table mentioned above, disüplaying the StuID field. If you followd my advice from above, this field will automatically be displayed in a combo box that lists all the names of students. Enter ClassNo in both the LinkChildFields and LinkMasterFields properties of the subform control (if it is not entered automatically) and specify datasheet view as the default view of this form. When the user is on a particular student's record in that subform, I want another subform below it to present a subform of events for that student in a datasheet view. Create a textbox in the main form (txtStuID), make it invisible (set it's visible property to false) and use the Expression Builder to change the controlsource property to a reference to the StuID textbox in the suform (that you already created for ClassesStudents). Then, create a second subform in the main form based on the Events table that displays all relevant information (EventDate and Comments). Set the default view to Datasheet. Set the LinkChildFields property of the subform control to StuID and set the LinkMasterFields property to [txtStuID].[Value]. This will filter all the Events for the selected student. I also want the user to be able to filter the events in the last subform to only show those with particular EventCodes which the user selects with a combo box. Create a combo box in the main form, maybe above the second subform. Make it an unbound combo box (value stored in the control for later reference, not stored in the database), make it display all possible event codes. Assume you name it cboEventCode. Then, add additional information in the two properties of the subform control, so that they read: LinkChildFields: StuID;EventCode LinkMasterFields: [txtStuID].[Value];[cboEventCode].[Value] Is it possible to do what I want? Can someone outline the steps I need to take? Will any coding be required to do this because my understanding of Access modules and code is extremely limited. I hope that this was helful, and until here, there's no code involved that you had to create yourself. The ComboBox wizard (for the Class selection in the form header) created a small VBA procedure for you, and it is important, that you do not rename that combo box. -- Regards, Wolfgang |
#3
|
|||
|
|||
Subform of a Subform
On May 8, 7:09 am, "Wolfgang Kais"
wrote: Hello Alan. "Alan" wrote: I have several years experience with Access queries and other reporting uses but very little experience with forms designed for inexperienced users. I am using Access 2002 with a database with the following: Tables Fields Classes ClassNo ClassName StuID StartDate EndDate Students StuID Name Address Events EventNo StuID EventDate EventCode Comments As an also experienced database developer, let me ask you this: Instead of storing a StuID in the Classes table, wouldn't it be much better to create an additional table ClassesStudents for a many-to-many relationship between Classes and Students that contains the two columns ClassNo and StuID? For convienience, I suggest (although many people and MVPs hate this) to make the StuID in that table a lookup field that displays the Name column from the Students table (and stores StuID in a hidden column). I want to set up a form that allows inexperienced users to review related data from the above three tables. I want the main form to be tied to the Classes table and allow the user to select from a combo box the ClassNo to be viewed. Create the main form based on the Classes table displaying all the information that is desired (ClassName, STartDate, EndDate). The create a ComboBox in the form header that allows the user to go to the selected class (Third option in the ComboBox wizard). Then, I want all the students in that ClassNo to be presented in a subformin datasheet view. Create asubformin the main form that is based on the ClassesStudents table mentioned above, disüplaying the StuID field. If you followd my advice from above, this field will automatically be displayed in a combo box that lists all the names of students. Enter ClassNo in both the LinkChildFields and LinkMasterFields properties of thesubform control (if it is not entered automatically) and specify datasheet view as the default view of this form. When the user is on a particular student's record in thatsubform, I want anothersubformbelow it to present asubformof events for that student in a datasheet view. Create a textbox in the main form (txtStuID), make it invisible (set it's visible property to false) and use the Expression Builder to change the controlsource property to a reference to the StuID textbox in the suform (that you already created for ClassesStudents). Then, create a secondsubformin the main form based on the Events table that displays all relevant information (EventDate and Comments). Set the default view to Datasheet. Set the LinkChildFields property of thesubformcontrol to StuID and set the LinkMasterFields property to [txtStuID].[Value]. This will filter all the Events for the selected student. I also want the user to be able to filter the events in the last subformto only show those with particular EventCodes which the user selects with a combo box. Create a combo box in the main form, maybe above the secondsubform. Make it an unbound combo box (value stored in the control for later reference, not stored in the database), make it display all possible event codes. Assume you name it cboEventCode. Then, add additional information in the two properties of thesubform control, so that they read: LinkChildFields: StuID;EventCode LinkMasterFields: [txtStuID].[Value];[cboEventCode].[Value] Is it possible to do what I want? Can someone outline the steps I need to take? Will any coding be required to do this because my understanding of Access modules and code is extremely limited. I hope that this was helful, and until here, there's no code involved that you had to create yourself. The ComboBox wizard (for the Class selection in the form header) created a small VBA procedure for you, and it is important, that you do not rename that combo box. -- Regards, Wolfgang Wolfgang, thanks so much for your most helpful reply. I have the following additional comments and questions: 1. Your comments about the poor design of my Classes table were spot on. To shorten my post, I combined two actual tables in my database, Classes and Class Rolls (what you called ClassesStudents), and called it Classes. I apologize for the confusion and appreciate your pointing out the apparent design flaw. 2. My StuID field is comprised of the first 3 letters of the last name, the first 3 letters of the first name and a numeric "tie- breaker." Although I don't have that field set up as a defined lookup field, I do use a combo box based on a sorted query to allow the user to pick the appropriate StuID based on name on any forms involving adding or editing students. 3. Does it matter whether the combo box on the main form where the user selects the ClassNo to be viewed is bound or unbound? Are there advantages or disadvantages of each? 4. Concerning the invisible textbox on the main form named txtStuID, if the StuID combo box on my ClassesStudents subform is named cboStuID, should the controlsource property of this textbox be something like =cboStuID? 5. Concerning the Events subform, you say I should set the LinkMasterFields property to [txtStuID].[Value]. I assume I literally type in the word Value inside the square brackets? Is that one of those special words used to represent the underlying control's value? I didn't know you could do that. Thanks again for your help. I would never have been able to work out what I needed to accomplish my goals. Alan |
#4
|
|||
|
|||
Subform of a Subform
Hello Alan.
Alan wrote: I have several years experience with Access queries and other reporting uses but very little experience with forms designed for inexperienced users. I am using Access 2002 with a database with the following: Tables Fields Classes ClassNo ClassName StuID StartDate EndDate Students StuID Name Address Events EventNo StuID EventDate EventCode Comments As an also experienced database developer, let me ask you this: Instead of storing a StuID in the Classes table, wouldn't it be much better to create an additional table ClassesStudents for a many-to-many relationship between Classes and Students that contains the two columns ClassNo and StuID? For convienience, I suggest (although many people and MVPs hate this) to make the StuID in that table a lookup field that displays the Name column from the Students table (and stores StuID in a hidden column). I want to set up a form that allows inexperienced users to review related data from the above three tables. I want the main form to be tied to the Classes table and allow the user to select from a combo box the ClassNo to be viewed. Create the main form based on the Classes table displaying all the information that is desired (ClassName, STartDate, EndDate). The create a ComboBox in the form header that allows the user to go to the selected class (Third option in the ComboBox wizard). Then, I want all the students in that ClassNo to be presented in a subformin datasheet view. Create asubformin the main form that is based on the ClassesStudents table mentioned above, disüplaying the StuID field. If you followd my advice from above, this field will automatically be displayed in a combo box that lists all the names of students. Enter ClassNo in both the LinkChildFields and LinkMasterFields properties of the subform control (if it is not entered automatically) and specify datasheet view as the default view of this form. When the user is on a particular student's record in thatsubform, I want anothersubformbelow it to present asubformof events for that student in a datasheet view. Create a textbox in the main form (txtStuID), make it invisible (set it's visible property to false) and use the Expression Builder to change the controlsource property to a reference to the StuID textbox in the suform (that you already created for ClassesStudents). Then, create a second subform in the main form based on the Events table that displays all relevant information (EventDate and Comments). Set the default view to Datasheet. Set the LinkChildFields property of the subform control to StuID and set the LinkMasterFields property to [txtStuID].[Value]. This will filter all the Events for the selected student. I also want the user to be able to filter the events in the last subform to only show those with particular EventCodes which the user selects with a combo box. Create a combo box in the main form, maybe above the secondsubform. Make it an unbound combo box (value stored in the control for later reference, not stored in the database), make it display all possible event codes. Assume you name it cboEventCode. Then, add additional information in the two properties of the subform control, so that they read: LinkChildFields: StuID;EventCode LinkMasterFields: [txtStuID].[Value];[cboEventCode].[Value] Is it possible to do what I want? Can someone outline the steps I need to take? Will any coding be required to do this because my understanding of Access modules and code is extremely limited. I hope that this was helful, and until here, there's no code involved that you had to create yourself. The ComboBox wizard (for the Class selection in the form header) created a small VBA procedure for you, and it is important, that you do not rename that combo box. Wolfgang, thanks so much for your most helpful reply. I have the following additional comments and questions: 1. Your comments about the poor design of my Classes table were spot on. To shorten my post, I combined two actual tables in my database, Classes and Class Rolls (what you called ClassesStudents), and called it Classes. I apologize for the confusion and appreciate your pointing out the apparent design flaw. Uh, I see. Ok. 2. My StuID field is comprised of the first 3 letters of the last name, the first 3 letters of the first name and a numeric "tie- breaker." Although I don't have that field set up as a defined lookup field, I do use a combo box based on a sorted query to allow the user to pick the appropriate StuID based on name on any forms involving adding or editing students. Good. 3. Does it matter whether the combo box on the main form where the user selects the ClassNo to be viewed is bound or unbound? Are there advantages or disadvantages of each? You mean the ClassNo in the form header. This should be unbound and named differently from ClassNo (for example cboClassNo). This combo is used solely for navigation and not for changing the ClassNo of a Class (which should not work if ClassNo was the primary key), therefore this combo box has to be unbound. Don't worry, the wizard knows that. 4. Concerning the invisible textbox on the main form named txtStuID, if the StuID combo box on my ClassesStudents subform is named cboStuID, should the controlsource property of this textbox be something like =cboStuID? No, it should be something like =Forms!NameOfMainForm!NameOfSubformControl.Form!cb oStuID 5. Concerning the Events subform, you say I should set the LinkMasterFields property to [txtStuID].[Value]. I assume I literally type in the word Value inside the square brackets? Is that one of those special words used to represent the underlying control's value? I didn't know you could do that. Sorry, I have to revise this, the Value property did not work. Just use the name of the control, LinkMasterFields: txtStuID So finally: LinkChildFields: StuID;EventCode LinkMasterFields: txtStuID;cboEventCode Thanks again for your help. I would never have been able to work out what I needed to accomplish my goals. You are wellcome. -- Regards, Wolfgang |
#5
|
|||
|
|||
Subform of a Subform
On May 8, 3:48 pm, "Wolfgang Kais"
wrote: Hello Alan. Alan wrote: I have several years experience with Access queries and other reporting uses but very little experience with forms designed for inexperienced users. I am using Access 2002 with a database with the following: Tables Fields Classes ClassNo ClassName StuID StartDate EndDate Students StuID Name Address Events EventNo StuID EventDate EventCode Comments As an also experienced database developer, let me ask you this: Instead of storing a StuID in the Classes table, wouldn't it be much better to create an additional table ClassesStudents for a many-to-many relationship between Classes and Students that contains the two columns ClassNo and StuID? For convienience, I suggest (although many people and MVPs hate this) to make the StuID in that table a lookup field that displays the Name column from the Students table (and stores StuID in a hidden column). I want to set up a form that allows inexperienced users to review related data from the above three tables. I want the main form to be tied to the Classes table and allow the user to select from a combo box the ClassNo to be viewed. Create the main form based on the Classes table displaying all the information that is desired (ClassName, STartDate, EndDate). The create a ComboBox in the form header that allows the user to go to the selected class (Third option in the ComboBox wizard). Then, I want all the students in that ClassNo to be presented in a subformin datasheet view. Create asubformin the main form that is based on the ClassesStudents table mentioned above, disüplaying the StuID field. If you followd my advice from above, this field will automatically be displayed in a combo box that lists all the names of students. Enter ClassNo in both the LinkChildFields and LinkMasterFields properties of the subformcontrol (if it is not entered automatically) and specify datasheet view as the default view of this form. When the user is on a particular student's record in thatsubform, I want anothersubformbelow it to present asubformof events for that student in a datasheet view. Create a textbox in the main form (txtStuID), make it invisible (set it's visible property to false) and use the Expression Builder to change the controlsource property to a reference to the StuID textbox in the suform (that you already created for ClassesStudents). Then, create a secondsubformin the main form based on the Events table that displays all relevant information (EventDate and Comments). Set the default view to Datasheet. Set the LinkChildFields property of thesubformcontrol to StuID and set the LinkMasterFields property to [txtStuID].[Value]. This will filter all the Events for the selected student. I also want the user to be able to filter the events in the last subformto only show those with particular EventCodes which the user selects with a combo box. Create a combo box in the main form, maybe above the secondsubform. Make it an unbound combo box (value stored in the control for later reference, not stored in the database), make it display all possible event codes. Assume you name it cboEventCode. Then, add additional information in the two properties of thesubform control, so that they read: LinkChildFields: StuID;EventCode LinkMasterFields: [txtStuID].[Value];[cboEventCode].[Value] Is it possible to do what I want? Can someone outline the steps I need to take? Will any coding be required to do this because my understanding of Access modules and code is extremely limited. I hope that this was helful, and until here, there's no code involved that you had to create yourself. The ComboBox wizard (for the Class selection in the form header) created a small VBA procedure for you, and it is important, that you do not rename that combo box. Wolfgang, thanks so much for your most helpful reply. I have the following additional comments and questions: 1. Your comments about the poor design of my Classes table were spot on. To shorten my post, I combined two actual tables in my database, Classes and Class Rolls (what you called ClassesStudents), and called it Classes. I apologize for the confusion and appreciate your pointing out the apparent design flaw. Uh, I see. Ok. 2. My StuID field is comprised of the first 3 letters of the last name, the first 3 letters of the first name and a numeric "tie- breaker." Although I don't have that field set up as a defined lookup field, I do use a combo box based on a sorted query to allow the user to pick the appropriate StuID based on name on any forms involving adding or editing students. Good. 3. Does it matter whether the combo box on the main form where the user selects the ClassNo to be viewed is bound or unbound? Are there advantages or disadvantages of each? You mean the ClassNo in the form header. This should be unbound and named differently from ClassNo (for example cboClassNo). This combo is used solely for navigation and not for changing the ClassNo of a Class (which should not work if ClassNo was the primary key), therefore this combo box has to be unbound. Don't worry, the wizard knows that. 4. Concerning the invisible textbox on the main form named txtStuID, if the StuID combo box on my ClassesStudentssubformis named cboStuID, should the controlsource property of this textbox be something like =cboStuID? No, it should be something like =Forms!NameOfMainForm!NameOfSubformControl.Form!cb oStuID 5. Concerning the Eventssubform, you say I should set the LinkMasterFields property to [txtStuID].[Value]. I assume I literally type in the word Value inside the square brackets? Is that one of those special words used to represent the underlying control's value? I didn't know you could do that. Sorry, I have to revise this, the Value property did not work. Just use the name of the control, LinkMasterFields: txtStuID So finally: LinkChildFields: StuID;EventCode LinkMasterFields: txtStuID;cboEventCode Thanks again for your help. I would never have been able to work out what I needed to accomplish my goals. You are wellcome. -- Regards, Wolfgang Wolfgang, thanks so much for your assistance with this project. I've learned a great deal about forms using your suggestions. I have one final request. I changed the default view of the second subform based on Events to be Continuous Forms rather than Datasheert. I have the second subform displaying all Event records that match the invisible txtStuID control on the main form as you suggested. I want to give the user the option to filter those displayed records based on a selected EventCode value from a combo box named CboEventCode that I put in that second subform's header. However, the combo box wizard does not provide a filtering option. I figure this can be done via some code in the After Update event of the combo but I've been unable to figure out how to do it with either code or macros. Is what I want to do possible? Can you help? Thanks. Alan |
#6
|
|||
|
|||
Subform of a Subform
Hello Alan.
Alan wrote: I have several years experience with Access queries and other reporting uses but very little experience with forms designed for inexperienced users. I am using Access 2002 with a database with the following: Tables Fields Classes ClassNo ClassName StuID StartDate EndDate Students StuID Name Address Events EventNo StuID EventDate EventCode Comments As an also experienced database developer, let me ask you this: Instead of storing a StuID in the Classes table, wouldn't it be much better to create an additional table ClassesStudents for a many-to-many relationship between Classes and Students that contains the two columns ClassNo and StuID? For convienience, I suggest (although many people and MVPs hate this) to make the StuID in that table a lookup field that displays the Name column from the Students table (and stores StuID in a hidden column). I want to set up a form that allows inexperienced users to review related data from the above three tables. I want the main form to be tied to the Classes table and allow the user to select from a combo box the ClassNo to be viewed. Create the main form based on the Classes table displaying all the information that is desired (ClassName, STartDate, EndDate). The create a ComboBox in the form header that allows the user to go to the selected class (Third option in the ComboBox wizard). Then, I want all the students in that ClassNo to be presented in a subformin datasheet view. Create a subform in the main form that is based on the ClassesStudents table mentioned above, displaying the StuID field. If you followed my advice from above, this field will automatically be displayed in a combo box that lists all the names of students. Enter ClassNo in both the LinkChildFields and LinkMasterFields properties of the subform control (if it is not entered automatically) and specify datasheet view as the default view of this form. When the user is on a particular student's record in that subform, I want another subform below it to present a subform of events for that student in a datasheet view. Create a textbox in the main form (txtStuID), make it invisible (set it's visible property to false) and use the Expression Builder to change the controlsource property to a reference to the StuID textbox in the subform (that you already created for ClassesStudents). Then, create a second subform in the main form based on the Events table that displays all relevant information (EventDate and Comments). Set the default view to Datasheet. Set the LinkChildFields property of the subform control to StuID and set the LinkMasterFields property to [txtStuID].[Value]. This will filter all the Events for the selected student. I also want the user to be able to filter the events in the last subform to only show those with particular EventCodes which the user selects with a combo box. Create a combo box in the main form, maybe above the second subform. Make it an unbound combo box (value stored in the control for later reference, not stored in the database), make it display all possible event codes. Assume you name it cboEventCode. Then, add additional information in the two properties of the subform control, so that they read: LinkChildFields: StuID;EventCode LinkMasterFields: [txtStuID].[Value];[cboEventCode].[Value] Is it possible to do what I want? Can someone outline the steps I need to take? Will any coding be required to do this because my understanding of Access modules and code is extremely limited. I hope that this was helful, and until here, there's no code involved that you had to create yourself. The ComboBox wizard (for the Class selection in the form header) created a small VBA procedure for you, and it is important that you do not rename that combo box. Wolfgang, thanks so much for your most helpful reply. I have the following additional comments and questions: 1. Your comments about the poor design of my Classes table were spot on. To shorten my post, I combined two actual tables in my database, Classes and Class Rolls (what you called ClassesStudents), and called it Classes. I apologize for the confusion and appreciate your pointing out the apparent design flaw. Uh, I see. Ok. 2. My StuID field is comprised of the first 3 letters of the last name, the first 3 letters of the first name and a numeric "tie- breaker." Although I don't have that field set up as a defined lookup field, I do use a combo box based on a sorted query to allow the user to pick the appropriate StuID based on name on any forms involving adding or editing students. Good. 3. Does it matter whether the combo box on the main form where the user selects the ClassNo to be viewed is bound or unbound? Are there advantages or disadvantages of each? You mean the ClassNo in the form header. This should be unbound and named differently from ClassNo (for example cboClassNo). This combo is used solely for navigation and not for changing the ClassNo of a Class (which should not work if ClassNo was the primary key), therefore this combo box has to be unbound. Don't worry, the wizard knows that. 4. Concerning the invisible textbox on the main form named txtStuID, if the StuID combo box on my ClassesStudents subform is named cboStuID, should the controlsource property of this textbox be something like =cboStuID? No, it should be something like =Forms!NameOfMainForm!NameOfSubformControl.Form!cb oStuID 5. Concerning the Events subform, you say I should set the LinkMasterFields property to [txtStuID].[Value]. I assume I literally type in the word Value inside the square brackets? Is that one of those special words used to represent the underlying control's value? I didn't know you could do that. Sorry, I have to revise this, the Value property did not work. Just use the name of the control, LinkMasterFields: txtStuID So finally: LinkChildFields: StuID;EventCode LinkMasterFields: txtStuID;cboEventCode Thanks again for your help. I would never have been able to work out what I needed to accomplish my goals. You are wellcome. Wolfgang, thanks so much for your assistance with this project. I've learned a great deal about forms using your suggestions. I have one final request. I changed the default view of the second subform based on Events to be Continuous Forms rather than Datasheet. I have the second subform displaying all Event records that match the invisible txtStuID control on the main form as you suggested. I want to give the user the option to filter those displayed records based on a selected EventCode value from a combo box named CboEventCode that I put in that second subform's header. However, the combo box wizard does not provide a filtering option. I figure this can be done via some code in the After Update event of the combo but I've been unable to figure out how to do it with either code or macros. Is what I want to do possible? Can you help? Thanks. So you did not like the EventCode linking main form and second subform, all right. You want an optional filter and placed a combo box in the header of that second subform that is displayed as continuous form now, that's perfect. To make the filter work, we indeed have to use some VBA code or a macro, where I prefer VBA. In the properties window of that cboEventCode (it should be unbound!), look for the AfterUpdate event property, click in that property and click on the period button beside the property. From the dialog box that appears, select "Code-Generator" and click "Ok". Edit the event procedure to look like this: Private Sub cboEventCode_AfterUpdate() If IsNull(Me.cboEventCode) Then Me.FilterOn = False Me.Filter = "" Else Me.Filter = "EventCode=" & Me.cboEventCode Me.FilterOn = True End If End Sub In case that EventCode was not numeric but text, the one line must be: Me.Filter = "EventCode='" & Me.cboEventCode & "'" -- Regards, Wolfgang |
#7
|
|||
|
|||
Subform of a Subform
On May 11, 4:10 pm, "Wolfgang Kais"
wrote: Hello Alan. Alan wrote: I have several years experience with Access queries and other reporting uses but very little experience with forms designed for inexperienced users. I am using Access 2002 with a database with the following: Tables Fields Classes ClassNo ClassName StuID StartDate EndDate Students StuID Name Address Events EventNo StuID EventDate EventCode Comments As an also experienced database developer, let me ask you this: Instead of storing a StuID in the Classes table, wouldn't it be much better to create an additional table ClassesStudents for a many-to-many relationship between Classes and Students that contains the two columns ClassNo and StuID? For convienience, I suggest (although many people and MVPs hate this) to make the StuID in that table a lookup field that displays the Name column from the Students table (and stores StuID in a hidden column). I want to set up a form that allows inexperienced users to review related data from the above three tables. I want the main form to be tied to the Classes table and allow the user to select from a combo box the ClassNo to be viewed. Create the main form based on the Classes table displaying all the information that is desired (ClassName, STartDate, EndDate). The create a ComboBox in the form header that allows the user to go to the selected class (Third option in the ComboBox wizard). Then, I want all the students in that ClassNo to be presented in a subformin datasheet view. Create asubformin the main form that is based on the ClassesStudents table mentioned above, displaying the StuID field. If you followed my advice from above, this field will automatically be displayed in a combo box that lists all the names of students. Enter ClassNo in both the LinkChildFields and LinkMasterFields properties of thesubformcontrol (if it is not entered automatically) and specify datasheet view as the default view of this form. When the user is on a particular student's record in that subform, I want anothersubformbelow it to present asubformof events for that student in a datasheet view. Create a textbox in the main form (txtStuID), make it invisible (set it's visible property to false) and use the Expression Builder to change the controlsource property to a reference to the StuID textbox in thesubform(that you already created for ClassesStudents). Then, create a secondsubformin the main form based on the Events table that displays all relevant information (EventDate and Comments). Set the default view to Datasheet. Set the LinkChildFields property of thesubformcontrol to StuID and set the LinkMasterFields property to [txtStuID].[Value]. This will filter all the Events for the selected student. I also want the user to be able to filter the events in the last subformto only show those with particular EventCodes which the user selects with a combo box. Create a combo box in the main form, maybe above the second subform. Make it an unbound combo box (value stored in the control for later reference, not stored in the database), make it display all possible event codes. Assume you name it cboEventCode. Then, add additional information in the two properties of thesubform control, so that they read: LinkChildFields: StuID;EventCode LinkMasterFields: [txtStuID].[Value];[cboEventCode].[Value] Is it possible to do what I want? Can someone outline the steps I need to take? Will any coding be required to do this because my understanding of Access modules and code is extremely limited. I hope that this was helful, and until here, there's no code involved that you had to create yourself. The ComboBox wizard (for the Class selection in the form header) created a small VBA procedure for you, and it is important that you do not rename that combo box. Wolfgang, thanks so much for your most helpful reply. I have the following additional comments and questions: 1. Your comments about the poor design of my Classes table were spot on. To shorten my post, I combined two actual tables in my database, Classes and Class Rolls (what you called ClassesStudents), and called it Classes. I apologize for the confusion and appreciate your pointing out the apparent design flaw. Uh, I see. Ok. 2. My StuID field is comprised of the first 3 letters of the last name, the first 3 letters of the first name and a numeric "tie- breaker." Although I don't have that field set up as a defined lookup field, I do use a combo box based on a sorted query to allow the user to pick the appropriate StuID based on name on any forms involving adding or editing students. Good. 3. Does it matter whether the combo box on the main form where the user selects the ClassNo to be viewed is bound or unbound? Are there advantages or disadvantages of each? You mean the ClassNo in the form header. This should be unbound and named differently from ClassNo (for example cboClassNo). This combo is used solely for navigation and not for changing the ClassNo of a Class (which should not work if ClassNo was the primary key), therefore this combo box has to be unbound. Don't worry, the wizard knows that. 4. Concerning the invisible textbox on the main form named txtStuID, if the StuID combo box on my ClassesStudentssubformis named cboStuID, should the controlsource property of this textbox be something like =cboStuID? No, it should be something like =Forms!NameOfMainForm!NameOfSubformControl.Form!cb oStuID 5. Concerning the Eventssubform, you say I should set the LinkMasterFields property to [txtStuID].[Value]. I assume I literally type in the word Value inside the square brackets? Is that one of those special words used to represent the underlying control's value? I didn't know you could do that. Sorry, I have to revise this, the Value property did not work. Just use the name of the control, LinkMasterFields: txtStuID So finally: LinkChildFields: StuID;EventCode LinkMasterFields: txtStuID;cboEventCode Thanks again for your help. I would never have been able to work out what I needed to accomplish my goals. You are wellcome. Wolfgang, thanks so much for your assistance with this project. I've learned a great deal about forms using your suggestions. I have one final request. I changed the default view of the second subformbased on Events to be Continuous Forms rather than Datasheet. I have the secondsubformdisplaying all Event records that match the invisible txtStuID control on the main form as you suggested. I want to give the user the option to filter those displayed records based on a selected EventCode value from a combo box named CboEventCode that I put in that secondsubform'sheader. However, the combo box wizard does not provide a filtering option. I figure this can be done via some code in the After Update event of the combo but I've been unable to figure out how to do it with either code or macros. Is what I want to do possible? Can you help? Thanks. So you did not like the EventCode linking main form and secondsubform, all right. You want an optional filter and placed a combo box in the header of that secondsubformthat is displayed as continuous form now, that's perfect. To make the filter work, we indeed have to use some VBA code or a macro, where I prefer VBA. In the properties window of that cboEventCode (it should be unbound!), look for the AfterUpdate event property, click in that property and click on the period button beside the property. From the dialog box that appears, select "Code-Generator" and click "Ok". Edit the event procedure to look like this: Private Sub cboEventCode_AfterUpdate() If IsNull(Me.cboEventCode) Then Me.FilterOn = False Me.Filter = "" Else Me.Filter = "EventCode=" & Me.cboEventCode Me.FilterOn = True End If End Sub In case that EventCode was not numeric but text, the one line must be: Me.Filter = "EventCode='" & Me.cboEventCode & "'" -- Regards, Wolfgang Wolfgang, I don't mean to gush but you're amazing. The form and subforms are working perfectly and I feel as if I've made substantial progress in climbing the form's learning curve. Thanks so much for all your help. Alan |
Thread Tools | |
Display Modes | |
|
|