Can I make a checkbox do this?

[Steve P.]

I am using Access97 to maintain a database that is ultimately used in
another application so I am under some constraints about data types in
certain fields.

On my frmMain I have sfmProducts that has qryProducts as its record source.
One of the fields is named "Hide" which is a 1 character long text field
that must contain a value of "Y" or "N". "Y" means that the product will be
hidden in the other app and "N" means that it will be shown.

On my subform I would like to use a checkbox to set the value of "Hide" but
is it possible to make a checkbox work with values other than 0 and -1?
Also, I would really like to reverse the logic as it's not very intuitive to
ask "check here if you don't want to show this product". So what I really
need is if chkSelected = True then "Hide" = N, else field "Hide" = "Y".

Thanks in advance for your help.

 

[Guest]

The underlying table or query is specified in the Form's RecordSource
property. Right click on the square at the top left of the form design
window, select Properties, and pick the source from the dropdown list.

Hope that helps.
Sprinks

 

[Guest]

Steve,

Sorry about the other post; I answered Eric's question in error.

It might be simpler to make your Hide field a boolean field named something
like Show, so the checkbox could be bound to it with no programming needed,
but if you want to keep your current structure, place an unbound checkbox,
and the following code in its AfterUpdate event:

If chkSelected Then
Me![Hide] = "N"
Else
Me![Hide] = "Y"
End If

Hope that helps.

Sprinks

 

[Steve P.]

The checkbox is on a continuous form. When I used an unbound checkbox it
would update a single record but the display of every checkbox on each
record would change.

Somehow I came up with this... I set the control source for chkSelected to:

=IIf([txtHide]="N",0,-1)

and put the following code in the AfterUpdate event of chkSelected:

Private Sub chkSelectItem_AfterUpdate()
If txtHide = "Y" Then
txtHide = "N"
Else: txtHide = "Y"
End If
Me.Refresh
End Sub

Which did not work. Nothing changed and I got the following message in the
status bar:

Control can't be edited; It's bound to the expression
'IIf([txtHide]="N",0,-1)'

I then tried the code in the OnMouseUp event:

Private Sub chkSelectItem_MouseUp(Button As Integer, Shift As Integer, X As
Single, Y As Single)
If txtHide = "Y" Then
txtHide = "N"
Else: txtHide = "Y"
End If
Me.Refresh
End Sub

Which does work as I want, the record is updated and the checkbox control
displays the proper state, but I still get the message and a "critical"
sound if the volume is up.

Is there a better way to do this and/or is it possible to supress the
message and sound?

Thanks

Steve,

Sorry about the other post; I answered Eric's question in error.

It might be simpler to make your Hide field a boolean field named something
like Show, so the checkbox could be bound to it with no programming needed,
but if you want to keep your current structure, place an unbound checkbox,
and the following code in its AfterUpdate event:

If chkSelected Then
Me![Hide] = "N"
Else
Me![Hide] = "Y"
End If

Hope that helps.

Sprinks

I am using Access97 to maintain a database that is ultimately used in
another application so I am under some constraints about data types in
certain fields.

On my frmMain I have sfmProducts that has qryProducts as its record source.
One of the fields is named "Hide" which is a 1 character long text field
that must contain a value of "Y" or "N". "Y" means that the product will be
hidden in the other app and "N" means that it will be shown.

On my subform I would like to use a checkbox to set the value of "Hide" but
is it possible to make a checkbox work with values other than 0 and -1?
Also, I would really like to reverse the logic as it's not very intuitive to
ask "check here if you don't want to show this product". So what I really
need is if chkSelected = True then "Hide" = N, else field "Hide" = "Y".

Thanks in advance for your help.

 

[Guest]

Steve,

There may be another way out of your dilemna, but I strongly suggest you use
Access' core technology, and add a Boolean field "Show". You will require no
code at all to achieve what you want. All you need to do is run an Update
query on existing records, then delete your current text field.

Good luck.
Sprinks

The checkbox is on a continuous form. When I used an unbound checkbox it
would update a single record but the display of every checkbox on each
record would change.

Somehow I came up with this... I set the control source for chkSelected to:

=IIf([txtHide]="N",0,-1)

and put the following code in the AfterUpdate event of chkSelected:

Private Sub chkSelectItem_AfterUpdate()
If txtHide = "Y" Then
txtHide = "N"
Else: txtHide = "Y"
End If
Me.Refresh
End Sub

Which did not work. Nothing changed and I got the following message in the
status bar:

Control can't be edited; It's bound to the expression
'IIf([txtHide]="N",0,-1)'

I then tried the code in the OnMouseUp event:

Private Sub chkSelectItem_MouseUp(Button As Integer, Shift As Integer, X As
Single, Y As Single)
If txtHide = "Y" Then
txtHide = "N"
Else: txtHide = "Y"
End If
Me.Refresh
End Sub

Which does work as I want, the record is updated and the checkbox control
displays the proper state, but I still get the message and a "critical"
sound if the volume is up.

Is there a better way to do this and/or is it possible to supress the
message and sound?

Thanks

Steve,

Sorry about the other post; I answered Eric's question in error.

It might be simpler to make your Hide field a boolean field named something
like Show, so the checkbox could be bound to it with no programming needed,
but if you want to keep your current structure, place an unbound checkbox,
and the following code in its AfterUpdate event:

If chkSelected Then
Me![Hide] = "N"
Else
Me![Hide] = "Y"
End If

Hope that helps.

Sprinks

I am using Access97 to maintain a database that is ultimately used in
another application so I am under some constraints about data types in
certain fields.

On my frmMain I have sfmProducts that has qryProducts as its record source.
One of the fields is named "Hide" which is a 1 character long text field
that must contain a value of "Y" or "N". "Y" means that the product will be
hidden in the other app and "N" means that it will be shown.

On my subform I would like to use a checkbox to set the value of "Hide" but
is it possible to make a checkbox work with values other than 0 and -1?
Also, I would really like to reverse the logic as it's not very intuitive to
ask "check here if you don't want to show this product". So what I really
need is if chkSelected = True then "Hide" = N, else field "Hide" = "Y".

Thanks in advance for your help.

 

[Steve P.]

In case you are still following, thanks for the assistance. I know that you
are correct, I am trying to do something the hard way when Access could
actually handle this quite easily if I could just change the data type.
However the underlying table is randomly imported into another app (FoxPro
db I think) and 0's & -1's don't work. I need to store Y's & N's.

Here is what I finally did; I set the recordsource of the checkbox to:
=IIf([txtHide]="Y",0,-1)
and set it's properties to enabled = No, locked = Yes.

so now the checkbox merely acts as an indicator as to whether an item is
selected or not. To toggle items on and off I placed a command button on the
form directly over the checkbox, made it transparent and inserted the
following code:

Private Sub cmdSelectItem_Click()
If txtHide = "Y" Then
txtHide = "N"
Else: txtHide = "Y"
End If
Me.Refresh
End Sub

Now the user sees a check if the item is selected and he can (apparently)
click directly on the checkbox to toggle items on or off and I am able to
store the data the way I need to (Y's and N's). Three controls for one field
but it does make for a better user interface.

 

[Guest]

Hi, Steve.

That's a good, creative solution.

I did have one other thought, however, that would enable you to use a
Boolean field. You could create a query that calculates the "Y" or "N"
field, and let your FoxPro users import that instead of the table directly.

Sprinks

In case you are still following, thanks for the assistance. I know that you
are correct, I am trying to do something the hard way when Access could
actually handle this quite easily if I could just change the data type.
However the underlying table is randomly imported into another app (FoxPro
db I think) and 0's & -1's don't work. I need to store Y's & N's.

Here is what I finally did; I set the recordsource of the checkbox to:
=IIf([txtHide]="Y",0,-1)
and set it's properties to enabled = No, locked = Yes.

so now the checkbox merely acts as an indicator as to whether an item is
selected or not. To toggle items on and off I placed a command button on the
form directly over the checkbox, made it transparent and inserted the
following code:

Private Sub cmdSelectItem_Click()
If txtHide = "Y" Then
txtHide = "N"
Else: txtHide = "Y"
End If
Me.Refresh
End Sub

Now the user sees a check if the item is selected and he can (apparently)
click directly on the checkbox to toggle items on or off and I am able to
store the data the way I need to (Y's and N's). Three controls for one field
but it does make for a better user interface.

Steve,

There may be another way out of your dilemna, but I strongly suggest you use
Access' core technology, and add a Boolean field "Show". You will require no
code at all to achieve what you want. All you need to do is run an Update
query on existing records, then delete your current text field.

Good luck.
Sprinks