Can't assign Value to this object (1 Viewer)

kirkm · Jul 5, 2019

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

theDBguy · Jul 5, 2019

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

The_Doc_Man · Jul 5, 2019

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.

kirkm · Jul 5, 2019

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 · Jul 5, 2019

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

JHB · Jul 5, 2019

Post your database.

kirkm · Jul 5, 2019

Good idea. thanks

JHB · Jul 5, 2019

I think the On Close event is to late, move the code to the On Unload event.

kirkm · Jul 5, 2019

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

JHB · Jul 5, 2019

You're welcome, good luck with the "bigger problem"!

kirkm · Jul 6, 2019

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.

The_Doc_Man · Jul 6, 2019

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.

kirkm · Jul 6, 2019

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.

arnelgp · Jul 6, 2019

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="theOriginalField"

kirkm · Jul 6, 2019

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

arnelgp · Jul 6, 2019

if you are calling the "Edit Form" from the main form (not on subform):

Me.BBSubForm.Form!theMemoName.ControlSource=""

kirkm · Jul 6, 2019

Managed to sort out the syntax and applied the code... sadly it didn't change anything (afaik).
I still get the locked message (on at least the one with the problem) and it's ok on the others.
Is there anything else I could try? I can work around the glitch, but would be nice to have it fixed and learn why it does what it does.

kirkm · Jul 6, 2019

thanks arnelgp. I shall do that.. what I ended up now is

Code:

Private Sub cmdEdtEntry_Click()
    With Form_BBsubform
    .Controls("AComment").ControlSource = ""
    End With

    DoCmd.OpenForm "frmMismatchComm", acNormal, , , , acDialog, "BoundData"

    With Form_BBsubform
    .Controls("AComment").ControlSource = "AComment"
    End With
    
End Sub

arnelgp · Jul 6, 2019

if nothing works, can you upload your db.

axsprog · Jul 6, 2019

default value setting in form

Can't assign Value to this object (1 Viewer)

Registered User.

I’m here to help

Immoderate Moderator

Registered User.

Registered User.

Have been here a while

Registered User.

Attachments

Have been here a while

Registered User.

Have been here a while

Registered User.

Immoderate Moderator

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

Similar threads

Users who are viewing this thread