Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-10-2018, 08:59 AM   #1
Dopeyjh
Newly Registered User
 
Join Date: Aug 2007
Posts: 21
Thanks: 1
Thanked 0 Times in 0 Posts
Dopeyjh is on a distinguished road
Intermitttent - "You can't assign a value..", with a bound checkbox

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 is offline   Reply With Quote
Old 12-10-2018, 09:01 AM   #2
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,275
Thanks: 105
Thanked 2,491 Times in 2,287 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Intermitttent - "You can't assign a value..", with a bound checkbox

Try removing the .Value
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
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.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
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.
isladogs is offline   Reply With Quote
Old 12-10-2018, 09:12 AM   #3
Dopeyjh
Newly Registered User
 
Join Date: Aug 2007
Posts: 21
Thanks: 1
Thanked 0 Times in 0 Posts
Dopeyjh is on a distinguished road
Re: Intermitttent - "You can't assign a value..", with a bound checkbox

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......

Dopeyjh is offline   Reply With Quote
Old 12-10-2018, 09:52 AM   #4
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,275
Thanks: 105
Thanked 2,491 Times in 2,287 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Intermitttent - "You can't assign a value..", with a bound checkbox

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?
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
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.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
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.
isladogs is offline   Reply With Quote
Old 12-10-2018, 12:57 PM   #5
Dopeyjh
Newly Registered User
 
Join Date: Aug 2007
Posts: 21
Thanks: 1
Thanked 0 Times in 0 Posts
Dopeyjh is on a distinguished road
Re: Intermitttent - "You can't assign a value..", with a bound checkbox

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
Dopeyjh is offline   Reply With Quote
Old 12-10-2018, 05:26 PM   #6
sneuberg
AWF VIP
 
Join Date: Oct 2014
Location: Tucson, Arizona
Posts: 3,507
Thanks: 362
Thanked 975 Times in 943 Posts
sneuberg will become famous soon enough sneuberg will become famous soon enough
Re: Intermitttent - "You can't assign a value..", with a bound checkbox

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 ....."
__________________
And now nothing will be restrained from them, which they have imagined to do. Genesis 11:6

Steve
sneuberg is offline   Reply With Quote
Old 12-11-2018, 05:38 AM   #7
Dopeyjh
Newly Registered User
 
Join Date: Aug 2007
Posts: 21
Thanks: 1
Thanked 0 Times in 0 Posts
Dopeyjh is on a distinguished road
Re: Intermitttent - "You can't assign a value..", with a bound checkbox

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.

Dopeyjh is offline   Reply With Quote
Old 12-11-2018, 05:53 AM   #8
sneuberg
AWF VIP
 
Join Date: Oct 2014
Location: Tucson, Arizona
Posts: 3,507
Thanks: 362
Thanked 975 Times in 943 Posts
sneuberg will become famous soon enough sneuberg will become famous soon enough
Re: Intermitttent - "You can't assign a value..", with a bound checkbox

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.
__________________
And now nothing will be restrained from them, which they have imagined to do. Genesis 11:6

Steve
sneuberg is offline   Reply With Quote
Old 12-11-2018, 06:19 AM   #9
Dopeyjh
Newly Registered User
 
Join Date: Aug 2007
Posts: 21
Thanks: 1
Thanked 0 Times in 0 Posts
Dopeyjh is on a distinguished road
Re: Intermitttent - "You can't assign a value..", with a bound checkbox

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.
Dopeyjh is offline   Reply With Quote
Old 12-11-2018, 06:34 AM   #10
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 13,802
Thanks: 76
Thanked 1,537 Times in 1,425 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: Intermitttent - "You can't assign a value..", with a bound checkbox

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"

    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
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.
__________________
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 12-11-2018, 06:56 AM   #11
Dopeyjh
Newly Registered User
 
Join Date: Aug 2007
Posts: 21
Thanks: 1
Thanked 0 Times in 0 Posts
Dopeyjh is on a distinguished road
Re: Intermitttent - "You can't assign a value..", with a bound checkbox

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 by Dopeyjh; 12-11-2018 at 07:06 AM. Reason: used the wrong terminology
Dopeyjh is offline   Reply With Quote
Old 12-11-2018, 12:06 PM   #12
Dopeyjh
Newly Registered User
 
Join Date: Aug 2007
Posts: 21
Thanks: 1
Thanked 0 Times in 0 Posts
Dopeyjh is on a distinguished road
Re: Intermitttent - "You can't assign a value..", with a bound checkbox

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.
Dopeyjh is offline   Reply With Quote
Old 12-11-2018, 12:58 PM   #13
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,807
Thanks: 0
Thanked 434 Times in 430 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Intermitttent - "You can't assign a value..", with a bound checkbox

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.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 12-11-2018, 01:14 PM   #14
Dopeyjh
Newly Registered User
 
Join Date: Aug 2007
Posts: 21
Thanks: 1
Thanked 0 Times in 0 Posts
Dopeyjh is on a distinguished road
Re: Intermitttent - "You can't assign a value..", with a bound checkbox

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??
Dopeyjh is offline   Reply With Quote
Old 12-11-2018, 02:24 PM   #15
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 13,802
Thanks: 76
Thanked 1,537 Times in 1,425 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: Intermitttent - "You can't assign a value..", with a bound checkbox

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:

Quote:
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.

__________________
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
The Following User Says Thank You to The_Doc_Man For This Useful Post:
Dopeyjh (12-14-2018)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
If "TableA.FieldA" exists in "TableB.FieldB" then return "1" else "2" shdale Queries 8 10-29-2014 05:20 AM
How to update "changed date" field on bound form? bulrush Forms 6 02-10-2010 07:33 AM
"On Dirty" in bound and un-bound forms 1jet Forms 1 09-27-2008 08:30 PM
Assign custom "group" number ghudson General 6 10-23-2003 06:18 AM




All times are GMT -8. The time now is 05:55 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