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?

"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

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]

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]

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]

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]

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]



.

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]