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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,006
Messages
5,834,835
Members
430,324
Latest member
bosphoruskid

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top