Check Box - Need "Yes" value to return 1 to linked table

[Arkeyt3kt]

I have a table linked from an SQL Server database that I would like to update
through an Access form. I need to use a series of check boxes to adjust
over 100 different fields, but I need the check boxes to update the fields in
the linked table with 1 for yes instead of -1. The reason for this is that
the other software that loads information from the database uses 1 as the
value for yes and cannot be configured otherwise. I cannot use custom
formatting on the table fields since they are linked. I am assuming that a
VB script is the way to go, but am having trouble getting it to work.

Below is the script that i have written. The check box is "Check46" and
"XRay_Shielding" is the field in the linked table that needs to be updated by
the check box. I have also set the control source for "Check46" to
"XRay_Shielding".

Private Sub Check46_Click()
If Check46 = True Then
Xray_Shielding.Value = "1"
ElseIf Check46 = False Then
Xray_Shielding.Value = "0"
End If
End Sub

After entering this code as a OnClick event, I still get a -1 when the box
is checked. If I remove the control source, I get nothing.

I would appreciate any direction on this.

 

[John W. Vinson]

I have a table linked from an SQL Server database that I would like to update
through an Access form. I need to use a series of check boxes to adjust
over 100 different fields, but I need the check boxes to update the fields in
the linked table with 1 for yes instead of -1. The reason for this is that
the other software that loads information from the database uses 1 as the
value for yes and cannot be configured otherwise. I cannot use custom
formatting on the table fields since they are linked. I am assuming that a
VB script is the way to go, but am having trouble getting it to work.

Below is the script that i have written. The check box is "Check46" and
"XRay_Shielding" is the field in the linked table that needs to be updated by
the check box. I have also set the control source for "Check46" to
"XRay_Shielding".

Private Sub Check46_Click()
If Check46 = True Then
Xray_Shielding.Value = "1"
ElseIf Check46 = False Then
Xray_Shielding.Value = "0"
End If
End Sub

After entering this code as a OnClick event, I still get a -1 when the box
is checked. If I remove the control source, I get nothing.

I would appreciate any direction on this.

A checkbox control only has values 0 and -1. I'd suggest either putting a
BeforeInsert trigger on your SQL backend to store the absolute value of the
entry, or using a Textbox control instead of a checkbox. You could create a
little function:

Public Function FakeCheckbox(ctl As Control)
If NZ(ctl) = 0 Then
Ctl = 1
Else
Ctl = 0
End If
End Sub

and put

=FakeCheckbox(Form.ActiveControl)

in the Click event of each textbox.

To have the textbox display a blank or a check, set its Font to Wingdings, and
its Format property to

"o";"";"";""

A lowercase o in Wingdings looks like a checkmark, and the four part format
will display positive numbers (1) as a checkmark, negative, zero and null as
blank.