Mysterious value added to text box (1 Viewer)

[hilian]

On my form if the user enters a 0 in text box TR_2, then text box TR_3 becomes invisible. However, if the user enters a value in TR_3 and then enters a 0 in TR_2, TR_3 will contain invalid data. To prevent this, I put in a sub before update to change the value of TR_3 to Null if TR_2 contains a 0. this works, but if any other value is entered in TR_3, it changes to a 3.

The code is:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.TR_2.Value = 0 Then
Me.TR_3.Value = Null
Else
Me.TR_3.Value = 0 Or 1 Or 2
End If
End Sub

I don't understand what is happening or how to fix it.

Can anyone help?

Henry

 

[Ranman256]

You can't say
me.box =1 or 2 or 3

You must put the box in for EVERY 'or'.
Me.box=1 or me.box=2 ...

 

[mjdemaris]

What are you trying to do with the text boxes?

What is the relationship between the two boxes?

If you have an event that fires every time the value of either box changes, for both boxes, then you're going to have problems.

What other event code is going on?

 

[hilian]

Ranman256: First I changed Me.TR_3.Value = 0 Or 1 Or 2 to Me.TR_3.Value = 0 Or Me.TR_3.Value = 1 Or Me.TR_3.Value = 2 and entered 1 in TR_3. When I saved the record, the 1 changed to -1. Then I realized that your correction left out the "Value," so I tried it without: Me.TR_3 = 0 Or Me.TR_3 = 1 Or Me.TR_3 = 2. I entered 1 in TR_3 and still got -1 when I saved the record. At least I didn't get 3, which I suppose is good.

mjdemaris: When the user enters 0 in TR_2 TR_3 should be skipped. To enforce that, TR_3 becomes invisible and focus changes to TR_4. The code is:

Private Sub TR_2_AfterUpdate()
If Me.TR_2.Value = 0 Or Me.TR_2.Value = 99 Then
Me.TR_3.Visible = False
Else
Me.TR_3.Visible = True
End If
End Sub

When I inspected the data, I found that in some cases, there was a value in TR_3, even though the value in TR_2 was 0. This throws off the count, so it's important to prevent. I'm trying to achieve this by changing any value entered in TR_3 when TR_2 is 0 to Null.

Thanks,

Henry

 

[mjdemaris]

Obviously something else is happening. It is easy to set TR_3 to visible = false, but something else is changing the data. What is it?

 

[hilian]

A colleague and I looked over the code in the control and for the form as carefully as we could, and we couldn't figure out what could be causing the problem.

 

[Minty]

What data type in the table is TR_3 ? It sounds like it is a yes/no (Boolean) field. Which will only store true (-1) or False (0) or if you are unlucky Null ...

 

[hilian]

I checked. It's number.

 

[MarkK]

You can successfully "OR" integers together, so...

0 Or 1 Or 2

...is a valid VBA expression, and it will produce a numeric result that you can assign to a control.

 

[hilian]

Mark,

This is where it all started. I used Me.TR_3.Value = 0 Or 1 Or 2, but I got 3 when I saved the record, which is not what I expected or can explain. It also isn't a valid value. When I substituted Me.TR_3 = 0 Or Me.TR_3 = 1 Or Me.TR_3 = 2 and entered 1 in TR_3, the 1 changed to -1 when I saved the record, which I also can't explain, since it's a number field and not Boolian.

Henry

 

[MarkK]

I used Me.TR_3.Value = 0 Or 1 Or 2, but I got 3 when I saved the record, which is not what I expected or can explain.

To explain, when you use boolean operators on integers you perform bitwise math on them, so if you look at the numbers in binary...

0001 = 1
0010 = 2

...and then perform the logical OR operation on each column...

00[COLOR="Red"]0[/COLOR][COLOR="Blue"]1[/COLOR] = 1
or
00[COLOR="Red"]1[/COLOR][COLOR="Blue"]0[/COLOR] = 2
==[COLOR="Red"]=[/COLOR][COLOR="Blue"]=[/COLOR]
00[COLOR="Red"]1[/COLOR][COLOR="Blue"]1[/COLOR] = 3

You got 3, which is correct.

When I substituted Me.TR_3 = 0 Or Me.TR_3 = 1 Or Me.TR_3 = 2 and entered 1 in TR_3, the 1 changed to -1 when I saved the record, which I also can't explain, since it's a number field and not Boolian.

This is a boolean expression where you test three different values for equality to a control...

Me.TR_3 = 0 Or Me.TR_3 = 1 Or Me.TR_3 = 2

If we start solving it we might get something like

True Or False Or False
[COLOR="Green"]'and solving that we get...[/COLOR]
True

So that is called a boolean expression, and in VBA the boolean values converted to numbers are False = 0, and True = -1.

So this explains your results.

 

[mjdemaris]

Very good description, Mark. You are saying that the logical operator then essentially adds the binary values for 1 and 2 producing 0011?

 

[MarkK]

But a few thoughts on what you are trying to solve...

1) I would not hide controls, rather, I would disable them. This makes more sense for the user that the thing is still present, but can't be used. This helps with training/learning too, that features that exist are always present.

2) Also, can the user navigate away from and back to this record? If so, you not only have to enable/disable controls while the user is using the form, but also when a record first loads. In this case you should write a single sub-routine that does all this configuration based on data in the form, that you can call as the user interacts with the form, and when a Current event occurs.

3) Consider a pattern like...

private sub Form_Current()
   ResetForm
end sub

private sub txtData1_AfterUpdate()
   ResetForm
end sub

private sub txtData2_AfterUpdate()
   ResetForm
end sub

private sub ResetForm()
[COLOR="Green"]   'enforce rule for dependent value AND enabled
[/COLOR]   If me.txtData1 > 5 then
      me.txtData2 = Null
      me.txtData2.enabled = false
   else
      me.txtData2.enabled = true
   end if
[COLOR="Green"]   'enforce rule for value only[/COLOR]
   If me.txtData2 < 0 Then me.txtData2 = 0
[COLOR="Green"]   'enforce rule for enabled only[/COLOR]
   me.txtData1.enabled = me.txtData2 < 100
end sub

...so all the logic is handled in one location, and various consumers can deploy that logic with ease.

 

[MarkK]

You are saying that the logical operator then essentially adds the binary values for 1 and 2 producing 0011?

Well, doesn't add then, but rather ORs them, so...

0001
or
0001
or
0001
====
0001

...which we can test in the immediate pane as...

? 1 or 1 or 1
 1

...and if you used the AND operator...

00[COLOR="Red"]0[/COLOR][COLOR="Purple"]1[/COLOR] = 1
and
00[COLOR="Red"]1[/COLOR][COLOR="Purple"]0[/COLOR] = 2
====
00[COLOR="Red"]0[/COLOR][COLOR="Purple"]0[/COLOR] = 0

? 1 And 2
 0

So this lets us leverage each bit in an integer using bitwise comparison so we could, if we wanted, set and retrieve multiple values in a single Long Integer!

 

[hilian]

Mark,

Thank you for your illuminating explanation of the mysterious values I've been getting. Clearly it pays to know how Access (or any program) does its work if you want to avoid surprises.

I discussed the way forward with a colleague, and we concluded that the probability of more unforeseen results as we added code for about two-dozen controls makes it a better bet, since I'm more comfortable in Excel than Access, to solve the problem once the data are downloaded to Excel rather than as they are entered in Access. My solution is, for anyone interested, to add a worksheet with the same fields as the sheet with the downloaded data and on the new sheet, to reference each cell with downloaded data and exclude invalid values cell-by-cell with a formula (e.g. IF(AB2=0,AC2="",Data!AC2)).

I think you're right about disabling controls rather than making them invisible. I can do that by modifying the code I'm already using. That is: I can change Visible = False to Enabled = False in each of the subroutines (again about two dozen). I'm unclear about what resetting the form does, which is why I'm reluctant to wade into it.

Again, many thanks,

Henry

 

[gemma-the-husky]

somebit wise operations are very useful

OR can be used to SET a bit
bytevalue OR bit 1-8. sets bit 1 to 8

AND can be used to TEST whether a bit is set or not
bytevalue AND bit 1-8, inidicates whether bit 1-8 is set or clear

XOR can be used to toggle a bit
bytevalue XOR bit 1-8 sets or clears a bit
bytevalue XOR bit 1-8 a second time clears or sets the same bit

because of this, XOR is especially usefully for simple encryption.
XOR'ing a value produces a "randomish" result
XOR'ing the result again produces the original value again.

 

[hilian]

Dave,

this is interesting as general information, but can it also help with data validation?

Henry

 

[MarkK]

I think Access gives you better tools to constrain user's choices more easily, and, catching mistakes at entry time is far more efficient.

I'm unclear about what resetting the form does, which is why I'm reluctant to wade into it.

I just named the routine ResetForm. You could call it ValidateDataConcisely if you prefer, but I would suggest wading into it and seeing what it does, and how it does it. Obviously working with a tool you are most comfortable with has tremendous advantages, but understanding how easy it is to constrain and validate data in code in an Access form is worth looking into too, IMO.

Cheers,

 

[hilian]

Mark,

I see what you’re saying, and I agree that it’s worth going further. I mostly understand this code (I’ve changed the control names and values to the ones I’m using, and I’ve put my questions in brackets ).

private sub ResetForm()
'enforce rule for dependent value AND enabled
If me.txtTR_2 = 0 then
me.txtTR_3 = Null
me.txtTR_3.enabled = false
else
me.txtTR_3.enabled = true
end if
'enforce rule for value only
If me.txtTR_3 < 0 Then me.txtTR_3 = 0 [I don’t understand what this line is doing]
'enforce rule for enabled only
me.txtTR_2.enabled = me.txtTR_3 < 3 [Is this the way to restrict entry to 0, 1 or 2?]
end sub

How do I enter the code? Do I just type it in? It doesn't seem to be connected to an event. also, I'll need If statements for each of the about two-dozen controls I'm concerned with. Can I put them all in the same subroutine?

What I don’t understand is what the three subroutines at the beginning are doing. Will I need to add these for each of the controls I need to restrict? Does the first one, which applies to the form, itself, cover all of the controls? If so, can it interfere with code that's attached to other controls?

private sub Form_Current()
ResetForm
end sub

private sub txtData1_AfterUpdate()
ResetForm
end sub

private sub txtData2_AfterUpdate()
ResetForm
end sub


Many thanks. I think I see the way to achieve what I need to do, but I'm not there yet.

Henry

 

[MarkK]

'enforce rule for value only
If me.txtTR_3 < 0 Then me.txtTR_3 = 0 [I don’t understand what this line is doing]

This line doesn't allow negative numbers. If the user entered -3, the value would simply be amended to zero without notice.

'enforce rule for enabled only
me.txtTR_2.enabled = me.txtTR_3 < 3 [Is this the way to restrict entry to 0, 1 or 2?]

This enables or disables TR2 based on the value in TR3. If TR3 < 3, TR2 is enabled, and in all other cases, TR2 is disabled.

But before we go any further, one thing worth knowing is: will a user ever navigate to this record and need to have various controls enabled or disabled, or have other form presentation work done, based on data in the form?

If so, then regardless of individual control validation, you are going to need an umbrella routine that does that form set-up work for every record you load, before any user interaction with that data. In that case it makes sense to bundle that work into a single routine that can be called when a new record loads. And in that case, since you need that routine anyway, just call it when data changes in the form too. See what I mean?

But if you are just doing a one-of validation, hitting a button, and exporting the data never to be seen again, then it might make sense to do it another way.

So let's establish that first, are we building a way for users to interact with the same data over time? Will users come back to this record, and need to have it set up again, based on this series of rules that enable and disable controls based on their values?