Enable or disable a form field based on a Check box in the form.

[bubai]

In my BOUND access form I have [Tolerance] check box, [Tolerance%] box, [LC value] box and [LCA value] box. First one is a check box, second one is integer and rest of the two are currency.


I'd like a code that if finds the [Tolerance] check box UNCHECKED, will make [LCA value] box blank and Disable for editing or adding value. And i f found CHECKED, will Enable [L CA value] box and in it put the result of {[LC value] box value * ([Tolerance%] box value + 100)/100}. Thanks in advance.

[ranman256]

txtLCA.locked =not chkTolerence.value 

if chkTolerence.value 
    txtLCA =  box value * txtTolerancePct
else
    txtLCA = ""
end if

[bubai]

Dear ranman256,
I guess the code goes to the form's module. But could you tell me, again What Event do I need to put this code. I tried this as it is (of course after adjusting the field names and formula) in the VBE but it didn't work. Please help.

[June7]

Try the checkbox Change event as well as the form Current event.

[bubai]

Do i have to put the same code against both events? There is no checkbox Change event. Then what event? I've put the following code in the form Current event:

Private Sub Form_Current()
[LCA Amount].Locked = Not Tolerance.Value
If Tolerance.Value Then
[LCA Amount] = [LC/TT Amount] * ((100 + Tolerance%) / 100)
Else
[LCA Amount] = ""
End If
End Sub

Then it shows the error: "Type-declaration character does not match declared data type" and highlight Tolerance%(blue). What gives?

[June7]

Sorry, guess it would be the checkbox Click event. Code in both events or in a Sub behind the form that can be called by both events.

You show Tolerance and Tolerance%. Is one the textbox name and the other the field name?

% is a special character.

Should avoid spaces and special characters/punctuation (underscore is exception) in naming convention, otherwise always enclose in []: [Tolerance%].

Better name would be TolerancePct or Tolerance_Pct.

[bubai]

I've used [Tolerance%] and for the first-time i worked. But as i've closed the form and open it again, it showed "Run-time error '94' Invalid use of Null" and highlighted the line: "[LCA Amount].Locked = Not Tolerance.Value" in yellow.

[June7]

The checkbox is bound to a Yes/No type field?

[bubai]

Yes it is bound to a Yes/No data type field of the table and in On/Off format.

[June7]

That is odd. The field should never be Null, must be either Yes or No (True or False, -1 or 0).

I can't replicate the issue. If you want to provide db for analysis, follow instructions at bottom of my post.

[bubai]

Whenver I try to add new record to the form the Tolerance checkbox is neither checked nor unchecked, it becomed blue filled. I couldn't find a -1 or 0 option, can you tell me how to get them. I have changed the On/off format to True/false, still it shows the same error. This time i am attaching the database for your checking. Please remove the .zip file extension which i have renamed myself as the server didn't let me attach a .rar file which actually it is. LCorTT.rar.zip

[June7]

I cannot open rar files, only Windows Compression zip files.

[bubai]

Please find the file in windows compression zip format.LCorTT.zip

[June7]

Normally a form can enter/edit data for only one table. You have the form bound to a query that includes both Order and Shipment tables with in an INNER JOIN. INNER JOIN requires related records in both tables for record to display in query. This form will not work. Should use a form/subform arrangement.

Naming convention will cause frustrations. Suggest you change.