Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-04-2019, 06:51 PM   #1
kirkm
Newly Registered User
 
Join Date: Oct 2008
Posts: 700
Thanks: 23
Thanked 5 Times in 5 Posts
kirkm is on a distinguished road
Can't assign Value to this object

I was having trouble with a locked ldb file and in trying to solve it made a new Form to update a memo field in my table. I also need to convert carriage returns.



Code:
Private Sub Form_Close()
Me.Text0 = Replace(Me.Text2, vbCrLf, vbLf)
End Sub

Private Sub Form_Load()
Me.Caption = Nz(Me.OpenArgs)
If Nz(Me.OpenArgs) > "" Then
Me.RecordSource = "Select * from MyTable Where Test = '" & Me.OpenArgs & "'"
    Me.Text2 = Replace(Me.Text0, vbLf, vbCrLf)
End If
End Sub

When I run this I first get
Run-time error '-2147352567 (80020009)':
You can't assign a value to this object.


And if I change Me.Text0 = Replace(Me.Text2, vbCrLf, vbLf) to
Me.Text0.text = Replace(Me.Text2, vbCrLf, vbLf)
I get
Run time error 2176
The Setting For this property is too Long.



What might be the problem ?


Thanks

kirkm is offline   Reply With Quote
Old 07-04-2019, 07:09 PM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,791
Thanks: 57
Thanked 1,268 Times in 1,249 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Can't assign Value to this object

Hi. You've shown us two sets of code. I am not sure which one is giving you issues.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 07-04-2019, 07:37 PM   #3
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,557
Thanks: 92
Thanked 1,682 Times in 1,560 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Can't assign Value to this object

Using the .Text value of a textbox doesn't work unless the textbox currently has focus. But unless there is an implied numeric field bound to the textbox, you don't need either .Text or .Value to update the contents.

I get the general idea when searching the 0x80020009 error that it is permissions-related. To see a system error code is already bizarre anyway. Something about the environment in which you run is messed up. But it is rare to see an error with an 0x800xxxxx value because that means that Access attempted something and the system told it "NO." (Normally, Access would tell you this itself.)

Check the permissions on the FE and/or BE files to verify that you have MODIFY permissions on both files in the context from which you are running. Also you need to have MODIFY on the folder containing the files.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 07-04-2019, 07:54 PM   #4
kirkm
Newly Registered User
 
Join Date: Oct 2008
Posts: 700
Thanks: 23
Thanked 5 Times in 5 Posts
kirkm is on a distinguished road
Re: Can't assign Value to this object

Something is really screwed up here. But I've making new databases just with the problem bits and troubles remain. I've also tested on a 2nd computer. I do have all the usual permissions afaiak (and can do anything else).
Those "2 sets of code" is all the code and results are underneath. There's just one table and one Form in the db. The form has 2 text boxes. Text0 is bound to a Field Test in MyTable. Does it work for you?

I next tried to update the table directly and that also failed.
3188 Could not update; currently locked by another session on this machine.
Is it because that field is bound to a control? It is the only thing left.
kirkm is offline   Reply With Quote
Old 07-04-2019, 08:58 PM   #5
kirkm
Newly Registered User
 
Join Date: Oct 2008
Posts: 700
Thanks: 23
Thanked 5 Times in 5 Posts
kirkm is on a distinguished road
Re: Can't assign Value to this object

It would seem the "other session" that has locked thing up, is either the Form or a control on the Form that is bound to the table. I didn't expect that as I thought binding a control to a table was to let that control use that data. But If it can never update it... well it doesn't make sense.

If I remove all bindings, then the code works fine. Tested in both Access 2016 and 2019 on two different computers.

Perhaps someone could confirm - the code in msg1 will never work - and isn't something else I've overlooked? Thanks
kirkm is offline   Reply With Quote
Old 07-04-2019, 09:03 PM   #6
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,720
Thanks: 3
Thanked 2,082 Times in 2,037 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Can't assign Value to this object

Post your database.
__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Old 07-04-2019, 09:30 PM   #7
kirkm
Newly Registered User
 
Join Date: Oct 2008
Posts: 700
Thanks: 23
Thanked 5 Times in 5 Posts
kirkm is on a distinguished road
Re: Can't assign Value to this object

Good idea. thanks
Attached Files
File Type: zip Databaset3.zip (79.2 KB, 10 views)

kirkm is offline   Reply With Quote
Old 07-05-2019, 12:07 AM   #8
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,720
Thanks: 3
Thanked 2,082 Times in 2,037 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Can't assign Value to this object

I think the On Close event is to late, move the code to the On Unload event.
__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
The Following User Says Thank You to JHB For This Useful Post:
kirkm (07-05-2019)
Old 07-05-2019, 12:25 AM   #9
kirkm
Newly Registered User
 
Join Date: Oct 2008
Posts: 700
Thanks: 23
Thanked 5 Times in 5 Posts
kirkm is on a distinguished road
Re: Can't assign Value to this object

Thank you, that has solved that. Interesting... and nothing to do with binding. Whew, that;s good. Now I might get somewhere with the bigger problem... Cheers
kirkm is offline   Reply With Quote
Old 07-05-2019, 12:51 AM   #10
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,720
Thanks: 3
Thanked 2,082 Times in 2,037 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Can't assign Value to this object

You're welcome, good luck with the "bigger problem"!
__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Old 07-05-2019, 04:18 PM   #11
kirkm
Newly Registered User
 
Join Date: Oct 2008
Posts: 700
Thanks: 23
Thanked 5 Times in 5 Posts
kirkm is on a distinguished road
Re: Can't assign Value to this object

The 'bigger problem" is a bit odd, in that everything works fine unless the field is large. How large not sure yet, it's a memo field. I can now edit it with that uploaded file, except if my main form is open - then "Could not update; currently locked by another session on this machine.".

I suspect when executing .Dirty = False (now remmed out) this somehow caused the locked ldb file as it hasn't happened since.How can I see what "session" is the other ? Would it be something to do with the table/query being used twice, that is only one Form can be bound at the one time ? I'm going to try un-binding one Form when a second one is open and see what happens. I'm clutching at straws so any expert advice is apprecaited.
kirkm is offline   Reply With Quote
Old 07-05-2019, 07:21 PM   #12
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,557
Thanks: 92
Thanked 1,682 Times in 1,560 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Can't assign Value to this object

It is possible that you are doing self-interference. In fact, MOST of the time we see that particular error, it is because you have the table open twice for update. Things that have worked in the past include verifying that the form is set for optimistic locking or no locking. Also instead of opening the table, make a query that matches every field in the table and is set for optimistic or no locking. Then open the query. Yeah, sounds crazy - but that implied layering sometimes insulates the two uses of the same recordset from each other.

If there is a way to make one of the uses READ-ONLY, that will also help because that form, you CAN set for NO LOCKS and feel safe.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 07-05-2019, 09:34 PM   #13
kirkm
Newly Registered User
 
Join Date: Oct 2008
Posts: 700
Thanks: 23
Thanked 5 Times in 5 Posts
kirkm is on a distinguished road
Re: Can't assign Value to this object

I've changed the Record Source to a query instead of the table and confirmed Lock is Off. (This is the default, yes?)
But having something open twice may be my norm. I select a record in a SubForm then open another Form to edit that record. I do this all the time with no problem, except where the text being edited is very large, say 2-3000 chars. Because this works most of the time I'm finding it hard to debug i.e why it would let me edit one record, but not another. Apart from the amount of text, everything is the same.
kirkm is offline   Reply With Quote
Old 07-05-2019, 10:04 PM   #14
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,568
Thanks: 68
Thanked 2,744 Times in 2,629 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Can't assign Value to this object

if you are opening another form to edit the memo while it is open in another form, you may try to disconnect it first to the first form, open the edit form in acDialog window mode. after edit is done, reinstate the control source of the memo, ie:

'disconnect it
me.theMemoTextboxName.ControlSource = ""
'open the edit form
docmd.openform FormName:="editFormName, WhereCondition:="ID=" & Me.ID, WindowMode:=acDialog
'reinstate the ControlSource
me.theMemoTextboxName.ControlSource="theOriginalFi eld"
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 07-05-2019, 10:31 PM   #15
kirkm
Newly Registered User
 
Join Date: Oct 2008
Posts: 700
Thanks: 23
Thanked 5 Times in 5 Posts
kirkm is on a distinguished road
Re: Can't assign Value to this object

Right... just struggling a bit with the syntax, The form with the Edit button is "frmMain"
but its subform is called "BBSubForm". When I look for Me.BBSunForm.TheMemoName theres's no intellisense showing the name.
I've also been opening the Edit Form as "acNormal" too.
Thanks

kirkm is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
can't assign a value to this object deltekkie Forms 10 04-02-2019 07:48 AM
You Can't Assign A Value To This Object forms_are_nightmares Forms 22 02-14-2013 09:19 PM
can't assign value to an object jeo Forms 1 01-13-2005 01:26 PM
You cant assign a value to this object?? desibasha General 0 04-08-2004 02:02 PM
You can't assign a value to this object kholm Forms 1 10-30-2002 12:31 PM




All times are GMT -8. The time now is 09:13 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World