Unbound control with bound controls

[Vince]

I have a form based on a linked table in Oracle. In that table, I have
column in my table which uses 1 = true/on/yes and 0 = false/off/no.
Due to Access form's checkbox control using -1 for "on/true/yes, in my
form, I unbound the column and put some simple code in the on
Forms_Current event, using a common module so all forms can use this
logic:

Private Sub Form_Current()

Me.chkboxIsActive.Value = modUtil.db2ChkBoxVal(Me.IS_ACTIVE)

End Sub

Public Function db2ChkBoxVal(dbValue As Integer) As Integer

If dbValue = 1 Then
db2ChkBoxVal = -1
Else
db2ChkBoxVal = 0
End If

End Function

I reverse the process in the Form_BeforeUpdate event.

This seems to work as expected as far as a single row is concerned.
However, in the tabular form I built (shows many records from the DB),
the unbound control seems to effect all records. If I check/uncheck
the first row's checkbox, all rows values are changed. Ditto updating
any row's checkbox. Is there a way to achieve the unbound control's
behaviour on a row-by-row basis?

- Vince

 

[Marshall Barton]

I have a form based on a linked table in Oracle. In that table, I have
column in my table which uses 1 = true/on/yes and 0 = false/off/no.
Due to Access form's checkbox control using -1 for "on/true/yes, in my
form, I unbound the column and put some simple code in the on
Forms_Current event, using a common module so all forms can use this
logic:

Private Sub Form_Current()

Me.chkboxIsActive.Value = modUtil.db2ChkBoxVal(Me.IS_ACTIVE)

End Sub

Public Function db2ChkBoxVal(dbValue As Integer) As Integer

If dbValue = 1 Then
db2ChkBoxVal = -1
Else
db2ChkBoxVal = 0
End If

End Function

I reverse the process in the Form_BeforeUpdate event.

This seems to work as expected as far as a single row is concerned.
However, in the tabular form I built (shows many records from the DB),
the unbound control seems to effect all records. If I check/uncheck
the first row's checkbox, all rows values are changed. Ditto updating
any row's checkbox. Is there a way to achieve the unbound control's
behaviour on a row-by-row basis?

You could try binding the check box directly to the field
and leaving the value at 1. You should only need to check
for -1 when you use the value to compare to another record's
value (e.g. query criteria).

The form's BeforeUpdate event can either be ignored because
the server db automatically converts -1 to +1 or you can
change it:

If Me.checkbox <> 0 Then Me.checkbox = 1

 

[Vince]

I have a form based on a linked table in Oracle. In that table, I have
column in my table which uses 1 = true/on/yes and 0 = false/off/no.
Due to Access form's checkbox control using -1 for "on/true/yes, in my
form, I unbound the column and put some simple code in the on
Forms_Current event, using a common module so all forms can use this
logic:

Private Sub Form_Current()

Me.chkboxIsActive.Value = modUtil.db2ChkBoxVal(Me.IS_ACTIVE)

End Sub

Public Function db2ChkBoxVal(dbValue As Integer) As Integer

If dbValue = 1 Then
db2ChkBoxVal = -1
Else
db2ChkBoxVal = 0
End If

End Function

I reverse the process in the Form_BeforeUpdate event.

This seems to work as expected as far as a single row is concerned.
However, in the tabular form I built (shows many records from the DB),
the unbound control seems to effect all records. If I check/uncheck
the first row's checkbox, all rows values are changed. Ditto updating
any row's checkbox. Is there a way to achieve the unbound control's
behaviour on a row-by-row basis?

You could try binding the check box directly to the field
and leaving the value at 1. You should only need to check
for -1 when you use the value to compare to another record's
value (e.g. query criteria).

The form's BeforeUpdate event can either be ignored because
the server db automatically converts -1 to +1 or you can
change it:

If Me.checkbox <> 0 Then Me.checkbox = 1

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

Thanks, Marsh.

If I do the "..leaving the value at 1", then the check box wont be
checked when I want it to be. I have to convert the values, don't I?

Also, regardless of the outcome of my issue using checkboxes, is there
no way to use an unbound control and somehow "link" it to the record
where it displays, in muti-record forms?

 

[Marshall Barton]

If I do the "..leaving the value at 1", then the check box wont be
checked when I want it to be. I have to convert the values, don't I?

Also, regardless of the outcome of my issue using checkboxes, is there
no way to use an unbound control and somehow "link" it to the record
where it displays, in muti-record forms?

A check box will display as checked for any non-zero value.
So, No, you do not need to convert it just for display
purposes. However, clicking on the check box will always
set it to True (-1) of False (0).

No, you can not display an unbound control with different
values on different rows in a continuous or datasheet form,
regardless of what you do. There is only one control and it
only has one set of properties so there is nowhere to put
the different values.

You can display the bound control while you work with the
unbound control by placing the unbound control directly
behind the bound control. Use the bound control's gotFocus
event to set the focus to the unbound control for users to
enter/edit the data. The unbound control's AfterUpdate
event can be used to set the bound control's value. This
sort of trick is used in situations where a bound, dependent
combo box on a continuous goes blank because it's value is
not in its row source. Another situation where this is
useful is when you want the bound value in a different unit
than what the user enters (e.g. user enters 999 in pennies
and you need to display $9.99). In your case, I believe
this kind of trickery is totally unecessary.

 

[marius]

Hi arch,

You could try something like this
www.mvp-access.com/marius/ejemplos/AWcheckbox.zip

If I do the "..leaving the value at 1", then the check box wont be
checked when I want it to be. I have to convert the values, don't I?

Look for "checkbyte" and/or "checkint"

Also, regardless of the outcome of my issue using checkboxes, is there
no way to use an unbound control and somehow "link" it to the record
where it displays, in muti-record forms?

I think it's posible, but not necessary in your case.

 

[marius]

Vince, I've changed your name, sorry :-(