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 Checkbox
Hi Al, the associated records are still not updating (sigh).
I had set a command button as you suggested but I think the problem is that I don’t understand what I’m doing, so although I copied and pasted your codes I must have made other mistakes or missed something out. Many thanks for all your help anyway, I appreciate the time you’ve spent trying to help me. "Al Campagna" wrote: 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. |
#12
|
|||
|
|||
Form/Subform Checkbox
Minnie,
What version of Access are you using? Can you send me the database, using the Contact feature of my website (below)? If you do send the database please indicate what form is involved, and what subform is involved. Also, include the word "newsgroup" in your email "subject." I have a pretty tough spam filter, so if you do send the file/s, please add a post to this thread indicating that you have done so, and I'll be on the lookout for it. -- 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, the associated records are still not updating (sigh). I had set a command button as you suggested but I think the problem is that I don't understand what I'm doing, so although I copied and pasted your codes I must have made other mistakes or missed something out. Many thanks for all your help anyway, I appreciate the time you've spent trying to help me. "Al Campagna" wrote: 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 | |
|
|