Intermitttent - "You can't assign a value..", with a bound checkbox (1 Viewer)

Dopeyjh

Registered User.
Local time
Today, 10:14
Joined
Aug 24, 2007
Messages
24
Hi,

I am intermittently getting a runtime error, "you cant assign a value to this object".

I'll start with the section of code;

Code:
Private Sub Form_Current()

On Error GoTo Error_Form_Current_Click

Dim initialNotify As Boolean
 
Forms![frmAccountDisplay].Refresh

If initialNotify = False Then
 
    If Me.chkFileInUse = True And Me.txtUserCheckout <> getUserName_check Then

   
    MsgBox "Account is currently being accessed by" & " " & Me.txtUserCheckout & vbCrLf & vbCrLf & "You cannot make changes to the file!.", vbOKOnly, "Account Already in Use"

    Forms("frmAccountDisplay").AllowEdits = False
    Me.bttnLoadMerlin.Enabled = False
    Me.bttnFindMatch.Enabled = False
    Me.bttnCloseFlie.Enabled = False
    Me.bttnSaveComments.Enabled = False
    Me.bttnAddMgrCmnt.Enabled = False
    Me.bttnSaveFile.Enabled = False

    Else

    Me.chkFileInUse.Value = True
    Me.txtUserCheckout = getUserName_check
    Forms![frmAccountDisplay].Refresh
    initialNotify = True
 
    End If

End If

Exit_Error_Form_Current_Click:

Exit Sub

Error_Form_Current_Click:

    MsgBox Err.Description
    Resume Exit_Error_Form_Current_Click

End Sub

The section that error comes up on is
Me.chkFileInUse.Value = True

This is a bound control and is enabled. The checkbox is in the details section of the form
I've tried running this on the form's load,activate,open and now current events and still getting the same intermittent issue.

I'm not sure what additional information you need, please let me know.
Any ideas?

Thanks!
 

Dopeyjh

Registered User.
Local time
Today, 10:14
Joined
Aug 24, 2007
Messages
24
For the moment it appears to be working, haven't been able to make the error occur. Loaded about 30 accounts and no error.

However i was also able to do that this morning, and then it crashed.

Should I have the section of code loading on a different event?
Should I put in a wait command?, there were a few instances when I was trying open the form and was getting the error, if I closed the form waited a few seconds that it would work....but then other times it did not.

The fact that its so intermittent and I can't trace it back to any one specific thing is very frustrating......
 

isladogs

MVP / VIP
Local time
Today, 14:14
Joined
Jan 14, 2017
Messages
18,186
If you put it in Form_Current it will run every time anything changes on the form.
Unless you want that, I would put it in Form_Load but without the .Value

Does the Boolean field it is bound to have a default value?
 

Dopeyjh

Registered User.
Local time
Today, 10:14
Joined
Aug 24, 2007
Messages
24
I've tried with and without the .value.
I removed it and am still getting the error intermittently.

the checkbox default is set to false
 

sneuberg

AWF VIP
Local time
Today, 07:14
Joined
Oct 17, 2014
Messages
3,506
Could this have anything to do with which record is the current record? If you scroll through the records does this occur on certain records?

Also I think I'd try to isolate whether this is a form problem or a problem with the form's record set. One way to do this would be to make chkFileInUse unbounded and then update the form's recordset query or table directly with SQL, something like,

CurrentDb.Execute "UPDATE TheTableOrQuery SET chkFileInUse = True WHERE ....."
 

Dopeyjh

Registered User.
Local time
Today, 10:14
Joined
Aug 24, 2007
Messages
24
I have the record scroll turned off, the form loads based off what the user selects from their worklist. (another form)

This form (Account Display) is based off a query, using a Select Distinct... query in the record source. (not sure if that helps you or not)

Its not all records, only some and even with that sometimes they will load and then the same record will sometimes not.

I'll try your suggestion and let you know how it goes.
 

sneuberg

AWF VIP
Local time
Today, 07:14
Joined
Oct 17, 2014
Messages
3,506
I'd bet the DISTINCT part of the record source query is your problem. Access typically doesn't like to update records that are part of an aggregate although I don't know why it would complain about just this one field.
 

Dopeyjh

Registered User.
Local time
Today, 10:14
Joined
Aug 24, 2007
Messages
24
Right now, without making your original change (unbound,update sql ..) I have open and closed the form 30+ times all different records no error.
In 15 minutes from now, I can try to access the form and say 20 out of 30 wont load.

I dont believe that its this particular control that its getting stuck on, I tried updating another control on the form's load event and would get the same error . I assume its only because this is the only control that i am trying to update.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:14
Joined
Feb 28, 2001
Messages
26,999
Look at highlighted code:

Code:
If initialNotify = False Then
 
    If Me.chkFileInUse = True And Me.txtUserCheckout <> getUserName_check Then

   
    MsgBox "Account is currently being accessed by" & " " & Me.txtUserCheckout & vbCrLf & vbCrLf & "You cannot make changes to the file!.", vbOKOnly, "Account Already in Use"

   [COLOR="Red"] Forms("frmAccountDisplay").AllowEdits = False[/COLOR]
    Me.bttnLoadMerlin.Enabled = False
    Me.bttnFindMatch.Enabled = False
    Me.bttnCloseFlie.Enabled = False
    Me.bttnSaveComments.Enabled = False
    Me.bttnAddMgrCmnt.Enabled = False
    Me.bttnSaveFile.Enabled = False

    Else

    Me.chkFileInUse.Value = True
    Me.txtUserCheckout = getUserName_check
    Forms![frmAccountDisplay].Refresh
    initialNotify = True
 
    End If

End If

Obviously, you have shown us a code snippet. My question is: Do you ever set the .AllowEdits property to TRUE elsewhere? And does the error occur ONLY after that "collision" occurs and is detected?

As long as that form is open, setting the the .AllowEdits to FALSE stays in effect. You have to close and re-open the form to reset the flag automatically. OR you have to have code somewhere else that enables everything.

If you set .AllowEdits to FALSE then you might get the reported error message. The other time I might expect that is if the .RecordSource query is, for some reason, not updatable. An aggregate or an ambiguous JOIN might have that effect.
 

Dopeyjh

Registered User.
Local time
Today, 10:14
Joined
Aug 24, 2007
Messages
24
The form is defaulted for allowedits = true
i only set it to false if someone else is accessing the record.
Should i be declaring it in code first in the form load event?

I'm using an inner join with 4 tables in query for loading the form;?
 
Last edited:

Dopeyjh

Registered User.
Local time
Today, 10:14
Joined
Aug 24, 2007
Messages
24
I've added in the statement for allowedits = true at the begining of the code for the form load event. - no change

also updated the executing query to remove "distinct row" - same error.

error occurring now 85% of the time.
:banghead::banghead::banghead::banghead:
 

June7

AWF VIP
Local time
Today, 06:14
Joined
Mar 9, 2014
Messages
5,423
Work flow allows multiple users to edit same record at same time?

If you want to provide db for analysis, follow instructions at bottom of my post.
 

Dopeyjh

Registered User.
Local time
Today, 10:14
Joined
Aug 24, 2007
Messages
24
First thank you for being patient and trying to help me out..My apologies for all of recent replies :)
Also apologies for this only dawning on me now;

The chkFileInUse checkbox is not the only control/field that is being impacted.
Once the error occurs, the form loads displays all the correct information but the record is not update-able and when no error occurs, the record is update-able.

To add and hopefully help solve this;

the form loads from a query , table name and field names not actual

Select * From table1 INNER JOIN table2 ON table1.id=table2.idNum INNER JOIN table 3 ON table1.id = table3.idNumber INNER JOIN table4 on table1.id=table4.mainid

As I was just looking at the property details for the form I noticed that the Record Locks is set to Edited Records.? --- Could this be issue??
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:14
Joined
Feb 28, 2001
Messages
26,999
Locking can always be an issue. Most of the time, we recommend setting forms to use "Optimistic Locking" if they use any locking at all.

However, this sentence tells me what I need to know:

Once the error occurs, the form loads displays all the correct information but the record is not update-able and when no error occurs, the record is update-able.

Somewhere, you have "diddled" with a setting to disallow updates. You need to reverse the settings once you have issued the error message via the message box. Or treat this differently by not blocking the .AllowEdits, but instead do an UNDO on the form (to block updates) and force it to close. If the form isn't dirty when you close it, then you do nothing behind the scenes when you close that form.

Your problem has to do with error handling. You need to assure that you DON'T lock down the form when the error occurs. You just prevent it from doing anything with that account. So maybe the situation is that you want to put enabling code in the OnCurrent event but put disabling code in the event that discovered the conflict.
 

Dopeyjh

Registered User.
Local time
Today, 10:14
Joined
Aug 24, 2007
Messages
24
Sorry for the late reply.
Thanks so much everyone for your help.
 

Users who are viewing this thread

Top Bottom