Before Update error with MS Access after upgrade from A2000 to A365.

I'm using MSAccess with Office365 to run an application created with MSAccess 2K.

I have a routine that runs from a form until a stop button is pressed.  This routine worked fine in Access2K, however in Office 365 I get the following message:

The Macro or function set to the BeforeUpdate or Validation Rule property for this field is preventing <dbname> from saving the data in the field.

In fact, I get this same message when I attempt to click any control on the form.  I'm pretty sure that none of the controls use the BeforeUpdate event, and I know I am not using any macros.  I do use a number of DoEvents throughout the code, which are intended to allow access to check a flag that indicates the stop action has been requested.

I need to solve this problem so I can stop the routine!  Please help.
pcalabriaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark EdwardsChief Technology OfficerCommented:
This may sound obvious, but you're not the first one to encounter this.  Not knowing your code, I could only speculate.
Have you tried googling:

"access vba The Macro or function set to the BeforeUpdate or Validation Rule property for this field is preventing <dbname> from saving the data in the field"

It came up with a bunch of other posts about it.  You might see if any of the solutions fit your problem, or if you can make the necessary changes to begin with.

Some things have changed in Access since Access 2K.
pcalabriaAuthor Commented:
Hey Mark, thanks for taking some time to help me with this.  I do appreciate it.

I did search the problem in google before posting... it seems like this would be a common problem... and the only possible clue I found is that I am using DoEvents.

With this said, I am NOT using any macros... and I am NOT using the BeforeUpdate event.

My code runs exclusively from an onclick event.  Once the code starts running, I SOMETIMES can click the stop button, but I have never been able to click on a checkbox that I also use.  Attempting to click on the checkmark, or the command button on occasion, causes the error.

Hopefully you, or someone else, will have other ideas.

Thank you.
Calabria
John TsioumprisSoftware & Systems EngineerCommented:
Maybe you have some macros in the form...check the option to convert the macros to VBA...
If this is not the case...maybe you have some obscure corruption...create a new form and copy paste everything...maybe in steps to check what might causing the error.
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Mark EdwardsChief Technology OfficerCommented:
Since it all seems to come down to your having code running and you can click a button or checkbox at any time during the execution of the code, my guess is that it has to do with where in the code you are trying to "illegally" update a recordset or recordsource.  Since we don't know what the code is doing and when, we can only give you general possible causes.

As your research has shown, you can get this error message without having any code in a "BeforeUpdate" event.  The message is part of Microsoft's "No Code" system and is built into the Access database.  If Access detects ANY data manipulation situation where it has programmed this message as a response, you'll get it.

 The "Validation Rule" part of the error message is a little broader in scope and means that you are trying to change a value that is violating a data constraint somewhere, such as trying to force a save in a new record that has a required field that is empty, etc.

The button click is not going to throw an error until the running VBA programming encounters a situation where you are "illegally" trying to change/save data or set a control value.

If clicking the checkbox, (which is probably bound?), seems to cause an immediate error, you might want to start with the checkbox click/afterupdate event.  Is the checkbox bound?  What does clicking it do?

You say you aren't using "macros".  Are you referring to the Macro database object?  You are not using any of those - just VBA code?

Can you trap the line of code where the error is occurring?  Sorry to be so general, but problems without troubleshooting details can only (usually) get general possible causes.
Gustav BrockCIOCommented:
Study the table(s) (of the query) which the form is bound to.
It sounds like a table field has a Validation rule set that prohibits the update.
Dale FyeOwner, Dev-Soln LLCCommented:
I'm inclined to agree with John and suggest you recreate the form from scratch.

You can copy objects over and any code that is behind the form can simply be copied as well, although you will probably have to go back into the form or control Events tab and make sure that each event is actually linked to the actual code with "Event Procedure".

Dale
Jim Dettman (EE MVE)President / OwnerCommented:
Note on the error message:

"The Macro or function"

"the BeforeUpdate or Validation Rule "

 It doesn't have to be a macro<g>.

 What the message is saying  is that you are in the process of saving the record and while doing that, you are trying to modify the record or carrying out an action (like moving to another record) that stops that.

These types of errors often crop up when moving to a faster/slower machine, or a different version that executes differently timing wise.    Would be best to see the code.  

 I don't believe there is anything wrong with the form.

Jim.
pcalabriaAuthor Commented:
Thanks everyone, I've think I tried all of your suggestions and have the same problem.

I never use any macros, only VBA code.  Also, I am not using any BeforeUpdate events.
I also completely recreated the form, and as Jim suspected, I still get the same errors.
I've also done a compact and repair and get the same errors.

Here's a bit about the routine.
1.When I click the start button I pop
ulate a text box that is not bound with the word RUNNING
2. I open a recordset to an MS Access backend table that contains a list of parts for which I need to find information.
3. I loop through this recordset one record at a time. So lets say the first part is called a Part Number abc123
4. For each record in the recordset (abc123 in this example), I open a webpage that contains the information I wish to capture.
5. I copy the entire webpage into a string variable. (Thanks for the code Gustav)
6. I extract information from the string variable that I wish to save and then append this information to a MS SQL backend table.  
7. I check the STATUS flag to determine if the routine should stop
8. I issue a Movenext command and repeat the process

The routine continues to loop until its natural end when it reaches EOF.
To stop the routine before EOF is reached I use a command button that is not BOUND
Clicking the command button populates the STATUS  textbox (also not bound) with the work "STOPPING"

I have the DOEVENTS command in numerous places throughout the routine so that it can capture when the command button changes the status from RUNNING to STOPPING.

So the button causing the problem is not doing anything other than populating an unbound textbox.

BTW.. there are numerous other buttons on the form, that do everything from display another form or select query.  These buttons cause the same error message.

It does not matter where the routine is in the process, steps 1-7, the same error is displayed, making the only way to close the loop be to wait until the list is processed, or rebooting the machine.

The EXACT code works flawlessly with Access 2K.

Hope this helps.
Dale FyeOwner, Dev-Soln LLCCommented:
In step 2 you open an Access recordset, is that local or to a SQL table?

in step 5, you indicate you are writing data that was captured from the web page to your MSSQL BE table.  Is this table used in the recordset you are getting in step 2?

How are you attempting to update that BE table (what is the code or the SQL)?

Dale
Jim Dettman (EE MVE)President / OwnerCommented:
Do you have any code in the activate/deactivate events?

Jim.
Jim Dettman (EE MVE)President / OwnerCommented:
<<7. I check the STATUS flag to determine if the routine should stop>>

 Do you check that flag as part of an OnTimer event, or after you have saved the record?  If the later, how?

Jim.
Mark EdwardsChief Technology OfficerCommented:
If the code runs to completion without clicking a button, but causes the error when you click a button, then the problem (obviously) is in the code that runs when you populate the unbound textbox with "STOPPING".  What are you doing to STOP (interrupt) the process?  In situations like this, it's detailed knowledge of the exact code that we need to know.  It's a detailed technical issue that's most likely the cause - a difference between A2K and A365.  Summaries of what the code is doing won't help. All we can do is guess at this point....
pcalabriaAuthor Commented:
This if very strange.  The error situation does not always occur.  For example, I can usually click the stop button without issue, but if I try to click a specific check box, which does not have any events, the message shows up and then will not go away.

@Dale  The code in step 2 opens a MS Access BE table.  It gets the list of parts we need to research...  The data that we collect is saved in an MS SQL BE table.  I can say with confidence that the SQL recordset is not open during the time that problems occur.  I update it with a RUNSQL "InsertInto" command executed much later in the code.

@Jim  No code in any events except onclick.  I do not use ontimer.  The code simply checks checks the textbox after the save is complete (RUNSQL insertinto) to determine whether the stop flag has been checkeds, and if so, it bails out of the loop and closes the form... if the flag is not checked, it gets to the next record as part of a loop while not eof routine.

While the code was running, I noticed that clicking the stop command button was not recognized, so I sprinkled doevents throughout the routine (not during the save, but during string manipulation commands).  This allows me to click the stop button while other things are going on... setting the flag as desired.

Thanks
John TsioumprisSoftware & Systems EngineerCommented:
Occasionally crashing is usually related to corruption or to a design that sometimes "provokes" it
Jim Dettman (EE MVE)President / OwnerCommented:
When you user clicks the button to start the process, add:

 Me.Dirty = False

 to the start of that.   See if that clear is up.

Jim.
Jim Dettman (EE MVE)President / OwnerCommented:
<<if the flag is not checked, it gets to the next record as part of a loop while not eof routine.>>

 This is on a recordset opened in code?   Your not moving through the forms records are you?

Jim.
pcalabriaAuthor Commented:
I'll give these suggestions a try this weekend....
Thanks

@Jim  Yes I have a recordset open and I'm running code.  The form is not bound, its strictly to report progress and to give me a place to mount buttons.
Jim Dettman (EE MVE)President / OwnerCommented:
Has to be a table validation rule then as gustav suggested.

Jim.
pcalabriaAuthor Commented:
This is still a problem but I have more info.

to recap the problem, I have a form with a start button.

when you press the start but it opens a record set that loops through a bunch of code. it would take a month to complete if you let it run.

to stop the code I use a stop button, which does nothing more than set a flag in a text box.

The code checks this flag at the end of each loop... do I continue looping or stop? is the logic used.

so far everything works perfectly.

next I place an inbound chkbox on the form. the chkbox does not have any code within it... ie none of the events are used to add code.

now I start the routine as before by clicking the start button.. and it starts running without issue.

if at any point I click the chkbox I get the error previously reported.

in fact, now that I have this error, clicking the stop button, or any other button, causes the same error.

only way to stop the code is reboot.

any ideas?
Gustav BrockCIOCommented:
it would take a month to complete if you let it run.

Something is very wrong here. This is not a way to have an application to operate.
John TsioumprisSoftware & Systems EngineerCommented:
Probably there is a form corruption but is strange that everything works till you put this unbound (?) checkbox
How about putting  a breakpoint and checking your code line by line....the goal is to get to the point that the error pops up..
Maybe is a good idea to create a copy of your form and check if this happens also...(maybe on a new mdb - import)
pcalabriaAuthor Commented:
@Gustav  Thank you... it would be nice if I could speed things up!  You have been helping me speed things up as I have a different question for the speed topic.  I worked on the other question last night also, so I'll give you an update on that today.

@John Correct.  When I place an unbound chkbox on the form everything works fine until I click on the checkbox while the code is running.  I have several other checkboxes on the form, each has the same behavior.  Interesting though, command buttons do not cause any problems.  I'll try to create a new form and copy the code into it.  Thanks for your help.
John TsioumprisSoftware & Systems EngineerCommented:
Is there a chance for event sinking ? ...some code that iterates the controls and dynamically assigns events (behavior)
pcalabriaAuthor Commented:
@John  Never heard of event sinking, but I can tell you my code is not doing anything like that.  Its just looping... its the same code as we are discussing with the performance question you and some of the other experts have been helping me with.
pcalabriaAuthor Commented:
UPDATE
I created a new form, copied the controls and code from the old form using Ctrl-C, and Ctrl-V, and the new form has the same problems.
The code does write to the form, but only to unbound text boxes.
John TsioumprisSoftware & Systems EngineerCommented:
I am thinking that something somehow is forgotten/omitted....not sure what it might be...but i have being on the same boat some times and it was something dead simple ........
Have you tried to make a new application and import the form along with the necessary data..
....do you have too many controls on your form ?
 ..have you tried to give a different name to the checkbox that causes the issue ?...maybe there is a naming conflict..
pcalabriaAuthor Commented:
@John  Copying everything into a new DB would be very difficult... the form uses at least 20 different subroutines and functions... many subroutines are nested... I guess I could create a different routine on another button...  I'm not sure what too many controls means for a form... I though the limit was something like 750... its my guess that I have 100... I don't know how to count the controls.

New info.. pressing the start button starts the process running... I can click any of the controls on the form without issue... EXCEPT the checkboxes.  Clicking on any checkbox causes the problem.  Clicking on command buttons that run queries or other code do NOT cause the problem... clicking in text boxes does NOT cause the problem.
John TsioumprisSoftware & Systems EngineerCommented:
To count controls
Dim ctl as Control
Dim counter as Integer
For each ctl in Me.Controls
Counter = Counter+1
Next
Msgbox counter

Open in new window

Are you 100% sure that there isn't any black box code....Check your code for classes....maybe is "hidden"
Also what if you minimize the checkboxes...leave 1 or 2.
pcalabriaAuthor Commented:
110 controls... thanks for the code... if anyone else tries to use you'll need to fix one type.. change Contol to Control

Any other ideas?
John TsioumprisSoftware & Systems EngineerCommented:
*fixed*...thanks
without a sample i guess we just have to wait for the next idea...
Jim Dettman (EE MVE)President / OwnerCommented:
Sorry about being missing...just back from vacation.

If you can do a sample DB, that would be ideal.

 To rule out form corruption, you may want to try a:

Application.SaveAsText

and then a

Application.LoadFromText

 and see if that changes anything.   I think however it is the process and it's nothing with Access (been wrong before though!)

Jim.
Jim Dettman (EE MVE)President / OwnerCommented:
and my only other suggestion would be to place Debug.Print statements (or use some other form of logging) for all the form events and at specific places in the code to determine what's happening.

Jim.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Office 365

From novice to tech pro — start learning today.