Update a TextBox based on a CheckBox

[Guest]

Hi all,

My question is as follows. In an access form, when a checkbox gets uticked,
I would like to change the value of a textBox, with the code I've copied
below I get a 2115 run-time error. Any ideas? Thanks in advance!
Ruth


If Not [Borrowed] Then
[LeaseRecord].SetFocus
oldTxt = [LeaseRecord].Text
[BorrowedBy].SetFocus
newTxt = oldTxt & [BorrowedBy].Text
[LeaseRecord].SetFocus
[LeaseRecord].Text = newTxt
End If

 

[Barry Gilbert]

On the 2nd, 4th, and 6th lines, it looks like you're trying to set
focus to a table field rather than a textbox. You can't refer to
controls using [] brackets.

HTH,
Barry

 

[Guest]

You don't say on which line the error occurs; however, looking at the code,
it would appear you have a validation rule set in your table on the field
that is bound to the control leaserecord. Also, you don't really need all
the code you have. I see you are repeatedly setting focus to get the .Text
property. The .Text property is only available when the control has the
focus; but that is not necessary. If you address the control with just the
control name, you will get the current value of the control. First, remove
the validation rule from the table. It is much easier to control validation
in VBA using either the control's or the form's Before Update events. Also,
it is a better practice to qualify your controls with the name of the form or
Me. which references the current form. It is easier for Access to understand
and helps others reading your code know it is a control on your form. Here is
a rewrite of your code:

If Not Me.Borrowed Then
Me.LeaseRecord = Me.LeaseRecord & Me.BorrowedBy
End If

Then in the Before Update event of LeaseRecord you can evaluate the value of
LeaseRecord to ensure it is valid. If it is not, you can present a message
to the user and cancel the update:

If Me.LeaseRecord 'Check for condition that makes it invalid and returns
false' Then
MsgBox "Invalid Value for LeaseRecord"
Cancel = True
End If

 

[Guest]

Not exactly true, Barry. To use brackets you need to qualify what you are
referencing.
forms![MyFormName]![MyControlName]
is valid.

 

[Barry Gilbert]

My mistake. I almost never use brackets.

 

[Guest]

Good for you. That means you are using proper naming conventions and don't
need to worry about an ambiguous name that might confuse Access.
For me, improper naming conventions is a sin worse than GoTo

 

[Guest]

Thanks!