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
|
|||
|
|||
Form/Subform Checkbox
In Access 2002 I have a form ‘Employee’ and a sub form ‘Training’ that lists
all the training courses that the employee has taken. There’s a checkbox on both forms called ‘Left’ but the user doesn’t want to go through the sub forms and check them all individually. I therefore need to link the forms so that when the user checks the ‘Left’ box on the employee form, the ‘Left’ box on all the sub forms for that employee also checks. I’d really appreciate some help on how to do this. |
#2
|
|||
|
|||
Form/Subform Checkbox
Minnie,
on "all the sub forms" for that employee also checks. I think you mean that when you select Left on the main form, all the "subform records" (associated with that employee) will be checked True. We should be bealing with only one subform... Your main form should have a unique key value that relates to a key value in the subform table. Like an EmployeeID... Using the AfterUpdate event of the main form's Left checkbox... If Left = True... then run an Update query against the subform table that sets all the Left values to True in the subform table... **where the EmployeeID is equal to the EmployeeID on the main form** If Left = False, run an Update query against the subform table that does the opposite. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Minnie M" wrote in message news In Access 2002 I have a form 'Employee' and a sub form 'Training' that lists all the training courses that the employee has taken. There's a checkbox on both forms called 'Left' but the user doesn't want to go through the sub forms and check them all individually. I therefore need to link the forms so that when the user checks the 'Left' box on the employee form, the 'Left' box on all the sub forms for that employee also checks. I'd really appreciate some help on how to do this. |
#3
|
|||
|
|||
Form/Subform Checkbox
Hi Al, sorry about the incorrect terminology in the question, you’re correct
about what you think I meant. I still don’t completely understand what I need to do, I don’t suppose you could give me an idiot guide could you? I’m new to writing code and am not sure what to type and where to type it, it’s quite confusing for a newbie. I’d really appreciate if you could give me a little more help. Thanks ever so much. "Al Campagna" wrote: Minnie, on "all the sub forms" for that employee also checks. I think you mean that when you select Left on the main form, all the "subform records" (associated with that employee) will be checked True. We should be bealing with only one subform... Your main form should have a unique key value that relates to a key value in the subform table. Like an EmployeeID... Using the AfterUpdate event of the main form's Left checkbox... If Left = True... then run an Update query against the subform table that sets all the Left values to True in the subform table... **where the EmployeeID is equal to the EmployeeID on the main form** If Left = False, run an Update query against the subform table that does the opposite. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Minnie M" wrote in message news In Access 2002 I have a form 'Employee' and a sub form 'Training' that lists all the training courses that the employee has taken. There's a checkbox on both forms called 'Left' but the user doesn't want to go through the sub forms and check them all individually. I therefore need to link the forms so that when the user checks the 'Left' box on the employee form, the 'Left' box on all the sub forms for that employee also checks. I'd really appreciate some help on how to do this. |
#4
|
|||
|
|||
Form/Subform Checkbox
Minnie,
First rule regarding Update queries... back up your original data! What is the name of your Main form, and your Subform? What is the name of the check field on the Main form. What is the field name, on the subform, that we want to set to true? What is the name of the table that your subform is based on? -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Minnie M" wrote in message ... Hi Al, sorry about the incorrect terminology in the question, you're correct about what you think I meant. I still don't completely understand what I need to do, I don't suppose you could give me an idiot guide could you? I'm new to writing code and am not sure what to type and where to type it, it's quite confusing for a newbie. I'd really appreciate if you could give me a little more help. Thanks ever so much. "Al Campagna" wrote: Minnie, on "all the sub forms" for that employee also checks. I think you mean that when you select Left on the main form, all the "subform records" (associated with that employee) will be checked True. We should be bealing with only one subform... Your main form should have a unique key value that relates to a key value in the subform table. Like an EmployeeID... Using the AfterUpdate event of the main form's Left checkbox... If Left = True... then run an Update query against the subform table that sets all the Left values to True in the subform table... **where the EmployeeID is equal to the EmployeeID on the main form** If Left = False, run an Update query against the subform table that does the opposite. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Minnie M" wrote in message news In Access 2002 I have a form 'Employee' and a sub form 'Training' that lists all the training courses that the employee has taken. There's a checkbox on both forms called 'Left' but the user doesn't want to go through the sub forms and check them all individually. I therefore need to link the forms so that when the user checks the 'Left' box on the employee form, the 'Left' box on all the sub forms for that employee also checks. I'd really appreciate some help on how to do this. |
#5
|
|||
|
|||
Form/Subform Checkbox
Hi Al, thanks for the reminder to back up my data, no problem, I’m working on
a copy of the db. To answer your questions: Main form is Employee Information Form, subform is Training Form The name of the check field on the main form is Left The field name on the subform to be set to true is Left Company The table that the subform is based on is Training Many thanks for your help. "Al Campagna" wrote: Minnie, First rule regarding Update queries... back up your original data! What is the name of your Main form, and your Subform? What is the name of the check field on the Main form. What is the field name, on the subform, that we want to set to true? What is the name of the table that your subform is based on? -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Minnie M" wrote in message ... Hi Al, sorry about the incorrect terminology in the question, you're correct about what you think I meant. I still don't completely understand what I need to do, I don't suppose you could give me an idiot guide could you? I'm new to writing code and am not sure what to type and where to type it, it's quite confusing for a newbie. I'd really appreciate if you could give me a little more help. Thanks ever so much. "Al Campagna" wrote: Minnie, on "all the sub forms" for that employee also checks. I think you mean that when you select Left on the main form, all the "subform records" (associated with that employee) will be checked True. We should be bealing with only one subform... Your main form should have a unique key value that relates to a key value in the subform table. Like an EmployeeID... Using the AfterUpdate event of the main form's Left checkbox... If Left = True... then run an Update query against the subform table that sets all the Left values to True in the subform table... **where the EmployeeID is equal to the EmployeeID on the main form** If Left = False, run an Update query against the subform table that does the opposite. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Minnie M" wrote in message news In Access 2002 I have a form 'Employee' and a sub form 'Training' that lists all the training courses that the employee has taken. There's a checkbox on both forms called 'Left' but the user doesn't want to go through the sub forms and check them all individually. I therefore need to link the forms so that when the user checks the 'Left' box on the employee form, the 'Left' box on all the sub forms for that employee also checks. I'd really appreciate some help on how to do this. |
#6
|
|||
|
|||
Form/Subform Checkbox
Minnie,
I forgot to ask what your key (Parent/Child) field on the Main and Sub is... so I'll just use EmpID for this example... you use your own name/s. Create an Update query. Open a new Query design grid, and select Query/Update from the query menu. Place your LeftCompany field on the grid, and fill in the UpdateTo for LeftCompany to... True Place your key field EmpID from Training onto the grid, and give it a criteria of... =Forms![Employee Information Form]![EmpID] Save this query as qryLeftCompanyTrue. This query will update every Training record to True... that has an EmpID equal to that of the EmpID on your open form. Why not just create a button on the Main form called cmdSetTrainingToTrue. Any time this button is clicked, it sets all the associated Traing records to True. Using the OnClick event of the button, use this code... Private Sub cmdSetTrainingToTrue_Click() DoCmd.OpenQuery "qryLeftCompanyTrue" End Sub OR... If your more comfortable with Macros... just use the OpenQuery Action instead. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Minnie M" wrote in message ... Hi Al, thanks for the reminder to back up my data, no problem, I'm working on a copy of the db. To answer your questions: Main form is Employee Information Form, subform is Training Form The name of the check field on the main form is Left The field name on the subform to be set to true is Left Company The table that the subform is based on is Training Many thanks for your help. "Al Campagna" wrote: Minnie, First rule regarding Update queries... back up your original data! What is the name of your Main form, and your Subform? What is the name of the check field on the Main form. What is the field name, on the subform, that we want to set to true? What is the name of the table that your subform is based on? -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Minnie M" wrote in message ... Hi Al, sorry about the incorrect terminology in the question, you're correct about what you think I meant. I still don't completely understand what I need to do, I don't suppose you could give me an idiot guide could you? I'm new to writing code and am not sure what to type and where to type it, it's quite confusing for a newbie. I'd really appreciate if you could give me a little more help. Thanks ever so much. "Al Campagna" wrote: Minnie, on "all the sub forms" for that employee also checks. I think you mean that when you select Left on the main form, all the "subform records" (associated with that employee) will be checked True. We should be bealing with only one subform... Your main form should have a unique key value that relates to a key value in the subform table. Like an EmployeeID... Using the AfterUpdate event of the main form's Left checkbox... If Left = True... then run an Update query against the subform table that sets all the Left values to True in the subform table... **where the EmployeeID is equal to the EmployeeID on the main form** If Left = False, run an Update query against the subform table that does the opposite. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Minnie M" wrote in message news In Access 2002 I have a form 'Employee' and a sub form 'Training' that lists all the training courses that the employee has taken. There's a checkbox on both forms called 'Left' but the user doesn't want to go through the sub forms and check them all individually. I therefore need to link the forms so that when the user checks the 'Left' box on the employee form, the 'Left' box on all the sub forms for that employee also checks. I'd really appreciate some help on how to do this. |
#7
|
|||
|
|||
Form/Subform Checkbox
Thanks Al, I’ve followed your instructions but I must have done something
wrong as I get the following ‘Compile error: Syntax error’ message when I click the ‘Left’ checkbox on my main form: On the visual basic screen, the top left hand box is set to Left, the top right hand box is set to After Update, the following is in the text part of the screen: Option Compare Database Option Explicit Private Sub Left_AfterUpdate() (this line is highlighted in yellow with a yellow arrow pointing to it from the left) If Left = True (this line is highlighted) End Sub Private Sub cmdSetTrainingToTrue_Click() DoCmd.OpenQuery "qryLeftCompanyTrue" End Sub Hope this makes sense to you, and I’d really appreciate a little more help. "Al Campagna" wrote: Minnie, I forgot to ask what your key (Parent/Child) field on the Main and Sub is... so I'll just use EmpID for this example... you use your own name/s. Create an Update query. Open a new Query design grid, and select Query/Update from the query menu. Place your LeftCompany field on the grid, and fill in the UpdateTo for LeftCompany to... True Place your key field EmpID from Training onto the grid, and give it a criteria of... =Forms![Employee Information Form]![EmpID] Save this query as qryLeftCompanyTrue. This query will update every Training record to True... that has an EmpID equal to that of the EmpID on your open form. Why not just create a button on the Main form called cmdSetTrainingToTrue. Any time this button is clicked, it sets all the associated Traing records to True. Using the OnClick event of the button, use this code... Private Sub cmdSetTrainingToTrue_Click() DoCmd.OpenQuery "qryLeftCompanyTrue" End Sub OR... If your more comfortable with Macros... just use the OpenQuery Action instead. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Minnie M" wrote in message ... Hi Al, thanks for the reminder to back up my data, no problem, I'm working on a copy of the db. To answer your questions: Main form is Employee Information Form, subform is Training Form The name of the check field on the main form is Left The field name on the subform to be set to true is Left Company The table that the subform is based on is Training Many thanks for your help. "Al Campagna" wrote: Minnie, First rule regarding Update queries... back up your original data! What is the name of your Main form, and your Subform? What is the name of the check field on the Main form. What is the field name, on the subform, that we want to set to true? What is the name of the table that your subform is based on? -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Minnie M" wrote in message ... Hi Al, sorry about the incorrect terminology in the question, you're correct about what you think I meant. I still don't completely understand what I need to do, I don't suppose you could give me an idiot guide could you? I'm new to writing code and am not sure what to type and where to type it, it's quite confusing for a newbie. I'd really appreciate if you could give me a little more help. Thanks ever so much. "Al Campagna" wrote: Minnie, on "all the sub forms" for that employee also checks. I think you mean that when you select Left on the main form, all the "subform records" (associated with that employee) will be checked True. We should be bealing with only one subform... Your main form should have a unique key value that relates to a key value in the subform table. Like an EmployeeID... Using the AfterUpdate event of the main form's Left checkbox... If Left = True... then run an Update query against the subform table that sets all the Left values to True in the subform table... **where the EmployeeID is equal to the EmployeeID on the main form** If Left = False, run an Update query against the subform table that does the opposite. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Minnie M" wrote in message news In Access 2002 I have a form 'Employee' and a sub form 'Training' that lists all the training courses that the employee has taken. There's a checkbox on both forms called 'Left' but the user doesn't want to go through the sub forms and check them all individually. I therefore need to link the forms so that when the user checks the 'Left' box on the employee form, the 'Left' box on all the sub forms for that employee also checks. I'd really appreciate some help on how to do this. |
#8
|
|||
|
|||
Form/Subform Checkbox
Minnie,
Don't name your field "Left". Left is a string function in Access, and as such... is a reserved word. Rename your check field to something like LeftCompany. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Minnie M" wrote in message ... Thanks Al, I've followed your instructions but I must have done something wrong as I get the following 'Compile error: Syntax error' message when I click the 'Left' checkbox on my main form: On the visual basic screen, the top left hand box is set to Left, the top right hand box is set to After Update, the following is in the text part of the screen: Option Compare Database Option Explicit Private Sub Left_AfterUpdate() (this line is highlighted in yellow with a yellow arrow pointing to it from the left) If Left = True (this line is highlighted) End Sub Private Sub cmdSetTrainingToTrue_Click() DoCmd.OpenQuery "qryLeftCompanyTrue" End Sub Hope this makes sense to you, and I'd really appreciate a little more help. "Al Campagna" wrote: Minnie, I forgot to ask what your key (Parent/Child) field on the Main and Sub is... so I'll just use EmpID for this example... you use your own name/s. Create an Update query. Open a new Query design grid, and select Query/Update from the query menu. Place your LeftCompany field on the grid, and fill in the UpdateTo for LeftCompany to... True Place your key field EmpID from Training onto the grid, and give it a criteria of... =Forms![Employee Information Form]![EmpID] Save this query as qryLeftCompanyTrue. This query will update every Training record to True... that has an EmpID equal to that of the EmpID on your open form. Why not just create a button on the Main form called cmdSetTrainingToTrue. Any time this button is clicked, it sets all the associated Traing records to True. Using the OnClick event of the button, use this code... Private Sub cmdSetTrainingToTrue_Click() DoCmd.OpenQuery "qryLeftCompanyTrue" End Sub OR... If your more comfortable with Macros... just use the OpenQuery Action instead. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Minnie M" wrote in message ... Hi Al, thanks for the reminder to back up my data, no problem, I'm working on a copy of the db. To answer your questions: Main form is Employee Information Form, subform is Training Form The name of the check field on the main form is Left The field name on the subform to be set to true is Left Company The table that the subform is based on is Training Many thanks for your help. "Al Campagna" wrote: Minnie, First rule regarding Update queries... back up your original data! What is the name of your Main form, and your Subform? What is the name of the check field on the Main form. What is the field name, on the subform, that we want to set to true? What is the name of the table that your subform is based on? -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Minnie M" wrote in message ... Hi Al, sorry about the incorrect terminology in the question, you're correct about what you think I meant. I still don't completely understand what I need to do, I don't suppose you could give me an idiot guide could you? I'm new to writing code and am not sure what to type and where to type it, it's quite confusing for a newbie. I'd really appreciate if you could give me a little more help. Thanks ever so much. "Al Campagna" wrote: Minnie, on "all the sub forms" for that employee also checks. I think you mean that when you select Left on the main form, all the "subform records" (associated with that employee) will be checked True. We should be bealing with only one subform... Your main form should have a unique key value that relates to a key value in the subform table. Like an EmployeeID... Using the AfterUpdate event of the main form's Left checkbox... If Left = True... then run an Update query against the subform table that sets all the Left values to True in the subform table... **where the EmployeeID is equal to the EmployeeID on the main form** If Left = False, run an Update query against the subform table that does the opposite. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Minnie M" wrote in message news In Access 2002 I have a form 'Employee' and a sub form 'Training' that lists all the training courses that the employee has taken. There's a checkbox on both forms called 'Left' but the user doesn't want to go through the sub forms and check them all individually. I therefore need to link the forms so that when the user checks the 'Left' box on the employee form, the 'Left' box on all the sub forms for that employee also checks. I'd really appreciate some help on how to do this. |
#9
|
|||
|
|||
Form/Subform Checkbox
Hi Al, changed the field name to LeftCompany as you suggested. Its now asking
for a Then or Else statement after If LeftCompany = True. Hope you can be a star once again. "Al Campagna" wrote: Minnie, Don't name your field "Left". Left is a string function in Access, and as such... is a reserved word. Rename your check field to something like LeftCompany. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Minnie M" wrote in message ... Thanks Al, I've followed your instructions but I must have done something wrong as I get the following 'Compile error: Syntax error' message when I click the 'Left' checkbox on my main form: On the visual basic screen, the top left hand box is set to Left, the top right hand box is set to After Update, the following is in the text part of the screen: Option Compare Database Option Explicit Private Sub Left_AfterUpdate() (this line is highlighted in yellow with a yellow arrow pointing to it from the left) If Left = True (this line is highlighted) End Sub Private Sub cmdSetTrainingToTrue_Click() DoCmd.OpenQuery "qryLeftCompanyTrue" End Sub Hope this makes sense to you, and I'd really appreciate a little more help. "Al Campagna" wrote: Minnie, I forgot to ask what your key (Parent/Child) field on the Main and Sub is... so I'll just use EmpID for this example... you use your own name/s. Create an Update query. Open a new Query design grid, and select Query/Update from the query menu. Place your LeftCompany field on the grid, and fill in the UpdateTo for LeftCompany to... True Place your key field EmpID from Training onto the grid, and give it a criteria of... =Forms![Employee Information Form]![EmpID] Save this query as qryLeftCompanyTrue. This query will update every Training record to True... that has an EmpID equal to that of the EmpID on your open form. Why not just create a button on the Main form called cmdSetTrainingToTrue. Any time this button is clicked, it sets all the associated Traing records to True. Using the OnClick event of the button, use this code... Private Sub cmdSetTrainingToTrue_Click() DoCmd.OpenQuery "qryLeftCompanyTrue" End Sub OR... If your more comfortable with Macros... just use the OpenQuery Action instead. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Minnie M" wrote in message ... Hi Al, thanks for the reminder to back up my data, no problem, I'm working on a copy of the db. To answer your questions: Main form is Employee Information Form, subform is Training Form The name of the check field on the main form is Left The field name on the subform to be set to true is Left Company The table that the subform is based on is Training Many thanks for your help. "Al Campagna" wrote: Minnie, First rule regarding Update queries... back up your original data! What is the name of your Main form, and your Subform? What is the name of the check field on the Main form. What is the field name, on the subform, that we want to set to true? What is the name of the table that your subform is based on? -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Minnie M" wrote in message ... Hi Al, sorry about the incorrect terminology in the question, you're correct about what you think I meant. I still don't completely understand what I need to do, I don't suppose you could give me an idiot guide could you? I'm new to writing code and am not sure what to type and where to type it, it's quite confusing for a newbie. I'd really appreciate if you could give me a little more help. Thanks ever so much. "Al Campagna" wrote: Minnie, on "all the sub forms" for that employee also checks. I think you mean that when you select Left on the main form, all the "subform records" (associated with that employee) will be checked True. We should be bealing with only one subform... Your main form should have a unique key value that relates to a key value in the subform table. Like an EmployeeID... Using the AfterUpdate event of the main form's Left checkbox... If Left = True... then run an Update query against the subform table that sets all the Left values to True in the subform table... **where the EmployeeID is equal to the EmployeeID on the main form** If Left = False, run an Update query against the subform table that does the opposite. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Minnie M" wrote in message news In Access 2002 I have a form 'Employee' and a sub form 'Training' that lists all the training courses that the employee has taken. There's a checkbox on both forms called 'Left' but the user doesn't want to go through the sub forms and check them all individually. I therefore need to link the forms so that when the user checks the 'Left' box on the employee form, the 'Left' box on all the sub forms for that employee also checks. I'd really appreciate some help on how to do this. |
#10
|
|||
|
|||
Form/Subform Checkbox
Minnie,
I had suggested using a button to trigger the query qryLeftCompanyTrue, but I'll use a checkbox if you insist... Any time you have problems with code, please copy (cut & paste) your code exactly to your post. Private Sub LeftCompany_AfterUpdate() If LeftCompany = True Then DoCmd.OpenQuery "qryLeftCompanyTrue" End If End Sub -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Minnie M" wrote in message ... Hi Al, changed the field name to LeftCompany as you suggested. Its now asking for a Then or Else statement after If LeftCompany = True. Hope you can be a star once again. "Al Campagna" wrote: Minnie, Don't name your field "Left". Left is a string function in Access, and as such... is a reserved word. Rename your check field to something like LeftCompany. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Minnie M" wrote in message ... Thanks Al, I've followed your instructions but I must have done something wrong as I get the following 'Compile error: Syntax error' message when I click the 'Left' checkbox on my main form: On the visual basic screen, the top left hand box is set to Left, the top right hand box is set to After Update, the following is in the text part of the screen: Option Compare Database Option Explicit Private Sub Left_AfterUpdate() (this line is highlighted in yellow with a yellow arrow pointing to it from the left) If Left = True (this line is highlighted) End Sub Private Sub cmdSetTrainingToTrue_Click() DoCmd.OpenQuery "qryLeftCompanyTrue" End Sub Hope this makes sense to you, and I'd really appreciate a little more help. "Al Campagna" wrote: Minnie, I forgot to ask what your key (Parent/Child) field on the Main and Sub is... so I'll just use EmpID for this example... you use your own name/s. Create an Update query. Open a new Query design grid, and select Query/Update from the query menu. Place your LeftCompany field on the grid, and fill in the UpdateTo for LeftCompany to... True Place your key field EmpID from Training onto the grid, and give it a criteria of... =Forms![Employee Information Form]![EmpID] Save this query as qryLeftCompanyTrue. This query will update every Training record to True... that has an EmpID equal to that of the EmpID on your open form. Why not just create a button on the Main form called cmdSetTrainingToTrue. Any time this button is clicked, it sets all the associated Traing records to True. Using the OnClick event of the button, use this code... Private Sub cmdSetTrainingToTrue_Click() DoCmd.OpenQuery "qryLeftCompanyTrue" End Sub OR... If your more comfortable with Macros... just use the OpenQuery Action instead. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Minnie M" wrote in message ... Hi Al, thanks for the reminder to back up my data, no problem, I'm working on a copy of the db. To answer your questions: Main form is Employee Information Form, subform is Training Form The name of the check field on the main form is Left The field name on the subform to be set to true is Left Company The table that the subform is based on is Training Many thanks for your help. "Al Campagna" wrote: Minnie, First rule regarding Update queries... back up your original data! What is the name of your Main form, and your Subform? What is the name of the check field on the Main form. What is the field name, on the subform, that we want to set to true? What is the name of the table that your subform is based on? -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Minnie M" wrote in message ... Hi Al, sorry about the incorrect terminology in the question, you're correct about what you think I meant. I still don't completely understand what I need to do, I don't suppose you could give me an idiot guide could you? I'm new to writing code and am not sure what to type and where to type it, it's quite confusing for a newbie. I'd really appreciate if you could give me a little more help. Thanks ever so much. "Al Campagna" wrote: Minnie, on "all the sub forms" for that employee also checks. I think you mean that when you select Left on the main form, all the "subform records" (associated with that employee) will be checked True. We should be bealing with only one subform... Your main form should have a unique key value that relates to a key value in the subform table. Like an EmployeeID... Using the AfterUpdate event of the main form's Left checkbox... If Left = True... then run an Update query against the subform table that sets all the Left values to True in the subform table... **where the EmployeeID is equal to the EmployeeID on the main form** If Left = False, run an Update query against the subform table that does the opposite. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Minnie M" wrote in message news In Access 2002 I have a form 'Employee' and a sub form 'Training' that lists all the training courses that the employee has taken. There's a checkbox on both forms called 'Left' but the user doesn't want to go through the sub forms and check them all individually. I therefore need to link the forms so that when the user checks the 'Left' box on the employee form, the 'Left' box on all the sub forms for that employee also checks. I'd really appreciate some help on how to do this. |
|
Thread Tools | |
Display Modes | |
|
|