graying out textbox in forms (1 Viewer)

[gizmogeek]

If someone can help me gray out one field if another field is filled in on the form. Example: If textbox containing Mass is filled in then gray out Volume and if Mass is not filled in then gray out Mass and allow to fill in Volume.

 

[John Big Booty]

You could use something like;

If Me.Mass = 0 And Me.Volume = 0 Then
     Me.Volume.Enabled = True
     Me.Mass.Enabled = True
Else If Me.Mass <> 0 And Me.Volume = 0 Then
     Me.Volume.Enabled = False
     Me.Mass.Enabled = True
Else If Me.Mass = 0 And Me.Volume <> 0 then
     Me.Volume.Enabled = True
     Me.Mass.Enabled = False
End If

You would put this in the Form's On Current event along with the On Lost Focus event for each of those text boxes.

 

[smig]

you can also use the condition format
it will also work for continous forms

 

[gizmogeek]

This didn't work for me?? Nothing changed for me. When I did it following:

Private Sub Form_Current()
If Me.Mass = 0 And Me.Volume = 0 Then
Me.Volume.Enabled = True
Me.Mass.Enabled = True
Else If Me.Mass <> 0 And Me.Volume = 0 Then
Me.Volume.Enabled = False
Me.Mass.Enabled = True
Else If Me.Mass = 0 And Me.Volume <> 0 then
Me.Volume.Enabled = True
Me.Mass.Enabled = False
[FONT=&quot]End If[/FONT]

End Sub

Private Sub SizeLbs_LostFocus()
If Me.Mass = 0 And Me.Volume = 0 Then
Me.Volume.Enabled = True
Me.Mass.Enabled = True
Else If Me.Mass <> 0 And Me.Volume = 0 Then
Me.Volume.Enabled = False
Me.Mass.Enabled = True
Else If Me.Mass = 0 And Me.Volume <> 0 then
Me.Volume.Enabled = True
Me.Mass.Enabled = False
[FONT=&quot]End If[/FONT]
End Sub

Private Sub SizeOz_LostFocus()
If Me.Mass = 0 And Me.Volume = 0 Then
Me.Volume.Enabled = True
Me.Mass.Enabled = True
Else If Me.Mass <> 0 And Me.Volume = 0 Then
Me.Volume.Enabled = False
Me.Mass.Enabled = True
Else If Me.Mass = 0 And Me.Volume <> 0 then
Me.Volume.Enabled = True
Me.Mass.Enabled = False
[FONT=&quot]End If[/FONT]
End Sub

 

[John Big Booty]

First up are mas and volume the names of fields on your Form

 

[gizmogeek]

Shoot. I see what your saying. They were added under the wrong fields. Still nothing happening when adding them under the right fields. I've been out of this for quite a few years. It's really hard to jump back in.

 

[John Big Booty]

Can you post a copy of your DB?

 

[gizmogeek]

I would love to post it but I can't. There is a lot of sensative information that would not be allowed to share. I tried your code in both the forms On Current and On Lost Focus. I've tried it a million times even trying one event at a time. I also tried the code from the forum post of "make a field appear or not in a form" it worked for me yesterday but not today. I just wanted it grayed out and not invisible but thought I'd try that anyway. Very frustrated. Thanks for the help. I think I will go back to square 1.

Anymore advice that you can give would be appreciated.[FONT=&quot][/FONT]

 

[missinglinq]

...If textbox containing Mass is filled in then gray out Volume and if Mass is not filled in then gray out Mass and allow to fill in Volume.

This code checking for a Value of Zero, but a Control that 'is not filled in' isn't equal to Zero but rather is Null!

To check for Nulls (as well as a Zero-Length String) you'd need to use a construct like

If Nz(Me.Mass, "") = "" Then

Linq ;0)>

 

[John Big Booty]

Ah yes, I had assumed that the default value for a numeric field would be zero

 

[John Big Booty]

... so if the fields are null by default then instead of testing for Zero you could test IsNull()

 

[gizmogeek]

[FONT=&quot]Okay. Trying again. I am posting my code and marked where it stops. Looks like I'm getting somewhat closer??[/FONT]
[FONT=&quot]Thanks for hanging in here with me.[/FONT]

[FONT=&quot]Private Sub Mass_AfterUpdate()[/FONT]
[FONT=&quot]If Nz(Me.Mass, "") = "" Then[/FONT]
[FONT=&quot]Me.Volume.Enabled = True[/FONT]
[FONT=&quot]Me.Mass.Enabled = True[/FONT]
[FONT=&quot]If Nz(Me.Volume, "") = "" Then[/FONT]
[FONT=&quot]Me.Volume.Enabled = True[/FONT]
[FONT=&quot]Me.Mass.Enabled = True[/FONT]
[FONT=&quot]Else If (Me.Mass, "") = "" Then [/FONT][FONT=&quot](Code stops here)[/FONT]
[FONT=&quot]'Else If Me.Mass <> 0 And Me.Volume = 0 Then[/FONT]
[FONT=&quot]Me.Volume.Enabled = False[/FONT]
[FONT=&quot]Me.Mass.Enabled = True[/FONT]
[FONT=&quot]‘Else If Me.Mass = 0 And Me.Volume <> 0 then[/FONT]
[FONT=&quot]Else If (Me.Volume, "") = "" Then[/FONT]
[FONT=&quot]Me.Volume.Enabled = True[/FONT]
[FONT=&quot]Me.Mass.Enabled = False[/FONT]
[FONT=&quot]End If[/FONT]

[FONT=&quot]End Sub[/FONT]

 

[John Big Booty]

Any chance you could post a copy of the DB? Pre '07 version is possible.

 

[GinaWhipp]

Ummm, this really will be much easier if you use Conditional Formatting especially if on a Continuous form which usually does not work otherwise.

Another thing to take into consideration is the is... NULL and then EMPTY which are two different things to Access. Depending on your query the field could be one or other, see...

http://allenbrowne.com/vba-NothingEmpty.html

And here are some of the issues with NULL...

http://allenbrowne.com/casu-12.html

 

[gizmogeek]

Okay... Gina I would use conditional formatting except I know nothing about it. I've been out of the field for 6 years and ended up taking on two projects for non profits now I'm in over my head. I suppose I can take the other raw project and post as I just want this over with. As soon as I get through one thing I'm asked for another.

 

[gizmogeek]

Okay. I have two projects I'm working on and need about the same for each. This one has no info in it yet so I can upload.

The two requests are:
In the frmProductMasterList there are fields called “Size(oz.)” and “Size(lbs.)”. Those are the fields that will hold entries regarding the size of the containers, etc. n the table or form, there can only be 1 of those filled out. For example, if it says “8” on the ounces column, I don’t want it to also say “.5” in the pound field. We need to find a way that an entry can only be out in one of those columns.


In the frmProductMasterList there is a field called WEIGHT. I want that field to be automatically calculated based on the entry which is put in the SizeOz or SizeLb field and multiplied by the PACKOUT field. So, if someone puts 8 in the SIZEOZ field and PACKOUT = 8, then it should yield 4 lbs. If someone put 2 in the SIZELBS field and packout = 10, then it should yield 20 lbs.


When I started out I thought this would only be tables, forms and reports. The demand has grown much larger and like I said, I don't remember too much about the coding??



Tia!

 

[GinaWhipp]

See if this helps...
http://msdn.microsoft.com/en-us/library/gg508986.aspx

 

[missinglinq]

From your exemplars it appears that you want WEIGHT to be given in pounds, regardless of the unit of measure that is entered, so this code is constructed to do just that. It also only allows one of the two Controls, either SizeOz or SizeLb, to be populated at a time. It does the same thing if the Packout Field is populated after one of the Size Fields is populated.

Private Sub SizeLb_AfterUpdate()
  If Nz(Me.SizeLb, 0) <> 0 Then
   Me.SizeOz = Null
   Me.Weight = (Me.SizeLb * Me.Packout)
 End If
End Sub

Private Sub SizeOz_AfterUpdate()
 If Nz(Me.SizeOz, 0) <> 0 Then
   Me.SizeLb = Null
   Me.Weight = (Me.SizeOz * Me.Packout) / 16
 End If
End Sub

Private Sub Packout_AfterUpdate()
  If Nz(Me.SizeLb, 0) <> 0 Then
   Me.SizeOz = Null
   Me.Weight = (Me.SizeLb * Me.Packout)
 End If
 If Nz(Me.SizeOz, 0) <> 0 Then
   Me.SizeLb = Null
   Me.Weight = (Me.SizeOz * Me.Packout) / 16
 End If
End Sub

You need to make sure that all of the names I've used match your actual names exactly.

Now, if WEIGHT is an Unbound Control, as it should be (Calculated Controls should not be Bound to a Field in a Table, but re-calculated when needed), you'll also need this

Private Sub Form_Current()
If Nz(Me.SizeLb, 0) <> 0 Then
   Me.SizeOz = Null
   Me.Weight = (Me.SizeLb * Me.Packout)
 End If
 If Nz(Me.SizeOz, 0) <> 0 Then
   Me.SizeLb = Null
   Me.Weight = (Me.SizeOz * Me.Packout) / 16
 End If
End Sub

Note that this code doesn't actually 'gray out' the unused Size Control, it simply 'zeros' it out. This allows the users to change their minds, after initially filling in the Record, as to which Size Control, SizeOz or SizeLb, they want to use.

Linq ;0)>

 

[gizmogeek]

missinglinq thank you so much!! I bit off more than I could chew this time. It seems to have worked but I won't know for sure until they test it. Is there some where we can talk outside of this forum or a chat on the forum? I don't know if I'm allowed to give my email address so I'll just wait to see. Thanks!!