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.

 

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

 

[spectrolab]

Apologies to all on the delay in getting back to this

The there are 2 buttons on the form, one adds records to one table based on the following code:

Private Sub LogSam_Click()

Select Case Me.CustomerID
    Case 9
        Select Case Left(Me.SamPre, 1)
        Case "D"
            Call LogCRLExpDiamond
        Case Else
            Call LogCRLExp
        End Select
    Case 74
        Call LogAurora
    Case 14
        Call LogGiralia
    Case 21
        Call LogBellzone
    Case 78
        Call LogKarara
    Case 65
        Select Case Right(Me.SubNum, 4)
        Case "FEED"
            Call LogCRLDTRFeed
        Case "CONC"
            Call LogCRLDTRCONC
        End Select
Case Else
        Call LogRoutine

End Select
 
End Sub

Most of the time it calls the below sub routine "LogRoutine", but it doesn't matter which is called:

Private Sub LogRoutine()

Const MyTable As String = "tblSampleSubmission"
Const MyField As String = "SampleName"
Dim DB As DAO.Database
Dim rs As DAO.Recordset
Dim intCounter As Double
Dim LastDub As Double
Dim minID, maxID, stdID As Long 'Used for randomly selected ID from table of standards
Dim stdName As String
Set DB = CurrentDb
Set rs = DB.OpenRecordset(MyTable)

'Find maximum and minimum ID number in tblStandards
maxID = DMax("StandardID", "tblStandards")
minID = DMin("StandardID", "tblStandards")





For intCounter = Me.TxtStartValue To Me.txtEndValue Step Me.cboIncrement
        rs.AddNew
        rs.Fields(MyField) = Me.SamPre & intCounter & Me.SamSuf
        rs.Fields("SubmissionNumber") = Me.SubNum
        rs.Fields("CustomerID") = Me.CustomerID
        rs.Fields("SamplePrep") = True
        rs.Fields("Fusion") = True
        rs.Fields("XRF") = True
        rs.Fields("LOI") = True
        rs.Fields("3StageLOI") = Me.chk3StageLOI
        rs.Fields("Magnasat") = Me.chkMagnasat
        rs.Fields("DavisTube") = Me.chkDavisTube
        rs.Fields("RecWt") = Me.chkRecWt
        rs.Fields("Sizing") = Me.Sizing
        rs.Fields("Moisture") = Me.Moisture
        rs.Fields("EnteredBy") = Me.cboEnteredBy
        rs.Fields("Priority") = Me.cboPriority
        rs.Update
       addString = ""
       samplecount = samplecount + 1
       If samplecount = 25 Then
        'Add duplicate record
        addString = " DUP"
        rs.AddNew
        rs.Fields(MyField) = Me.SamPre & intCounter & Me.SamSuf & " DUP"
        rs.Fields("SubmissionNumber") = Me.SubNum
        rs.Fields("CustomerID") = Me.CustomerID
        rs.Fields("SamplePrep") = True
        rs.Fields("Fusion") = True
        rs.Fields("XRF") = True
        rs.Fields("LOI") = True
        rs.Fields("3StageLOI") = Me.chk3StageLOI
        rs.Fields("Magnasat") = Me.chkMagnasat
        rs.Fields("DavisTube") = Me.chkDavisTube
        rs.Fields("RecWt") = Me.chkRecWt
        rs.Fields("Sizing") = False
        rs.Fields("Moisture") = False
        rs.Fields("EnteredBy") = Me.cboEnteredBy
        rs.Fields("Priority") = Me.cboPriority
        rs.Update
        'Add Standard
        stdID = stdCnt
        stdName = DFirst("standard", "tblStandards", "[standardid]= " & stdID)
        rs.AddNew
        rs.Fields(MyField) = "STD1:" & stdName
        rs.Fields("SubmissionNumber") = Me.SubNum
        rs.Fields("CustomerID") = Me.CustomerID
        rs.Fields("SamplePrep") = Me.SamplePrep
        rs.Fields("Fusion") = Me.Fusion
        rs.Fields("XRF") = Me.XRF
        rs.Fields("LOI") = True
        rs.Fields("Sizing") = False
        rs.Fields("Moisture") = False
        rs.Fields("EnteredBy") = Me.cboEnteredBy
        rs.Update
        stdCnt = stdCnt + 1
        samplecount = 0
                
        
        
      End If
    Next intCounter
    rs.Close
   DB.Close
Set rs = Nothing
Set DB = Nothing

Exit_EnterBlast_Click:
    Exit Sub

Err_EnterBlast_Click:
    MsgBox Err.Description
    Resume Exit_EnterBlast_Click

 
      
End Sub

The second button adds all of the added records in the table tblSampleSubmission to other tables using append queries. Not the best way of doing things, but, it has worked fine until now, apart from being slow.

All of this works fine, but when the form is closed, the first record in the table (which is from years ago and isn't related to what is in the form) tblSampleSubmission gets changed to whatever is in the field "Submission Number" on the form. It isn't really a huge inconvenience, just weird that it is happening now.