?
Solved

Run-time error 3197 on MS Access front-end to MySql Database

Posted on 2008-10-07
2
Medium Priority
?
1,270 Views
Last Modified: 2013-11-28
I'm having an issue with an MS Access (2003) front-end to a MySql database.

Under certain circumstances, I will get the errors
      Write Conflict- The record has been changed...
      Microsoft Visual Basic- Run-time error 3197...

About the Access front-end-
On the Access form, I have several text controls, a few buttons, a checkbox, and a subform.

The SubForm displays previously entered records.

The checkbox on the Form 'flags' a record/project as being a 'priority' project. This causes the 'flagged' record to be displayed in the SubForm to have a Red background (using conditional formatting)

The buttons on the Form sort the SubForms records according to either-
   -the primary key (ascending or descding)
   -the assigned 'project number'(ascending or descending)
   -whether the record/project is 'flagged' as priority (orders 'flagged' records first).

For the most part, the front-end works without error-
-When the front-end starts, the SubForm records are sorted by default by the primary key.
-As such, I can select a record on the SubForm (which populates the Forms text fields with the records data) or scroll the Forms records, and use the Forms checkbox to flag a record as Priority. This works well- the record on the SubForm changes background to Red, or if unchecked, to White.

However, if I use one of the buttons to sort by 'project number' or by 'priority' flagged records, and then select a record and click the checkbox -  then I get the errors stated at the beginning of this post. I'll get the error when the checkbox is clicked.

I've read that these errors are sometimes related to a control being bound or unbound (I'm still fuzzy here).

If it helps, the checkbox isn't bound. In its 'On-Click' event procedure, I've coded it to set the value of a text box which IS bound to the table.

Sorry if this is confusing. It's pretty much how I live my life.
If a sample of the code used on the buttons is desired, please ask.

Thanks all, for your time and your help.
0
Comment
Question by:montarch
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
2 Comments
 

Author Comment

by:montarch
ID: 22661158
Actually, I've discovered more regarding this issue.

It seems that I get the 3197 error only on certain records, regardless of the sorting being done via the buttons on the Form. This is odd.

Regardless of the sorting done, I can check/unckeck, without error, all records whose project numbers are 08, 07, 06, and 05 (these represent the year that the project was performed).

Any project numbers 04 or below, will trigger the error, regardless of sort order.
I'm checking the MySql tables (thru Navicat), to see what might be the difference with these 2004 and earlier records, and the 2005 thry 2008 records.
0
 

Accepted Solution

by:
montarch earned 0 total points
ID: 22662248
I've found out more by comapring the records that 'work' with the ones that don't.

It seems that the records have a date field with the data '0000-00-00', a valid date (such as 07/07/2006) or (null).

The records that have 0000-00-00 always return the error. The valid dates and (null) do not.

Working on a fix for this now. I'm not sure how I can easily convert all of the 0000-00-00 date entries to (null).
0

Featured Post

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question