Clear part of form on open

[Hank]

I have an attendance form with a list of employees, a check box (checked if
presemt) and a comment text box. A command button on the form appends
records to an work attendance table..

I would like to have the check box and comment text box cleared when the
form is opened OR when the command button is clicked.

I can use DoCmd.GoToRecord,,acnewRecord - but this clears everything on the
form. In the OnOpen event I have tried:
Me.Comments.value=""
this give me a run time error stating "You can't assign a value to this
object". Other code varation I have tried in the OnOpen event are:
Comments=Null - same runtime error
I have also tried this code in the OnLoad event. I do not get an error- but
nothing is cleared on the form.

Is is possible to clear PART of a form? How?

Thanks
Hank

 

[Douglas J. Steele]

The OnOpen event is probably too soon: the form hasn't been completely
instantiated yet.

Try putting your code into the OnLoad event instead.

 

[Hank]

Thanks for your reply Douglas

I have tried the code in the OnLoad event - but nothing happens.
I don't get an error - but the text box does not clear.
The code looks like this:
Private Sub Form_Load()
Me.Comments.Value = ""
End Sub

I have also tried
Comments=Null
Comments=""

Same results.

Thanks
Hank

 

[Douglas J. Steele]

You sure the code is firing?

 

[Hank]

Sorry for the slow reply - power outage here for a few days.

No, I not sure if the code is firing or not. Like I said, the OnLoad event
does not clear the text box, but I do not get an error.

When I use the OnOpen event with the same code I get an error stating "You
can't assign a value to this object".

Hank

 

[Douglas J. Steele]

To check whether the code's firing, you can set a breakpoint inside the sub
(click on the gray border to the right of the code), or you can put a
message box into it.

 

[Hank]

Douglas,

Here is the code I'm using:
Private Sub Form_Load()
Me.Comments = ""
msgbox "OK it's Done!"
End Sub

I have also tried:
Private Sub Form_Load()
Me.Comments = Null
msgbox "OK it's Done!"
End Sub

Either way, the message box shows - so yes, I think the code is firing - but
the text box "Comments" does not clear.

The same code under the OnOpen event produces an error stating "You can't
assign a value to this object"

Hank

 

[John W. Vinson]

Douglas,

Here is the code I'm using:
Private Sub Form_Load()
Me.Comments = ""
msgbox "OK it's Done!"
End Sub

I have also tried:
Private Sub Form_Load()
Me.Comments = Null
msgbox "OK it's Done!"
End Sub

Either way, the message box shows - so yes, I think the code is firing - but
the text box "Comments" does not clear.

The same code under the OnOpen event produces an error stating "You can't
assign a value to this object"

Try changing it to

Me.[Comments] = Null

I suspect that Comments is a reserved word and a property of the form... which
you can't edit.

 

[Hank]

Same results with me.[Comments]=Null

I even changed the table, query, and form from "Comments" to "Notes" and get
the same error.

Just what is ment by "You can't assign a value to this object" when it shows
in the On Load event?

Douglas,

Here is the code I'm using:
Private Sub Form_Load()
Me.Comments = ""
msgbox "OK it's Done!"
End Sub

I have also tried:
Private Sub Form_Load()
Me.Comments = Null
msgbox "OK it's Done!"
End Sub

Either way, the message box shows - so yes, I think the code is firing -
but
the text box "Comments" does not clear.

The same code under the OnOpen event produces an error stating "You can't
assign a value to this object"

Try changing it to

Me.[Comments] = Null

I suspect that Comments is a reserved word and a property of the form...
which
you can't edit.

 

[Douglas J. Steele]

The error message can mean that the control you're trying to update may not
be in a usable state yet (as would likely be the case in the Open event,
where it's not fully instantiated), or it may not be updatable.

What's the ControlSource of the text box in question?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Same results with me.[Comments]=Null

I even changed the table, query, and form from "Comments" to "Notes" and
get the same error.

Just what is ment by "You can't assign a value to this object" when it
shows in the On Load event?

Douglas,

Here is the code I'm using:
Private Sub Form_Load()
Me.Comments = ""
msgbox "OK it's Done!"
End Sub

I have also tried:
Private Sub Form_Load()
Me.Comments = Null
msgbox "OK it's Done!"
End Sub

Either way, the message box shows - so yes, I think the code is firing -
but
the text box "Comments" does not clear.

The same code under the OnOpen event produces an error stating "You can't
assign a value to this object"

Try changing it to

Me.[Comments] = Null

I suspect that Comments is a reserved word and a property of the form...
which
you can't edit.

 

[Hank]

The controlSource of the text box is a table (EmpTbl). There is also a
check box to check if the employee is present (or leave unchecked if
absent). A command button on the form runs an append query and post to
AttendanceTbl. All of this works great.
What I need is: When the attendancefrm opens, clear the notes text box and
the present check box.
I do not want the entire form cleared. The list of employees and employeeID
should always show.

Thanks

The error message can mean that the control you're trying to update may
not be in a usable state yet (as would likely be the case in the Open
event, where it's not fully instantiated), or it may not be updatable.

What's the ControlSource of the text box in question?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Same results with me.[Comments]=Null

I even changed the table, query, and form from "Comments" to "Notes" and
get the same error.

Just what is ment by "You can't assign a value to this object" when it
shows in the On Load event?

Douglas,

Here is the code I'm using:
Private Sub Form_Load()
Me.Comments = ""
msgbox "OK it's Done!"
End Sub

I have also tried:
Private Sub Form_Load()
Me.Comments = Null
msgbox "OK it's Done!"
End Sub

Either way, the message box shows - so yes, I think the code is firing -
but
the text box "Comments" does not clear.

The same code under the OnOpen event produces an error stating "You
can't
assign a value to this object"

Try changing it to

Me.[Comments] = Null

I suspect that Comments is a reserved word and a property of the form...
which
you can't edit.

 

[Hank]

OK, I have been reading everything I can find and may have a clue.

The form I am opening (AttendanceFrm) is opened in the switchboard in EDIT
mode. Some places I have read where I will get the error "You can't assign
a value to this object" if the form is opened in edit mode. This is the
error I get when the OnOpen event has ( me.notes="")
Is this true? If so,is there another way to open the form where all
employee names are shown and that the "present" checkbox and the "Notes"
textbox are clear? These and the UnBound text box in the footer (atnDate)
are the only items on the form that needs to be updated.

Hank

The controlSource of the text box is a table (EmpTbl). There is also a
check box to check if the employee is present (or leave unchecked if
absent). A command button on the form runs an append query and post to
AttendanceTbl. All of this works great.
What I need is: When the attendancefrm opens, clear the notes text box and
the present check box.
I do not want the entire form cleared. The list of employees and
employeeID should always show.

Thanks

The error message can mean that the control you're trying to update may
not be in a usable state yet (as would likely be the case in the Open
event, where it's not fully instantiated), or it may not be updatable.

What's the ControlSource of the text box in question?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Same results with me.[Comments]=Null

I even changed the table, query, and form from "Comments" to "Notes" and
get the same error.

Just what is ment by "You can't assign a value to this object" when it
shows in the On Load event?

Douglas,

Here is the code I'm using:
Private Sub Form_Load()
Me.Comments = ""
msgbox "OK it's Done!"
End Sub

I have also tried:
Private Sub Form_Load()
Me.Comments = Null
msgbox "OK it's Done!"
End Sub

Either way, the message box shows - so yes, I think the code is
firing - but
the text box "Comments" does not clear.

The same code under the OnOpen event produces an error stating "You
can't
assign a value to this object"

Try changing it to

Me.[Comments] = Null

I suspect that Comments is a reserved word and a property of the
form... which
you can't edit.

 

[Douglas J. Steele]

The ControlSource of a text box cannot be a table. I'll assume you mean it's
a field in the form's RecordSource. (What I was trying to determine is that
it wasn't actually a function wrapped around a field)

Given what you're trying to do, can you not simply update the underlying
table first?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

The controlSource of the text box is a table (EmpTbl). There is also a
check box to check if the employee is present (or leave unchecked if
absent). A command button on the form runs an append query and post to
AttendanceTbl. All of this works great.
What I need is: When the attendancefrm opens, clear the notes text box and
the present check box.
I do not want the entire form cleared. The list of employees and
employeeID should always show.

Thanks

The error message can mean that the control you're trying to update may
not be in a usable state yet (as would likely be the case in the Open
event, where it's not fully instantiated), or it may not be updatable.

What's the ControlSource of the text box in question?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Same results with me.[Comments]=Null

I even changed the table, query, and form from "Comments" to "Notes" and
get the same error.

Just what is ment by "You can't assign a value to this object" when it
shows in the On Load event?

Douglas,

Here is the code I'm using:
Private Sub Form_Load()
Me.Comments = ""
msgbox "OK it's Done!"
End Sub

I have also tried:
Private Sub Form_Load()
Me.Comments = Null
msgbox "OK it's Done!"
End Sub

Either way, the message box shows - so yes, I think the code is
firing - but
the text box "Comments" does not clear.

The same code under the OnOpen event produces an error stating "You
can't
assign a value to this object"

Try changing it to

Me.[Comments] = Null

I suspect that Comments is a reserved word and a property of the
form... which
you can't edit.

 

[Hank]

Doug,
Updating the table may be a better idea. I assume I would be updating the
Employeetbl not the Attendancetbl (where the records are appended).
If by updating, you mean clearing the check box and notes text box on the
Employeetbl after they are recorded on the Attendancetble.

When I do this manually it does clear the form - just like I want, but I do
not know how to write the code to make it happen when the "Post Attendance"
Command button on the form is clicked. Can you help me there?
Hank

The ControlSource of a text box cannot be a table. I'll assume you mean
it's a field in the form's RecordSource. (What I was trying to determine
is that it wasn't actually a function wrapped around a field)

Given what you're trying to do, can you not simply update the underlying
table first?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

The controlSource of the text box is a table (EmpTbl). There is also a
check box to check if the employee is present (or leave unchecked if
absent). A command button on the form runs an append query and post to
AttendanceTbl. All of this works great.
What I need is: When the attendancefrm opens, clear the notes text box
and the present check box.
I do not want the entire form cleared. The list of employees and
employeeID should always show.

Thanks

The error message can mean that the control you're trying to update may
not be in a usable state yet (as would likely be the case in the Open
event, where it's not fully instantiated), or it may not be updatable.

What's the ControlSource of the text box in question?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Same results with me.[Comments]=Null

I even changed the table, query, and form from "Comments" to "Notes"
and get the same error.

Just what is ment by "You can't assign a value to this object" when it
shows in the On Load event?

Douglas,

Here is the code I'm using:
Private Sub Form_Load()
Me.Comments = ""
msgbox "OK it's Done!"
End Sub

I have also tried:
Private Sub Form_Load()
Me.Comments = Null
msgbox "OK it's Done!"
End Sub

Either way, the message box shows - so yes, I think the code is
firing - but
the text box "Comments" does not clear.

The same code under the OnOpen event produces an error stating "You
can't
assign a value to this object"

Try changing it to

Me.[Comments] = Null

I suspect that Comments is a reserved word and a property of the
form... which
you can't edit.

 

[Hank]

Thanks for all your help.

I finally solved the problem with the following code in the OnOpen event:

Private Sub Form_Open(Cancel As Integer)
Dim strSQL As String
strSQL = "UPDATE Employeetbl SET notes = '',Present=0;"
DoCmd.RunSQL strSQL
Me.Refresh
End Sub

Works great now...Thanks again
Hank

 

[Douglas J. Steele]

If you've created a query that'll update your table, you can use

CurrentDb.QueryDefs("NameOfQuery").Execute dbFailOnError

If you don't have such a query, you can try something like

Dim strSQL As String

strSQL = "UDPATE MyTable SET MyField = False"
CurrentDb.Execute strSQL, dbFailOnError


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug,
Updating the table may be a better idea. I assume I would be updating
the Employeetbl not the Attendancetbl (where the records are appended).
If by updating, you mean clearing the check box and notes text box on the
Employeetbl after they are recorded on the Attendancetble.

When I do this manually it does clear the form - just like I want, but I
do not know how to write the code to make it happen when the "Post
Attendance" Command button on the form is clicked. Can you help me there?
Hank

The ControlSource of a text box cannot be a table. I'll assume you mean
it's a field in the form's RecordSource. (What I was trying to determine
is that it wasn't actually a function wrapped around a field)

Given what you're trying to do, can you not simply update the underlying
table first?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

The controlSource of the text box is a table (EmpTbl). There is also a
check box to check if the employee is present (or leave unchecked if
absent). A command button on the form runs an append query and post to
AttendanceTbl. All of this works great.
What I need is: When the attendancefrm opens, clear the notes text box
and the present check box.
I do not want the entire form cleared. The list of employees and
employeeID should always show.

Thanks


The error message can mean that the control you're trying to update may
not be in a usable state yet (as would likely be the case in the Open
event, where it's not fully instantiated), or it may not be updatable.

What's the ControlSource of the text box in question?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Same results with me.[Comments]=Null

I even changed the table, query, and form from "Comments" to "Notes"
and get the same error.

Just what is ment by "You can't assign a value to this object" when it
shows in the On Load event?

Douglas,

Here is the code I'm using:
Private Sub Form_Load()
Me.Comments = ""
msgbox "OK it's Done!"
End Sub

I have also tried:
Private Sub Form_Load()
Me.Comments = Null
msgbox "OK it's Done!"
End Sub

Either way, the message box shows - so yes, I think the code is
firing - but
the text box "Comments" does not clear.

The same code under the OnOpen event produces an error stating "You
can't
assign a value to this object"

Try changing it to

Me.[Comments] = Null

I suspect that Comments is a reserved word and a property of the
form... which
you can't edit.