can't assign a value to this object (1 Viewer)

deltekkie

New member
Local time
Today, 07:37
Joined
Mar 23, 2019
Messages
7
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

I’m here to help
Staff member
Local time
Today, 05:37
Joined
Oct 29, 2018
Messages
21,357
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

New member
Local time
Today, 07:37
Joined
Mar 23, 2019
Messages
7
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

New member
Local time
Today, 07:37
Joined
Mar 23, 2019
Messages
7
Here is a pic of the way the form looks.
 

Attachments

  • form.jpg
    form.jpg
    102.6 KB · Views: 81

deltekkie

New member
Local time
Today, 07:37
Joined
Mar 23, 2019
Messages
7
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

I’m here to help
Staff member
Local time
Today, 05:37
Joined
Oct 29, 2018
Messages
21,357
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

Registered User.
Local time
Today, 12:37
Joined
Jul 24, 2013
Messages
10
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

Super Moderator
Staff member
Local time
Today, 08:37
Joined
Jan 23, 2006
Messages
15,361
Further to JohnF53's post
Or possibly on the click event of the Calc Time button.
 

Micron

AWF VIP
Local time
Today, 08:37
Joined
Oct 20, 2018
Messages
3,476
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

New member
Local time
Today, 07:37
Joined
Mar 23, 2019
Messages
7
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

I’m here to help
Staff member
Local time
Today, 05:37
Joined
Oct 29, 2018
Messages
21,357
Hi. Congratulations! Glad to hear you got it going. Good luck with your project.
 

Users who are viewing this thread

Top Bottom