checkbox

[krishnanhemanth]

hi everybody
i have a form called MATERIAL DETAILS
i have 2 textbox and a checkbox on the form
qty-------bound textbox to a table
qtybalance----unbound textbox .. a calculated textbox getting its value thru dsum
status---checkbox bound to table

i want the checkbox to be true if qty = qtybalance
please help

thanks in advance
krishnanhemanth

[Delerna]

One way to do that is with vba
In the after update event for "qty" (your bound textbox)

Get to the VBA window for that event from the Events tab on the textbox properties window
click afterupdate and then the button with 3 dots in it

If cstr(qty)=cstr(qtybalance) then checkbox.value=true
 

This is using the names you gave the controls in your question.
I have used cstr() on each textbox in the If statement because
a calculated value in one textbox might not be of the same type as an
entered value in another textbox.
If that is the case then the If will never evaluate to true.
So converting the textbox contents so that they are both strings
guarantees they will be of the same type.

[OldBirdman]

1) Don't you need to either have a default value for checkbox or set it in your code?

If cstr(qty)=cstr(qtybalance) then checkbox.value=true

might be

If cstr(qty)=cstr(qtybalance) then checkbox.value=true else checkbox.value=false
---or---
checkbox.value = (cstr(qty)=cstr(qtybalance))

2) Checkbox is a bound control and a calculated value. Isn't this bad database design. Calculated values on a form should not be saved in the table, but recomputed each time. I assume that qtybalance is computed from another table, and that table might change, changing the value computed thru dsum.
3) I don't understand the need for cstr() function. I thought the .value stored by a textbox control was String. Am I correct to think that a bound textbox control displays a formatted string representing the value in the table, and is not a window to the table itself?

[ADezii]

@OldBirdman

3) I don't understand the need for cstr() function. I thought the .value stored by a textbox control was String.

I do believe that you are correct in this matter, OldBirdman, since the following Expressions will both evaluate to True:

MsgBox VarType(Me![qty]) = vbString
MsgBox VarType(Me![qtybalance]) = vbString

[Delerna]

3) I don't understand the need for cstr() function. I thought the .value stored by a textbox control was String.

I thought the same when I tested my code for the post.
My test scenario was this

Create a form with 2 textboxes and a checkbox
Set the default value for one of the textboxes to 10
enter code ( without cstr() ) 
into the after update event of the other textbox
Run form and enter 10 into the second textbox
 

I set the default value to 10 because

qtybalance----unbound textbox .. a calculated textbox getting its value thru dsum

But the code didnt work, the if statement always evaluated false.

use of Adezii's code shows the values in the 2 textboxes are of a different type.

At least it does in my version of access.
So my conclusion is that the contents of a textbox is not necessarily of type string.
Actually I remember this happening for me in the past in one of my own projects
Therefore my use of CStr()

2) Checkbox is a bound control and a calculated value. Isn't this bad database design. Calculated values on a form should not be saved in the table, but recomputed each time.

Yes it is. A general rule of good database design is "don't save calculated values".
Good or bad, I just answered the question.
To be honest I didn't even notice the statement that the checkbox was bound.

[OldBirdman]

At least it does in my version of access.
So my conclusion is that the contents of a textbox is not necessarily of type string.
Actually I remember this happening for me in the past in one of my own projects
Therefore my use of CStr()

You are correct here. I tend to think of Text and String as the same thing. They are, and they're not. "Text" in a table (Access) is a string, but in a "TextBox", it is not. VBA uses "String" and not "Text".
If a textbox is bound to a "Text" field, it is a string. If it is bound to a numeric field, it is not. Data being entered in the temporary area TextBox.Text is a string, and may be converted to other data types during Update.
I will now mentally think of TextBox as DisplayBox (or DBox), which may or may not be text, and may be text while being edited. Thank you for the clarification.