How do I assign text to a table field via checkbox on a form?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form that I want to use to update information in a table. I've
decided to use checkboxes to make it as simple and easy to use as possible. I
want to use two of the checkboxes as an either/or kinda thing. Something like
this:
Private Sub CkBox1_Click( )
If CkBox1.Value = True Then
Table.Field = "Option 1"
End If
End Sub
Private Sub CkBox2_Click( )
If CkBox2.Value = True Then
Table.Field = "Option 2"
End If
End Sub
Only thing is I can't get it to work properly. I tried adding brackets, no
brackets, quotes, no quotes. Any ideas?
 
The first problem I see is that your CkBox objects are not qualified. Should
be
Me.CkBox1. Then, what is Table.Field? Whatever it is, the syntax is
incorrect. If it is, as you describe it, a field in a table you are trying
to update, then there are some issues. First, is the Form bound or unbound?

If it is unbound, then you need to be sure Table is defined as a recordset
and that you have a current record in the table. I would recommend you use a
name other than Table, and the syntax should be:
Table![Field] = "Option 1"

Even then, I would not recommend you do it at that point. You should wait
until you are ready to update your record and assign all the field values at
one time:
With Table
.[Field] = "Option 1"
.[SomeOtherFields] = "whatever"
.Update
End With

If it is a bound form, then the code will not be the way to do it if the
check box is bound to the field. A better way is to create an invisible text
box control on your form, and bind the field to that. Then, in the After
Update event of the check box:
If Me.CktBox1 = True Then
Me.TxtBox1 = "Option 1"
End If
 
Thank you for your response. I understand what you mean by having qualified
variable names. I just did that to kind of give an idea of what I am trying
to do but I actually have other names for them. However, I think my biggest
problem is b/c in the table the field is a listbox with two or more items,
but on the form I want checkboxes and depending on which checkbox is selected
I want that to be reflected in the table but not with a number as you would
get using an option group. I want it to actually show the text. I have also
tried using an unbound check box:
Private Sub CkBox1_Click( )
If CkBox1.Value = True Then
[Table.Field] = "Phase 1"
End If
End Sub
This seems to work, but when I change to the next record, it doesn't change
with that record it is already selected from the previous record. I don't
want to have it clear everytime I open a record b/c then it will erase
previously saved information. So I'm stuck again.
 
Put the same code in th Current event of your form. It will fire each time
you move to a record.
 
Thank you again. I got it working. I did add a textbox and I have that bound
to the field in the table. Then I left the checkboxes unbound. I ended up
writing this for the Current event:
With Table
If IsNull(Field) Then
Me!ckBox1 = False
ElseIf txtField.Value = "Option 1" Then
Me!ckBox1 = True
Me!ckBox2 = False
ElseIf txtField.Value = "Option 2" Then
Me!ckBox2 = True
Me!ckBox1 = False
End If
End With

Then for the checkboxes I wrote:

Private Sub ckBox1_Click()
If ckBox1.Value = True Then
txtField.Value = "Option 1"
ElseIf ckBox1.Value = False Then
txtField.Value = " "
End If
End Sub
Private Sub ckBox2_Click()
If ckBox2.Value = True Then
txtField.Value = "Option 2"
ElseIf ckBox2.Value = False Then
txtField.Value = " "
End If
End Sub
 
Oh, I also added Enable/Disable code to the checkboxes, so that when one is
selected the other one can not be selected unless the first one is
de-selected.
 
Great! Now, a couple of comments:
In your first piece of code, If Fied is Null you don't do anything with ckBox2

It is not bad practice, but not necessary to use .Value. It is the default
property.
Me.txtField works just as well.

It is a matter of style here, but rathter than a string of ElseIfs I prefer
the Select statement:

With Table
Select Case Me.txtField
Case is Null
Me!ckBox1 = False
Case is = "Option 1"
Me!ckBox1 = True
Me!ckBox2 = False
Case is = "Option 2"
Me!ckBox2 = True
Me!ckBox1 = False
End Select
End With

With Table
If IsNull(Field) Then
Me!ckBox1 = False
ElseIf txtField.Value = "Option 1" Then
Me!ckBox1 = True
Me!ckBox2 = False
ElseIf txtField.Value = "Option 2" Then
Me!ckBox2 = True
Me!ckBox1 = False
End If
End With

For this, my style would be:
Me.txtField = IIf(Me.ckBox1, "Option 1", "")
Me.txtField = IIf(Me.ckBox2, "Option 2", "")

There is nothing wrong with your code. It looks fine. Just thought I'd
share some different way with you.

I do see one problem, I think. If both check boxes are modifying the same
text box, then the value in the text box for a new or updated record will
depend on which check box was clicked last. If this is the case, you could
use a Tri state option group to do this. If no selection is made, it will
return Null, and with two buttons or checkboxes (I prefer buttons) one will
return 1 and the other will return 2.

The code you have will still work, you would just have to change the names.

Good Luck!


Then for the checkboxes I wrote:

Private Sub ckBox1_Click()
If ckBox1.Value = True Then
txtField.Value = "Option 1"
ElseIf ckBox1.Value = False Then
txtField.Value = " "
End If
End Sub
Private Sub ckBox2_Click()
If ckBox2.Value = True Then
txtField.Value = "Option 2"
ElseIf ckBox2.Value = False Then
txtField.Value = " "
End If
End Sub
 
Back
Top