disable form field based on value in another field

aduval

New Member
Joined
Jul 18, 2003
Messages
17
For simplicity, I have a data entry form with 2 fields. Data can either be entered in one field or the other but never both.

How do I have both enabled until data is entered into one of them?

Thanks
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

nullZero

Active Member
Joined
Nov 14, 2005
Messages
497
Assuming your talking about two textboxes you could add the following into the before_update events for each textbox...

Private Sub text1_BeforeUpdate(Cancel As Integer)
Me.Text2.Enabled = Len(Me.Text1.Text) = 0
End Sub

Private Sub text2_BeforeUpdate(Cancel As Integer)
Me.Text1.Enabled = Len(Me.Text2.Text) = 0
End Sub
 

aduval

New Member
Joined
Jul 18, 2003
Messages
17
That worked! Thank you, thank you, thank you!

But there is one more little thing. How do I make that enable/disable formatting update based on each record in the form? In other words, I want each record to have one text box enabled and containing data and the other box disabled and empty.

Make sense?
 

nullZero

Active Member
Joined
Nov 14, 2005
Messages
497

ADVERTISEMENT

Nope, not following you. Sorry. Can you provide a little more detail and possibly some background as to why you need to do this?
 

aduval

New Member
Joined
Jul 18, 2003
Messages
17
Here is a little more detail:

This is an inventory cycle count database where we enter our inventory discrepancies. When inventory is inaccurate, it can either be off by a full pallet amount or a partial pallet amount. So there are two text boxes:

Text box 1 = Qty of full pallets
Text box 2 = Qty of partial pallets

The rule is you can either have a full pallet discrepancy OR a partial pallet discrepancy but never both.

I want to prevent the data entry clerk from ever entering a qty in Text box 1 AND a qty in Text box 2 on the form. My ideal scenario would be that for each record, when something is entered into Text box 1, Text box 2 would become disabled, and likewise for Text box 2.

The solution you provided was limited in that the property for the Disabled text box would carry over to the next record regardless of whether something had been entered into the field or not. I want the property to reflect whatever values are in Text box 1 or 2 based on each record.

For example:

Record #1
TB1 = 5
TB2 = Disabled

Record #2
TB1 = Disabled
TB2 = 5

Does this help?

Thanks for your patience and time.
 

nullZero

Active Member
Joined
Nov 14, 2005
Messages
497
Much clearer now. My last job was in Logistics and we'd always have to do inventory reconciliation and adjustments so this is all very familiar.

Now in our database we had a unit of measure (UOM) field for cases and eaches. So when we did a cycle count there was only one field to enter a qty and then we would chose the UOM from a combo box.

Anyway I think you can move the code to the form_current event and it will do what you want:

Code:
Private Sub Form_Current()
    Me.full_pallet_qty.Enabled = Me.partial_pallet_qty = "0"
    Me.partial_pallet_qty.Enabled = Me.full_pallet_qty = "0"
End Sub

Also, I am assuming that these are bound fields on your form so that's why I changed it to = "0"

does that help?
 

aduval

New Member
Joined
Jul 18, 2003
Messages
17
That did the trick! Those fields are bound to a table and I set the table field default to zero because i was getting an error message about not accepting 'null' values. But that's fixed now.

I like your way of doing it too where you just tied it to the UOM field. I should have thought of that earlier. O well. I'm back on 'go' now.

Thanks so much!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,462
Messages
5,572,260
Members
412,451
Latest member
newbie22922792
Top