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
|
|||
|
|||
2 Fields-How to keep 1 empty if other has data and vice versa??
Hi, Wondering if someone can help??
If I have two fields in an Access form and I want a user to be able to enter data in either one but not both is there a way I can set this up? |
#2
|
|||
|
|||
2 Fields-How to keep 1 empty if other has data and vice versa??
"Liam" wrote If I have two fields in an Access form and I want a user to be able to enter data in either one but not both is there a way I can set this up? Form Controls normally display and allow access to Fields that exist in a Table, accessed directly or via a Query, as the Record Source of the Form. You can almost certainly do what you want, in more than one way, depending on the details. Consider some scenarios and what you'd like to happen, then clarify and someone can likely offer you useful suggestions. For example: On a new record, if the user enters data in the first of the two Controls, do you want to Lock the second one, or do you want to allow the user to enter data in the second of the two Controls, and you clear the other one... so that the last one into which the user types is the "winner" of the "race"? On an existing record, if there's data in the Control Source of one of the Controls, do you want to allow the user to enter data into the other and clear the data that is already there? Probably, if you'd describe the actual data you have, how it is stored, the situation, and _what_ you are trying to accomplish rather than _how_ you expected to accomplish it, that would be even better. There's, at least, a chance that the perceived need to do what you ask might indicate that you need to revise your design. Larry Linson Microsoft Access MVP |
#3
|
|||
|
|||
2 Fields-How to keep 1 empty if other has data and vice versa??
On Wed, 7 Nov 2007 14:41:02 -0800, Liam
wrote: Hi, Wondering if someone can help?? If I have two fields in an Access form and I want a user to be able to enter data in either one but not both is there a way I can set this up? You can use the Form's BeforeUpdate event to check these controls, and cancel the entry if the record is invalid. For example: Private Sub Form_BeforeUpdate(Cancel as Integer) If IsNull(Me!txtTweedleDum) Or IsNull(Me!txtTweedleDee) Then ' all is well, do nothing Else MsgBox "Please enter data in Tweedledum or Tweedledee, not both", vbOKOnly Cancel = True End If End Sub John W. Vinson [MVP] |
#4
|
|||
|
|||
2 Fields-How to keep 1 empty if other has data and vice versa?
Thank Alot John for your help. That was exactly what I was looking for.
All the best. "John W. Vinson" wrote: On Wed, 7 Nov 2007 14:41:02 -0800, Liam wrote: Hi, Wondering if someone can help?? If I have two fields in an Access form and I want a user to be able to enter data in either one but not both is there a way I can set this up? You can use the Form's BeforeUpdate event to check these controls, and cancel the entry if the record is invalid. For example: Private Sub Form_BeforeUpdate(Cancel as Integer) If IsNull(Me!txtTweedleDum) Or IsNull(Me!txtTweedleDee) Then ' all is well, do nothing Else MsgBox "Please enter data in Tweedledum or Tweedledee, not both", vbOKOnly Cancel = True End If End Sub John W. Vinson [MVP] |
#5
|
|||
|
|||
2 Fields-How to keep 1 empty if other has data and vice versa?
Hi - this thread has solved a problem I was also having but am wanting to
also take it one stage further. Once either field has been selected I then have a series of additional checkboxes that need to be populated. How can I prompt the user to ensure these are populated correctly and not left blank upon saving/exiting the record? thanks in advance. Nicola "John W. Vinson" wrote: On Wed, 7 Nov 2007 14:41:02 -0800, Liam wrote: Hi, Wondering if someone can help?? If I have two fields in an Access form and I want a user to be able to enter data in either one but not both is there a way I can set this up? You can use the Form's BeforeUpdate event to check these controls, and cancel the entry if the record is invalid. For example: Private Sub Form_BeforeUpdate(Cancel as Integer) If IsNull(Me!txtTweedleDum) Or IsNull(Me!txtTweedleDee) Then ' all is well, do nothing Else MsgBox "Please enter data in Tweedledum or Tweedledee, not both", vbOKOnly Cancel = True End If End Sub John W. Vinson [MVP] |
#6
|
|||
|
|||
2 Fields-How to keep 1 empty if other has data and vice versa?
Nicola
One solution would be to make those checkboxes/underlying fields "Required". -- Regards Jeff Boyce Microsoft Access MVP Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or psuedocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "nicolam1980" wrote in message ... Hi - this thread has solved a problem I was also having but am wanting to also take it one stage further. Once either field has been selected I then have a series of additional checkboxes that need to be populated. How can I prompt the user to ensure these are populated correctly and not left blank upon saving/exiting the record? thanks in advance. Nicola "John W. Vinson" wrote: On Wed, 7 Nov 2007 14:41:02 -0800, Liam wrote: Hi, Wondering if someone can help?? If I have two fields in an Access form and I want a user to be able to enter data in either one but not both is there a way I can set this up? You can use the Form's BeforeUpdate event to check these controls, and cancel the entry if the record is invalid. For example: Private Sub Form_BeforeUpdate(Cancel as Integer) If IsNull(Me!txtTweedleDum) Or IsNull(Me!txtTweedleDee) Then ' all is well, do nothing Else MsgBox "Please enter data in Tweedledum or Tweedledee, not both", vbOKOnly Cancel = True End If End Sub John W. Vinson [MVP] |
#7
|
|||
|
|||
2 Fields-How to keep 1 empty if other has data and vice versa?
Hi Jeff
The checkboxes would only be 'Required' however once the initial field had been chosen, not all would be required each time. Any thoughts? thanks Nicola "Jeff Boyce" wrote: Nicola One solution would be to make those checkboxes/underlying fields "Required". -- Regards Jeff Boyce Microsoft Access MVP Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or psuedocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "nicolam1980" wrote in message ... Hi - this thread has solved a problem I was also having but am wanting to also take it one stage further. Once either field has been selected I then have a series of additional checkboxes that need to be populated. How can I prompt the user to ensure these are populated correctly and not left blank upon saving/exiting the record? thanks in advance. Nicola "John W. Vinson" wrote: On Wed, 7 Nov 2007 14:41:02 -0800, Liam wrote: Hi, Wondering if someone can help?? If I have two fields in an Access form and I want a user to be able to enter data in either one but not both is there a way I can set this up? You can use the Form's BeforeUpdate event to check these controls, and cancel the entry if the record is invalid. For example: Private Sub Form_BeforeUpdate(Cancel as Integer) If IsNull(Me!txtTweedleDum) Or IsNull(Me!txtTweedleDee) Then ' all is well, do nothing Else MsgBox "Please enter data in Tweedledum or Tweedledee, not both", vbOKOnly Cancel = True End If End Sub John W. Vinson [MVP] . |
#8
|
|||
|
|||
2 Fields-How to keep 1 empty if other has data and vice versa?
On Wed, 11 Nov 2009 04:02:01 -0800, nicolam1980
wrote: Hi - this thread has solved a problem I was also having but am wanting to also take it one stage further. Once either field has been selected I then have a series of additional checkboxes that need to be populated. How can I prompt the user to ensure these are populated correctly and not left blank upon saving/exiting the record? thanks in advance. The need to do this suggests that your table is not properly normalized. One basic principle is that a field should depend only on the Primary Key of its record, not on any other field. Multiple checkboxes is another red flag - might this not better be a many-to-many relationship to another table? That said, you'll need to use VBA code in the form's BeforeUpdate event: something like Private Sub Form_BeforeUpdate(Cancel as Integer) If Not (IsNull(Me!ThisField) OR IsNull(Me!ThatField) Then check the checkboxes to see if they're valid if not then Cancel = True MsgBox "If ThisField or ThatField is selected fill in these checkboxes" End If End Sub -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|