can't assign a value to this object

[deltekkie]

Hello,

I have a table that I am convert over for Excel which my group uses to track their work progress. I have created three particular fields in this table and linked a form to this table where they actually input the information. They input a start time and a stop time. I have three unbound text boxes as an experiment to test the VB math functions.

Table has
txtStartTime set to date/time
txtStopTime set to date/time
txtTimeSpent set to Short Text

Form has
table txtStartTime set to plain text bound to table StartTime
user will input this value

table txtStopTime set to plain text bound to table StopTime
user will input this value

table txtTimeSpent set to plain text bound to table TimeSpent
will receive value from calc2

txtCalcBox set to plain text is unbound
DayCalc set to plain text is unbound
calc2 set to plain text is unbound

This code will run
Me.txtCalcBox.Value = Me.txtStopTime.Value - Me.txtStartTime.Value
Me.DayCalc.Value = (Me.txtCalcBox.Value) Mod 24
Me.calc2 = Me.DayCalc & " Days " & Me.txtCalcBox
Me.txtTimeSpent = Me.calc2

every time
the form is opened
OR
tbStartTime is updated
OR
tbStopTime is updated

The first three work just fine. The last one throws the error.

I have tried many iterations of the last line and none work
examples:
Me.txtTimeSpent = Me.calc2
Me.txtTimeSpent.Value = Me.calc2.Value
Me!txtTimeSpent = Me!calc2
Me![txtTimeSpent] = Me![calc2]
Me![txtTimeSpent].Value = Me![calc2].Value
Me.[txtTimeSpent].Value = Me.[calc2].Value

Can anyone tell me what I am doing wrong?

Thank you!

 

[theDBguy]

Hi. Welcome to the forum. I actually don't understand what you're doing but if you're doing Me.txtTimeSpent = Me.calc2, it sounds to me like you want to display the same exact information on two different places on your form. If so, have you tried? Me.txtTimeSpent = Me.DayCalc & " Days " & Me.txtCalcBox

 

[deltekkie]

What you suggested is what I originally had in the code. I got the same error. That is why I started using the unbound text boxes to test the code. For some reason, there is something about the txtTimeSpent bound textbox that will not allow me to update that value with a formula.

 

[deltekkie]

Here is a pic of the way the form looks.

 

[deltekkie]

Here is the actual VB code
Some of it is commented out because I was troubleshooting

Option Compare Database

Private Sub Form_Open(Cancel As Integer)
'Forms!frmLogsTab!txtTimeSpent.Format = "mmm yyyy"
Me.txtCalcBox.Value = Me.txtStopTime.Value - Me.txtStartTime.Value
Me.DayCalc.Value = (Me.txtCalcBox.Value) Mod 24
Me.calc2 = Me.DayCalc & " Days " & Me.txtCalcBox
'Me.[txtTimeSpent].Value = Me.[calc2].Value
End Sub

Private Sub txtStartTime_AfterUpdate()
Me.txtCalcBox.Value = Me.txtStopTime.Value - Me.txtStartTime.Value
Me.DayCalc.Value = (Me.txtCalcBox.Value) Mod 24
' Me.calc2.Text = Me.DayCalc.Text & " Days " & Me.txtCalcBox.Text
'Me.txtTimeSpent.Text = Me.calc2.Text
End Sub

Private Sub txtStopTime_AfterUpdate()
Me.txtCalcBox.Value = Me.txtStopTime.Value - Me.txtStartTime.Value
Me.DayCalc.Value = (Me.txtCalcBox.Value) Mod 24
'Me.calc2.Text = Me.DayCalc.Value & " Days " & Me.txtCalcBox.Value
'Me.txtTimeSpent.Text = Me.calc2.Text
End Sub

 

[theDBguy]

Hi. You're saying you're using bound and unbound controls. Can you make manual changes to the bound textbox? Please try typing a valid entry into txtTimeSpent (assuming txtTimeSpent is the bound control) and let us know what happens.

 

[JohnF53]

I think that having your code in the OnOpen event is causing the problem.
The Access Help for the Form.Open event:
"The Open event occurs when a form is opened, but before the first record is displayed."
... as I understand it that means that at the point where the OnOpen event is triggered there is no record yet available to be updated, hence the error.
If you move your code to the OnCurrent event that might do the trick.

 

[jdraw]

Further to JohnF53's post
Or possibly on the click event of the Calc Time button.

 

[Deleted member 147267]

I've only ever come across this error if I attempted to set a value in a textbox that has an expression for its controlsource property; i.e. you can't set a value in a calculated control (one whose controlsource begins with = ). Since the picture doesn't show the word "Days" in TimeSpent field yet it appears there's an attempt to include that word, I'd say there's something that's not evident from the description of what's going on. If the control isn't a calculated one, then could it be that there's an attempt to enter text in what looks like a date/time field?

 

[deltekkie]

Thank you all for your suggestions. It is working now as intended. There must have been something messed up in the database structure. It should have worked as some of you described but it did not. Strange! I am now updating the elapsed time in the table from the form and without the extra text box. Thanks again!

 

[theDBguy]

Hi. Congratulations! Glad to hear you got it going. Good luck with your project.