Strange Error

[spectrolab]

Hi all,

I have a form that adds records into a table based on the fields in the form. It then calls for a few append queries to add these records to other tables based on check boxes on the form, i.e, if this box is checked, run this append query etc. It all works fines and has been ok for many years. Recently I trimmed down the original table by archiving old records to get the table smaller and speed up the append queries. This access front end links to a mySQL backend and the front end is installed on a few desktops around the office.
Just recently, if I add records to the original table, when I close the form used to add the records it changes one of the fields in the first record of the table to whatever is entered in the form. Only when I actually close the form. There is no 'On Close' event in the form.

Any ideas?

 

[theDBguy]

Is the form bound or unbound?

 

[spectrolab]

Hi DB Guy,

The form is unbound. Shows blank fields on opening. There is a button to add the records to the table.

 

[The_Doc_Man]

If there is no "ON CLOSE" event then the next places to look would be "ON UNLOAD" or perhaps an "ON CLICK" of whatever you use to close the form. Since the form is unbound, there would be no "ON BEFORE_UPDATE" or "ON AFTER_UPDATE" and no "ON CURRENT" so none of those events should do anything. Those actions don't come from nowhere and the form is unbound, so there has to be code dangling somewhere.

However, there is an oddball chance... do you have a Data Macro associated with the main table?

 

[JimChristmas]

Hi all,

I have a form that adds records into a table based on the fields in the form. It then calls for a few append queries to add these records to other tables based on check boxes on the form, i.e, if this box is checked, run this append query etc. It all works fines and has been ok for many years. Recently I trimmed down the original table by archiving old records to get the table smaller and speed up the append queries. This access front end links to a mySQL backend and the front end is installed on a few desktops around the office.
Just recently, if I add records to the original table, when I close the form used to add the records it changes one of the fields in the first record of the table to whatever is entered in the form. Only when I actually close the form. There is no 'On Close' event in the form.

Any ideas?

If the code in the form isn't terribly long and doesn't reveal any "corporate secrets," would you be willing to post it here?
Also, I'm curious about the key structure of the table that's getting modified. What data type is the primary key field?

 

[Isaac]

Are you positive that this form is not bound?

 

[GPGeorge]

If there is no "ON CLOSE" event then the next places to look would be "ON UNLOAD" or perhaps an "ON CLICK" of whatever you use to close the form. Since the form is unbound, there would be no "ON BEFORE_UPDATE" or "ON AFTER_UPDATE" and no "ON CURRENT" so none of those events should do anything. Those actions don't come from nowhere and the form is unbound, so there has to be code dangling somewhere.

However, there is an oddball chance... do you have a Data Macro associated with the main table?

Since the backend is in MySQL, you'd be looking for triggers on that table. I assume MySQL supports triggers?

 

[Isaac]

Hi DB Guy,

The form is unbound. Shows blank fields on opening. There is a button to add the records to the table.

What's the code behind the button?
What is the RecordSource property of the form?

 

[The_Doc_Man]

Since the backend is in MySQL, you'd be looking for triggers on that table. I assume MySQL supports triggers?

I looked it up. According to the MySQL 8.4 manual, section 27.3, MySQL supports SQL-based changes as trigger events on tables, but it does not support triggers based on API-based changes. Since I'm not up on specifics of MySQL/Access interactions, I would guess that triggers would occur only on PassThru queries, and in the specific case mentioned (a form Close of an unbound form), I'm hard-pressed to imagine that you would get an SQL-based trigger that way.

 

[Isaac]

I don't see how a trigger would fire specifically when the form closed - but I do see a bound form doing that, and I think possibly the OP missed the fact that the form is indeed bound , probably missed because the form does a lot of unbound things in addition. Just a hunch.

 

[Pat Hartman]

I would guess that triggers would occur only on PassThru queries,

Triggers are based on actions to tables. It doesn't matter how the action was initiated.

 

[GPGeorge]

Triggers are based on actions to tables. It doesn't matter how the action was initiated.

Exactly, and that's the whole point of both triggers (SQL Server) and Data Macros (Access). They fire when data in a table changes regardless of anything happening in the interface. They are triggered directly by changes to tables in the database, not in the interface.

 

[Isaac]

Exactly, and that's the whole point of both triggers (SQL Server) and Data Macros (Access). They fire when data in a table changes regardless of anything happening in the interface. They are triggered directly by changes to tables in the database, not in the interface.

like one of the most entertaining ways you can make a sql server View non-updateable in Access by using an instead of trigger!!

 

[The_Doc_Man]

Triggers are based on actions to tables. It doesn't matter how the action was initiated.

Not according to the manual that I quoted. SQL-based actions will activate a trigger. But apparently MySQL has another way to make changes that will NOT activate a trigger. That's why I put a disclaimer in the comment. Doesn't make sense to me either but that's what they said.

 

[cheekybuddha]

like one of the most entertaining ways you can make a sql server View non-updateable in Access by using an instead of trigger!!

It feels like the important word is missing from this sentence.
???

 

[Isaac]

I'm sorry, David, that was just a bit of personal opinion.
Sorry if it offended.

 

[cheekybuddha]

It didn't offend.

It just doesn't make sense (to me )

By using what instead of a trigger will make the view un-updateable?

 

[Isaac]

by using an 'instead-of' type of trigger.

Using INSTEAD OF triggers in SQL Server for DML operations

I have created some views to provide limited data access for an application. Several of these views are composed of more than one table and there are problems while performing insert, delete, or update operations on multi-base table views. I am required t

www.mssqltips.com

 

[cheekybuddha]

Ah makes sense now! Not clear from how it was written!

 

[Isaac]

Sorry, bad punctuation on my part.