Allow only 1 Addition

  • Thread starter Thread starter jtfalk
  • Start date Start date
J

jtfalk

Hello,

I have a form with 2 combo boxes and I want to make ity so that if you use
the pull down adn put a number into one box the other goes grey adn can not
have information in it.

The combo boxes are: Combo14 and Combo24
 
Assuming those controls are bound to 2 different data fields in the same
table, I would add a table validation rule requiring:
FieldA Is Null Or FieldB Is Null.
That prevents anyone from storing invalid data.

To make the form work nicely, you need to write VBA code for some form
events:
Form_OnCurrent: intialize the .Locked property of the two controls based on
the current record's data.
Comb14_AfterUpdate: Set Combo24.Locked = Not IsNull(combo14)
Comb24_AfterUpdate: Set Combo14.Locked = Not IsNull(combo24)

You might also want code in the form's UnDo event to reapply the appropriate
settings, and maybe the UnDo events of the two combo boxes.
 
You can use the After Update event of both combos to set the Enabled property
of the other. You will also need to set them back in the form current event
for the next record. Also, what about existing records? For example, if you
go to an existing record and Combo12 has a value and 14 does not, what do you
do? That is, do you enable only the one with a value? And what if the user
needs to change what has been entered and needs to use Combo14 instead?

In the form current event you would do this if you don't want to enable both
for an existing record:

With Me
If .NewRecord Then
.Combo12.Enabled = True
.Combo14.Enabled = True
Else
.Combo12.Enabled = Not IsNull(.Combo12)
.Combo14.Enabled = Not IsNull(.Combo12)
End If
End With

Then in the After Update events:

'Combo12

With Me
.Combo14.Enabled = Not IsNull(.Combo12)
End With

And the same for Combo14 with the names changed.
 
I need the form to open up and allow me to use a drop down box. So I need
both drop down boxes to be open for editing. When I pick either one and
select an item the other box disables and is null. This way the person can
only pick one or the other and I can pass this along to the report.
 
Got it. This is your code with a little change. It now allows me to limit the
answer to just one and now I can query the report to adjust it accordingly.
Thanks
Private Sub Combo14_AfterUpdate()
'Combo14

With Me
.Combo24.Enabled = IsNull(.Combo14)
End With
End Sub

Private Sub Combo24_AfterUpdate()
'Combo24

With Me
.Combo14.Enabled = IsNull(.Combo24)
End With

End Sub

Private Sub Form_Current()
With Me
If .NewRecord Then
.Combo14.Enabled = True
.Combo24.Enabled = True
Else
.Combo14.Enabled = IsNull(.Combo14)
.Combo24.Enabled = IsNull(.Combo14)
End If
End With

End Sub

Private Sub Form_Load()
Me.Combo14.Value = Null
Me.Combo24.Value = Null
ReSizeForm Me
End Sub
 
Back
Top