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
|
|||
|
|||
Is it possible to show both "Yes" and "No" answers from a "yes/no" checkbox?
Hi All,
I have a subform where one of the fields is a yes/no checkbox to indicate whether or not the record is "active": Active returns -1, inactive 0 On the main form, I need to be able to see either the "active" records, or the "inactive" records or all records. So, what I've done is to set up an option group to allow the selection... one button selects the "Active" and one the "inactive"... however, that's where the problem starts, I can't get the third button to show all items. Basically, the option group result is used by the query that is used as the source for the subform. I've tried using a statement within the criteria that says... IIf([forms]![customer form new]![selectcontact]=1,1,[forms]![customer form new]![selectcontact]) but this then shows all active records, rather than all If there is a way that I can achieve this, I'd be really interested to know! Regards Colin Foster |
#2
|
|||
|
|||
You're using an option group on the form to select which to show. Let's
assume that the option group has a value of 1 when you want the "active" ones to show, a value of 2 when you want the "inactive" ones to show, and a value of 3 when you want "all" to show. The crtierion expression in the query would be something like this: ([forms]![customer form new]![selectcontact] - 2) Or ([forms]![customer form new]![selectcontact] = 3) -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Hi All, I have a subform where one of the fields is a yes/no checkbox to indicate whether or not the record is "active": Active returns -1, inactive 0 On the main form, I need to be able to see either the "active" records, or the "inactive" records or all records. So, what I've done is to set up an option group to allow the selection... one button selects the "Active" and one the "inactive"... however, that's where the problem starts, I can't get the third button to show all items. Basically, the option group result is used by the query that is used as the source for the subform. I've tried using a statement within the criteria that says... IIf([forms]![customer form new]![selectcontact]=1,1,[forms]![customer form new]![selectcontact]) but this then shows all active records, rather than all If there is a way that I can achieve this, I'd be really interested to know! Regards Colin Foster |
#3
|
|||
|
|||
Ken,
You are a star!! This works like a dream :-) Now I've got that working, I wonder if you could help with the next stage... When I select the "Active" record, I need the details to be transferred into th emain form, so I've put the following code behind the OnClick property of the option group... Private Sub SelectContact_Click() Forms![customer form new]![FrmAltContacts].Requery Me!Salutation = Forms![customer form new]![FrmAltContacts].Form![Salutation] Me!ContactFirstName = Forms![customer form new]![FrmAltContacts].Form![ContactFirstName] Me!ContactLastName = Forms![customer form new]![FrmAltContacts].Form![ContactLastName] Me!ContactTitle = Forms![customer form new]![FrmAltContacts].Form![ContactTitle] Forms![customer form new]![FrmAltContacts].Requery End Sub Sometimes it works & the information is transferred into th emain form (the "me!" part of the above code) and sometimes it doesn't. I tried to attach it to the "got focus" property of the "Active" action button, but this didn't help either. I suspect that the problem is that in the code above, I need to do something else to "tell" the main form to pick up the "Active" record only. Any idea what this might be? Once again, thanks for your help so far. Regards Colin "Ken Snell [MVP]" wrote in message ... You're using an option group on the form to select which to show. Let's assume that the option group has a value of 1 when you want the "active" ones to show, a value of 2 when you want the "inactive" ones to show, and a value of 3 when you want "all" to show. The crtierion expression in the query would be something like this: ([forms]![customer form new]![selectcontact] - 2) Or ([forms]![customer form new]![selectcontact] = 3) -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Hi All, I have a subform where one of the fields is a yes/no checkbox to indicate whether or not the record is "active": Active returns -1, inactive 0 On the main form, I need to be able to see either the "active" records, or the "inactive" records or all records. So, what I've done is to set up an option group to allow the selection... one button selects the "Active" and one the "inactive"... however, that's where the problem starts, I can't get the third button to show all items. Basically, the option group result is used by the query that is used as the source for the subform. I've tried using a statement within the criteria that says... IIf([forms]![customer form new]![selectcontact]=1,1,[forms]![customer form new]![selectcontact]) but this then shows all active records, rather than all If there is a way that I can achieve this, I'd be really interested to know! Regards Colin Foster |
#4
|
|||
|
|||
Use the AfterUpdate event of the option group, not the Click event, to run
the code. This option group is in the main part of the form? Or in the subform? If it's in the subform, then you cannot use the Me object to reference the control on the main form. You'd need Me.Parent!Salutation = Forms![customer form new]![FrmAltContacts].Form![Salutation] And, I would rewrite the above this way: Me.Parent!Salutation = Me.[Salutation] -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Ken, You are a star!! This works like a dream :-) Now I've got that working, I wonder if you could help with the next stage... When I select the "Active" record, I need the details to be transferred into th emain form, so I've put the following code behind the OnClick property of the option group... Private Sub SelectContact_Click() Forms![customer form new]![FrmAltContacts].Requery Me!Salutation = Forms![customer form new]![FrmAltContacts].Form![Salutation] Me!ContactFirstName = Forms![customer form new]![FrmAltContacts].Form![ContactFirstName] Me!ContactLastName = Forms![customer form new]![FrmAltContacts].Form![ContactLastName] Me!ContactTitle = Forms![customer form new]![FrmAltContacts].Form![ContactTitle] Forms![customer form new]![FrmAltContacts].Requery End Sub Sometimes it works & the information is transferred into th emain form (the "me!" part of the above code) and sometimes it doesn't. I tried to attach it to the "got focus" property of the "Active" action button, but this didn't help either. I suspect that the problem is that in the code above, I need to do something else to "tell" the main form to pick up the "Active" record only. Any idea what this might be? Once again, thanks for your help so far. Regards Colin "Ken Snell [MVP]" wrote in message ... You're using an option group on the form to select which to show. Let's assume that the option group has a value of 1 when you want the "active" ones to show, a value of 2 when you want the "inactive" ones to show, and a value of 3 when you want "all" to show. The crtierion expression in the query would be something like this: ([forms]![customer form new]![selectcontact] - 2) Or ([forms]![customer form new]![selectcontact] = 3) -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Hi All, I have a subform where one of the fields is a yes/no checkbox to indicate whether or not the record is "active": Active returns -1, inactive 0 On the main form, I need to be able to see either the "active" records, or the "inactive" records or all records. So, what I've done is to set up an option group to allow the selection... one button selects the "Active" and one the "inactive"... however, that's where the problem starts, I can't get the third button to show all items. Basically, the option group result is used by the query that is used as the source for the subform. I've tried using a statement within the criteria that says... IIf([forms]![customer form new]![selectcontact]=1,1,[forms]![customer form new]![selectcontact]) but this then shows all active records, rather than all If there is a way that I can achieve this, I'd be really interested to know! Regards Colin Foster |
#5
|
|||
|
|||
Hi Ken,
Done that, and again, sometimes it works & sometimes it doesnt :-( The option group is on the main form. I'm going to finish watching "Comic Relief" now (& then to bed!! - here in the UK it's 22:50), but I'll check back tomorrow to see if you've had any other ideas, or have spotted what I'm doing wrong! Regards Colin "Ken Snell [MVP]" wrote in message ... Use the AfterUpdate event of the option group, not the Click event, to run the code. This option group is in the main part of the form? Or in the subform? If it's in the subform, then you cannot use the Me object to reference the control on the main form. You'd need Me.Parent!Salutation = Forms![customer form new]![FrmAltContacts].Form![Salutation] And, I would rewrite the above this way: Me.Parent!Salutation = Me.[Salutation] -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Ken, You are a star!! This works like a dream :-) Now I've got that working, I wonder if you could help with the next stage... When I select the "Active" record, I need the details to be transferred into th emain form, so I've put the following code behind the OnClick property of the option group... Private Sub SelectContact_Click() Forms![customer form new]![FrmAltContacts].Requery Me!Salutation = Forms![customer form new]![FrmAltContacts].Form![Salutation] Me!ContactFirstName = Forms![customer form new]![FrmAltContacts].Form![ContactFirstName] Me!ContactLastName = Forms![customer form new]![FrmAltContacts].Form![ContactLastName] Me!ContactTitle = Forms![customer form new]![FrmAltContacts].Form![ContactTitle] Forms![customer form new]![FrmAltContacts].Requery End Sub Sometimes it works & the information is transferred into th emain form (the "me!" part of the above code) and sometimes it doesn't. I tried to attach it to the "got focus" property of the "Active" action button, but this didn't help either. I suspect that the problem is that in the code above, I need to do something else to "tell" the main form to pick up the "Active" record only. Any idea what this might be? Once again, thanks for your help so far. Regards Colin "Ken Snell [MVP]" wrote in message ... You're using an option group on the form to select which to show. Let's assume that the option group has a value of 1 when you want the "active" ones to show, a value of 2 when you want the "inactive" ones to show, and a value of 3 when you want "all" to show. The crtierion expression in the query would be something like this: ([forms]![customer form new]![selectcontact] - 2) Or ([forms]![customer form new]![selectcontact] = 3) -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Hi All, I have a subform where one of the fields is a yes/no checkbox to indicate whether or not the record is "active": Active returns -1, inactive 0 On the main form, I need to be able to see either the "active" records, or the "inactive" records or all records. So, what I've done is to set up an option group to allow the selection... one button selects the "Active" and one the "inactive"... however, that's where the problem starts, I can't get the third button to show all items. Basically, the option group result is used by the query that is used as the source for the subform. I've tried using a statement within the criteria that says... IIf([forms]![customer form new]![selectcontact]=1,1,[forms]![customer form new]![selectcontact]) but this then shows all active records, rather than all If there is a way that I can achieve this, I'd be really interested to know! Regards Colin Foster |
#6
|
|||
|
|||
I'm guessing that the times that don't work are when you don't actually
change the choice in the option group, even though you click on the selected option button. If yes, the AfterUpdate event isn't occurring unless you change the value of the option group. Can you note carefully the actions that you take when it works and when it doesn't and let's see if it's because an event isn't happening in some situations? Alternatively, you might consider using the form's BeforeUpdate event to run the code, as that will fire any time you're ready to update a record with changes; but also note that it won't "fire" if no edits are made to the record (edit includes the creation of a new record). -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Hi Ken, Done that, and again, sometimes it works & sometimes it doesnt :-( The option group is on the main form. I'm going to finish watching "Comic Relief" now (& then to bed!! - here in the UK it's 22:50), but I'll check back tomorrow to see if you've had any other ideas, or have spotted what I'm doing wrong! Regards Colin "Ken Snell [MVP]" wrote in message ... Use the AfterUpdate event of the option group, not the Click event, to run the code. This option group is in the main part of the form? Or in the subform? If it's in the subform, then you cannot use the Me object to reference the control on the main form. You'd need Me.Parent!Salutation = Forms![customer form new]![FrmAltContacts].Form![Salutation] And, I would rewrite the above this way: Me.Parent!Salutation = Me.[Salutation] -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Ken, You are a star!! This works like a dream :-) Now I've got that working, I wonder if you could help with the next stage... When I select the "Active" record, I need the details to be transferred into th emain form, so I've put the following code behind the OnClick property of the option group... Private Sub SelectContact_Click() Forms![customer form new]![FrmAltContacts].Requery Me!Salutation = Forms![customer form new]![FrmAltContacts].Form![Salutation] Me!ContactFirstName = Forms![customer form new]![FrmAltContacts].Form![ContactFirstName] Me!ContactLastName = Forms![customer form new]![FrmAltContacts].Form![ContactLastName] Me!ContactTitle = Forms![customer form new]![FrmAltContacts].Form![ContactTitle] Forms![customer form new]![FrmAltContacts].Requery End Sub Sometimes it works & the information is transferred into th emain form (the "me!" part of the above code) and sometimes it doesn't. I tried to attach it to the "got focus" property of the "Active" action button, but this didn't help either. I suspect that the problem is that in the code above, I need to do something else to "tell" the main form to pick up the "Active" record only. Any idea what this might be? Once again, thanks for your help so far. Regards Colin "Ken Snell [MVP]" wrote in message ... You're using an option group on the form to select which to show. Let's assume that the option group has a value of 1 when you want the "active" ones to show, a value of 2 when you want the "inactive" ones to show, and a value of 3 when you want "all" to show. The crtierion expression in the query would be something like this: ([forms]![customer form new]![selectcontact] - 2) Or ([forms]![customer form new]![selectcontact] = 3) -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Hi All, I have a subform where one of the fields is a yes/no checkbox to indicate whether or not the record is "active": Active returns -1, inactive 0 On the main form, I need to be able to see either the "active" records, or the "inactive" records or all records. So, what I've done is to set up an option group to allow the selection... one button selects the "Active" and one the "inactive"... however, that's where the problem starts, I can't get the third button to show all items. Basically, the option group result is used by the query that is used as the source for the subform. I've tried using a statement within the criteria that says... IIf([forms]![customer form new]![selectcontact]=1,1,[forms]![customer form new]![selectcontact]) but this then shows all active records, rather than all If there is a way that I can achieve this, I'd be really interested to know! Regards Colin Foster |
#7
|
|||
|
|||
Hi Ken...
Spot on. I'll take you through the process thatI've just done... 1. Opened the database & went to the form which has 3 "contacts"... Colin Foster (active check box selected), Richard Foster & Ken Snell (both inactive) 2. Clicked the "Select Active" option button & "Colin Foster" appeared on the main form (correct result!) 3. Clicked the "Select All" option button & changed the "Active" record to Ken Snell (by unchecking "Colin Foster" & checking "Ken Snell") 4. Clicked the "Select Active" option button & nothing appeared on the main form, i.e. the "Colin Foster" also disappeared (incorrect result!!) 5. Interestingly, Clicked the "Select InActive" option button & "Colin Foster" appeared on the main form (interesting result!) 6. Closed the form down ("Ken Snell" still shown as active), reopened selected "Active" option button & "Ken Snell" appeared. So, I guess that what I now have to figure out is how to fire the "after Update" event more effectively. Hmmm... just before I decided to hit the "Send" button to forward this response to you, I had a further "play"... by accident, after changing the "active" person, I clicked on the "active" header on th esubform (it's displayed as a datasheet) and, lo... it worked. Flushed with this success, I tried it again and again and it still worked! Oh joy! Now, the question is why (and do I care as long as it works... well I suppose that I should!) and how can I do this programatically? Interesting.... very interesting Regards Colin "Ken Snell [MVP]" wrote in message ... I'm guessing that the times that don't work are when you don't actually change the choice in the option group, even though you click on the selected option button. If yes, the AfterUpdate event isn't occurring unless you change the value of the option group. Can you note carefully the actions that you take when it works and when it doesn't and let's see if it's because an event isn't happening in some situations? Alternatively, you might consider using the form's BeforeUpdate event to run the code, as that will fire any time you're ready to update a record with changes; but also note that it won't "fire" if no edits are made to the record (edit includes the creation of a new record). -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Hi Ken, Done that, and again, sometimes it works & sometimes it doesnt :-( The option group is on the main form. I'm going to finish watching "Comic Relief" now (& then to bed!! - here in the UK it's 22:50), but I'll check back tomorrow to see if you've had any other ideas, or have spotted what I'm doing wrong! Regards Colin "Ken Snell [MVP]" wrote in message ... Use the AfterUpdate event of the option group, not the Click event, to run the code. This option group is in the main part of the form? Or in the subform? If it's in the subform, then you cannot use the Me object to reference the control on the main form. You'd need Me.Parent!Salutation = Forms![customer form new]![FrmAltContacts].Form![Salutation] And, I would rewrite the above this way: Me.Parent!Salutation = Me.[Salutation] -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Ken, You are a star!! This works like a dream :-) Now I've got that working, I wonder if you could help with the next stage... When I select the "Active" record, I need the details to be transferred into th emain form, so I've put the following code behind the OnClick property of the option group... Private Sub SelectContact_Click() Forms![customer form new]![FrmAltContacts].Requery Me!Salutation = Forms![customer form new]![FrmAltContacts].Form![Salutation] Me!ContactFirstName = Forms![customer form new]![FrmAltContacts].Form![ContactFirstName] Me!ContactLastName = Forms![customer form new]![FrmAltContacts].Form![ContactLastName] Me!ContactTitle = Forms![customer form new]![FrmAltContacts].Form![ContactTitle] Forms![customer form new]![FrmAltContacts].Requery End Sub Sometimes it works & the information is transferred into th emain form (the "me!" part of the above code) and sometimes it doesn't. I tried to attach it to the "got focus" property of the "Active" action button, but this didn't help either. I suspect that the problem is that in the code above, I need to do something else to "tell" the main form to pick up the "Active" record only. Any idea what this might be? Once again, thanks for your help so far. Regards Colin "Ken Snell [MVP]" wrote in message ... You're using an option group on the form to select which to show. Let's assume that the option group has a value of 1 when you want the "active" ones to show, a value of 2 when you want the "inactive" ones to show, and a value of 3 when you want "all" to show. The crtierion expression in the query would be something like this: ([forms]![customer form new]![selectcontact] - 2) Or ([forms]![customer form new]![selectcontact] = 3) -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Hi All, I have a subform where one of the fields is a yes/no checkbox to indicate whether or not the record is "active": Active returns -1, inactive 0 On the main form, I need to be able to see either the "active" records, or the "inactive" records or all records. So, what I've done is to set up an option group to allow the selection... one button selects the "Active" and one the "inactive"... however, that's where the problem starts, I can't get the third button to show all items. Basically, the option group result is used by the query that is used as the source for the subform. I've tried using a statement within the criteria that says... IIf([forms]![customer form new]![selectcontact]=1,1,[forms]![customer form new]![selectcontact]) but this then shows all active records, rather than all If there is a way that I can achieve this, I'd be really interested to know! Regards Colin Foster |
#8
|
|||
|
|||
Ahh... the answer to your question is that you're not "saving" the edited
record in the subform before you go to the option group. In a form/subform setup, the "current" focus is on just one control in the main form -- when you're in the subform, the subform control (the control that holds the subform object) has focus for the main form, but there also is a focus within the subform. When you change the data in a record in the subform by clicking the checkbox, and then immediately click on the option group, the subform record that you just edited is still dirty (it's still being edited) and hasn't been saved to the underlying record source yet. Thus, the main form does not "see" the changed data and thus the Ken Snell record does not show in the subform because your change from Inactive to Active hasn't been "saved" yet. When you click on the header in the subform, you're moving focus from the record that you just edited, and thus the subform saves the changes you made. And thus you see the change when you click on the option group setting. With your form/subform setup, you may be able to use the Exit event of the subform control (the control (on the main form) that holds the subform) to save the current record in the subform, which will do what you seek. But note that this code will run whenever you click from the subform to the main form, so it is not the correct thing to do with all form/subform setups. Assuming that FrmAltContacts is the name of the subform control, put code like this on the Exit event of the subform control: Private Sub FrmAltContacts_Exit((Cancel As Integer) On Error Resume Next Me![FrmAltContacts].Form.Dirty = False DoEvents Me![FrmAltContacts].Form.Requery End Sub That should cause the current record in the subform to be saved, whether you've edited that record or not, and then will requery the subform to reflect any changes you made to those records. And then your option group setting should work correctly. -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Hi Ken... Spot on. I'll take you through the process thatI've just done... 1. Opened the database & went to the form which has 3 "contacts"... Colin Foster (active check box selected), Richard Foster & Ken Snell (both inactive) 2. Clicked the "Select Active" option button & "Colin Foster" appeared on the main form (correct result!) 3. Clicked the "Select All" option button & changed the "Active" record to Ken Snell (by unchecking "Colin Foster" & checking "Ken Snell") 4. Clicked the "Select Active" option button & nothing appeared on the main form, i.e. the "Colin Foster" also disappeared (incorrect result!!) 5. Interestingly, Clicked the "Select InActive" option button & "Colin Foster" appeared on the main form (interesting result!) 6. Closed the form down ("Ken Snell" still shown as active), reopened selected "Active" option button & "Ken Snell" appeared. So, I guess that what I now have to figure out is how to fire the "after Update" event more effectively. Hmmm... just before I decided to hit the "Send" button to forward this response to you, I had a further "play"... by accident, after changing the "active" person, I clicked on the "active" header on th esubform (it's displayed as a datasheet) and, lo... it worked. Flushed with this success, I tried it again and again and it still worked! Oh joy! Now, the question is why (and do I care as long as it works... well I suppose that I should!) and how can I do this programatically? Interesting.... very interesting Regards Colin "Ken Snell [MVP]" wrote in message ... I'm guessing that the times that don't work are when you don't actually change the choice in the option group, even though you click on the selected option button. If yes, the AfterUpdate event isn't occurring unless you change the value of the option group. Can you note carefully the actions that you take when it works and when it doesn't and let's see if it's because an event isn't happening in some situations? Alternatively, you might consider using the form's BeforeUpdate event to run the code, as that will fire any time you're ready to update a record with changes; but also note that it won't "fire" if no edits are made to the record (edit includes the creation of a new record). -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Hi Ken, Done that, and again, sometimes it works & sometimes it doesnt :-( The option group is on the main form. I'm going to finish watching "Comic Relief" now (& then to bed!! - here in the UK it's 22:50), but I'll check back tomorrow to see if you've had any other ideas, or have spotted what I'm doing wrong! Regards Colin "Ken Snell [MVP]" wrote in message ... Use the AfterUpdate event of the option group, not the Click event, to run the code. This option group is in the main part of the form? Or in the subform? If it's in the subform, then you cannot use the Me object to reference the control on the main form. You'd need Me.Parent!Salutation = Forms![customer form new]![FrmAltContacts].Form![Salutation] And, I would rewrite the above this way: Me.Parent!Salutation = Me.[Salutation] -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Ken, You are a star!! This works like a dream :-) Now I've got that working, I wonder if you could help with the next stage... When I select the "Active" record, I need the details to be transferred into th emain form, so I've put the following code behind the OnClick property of the option group... Private Sub SelectContact_Click() Forms![customer form new]![FrmAltContacts].Requery Me!Salutation = Forms![customer form new]![FrmAltContacts].Form![Salutation] Me!ContactFirstName = Forms![customer form new]![FrmAltContacts].Form![ContactFirstName] Me!ContactLastName = Forms![customer form new]![FrmAltContacts].Form![ContactLastName] Me!ContactTitle = Forms![customer form new]![FrmAltContacts].Form![ContactTitle] Forms![customer form new]![FrmAltContacts].Requery End Sub Sometimes it works & the information is transferred into th emain form (the "me!" part of the above code) and sometimes it doesn't. I tried to attach it to the "got focus" property of the "Active" action button, but this didn't help either. I suspect that the problem is that in the code above, I need to do something else to "tell" the main form to pick up the "Active" record only. Any idea what this might be? Once again, thanks for your help so far. Regards Colin "Ken Snell [MVP]" wrote in message ... You're using an option group on the form to select which to show. Let's assume that the option group has a value of 1 when you want the "active" ones to show, a value of 2 when you want the "inactive" ones to show, and a value of 3 when you want "all" to show. The crtierion expression in the query would be something like this: ([forms]![customer form new]![selectcontact] - 2) Or ([forms]![customer form new]![selectcontact] = 3) -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Hi All, I have a subform where one of the fields is a yes/no checkbox to indicate whether or not the record is "active": Active returns -1, inactive 0 On the main form, I need to be able to see either the "active" records, or the "inactive" records or all records. So, what I've done is to set up an option group to allow the selection... one button selects the "Active" and one the "inactive"... however, that's where the problem starts, I can't get the third button to show all items. Basically, the option group result is used by the query that is used as the source for the subform. I've tried using a statement within the criteria that says... IIf([forms]![customer form new]![selectcontact]=1,1,[forms]![customer form new]![selectcontact]) but this then shows all active records, rather than all If there is a way that I can achieve this, I'd be really interested to know! Regards Colin Foster |
#9
|
|||
|
|||
Ken,
Once again thanks for your detailed assitance - not just in working out the code, but also the explanations that go with it; it is greatly appreciated In fact this solution is a help to a similar problem that I had on another database (once again someone had asked for changes that would have been better resolved in the original build!!) Having solved this problem (with your help, guidance and code - in fact I seemed to do very little!), I realise that I had another where the name of the contact for the quote kept changing each time that I changed the contact. I then realised that the reason for this is that I was "just" holding the name of the contact within the form as far as the quote was concerned, so each time that the contact changed (even for a new quote), it changed within the quote, too. So what I've now done is to asdd these details into the quote table and set up a check box which, if checked, pulls through the latest contact details, if not allows the user to manually input. So, I think that's it, now (except that I've got to pull it all together in a report!), so once again thanks enormously for your help, support & guidance. Regards Colin Foster "Ken Snell [MVP]" wrote in message ... Ahh... the answer to your question is that you're not "saving" the edited record in the subform before you go to the option group. In a form/subform setup, the "current" focus is on just one control in the main form -- when you're in the subform, the subform control (the control that holds the subform object) has focus for the main form, but there also is a focus within the subform. When you change the data in a record in the subform by clicking the checkbox, and then immediately click on the option group, the subform record that you just edited is still dirty (it's still being edited) and hasn't been saved to the underlying record source yet. Thus, the main form does not "see" the changed data and thus the Ken Snell record does not show in the subform because your change from Inactive to Active hasn't been "saved" yet. When you click on the header in the subform, you're moving focus from the record that you just edited, and thus the subform saves the changes you made. And thus you see the change when you click on the option group setting. With your form/subform setup, you may be able to use the Exit event of the subform control (the control (on the main form) that holds the subform) to save the current record in the subform, which will do what you seek. But note that this code will run whenever you click from the subform to the main form, so it is not the correct thing to do with all form/subform setups. Assuming that FrmAltContacts is the name of the subform control, put code like this on the Exit event of the subform control: Private Sub FrmAltContacts_Exit((Cancel As Integer) On Error Resume Next Me![FrmAltContacts].Form.Dirty = False DoEvents Me![FrmAltContacts].Form.Requery End Sub That should cause the current record in the subform to be saved, whether you've edited that record or not, and then will requery the subform to reflect any changes you made to those records. And then your option group setting should work correctly. -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Hi Ken... Spot on. I'll take you through the process thatI've just done... 1. Opened the database & went to the form which has 3 "contacts"... Colin Foster (active check box selected), Richard Foster & Ken Snell (both inactive) 2. Clicked the "Select Active" option button & "Colin Foster" appeared on the main form (correct result!) 3. Clicked the "Select All" option button & changed the "Active" record to Ken Snell (by unchecking "Colin Foster" & checking "Ken Snell") 4. Clicked the "Select Active" option button & nothing appeared on the main form, i.e. the "Colin Foster" also disappeared (incorrect result!!) 5. Interestingly, Clicked the "Select InActive" option button & "Colin Foster" appeared on the main form (interesting result!) 6. Closed the form down ("Ken Snell" still shown as active), reopened selected "Active" option button & "Ken Snell" appeared. So, I guess that what I now have to figure out is how to fire the "after Update" event more effectively. Hmmm... just before I decided to hit the "Send" button to forward this response to you, I had a further "play"... by accident, after changing the "active" person, I clicked on the "active" header on th esubform (it's displayed as a datasheet) and, lo... it worked. Flushed with this success, I tried it again and again and it still worked! Oh joy! Now, the question is why (and do I care as long as it works... well I suppose that I should!) and how can I do this programatically? Interesting.... very interesting Regards Colin "Ken Snell [MVP]" wrote in message ... I'm guessing that the times that don't work are when you don't actually change the choice in the option group, even though you click on the selected option button. If yes, the AfterUpdate event isn't occurring unless you change the value of the option group. Can you note carefully the actions that you take when it works and when it doesn't and let's see if it's because an event isn't happening in some situations? Alternatively, you might consider using the form's BeforeUpdate event to run the code, as that will fire any time you're ready to update a record with changes; but also note that it won't "fire" if no edits are made to the record (edit includes the creation of a new record). -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Hi Ken, Done that, and again, sometimes it works & sometimes it doesnt :-( The option group is on the main form. I'm going to finish watching "Comic Relief" now (& then to bed!! - here in the UK it's 22:50), but I'll check back tomorrow to see if you've had any other ideas, or have spotted what I'm doing wrong! Regards Colin "Ken Snell [MVP]" wrote in message ... Use the AfterUpdate event of the option group, not the Click event, to run the code. This option group is in the main part of the form? Or in the subform? If it's in the subform, then you cannot use the Me object to reference the control on the main form. You'd need Me.Parent!Salutation = Forms![customer form new]![FrmAltContacts].Form![Salutation] And, I would rewrite the above this way: Me.Parent!Salutation = Me.[Salutation] -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Ken, You are a star!! This works like a dream :-) Now I've got that working, I wonder if you could help with the next stage... When I select the "Active" record, I need the details to be transferred into th emain form, so I've put the following code behind the OnClick property of the option group... Private Sub SelectContact_Click() Forms![customer form new]![FrmAltContacts].Requery Me!Salutation = Forms![customer form new]![FrmAltContacts].Form![Salutation] Me!ContactFirstName = Forms![customer form new]![FrmAltContacts].Form![ContactFirstName] Me!ContactLastName = Forms![customer form new]![FrmAltContacts].Form![ContactLastName] Me!ContactTitle = Forms![customer form new]![FrmAltContacts].Form![ContactTitle] Forms![customer form new]![FrmAltContacts].Requery End Sub Sometimes it works & the information is transferred into th emain form (the "me!" part of the above code) and sometimes it doesn't. I tried to attach it to the "got focus" property of the "Active" action button, but this didn't help either. I suspect that the problem is that in the code above, I need to do something else to "tell" the main form to pick up the "Active" record only. Any idea what this might be? Once again, thanks for your help so far. Regards Colin "Ken Snell [MVP]" wrote in message ... You're using an option group on the form to select which to show. Let's assume that the option group has a value of 1 when you want the "active" ones to show, a value of 2 when you want the "inactive" ones to show, and a value of 3 when you want "all" to show. The crtierion expression in the query would be something like this: ([forms]![customer form new]![selectcontact] - 2) Or ([forms]![customer form new]![selectcontact] = 3) -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Hi All, I have a subform where one of the fields is a yes/no checkbox to indicate whether or not the record is "active": Active returns -1, inactive 0 On the main form, I need to be able to see either the "active" records, or the "inactive" records or all records. So, what I've done is to set up an option group to allow the selection... one button selects the "Active" and one the "inactive"... however, that's where the problem starts, I can't get the third button to show all items. Basically, the option group result is used by the query that is used as the source for the subform. I've tried using a statement within the criteria that says... IIf([forms]![customer form new]![selectcontact]=1,1,[forms]![customer form new]![selectcontact]) but this then shows all active records, rather than all If there is a way that I can achieve this, I'd be really interested to know! Regards Colin Foster |
#10
|
|||
|
|||
Glad to hear of your success.. I wish you well!
and, you're welcome! -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Ken, Once again thanks for your detailed assitance - not just in working out the code, but also the explanations that go with it; it is greatly appreciated In fact this solution is a help to a similar problem that I had on another database (once again someone had asked for changes that would have been better resolved in the original build!!) Having solved this problem (with your help, guidance and code - in fact I seemed to do very little!), I realise that I had another where the name of the contact for the quote kept changing each time that I changed the contact. I then realised that the reason for this is that I was "just" holding the name of the contact within the form as far as the quote was concerned, so each time that the contact changed (even for a new quote), it changed within the quote, too. So what I've now done is to asdd these details into the quote table and set up a check box which, if checked, pulls through the latest contact details, if not allows the user to manually input. So, I think that's it, now (except that I've got to pull it all together in a report!), so once again thanks enormously for your help, support & guidance. Regards Colin Foster |
Thread Tools | |
Display Modes | |
|
|